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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Regular Visitor

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors