Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello!
I am wanting to combine values based on the unique ID of that table, so for any values that have the same ID I want them to be pushed into the same cell. To explain this better I've included 2 screenshots labelled Old and New. Would I use Power Query or something to do with pivoting or grouping columns rows based on a column value? Unsure where to start
Old (Table I have currently)
New (Table I would like to have)
Solved! Go to Solution.
You can do this with a small tweak to Group By.
Click Group By under the Home tab and group by ID taking the max over Value.
This generates code that looks like this:
= Table.Group(#"Changed Type", {"ID"}, {{"Value", each List.Max([Value]), type nullable text}})
We don't actually want List.Max though. Replace that with Text.Combine like this:
= Table.Group(#"Changed Type", {"ID"}, {{"Value", each Text.Combine([Value], ", "), type text}})
I did this and everything looked great (the top row of data was combined and concatenated). I refreshed my data and had a new row that needed to be brought in to the existing merged row b/c it had the same ID and it didn't bring it in. It now has a separate row.
The 7332 is the key. this is what it currently looks like with the first row being the result of the inital grouping:
ID | P Number | Store Number | File Number | Names of Product |
7332 | 6325 | 7 | 3921, 34749 | Teflon Foot, Compensating Foot |
7332 | 6325 | 7 | 3925 | Piping Foot |
This is what it should look like based after today's data refresh:
ID | P Number | Store Number | File Number | Names of Product |
7332 | 6325 | 7 | 3921, 34749, 3925 | Teflon Foot, Compensating Foot, Piping Foot |
Do I have to regroup every time I have a data refresh?
Hello Syndicate_Admin
Can this be done twice or more? Can you Group by ID and Date and Order Number all at once?
You can do this with a small tweak to Group By.
Click Group By under the Home tab and group by ID taking the max over Value.
This generates code that looks like this:
= Table.Group(#"Changed Type", {"ID"}, {{"Value", each List.Max([Value]), type nullable text}})
We don't actually want List.Max though. Replace that with Text.Combine like this:
= Table.Group(#"Changed Type", {"ID"}, {{"Value", each Text.Combine([Value], ", "), type text}})
Perfect! Thank you!
how to combine if the column has text and number both?
Thank you! This is exactly what I needed
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
61 | |
19 | |
17 | |
13 |