Forum Discussion

testyuiiop's avatar
testyuiiop
Helper I
2 years ago
Solved

Need help with power query

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 q...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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

     

dufoq3's avatar
dufoq3
Community Champion
2 years ago

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's avatar
    testyuiiop
    Helper I
    2 years ago

    Thank you!!

     

    • dufoq3's avatar
      dufoq3
      Community Champion
      2 years ago

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

    • testyuiiop's avatar
      testyuiiop
      Helper I
      2 years ago

      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's avatar
        dufoq3
        Community Champion
        2 years ago

        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.