Formula with data from two not directly connected tables

[Namoh]

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?

[amitchandak]

@Namoh ,Can you share sample data and sample output in table format?

[Namoh]

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.

[amitchandak]

@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])

[Namoh]

Thanks for your reply, but I don't get it to work.

I think this is a DAX query, while I'm in PQE.

What would be the PQE formula?

[v-lili6-msft · Accepted solution]

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

[Namoh]

Merge, of course!!

Why do I always want to make it difficult with formulas..??

This worked perfectly.

Thanks.

[Namoh]

Is what I want not possible in PQE or am I doing something wrong?

If more information is needed, please let me know.