Forum Discussion
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.
Thank you 🙂
Hi HarveyM ,
Right click on column "envID">select “group by”:
Then fill in as shown below:
Then add a custom column as below:
= Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column(Table.Distinct([ALL]),"Service Owner"))Finally choose extract values:
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my reply as a solution!
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.
8 Replies
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"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.
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"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”:
Then fill in as shown below:
Then add a custom column as below:
= Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column(Table.Distinct([ALL]),"Service Owner"))Finally choose extract values:
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my reply as a solution!
