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
HarveyM
Helper I
Helper I

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.

sevenhills
Super User
Super User

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

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!

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