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 all,
My data looks like this:
column | id |
|158| | 9978 |
|116|623| | 48724 |
|116|437|852|863| | 48344 |
|116|437|852| | 48337 |
|102| | 4744 |
4746 | |
|102| | 4747 |
|116|437|852|863| | 4755 |
4845 | |
|102| | 4846 |
4856 | |
|102| | 4882 |
I have another table where the column values are mapped to string values:
column_id | value |
102 | a |
116 | b |
158 | c |
437 | d |
623 | e |
852 | f |
863 | g |
What I am looking to achieve is:
column_values | issueid |
c | 9978 |
b, e | 48724 |
b, d, f, g | 48344 |
b,d, f | 48337 |
a | 4744 |
4746 | |
a | 4747 |
b, d, f, g | 4755 |
4845 | |
a | 4846 |
4856 | |
a | 4882 |
How can I achieve this as efficient as possible?
I can split column by delimiter and then merge one by one, but this will get out of hand quickly.
#"Split Column by Delimiter" = Table.SplitColumn(#"previous_step", "column", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"column.1", "column.2", "column.3", "column.4", "column.5", "column.6", "column.7", "column.8"}),
Anybody better ideas?
Solved! Go to Solution.
Assuming TableNames are Table1 and Table2, try this custom column
It gives me correct results with sample data
See attached file's Query Editor as well
=let Replacements=Record.FromList(Table2[value],List.Transform(Table2[column_id],each Number.ToText(_))) in Text.Combine(List.RemoveItems(List.Transform(Text.Split([column],"|"), each Record.FieldOrDefault(Replacements, _, _)),{null,""}),",")
Assuming TableNames are Table1 and Table2, try this custom column
It gives me correct results with sample data
See attached file's Query Editor as well
=let Replacements=Record.FromList(Table2[value],List.Transform(Table2[column_id],each Number.ToText(_))) in Text.Combine(List.RemoveItems(List.Transform(Text.Split([column],"|"), each Record.FieldOrDefault(Replacements, _, _)),{null,""}),",")
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 |
---|---|
113 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |