Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to Solution.
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
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
Hi @ErikPettersson,
Does that make sense? If so, kindly mark my answer as a solution to close the case.
Regards,
Frank
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
Can you post an image of your data model? Will be easier to understand.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |