Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
Thanks Nandu
But I am expecting value null in the column 1, Column 3 & Column 4 for row 3
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |