Forum Discussion
Helper IRemove duplicates but consolidate data from other columns
Hi folks,
First time poster and recent convert to Power BI but using Excel for many years.
I have a table that I want to later merge with another table but first I need to do some work to it. Unfortunately I've spent hours scouring forums and videos and can't find a solution so would appreciate some help.
My data looks like this (tried using the table feature on this forum but kept getting an error so had to use an image)
How can I consolidate the data so that all duplicates are removed from the ID column and the values from the ADR & ADV columns end up on the same row so the end result looks like this
Appreciate any help with this
Table.Group(PreviousStep, "ID", {{"ADR", each List.Select([ADR], each _ <> "" and _ <> null){0}?}, {"ADV", each List.Select([ADV], each _ <> "" and _ <> null){0}?}})Where PreviousStep is your previous step in the query.
6 Replies
artemusMicrosoft Employee
Table.Group(PreviousStep, "ID", {{"ADR", each List.Select([ADR], each _ <> "" and _ <> null){0}?}, {"ADV", each List.Select([ADV], each _ <> "" and _ <> null){0}?}})Where PreviousStep is your previous step in the query.
DaveCorThank you artemus!
I tried a load of different types of "Group By" but couldn't get this to work. As a matter of interest, would it actually be possible to do what I was looking for using the "Group By" window or does it require the code you've provided?
- artemus
Can you explain what you are looking for?
You can do anything in a group by that you can do in a normal table.
- Greg_Deckler
Community Champion
