Combine Rows with same ID Column.

[StuartSmith]

I have a table (sample data below) and need to combine all rows with the same ID. All rows with the same ID are identical, apart from the "Store Room Security" column that may contain 1 value, multiple values or empty.

So as an example, I would like all rows to remain intact, unless there are duplicate ID vlaues and then combine the "Secure Store Security" column so it looks like ID 61 "CCTV, Pin Code, Access Code"

Thank in advance.

[StuartSmith]

Thanks everyone 🙂 all sorted.

[miTutorials]

I have made a tutorial incase you find this helpful.

Combine / Merge Rows based on ID column using power Query in Power BI | MiTutorials 

[Dhairya]

Hey @StuartSmith 

Please follow the below steps to achieve the required output

Step 1: Select columns by which you want to "group by" and click on "Group By" button in "transform" pane.

Step2: 

Step 3: You will get the following output with the error

Step 4: Now update the above-highlighted query with the following query

= Table.Group(#"Changed Type", 
       {"Segment", "Country"}, 
       {{"Products", each Text.Combine(List.Distinct([Product]),", "), 
       type nullable text}})

Here I have used two functions

Text.Combine(): for combining rows with delimiter (in your case delimiter is ",")

List.Distinct(): for removing duplicate values(you can ignore if you want duplicate values)

You will the expected output below:
 

If this helps you, please mark my solution as accepted so that others can find it quickly while facing a similar issue. Thank you!

[Nithinr · Accepted solution]

Group by the ID, Region, text and combine the Store room security as below M query. refer below link for better understanding . Solved: Combining Rows based on the ID of that table - Microsoft Fabric Community

= Table.Group(#"Specify the above step name", {"ID","Region","Country"}, {{"Store Room Security", each Text.Combine([Store Room Security], ", "), type nullable text}})

[StuartSmith]

On first inspection, that looks good, so thanks. Is there a simply way to group by all the columns in the table or would I need to manually add all the columns here...

= Table.Group(#"Reordered Columns", {"ID", "Region", "Country", Remaining Columns...}