topic Re: Grouping Issue in DAX Commands and Tips

Grouping Issue

Renjith — Wed, 07 Aug 2019 09:35:54 GMT

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

Re: Grouping Issue

nandukrishnavs — Wed, 07 Aug 2019 10:44:27 GMT

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

Re: Grouping Issue

Renjith — Wed, 07 Aug 2019 10:57:55 GMT

Thanks Nandu

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

Re: Grouping Issue

Anonymous — Mon, 12 Aug 2019 09:46:35 GMT

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