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.

HarveyM's avatar
HarveyM
Helper I
4 years ago

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

v-kelly-msft's avatar
v-kelly-msft
Community Support
4 years ago

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.

In dialog,it should be as below:

 

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