Forum Discussion

o59393's avatar
o59393
Post Prodigy
5 years ago
Solved

How to combine only unique values for different columns

Hi 

 

I have gone from 

 

 

 

to

 

 

 

Would it be possible in the second image to include the Region and Country in separated columns?

 

I try the code:

 

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

 

But I get an error.

 

Is it possible to have this in M code?

 

Thanks.

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi o59393 

     

    I think you probably want to do it

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi o59393 

     

    I think you probably want to do it

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