cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DaveCor
Helper I
Helper I

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

 

Annotation 2020-03-17 184648.png

 

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

 

Annotation 2020-03-17 1846484tgwg.png

 

Appreciate any help with this

 

1 ACCEPTED SOLUTION
artemus
Microsoft
Microsoft

 

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.

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User IV
Super User IV

@ImkeF

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




artemus
Microsoft
Microsoft

 

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.

View solution in original post

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?

Can you explain what you are looking for?

 

You can do anything in a group by that you can do in a normal table.

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:Annotation 2020-03-17 230845.png

Yes, thank you @HotChilli 

 

Much obliged for your screenshot

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors