Remove replicated values in a grouped column

[huguest]

Hello,

I have some data on various invoices with a department and client column.  Some invoices can apply to either multiple clients or multiple departments.

I use Group By a to concatenate the Client and Department values on a single row.

That works, except that I get combination of the same values for the respective fields.  

How can I get only unique values for each column? 

i.e. for Invoice 1, I want Used; A,F and for Invoice 5 I want Used,Repair; E.

Thank you.

[v-yuta-msft · Accepted solution]

@huguest ,

Try code below:

Result = Table.Group(#"Changed Type", {"Invoice"}, {{"Department Concatenated", each Text.Combine(List.Distinct([Department]), ","), type text}, {"Client Concatenated", each Text.Combine(List.Distinct([Client]), ","), type text}})

  

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mariusz · Accepted solution]

Hi @huguest

Just add List. Distinct before Text.Combine

= Table.Group(#"Changed Type", {"Invoice"}, {{"Department", each Text.Combine( List.Distinct([Department]), ", " ), type text}, {"Client", each Text.Combine( List.Distinct([Client]), ", "), type text}})

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[v-yuta-msft · Accepted solution]

@huguest ,

Try code below:

Result = Table.Group(#"Changed Type", {"Invoice"}, {{"Department Concatenated", each Text.Combine(List.Distinct([Department]), ","), type text}, {"Client Concatenated", each Text.Combine(List.Distinct([Client]), ","), type text}})

  

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.