Grouping Issue

[Renjith]

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

[nandukrishnavs]

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]

Thanks Nandu

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

[Anonymous]

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