Search code examples
powerbipowerquerypowerbi-desktopdata-cleaningm

How to groupby two columns and list unique values of two other columns?

I have this csv:

car     color  code city
ferrari pink   01   LA
ferrari red    02   LA
lambo   yellow 09  Texas
lambo   orange 08  Texas

I would like to have this kind of groupby in powerbi, groupby car and city, list unique values of code and color and count uniques rows. Expected oupput:

car     color            code      city  count
ferrari [pink, red]      [01,02]   LA    2
lambo   [yellow, orange] [09,08]   Texas 2

Maybe CONCATENEX ? But really don't know it

Solution

  • Here you go.

    enter image description here

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkstKkosylTSUSrIzMsGUoZA7OOoFKuDLFeUmgIkjRBSOYm5SflAbmVqTk5+OZBhCcQhqRWJxSjS+UWJeempQIYFQjoWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [car = _t, color = _t, code = _t, city = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"car", type text}, {"color", type text}, {"code", type text}, {"city", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"car", "city"}, {{"count", each Table.RowCount(_), Int64.Type}, {"color", each "["& Text.Combine( _[color],",")&"]", type text },{"code", each "["& Text.Combine( _[code],",")&"]", type text }}),
    #"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"car", "color", "code", "city", "count"})
in
    #"Reordered Columns"