cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
o59393
Post Prodigy
Post Prodigy

How to combine only unique values for different columns

Hi 

 

I have gone from 

 

o59393_0-1628705706080.png

 

 

to

 

o59393_1-1628705717335.png

 

 

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.

 

1 ACCEPTED SOLUTION
Vera_33
Super User
Super User

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"

View solution in original post

2 REPLIES 2
o59393
Post Prodigy
Post Prodigy

Thanks 🙂 @Vera_33 

Vera_33
Super User
Super User

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"

View solution in original post

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors