How to group by to merge texts without duplicates

[o59393]

Hi all

I am trying on Power Query to go from this:

To this:

The only problem I have is that as seen above on column count, is duplicating me the values. How can I have distinct ones only?

Here is the M code:

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Id"}, {{"Count", each Text.Combine ([Sub Region],", "), type text}})
in
    #"Grouped Rows"

Thanks!

[CNENFRNL · Accepted solution]

#"Grouped Rows" = Table.Group(#"Changed Type", {"Id"}, {{"Count", each Text.Combine(List.Distinct([Sub Region]),", "), type text}})

[Syndicate_Admin]

@CNENFRNL  question.

Is it possible to have more than one column?

In our solution we just have Sub region:

Can I have also region and country?

Could I try for example in the code:

#"Grouped Rows" = Table.Group(#"Changed Type", {"Id"}, {{"Count", each Text.Combine(List.Distinct([Sub Region]),List.Distinct([Region],List.Distinct([Country])),", "), type text}}),

?

Thanks!

[o59393]

@CNENFRNL  question.

Is it possible to have more than one column?

In our solution we just have Sub region:

Can I have also region and country?

Could I try for example in the code:

#"Grouped Rows" = Table.Group(#"Changed Type", {"Id"}, {{"Count", each Text.Combine(List.Distinct([Sub Region]),List.Distinct([Region],List.Distinct([Country])),", "), type text}}),

?

Thanks!

[CNENFRNL · Accepted solution]

#"Grouped Rows" = Table.Group(#"Changed Type", {"Id"}, {{"SR", each Text.Combine(List.Distinct([Sub Region]),", ")}, {"R", each Text.Combine(List.Distinct([Region]),", ")}, {"C", each Text.Combine(List.Distinct([Country]),", ")}})

[o59393]

Hi @CNENFRNL 

I got the following error:

Could it be because of the renamed columns below?

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Id"}, {{"SR", each Text.Combine(List.Distinct([Sub Region]),", ")}, {"R", each Text.Combine(List.Distinct([Region]),", ")}, {"C", each Text.Combine(List.Distinct([Country]),", ")}}),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Count", "Sub Region"}})
in
    #"Renamed Columns"

Thanks!

[o59393]

It worked!! @CNENFRNL 

I changed it to:

#"Grouped Rows" = Table.Group(#"Changed Type", {"Id"}, {{"SR", each Text.Combine(List.Distinct([Sub Region]),", ")}, {"R", each Text.Combine(List.Distinct([Region]),", ")}, {"C", each Text.Combine(List.Distinct([Country]),", ")}})
in
#"Grouped Rows"

Thanks a million!!

[o59393]

Thanks @CNENFRNL !

[CNENFRNL · Accepted solution]

#"Grouped Rows" = Table.Group(#"Changed Type", {"Id"}, {{"Count", each Text.Combine(List.Distinct([Sub Region]),", "), type text}})