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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

@ImkeF

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
artemus
Employee
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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors