Forum Discussion
Supertricky modelling..
Hi!
I'm encountered a bit of trick problem and would be open to suggestions how to work around it. I have 2 tables (see below) that I need to match. However, the only common denominater contains multiples on both sides. However, in the first table I have an unique ID that I would like to add to the second table (I can do this exercise in Excel of course but I would like to keep it clean in the queary). See below for what I'm looking for:
Hi ErikPettersson,
Based on my test, we can merget the two tables in power query based on the column ID B.
Here is the M code for your reference.
let Source = Table.NestedJoin(Table1,{"ID B"},Table2,{"ID B"},"Table2",JoinKind.LeftOuter), #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"ID A"}, {"Table2.ID A"}) in #"Expanded Table2"For more details, please check the pbix as attached.
https://www.dropbox.com/s/f0d3fd6xaafy2n6/merge.pbix?dl=0
Regards,
Frank
5 Replies
You could get a unique list of values from the column you want by creating a new query.
Heres a video
https://www.youtube.com/watch?v=vAvQ8pCnWDk
Hope this helps :)
Well, I need to create the C-column because I need it when connecting other tables
v-frfei-msftCommunity Support
Hi ErikPettersson,
Based on my test, we can merget the two tables in power query based on the column ID B.
Here is the M code for your reference.
let Source = Table.NestedJoin(Table1,{"ID B"},Table2,{"ID B"},"Table2",JoinKind.LeftOuter), #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"ID A"}, {"Table2.ID A"}) in #"Expanded Table2"For more details, please check the pbix as attached.
https://www.dropbox.com/s/f0d3fd6xaafy2n6/merge.pbix?dl=0
Regards,
Frank
- v-frfei-msft
Hi ErikPettersson,
Does that make sense? If so, kindly mark my answer as a solution to close the case.
Regards,
Frank
