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.
I'm working on hierarchial data.
My data is like below, rank represents the hierachy level.
and I want the expected outpout like below:
How I can achieve this in power query?
and I want my
Solved! Go to Solution.
Hi @testyuiiop,
v1 Group should be faster, but if you do not have many rows - you won't see the difference.
Result
v1 Group
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVlJwAjKNIEwFZyDbGMpWcAFyTMAcR0Mk5YbI6g1RNBhCdMQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Rank = _t]),
TrimmedText = Table.TransformColumns(Source,{{"Name", Text.Trim, type text}}),
GroupedRows = Table.Group(TrimmedText, {"Rank"}, {{"All", each Table.ToColumns(Table.RemoveColumns(_, {"Rank"})), type table}}),
Combined = Table.FromColumns(List.Combine(GroupedRows[All]), List.Transform(GroupedRows[Rank], Text.From))
in
Combined
v2 Pivot
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVlJwAjKNIEwFZyDbGMpWcAFyTMAcR0Mk5YbI6g1RNBhCdMQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Rank = _t]),
TrimmedText = Table.TransformColumns(Source,{{"Name", Text.Trim, type text}}),
PivotedColumn = Table.Pivot(Table.TransformColumnTypes(TrimmedText, {{"Rank", type text}}, "sk-SK"), List.Distinct(Table.TransformColumnTypes(TrimmedText, {{"Rank", type text}}, "sk-SK")[Rank]), "Rank", "Name", each _),
Ad_FinalTable = Table.AddColumn(PivotedColumn, "FinalTable", each Table.FromColumns(Record.ToList(_), Table.ColumnNames(PivotedColumn)), type table),
FinalTable = Ad_FinalTable{0}[FinalTable]
in
FinalTable
Hi @testyuiiop,
v1 Group should be faster, but if you do not have many rows - you won't see the difference.
Result
v1 Group
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVlJwAjKNIEwFZyDbGMpWcAFyTMAcR0Mk5YbI6g1RNBhCdMQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Rank = _t]),
TrimmedText = Table.TransformColumns(Source,{{"Name", Text.Trim, type text}}),
GroupedRows = Table.Group(TrimmedText, {"Rank"}, {{"All", each Table.ToColumns(Table.RemoveColumns(_, {"Rank"})), type table}}),
Combined = Table.FromColumns(List.Combine(GroupedRows[All]), List.Transform(GroupedRows[Rank], Text.From))
in
Combined
v2 Pivot
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVlJwAjKNIEwFZyDbGMpWcAFyTMAcR0Mk5YbI6g1RNBhCdMQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Rank = _t]),
TrimmedText = Table.TransformColumns(Source,{{"Name", Text.Trim, type text}}),
PivotedColumn = Table.Pivot(Table.TransformColumnTypes(TrimmedText, {{"Rank", type text}}, "sk-SK"), List.Distinct(Table.TransformColumnTypes(TrimmedText, {{"Rank", type text}}, "sk-SK")[Rank]), "Rank", "Name", each _),
Ad_FinalTable = Table.AddColumn(PivotedColumn, "FinalTable", each Table.FromColumns(Record.ToList(_), Table.ColumnNames(PivotedColumn)), type table),
FinalTable = Ad_FinalTable{0}[FinalTable]
in
FinalTable
Thank you!!
Could you please help me in understanding how you have grouped the rows?
GroupedRows = Table.Group(TrimmedText, {"Rank"}, {{"All", each Table.ToColumns(Table.RemoveColumns(_, {"Rank"})), type table}}),
I've grouped by Rank. Inside [All] column you see:
Table.ToColumns(Table.RemoveColumns(_, {"Rank"}))
which means:
At last step Combined I'm combining all list from GropedRows[All] Column to this where every list represents one column.
To create table from columns I'm using Table.FromColumns function with 2nd parameter column names --> List.Transform(GroupedRows[Rank], Text.From) - but just for sure I'm transforming column names --> GropedRows[Rank] column to text (because you can have it at that step as number and it would finished as an error).
My english is not that good as my power query - sorry for that in advance.
Thanks you so much for the explantation.
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.