Forum Discussion
Formula with data from two not directly connected tables
Hi.
I have a bunch of tables, connected as follows.
Table FM connected to table ID
Table kpi connected to ID
Table Targets connected to FM
I've got another table kpi2 which is a copy of kpi
I want to add a column to kpi2 with the following requierments:
- if column A from table kpi2 = column B from table Targets AND column C from table kpi2 = column D from table targets then column E from Targets otherwise null
Is this possible?
HI Namoh
You could use this formula to add a custom column in power query
List.Sum(Table.SelectRows(Target, (Target) => ( Target[Column B]= [Column A]) and (Target[Column D]=[Column C]) )[Column E])Result:
https://docs.microsoft.com/en-us/powerquery-m/table-selectrows
https://docs.microsoft.com/en-us/powerquery-m/list-sum
and for your case, I also suggest you use merge to get it
then expand it:
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
here is sample pbix file, please try it.
Regards,
Lin
7 Replies
Namoh ,Can you share sample data and sample output in table format?
Basically table kpi2 has data from multiple years and multiple months for multiple units.
Table Targets has only data for some years and months.
I need to add that data as a column for the related years/months/units.
Table KPI2
Column A Column C 123 apr-20 213 apr-20 325 apr-20 623 may-20 123 may-20 213 may-20 432 may-20 Column B Column D Column E 213 apr-20 80 325 apr-20 54 100 apr-20 23 123 may-20 65 213 may-20 25 420 may-20 90 KPI2 wanted
Column A Column C New Column 123 apr-20 null 213 apr-20 80 325 apr-20 54 623 may-20 null 123 may-20 65 213 may-20 25 432 may-20 null Hope this explains what I want.
And hope this is what you wanted to see.Namoh , Try new column in KPI2 table
sumx(filter(Target, Target[Column B] = KPI2[Column A] && Target[Column D] = KPI2[Column C]),Target[Column E])
