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...
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