Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.