Forum Discussion

Renjith's avatar
Renjith
Helper II
7 years ago

Grouping Issue

Hi 

I need a help

 

When I loads data on power bi 

it looks like

 

Group  ID

A          1

B           2

B          3

C          4

D          5

 

My expected out put

 

A    B     C    D

1    2      4    5

nul  3    nul   nul 

 

please help on this

3 Replies

  • nandukrishnavs's avatar
    nandukrishnavs
    Community Champion
    7 years ago

    Hi,

     

    Try below query

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVnICsozgLGMwyxnIMgGzXIAsU6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Group " = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group ", type text}, {"ID", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Group "}, {{"ID table", each _, type table [#"Group "=text, ID=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "ID List", each Table.Column([ID table],"ID")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"ID List", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"ID table"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns")
in
    #"Transposed Table"

     

    Then you can try "Split Column by Delimiter".

     

     

     

    Regards,

    Nandu Krishna

    • Renjith's avatar
      Renjith
      Helper II
      7 years ago

      Thanks Nandu

      But I am expecting value null in the column 1, Column 3 & Column 4 for row 3

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago
        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVnICsozgLGMwyxnIMgGzXGLygGxTpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Group = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"ID", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Group"}, {{"Rows", each _, type table [Group=text, ID=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Add Index", each Table.AddIndexColumn([Rows], "Index", 1, 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Rows"}),
    #"Expanded Add Index" = Table.ExpandTableColumn(#"Removed Columns", "Add Index", {"ID", "Index"}, {"ID", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Add Index",{"Index", "Group", "ID"}),
    #"Pivoted Column" = Table.Pivot(#"Reordered Columns", List.Distinct(#"Reordered Columns"[Group]), "Group", "ID"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns1"

        Here's your code... To get this always working, you'll probably have to also sort the table by the ID column upfront. I did not do it since it was already sorted but you might have to.

         

        Best

        Darek