Need help with power query

[testyuiiop]

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 

[dufoq3 · Accepted 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

[testyuiiop]

Thank you!!

[testyuiiop]

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}}),

[dufoq3]

I've grouped by Rank. Inside [All] column you see:

Table.ToColumns(Table.RemoveColumns(_, {"Rank"}))

which means: 

1. delete [Rank] column - Table.RemoveColumns
2. transform table to list of lists where every row (list) represent one column - Table.ToColumns

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.

[testyuiiop]

Thanks you so much for the explantation.

[dufoq3]

👍

[dufoq3]

You're welcome. Few seconds ago I've added v2 into prev. post.

[testyuiiop]

@Ibendlin @Vijay_A_Verma  @dufoq3