Forum Discussion

HarveyM's avatar
HarveyM
Helper I
4 years ago
Solved

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 pu...
Need Help
Tutorial Requests
  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

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

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

  • HotChilli's avatar
    HotChilli
    4 years ago

    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's avatar
sevenhills
Super User
4 years ago

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"