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 trying to join two datasets, one of which has a unique identifier and second of which has a column that contains that unique identifier (in a string along with other information).
This is what it looks like:
Dataset 1
Unique ID Name Info
Z01 --- ---
Z03 --- ---
Z04 --- ---
Z05 --- ---
Dataset 2
Unique ID Name Info
Z01+Z02 --- ---
Z01+Z04 --- ---
Z04+Z05 --- ---
Z01+Z04+Z05 --- ---
Is there any way of joining the two together? I considered splitting the column in dataset 2 but I'm not sure if that'll work - especially if I have a large number of unique IDs.
Thanks for your help
Solved! Go to Solution.
You may take a look at the post below.
http://community.powerbi.com/t5/Desktop/joinAlgorithm-and-keyEqualityComparers/td-p/110108
Change it as follows.
each Text.Contains([Unique ID], Earlier[Unique ID],
You may take a look at the post below.
http://community.powerbi.com/t5/Desktop/joinAlgorithm-and-keyEqualityComparers/td-p/110108
Thank you,
I'm not very good with the m language - are you able to help me out further? How can I integrate this into my report? Through 'Advanced Editor' within the 'Query Editor'?
Also assuming that my table 1 was "Dog", table 2 was "Cat", unique identifier in both of the tables was "Unique ID", is this what it'd look like?
let Table1 = Table.Buffer(Dog), Table2 = Table.Buffer(Cat), RelativeMerge = Table.AddColumn(Table1, "RelativeJoin", (Earlier) => Table.SelectRows(Table2, each Text.Contains(Cat[Unique ID], Comparer.OrdinalIgnoreCase))), #"Expanded RelativeJoin" = Table.ExpandTableColumn(RelativeMerge, "RelativeJoin") in #"Expanded RelativeJoin"
Again, thanks for your help.
Change it as follows.
each Text.Contains([Unique ID], Earlier[Unique ID],
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |