cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HarveyM
Frequent Visitor

Remove Duplicates, appending following columns

Hi,

 

I have a column called envID, with duplicate values, one envID can have multiple service owners. I want to remove the duplicate envID's but not lose the service owners, as it will randomly pull one through. I want to create a custom column when removing duplicates to append the services owners together in one column associated to the envID. Below is an example of the data, and the desired outcome.

custom column.png

 

Thank you 🙂 

2 ACCEPTED SOLUTIONS

Hi  @HarveyM ,

 

Right click on column "envID">select “group by”:

vkellymsft_0-1632128913522.png

Then  fill in as shown below:

vkellymsft_1-1632128944999.png

 

Then add a custom column as below:

 

= Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column(Table.Distinct([ALL]),"Service Owner"))

 

Finally choose extract values:

vkellymsft_3-1632129368335.png

 

vkellymsft_2-1632129348815.png

And you will see:

vkellymsft_4-1632129381979.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

HotChilli
Super User
Super User

Is it possible that you are putting this entire line in the Custom Column formula box ?

Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column(Table.Distinct([ALL]),"Service Owner"))

which you don't want to do.

 

You just want to put this in the formula box of the Custom Column:

Table.Column(Table.Distinct([ALL]),"Service Owner")

 

The confusion could be caused by Kelly's providing the line as it is in the Formula Bar (not as it is in the Custom Column dialog).  Let us know.

View solution in original post

8 REPLIES 8
HotChilli
Super User
Super User

Is it possible that you are putting this entire line in the Custom Column formula box ?

Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column(Table.Distinct([ALL]),"Service Owner"))

which you don't want to do.

 

You just want to put this in the formula box of the Custom Column:

Table.Column(Table.Distinct([ALL]),"Service Owner")

 

The confusion could be caused by Kelly's providing the line as it is in the Formula Bar (not as it is in the Custom Column dialog).  Let us know.

View solution in original post

sevenhills
Impactful Individual
Impactful Individual

Another approach, using @Greg_Deckler code, you can change from "Grouped Rows" as below 

... i.e., after this line  #"Removed Duplicates" = Table.Distinct ...
change to 


    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"envID"}, {{"Name - CSV", each Text.Combine([Service Owner], ","), type text}})
in
    #"Grouped Rows"

 

 

Greg_Deckler
Super User
Super User

@HarveyM Try:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRX0lHySCwqS61UitUhku+UmgfmGBsBOV75xRm4eD6JRUWVGNxYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [envID = _t, #"Service Owner" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"envID", Int64.Type}, {"Service Owner", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type"),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"envID"}, {{"Data", each _, type table [envID=nullable number, Service Owner=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Data],"Service Owner")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Data"})
in
    #"Removed Columns"

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Hi @Greg_Deckler 

 

This worked perfectly when I ran it for the example data. However I am having troubles when trying to replace the example data with my Power BI columns, would you be able to post an eddited version where you indicate what should go where please.

 

Thank you 🙂

Hi  @HarveyM ,

 

Right click on column "envID">select “group by”:

vkellymsft_0-1632128913522.png

Then  fill in as shown below:

vkellymsft_1-1632128944999.png

 

Then add a custom column as below:

 

= Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column(Table.Distinct([ALL]),"Service Owner"))

 

Finally choose extract values:

vkellymsft_3-1632129368335.png

 

vkellymsft_2-1632129348815.png

And you will see:

vkellymsft_4-1632129381979.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

Also worth mentioning @v-kelly-msft that when I download your pbix file, remove the step where you added the custom column and then re-add it with the exact same formula, I get a different result, instead of a list I get a table created.

Hi @v-kelly-msft 

 

I can't get this to work, as every time I create the custom column it creates it as a table, instead of a list meaning I can't extract values.

I have tried a few different methods of getting it to create a list instead of a table but have had no luck. Do you have any pointers on this ?

 

Thank you

Hi  @HarveyM ,

 

What @HotChilli  considered is correct,the expression I showed you is a new query in the formular bar,not in the Custom Column dialog.

vkellymsft_0-1632191814221.png

In dialog,it should be as below:

vkellymsft_1-1632191847889.png

 

=Table.Column(Table.Distinct([ALL]),"Service Owner")

 

 

Once you will get a distinct list from each ID using the above expression,then expand it,you will get the final result.

 

Best Regards,
Kelly

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors