Forum Discussion

HarveyM's avatar
HarveyM
Icon for Helper I rankHelper I
5 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
Icon for Community Support rankCommunity Support
5 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!

HarveyM's avatar
HarveyM
Icon for Helper I rankHelper I
5 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
    Icon for Community Support rankCommunity Support
    5 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!