Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Adam01
Advocate I
Advocate I

Combining Rows based on the ID of that table

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.PNG Old (Table I have currently)

New.PNG New (Table I would like to have)

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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.

AlexisOlson_0-1646411950875.png

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}})

AlexisOlson_1-1646412111397.png

View solution in original post

6 REPLIES 6
lynnzrae
Helper I
Helper I

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:

 

 
IDP NumberStore NumberFile NumberNames of Product
7332632573921, 34749Teflon Foot, Compensating Foot
7332632573925Piping Foot

 

This is what it should look like based after today's data refresh:

 

IDP NumberStore NumberFile NumberNames of Product
7332632573921, 34749, 3925Teflon Foot, Compensating Foot, Piping Foot

 

Do I have to regroup every time I have a data refresh?

 

 

Syndicate_Admin
Administrator
Administrator

Hello Syndicate_Admin

Can this be done twice or more? Can you Group by ID and Date and Order Number all at once?

AlexisOlson
Super User
Super User

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.

AlexisOlson_0-1646411950875.png

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}})

AlexisOlson_1-1646412111397.png

Perfect! Thank you!

how to combine if the column has text and number both?

Thank you! This is exactly what I needed

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors