Remove duplicates but consolidate data from other columns

[DaveCor]

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

[Greg_Deckler]

@ImkeF

[artemus · Accepted solution]

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.

[DaveCor]

Thank 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.

[HotChilli]

I think the poster just means can they use the 'Group By' dialog instead of typing in a line of code.

If so, this should do it:

[DaveCor]

Yes, thank you @HotChilli 

Much obliged for your screenshot