SOLVED

Remove duplicate words in a cell after power query grouping and aggregating from a table

Copper Contributor

Hello,

 

I am using a power query to group and aggregate columns of a table. After the power query, the aggregated columns contain duplicate values. For example one of the aggregated columns is ISO country code, so after aggregating the cells contain "UK, UK, FR, FR, FR, DE, DE" etc. Is there a step I can add to the power query to remove such duplicates?

 

Thanks!

6 Replies

@102938 

You can try to remove duplicates before grouping.

duplicates power query.JPG

@OliverScheurich Thanks for your reply! Unfortunately I don't think this will solve it. Below is a screenshot of the grouping - if two or more Channels feature the same country, then removing duplicates prior to grouping won't work...

 

1 - Copy (2).JPG

best response confirmed by 102938 (Copper Contributor)
Solution

@102938 

As variant you may aggregate ISO as sum, that will return an error, in formula bar will be something like this

image.png

Change in formula bar on that

image.png

Other words instead of List.Sum first List.Distinct to remove duplicates, on the top Text.Combine to have one string.

With that doesn't matter how many aggregations you have, you correct only specific one.

@Sergei Baklan this worked perfectly, thanks!

 

As a follow up question, is it possible to add a step to the power query to re-order the ISO codes alphabetically in each cell?

@102938 

list sort.JPG

With @Sergei Baklan solution you can try to add List.Sort.

1 best response

Accepted Solutions
best response confirmed by 102938 (Copper Contributor)
Solution

@102938 

As variant you may aggregate ISO as sum, that will return an error, in formula bar will be something like this

image.png

Change in formula bar on that

image.png

Other words instead of List.Sum first List.Distinct to remove duplicates, on the top Text.Combine to have one string.

With that doesn't matter how many aggregations you have, you correct only specific one.

View solution in original post