Search code examples
powerbipowerbi-desktop

Need a comma separated group by in powerbi

I have a table below.

ID  Value
12  foo
12  bar
34  foo
45  bar
56  foo
56  bar

I wish to have the below

ID  Value
12  foo,bar
34  foo
45  bar
56  foo,bar

I know how to do it in Python but powerbi is making it difficult for me. Just to add I am using power bi version 2.65.5313.1421 64 bit

Solution

  • You can achieve this in Power Query by couple of steps stated as below-

    Step-1: Apply Group on Column ID as shown below-

    enter image description here

    Step-2: Now add a custom column as shown below-

    enter image description here

    Step-3: Now extract values as below-

    enter image description here

    Step-4: Select Comma as below after selecting the Extract value option-

    enter image description here

    Step-5: Now you have your desired output (You can remove column1 if required)

    enter image description here

    Here is M query for you for your further reference

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRS0lFKy89XitWBcpISi8AcYxMkGRNTJBlTMyQZMAcsEwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Value", type text}}),

    //--Steps started from here.
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Column1", each _, type table [ID=nullable number, Value=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Column1][Value]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"