Forum Discussion

PowerBI_Query's avatar
PowerBI_Query
Helper II
4 years ago
Solved

Nest Column Values Under Main Column

My original data is shown as below. I want to nest respective B and C columns under A column. If A and B values are same I skip those values.

Output after nesting under column A.

 

Interesting Links
Need Help
Show and Tell
Tips and Tricks
  • jennratten's avatar
    jennratten
    4 years ago

    Sorry about that.  Here you are.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc6xDoQwCAbgd2HW5NQCddWol5tJTGM63wvc4ttL42A5FsIPXwLHAQGJOZQKDayCGFCbrh9anbWEhJCbPzYXRpaRZ9OsjC3jiknaFt2aUNHo6frTFA2LNQuT7IV9ke4XHjeO3plQXTY0ieg26d3TKH559faq8+qTnOq9WnanBsj5Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Main = _t, Sub = _t, #"Sub-Code" = _t]),
    #"Replaced Value" = Table.SelectRows(Source,each [Main]<>[Sub]),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Main", "Main/Sub"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Main/Sub"}, {{"Level", each 1, Int64.Type}, {"Data", each Table.RenameColumns ( Table.RemoveColumns ( Table.AddColumn ( _, "Level", each 2, Int64.Type), {"Main/Sub"} ), {{"Sub", "Main/Sub"}} ), type table }}),
    Custom1 = Table.AddColumn ( #"Grouped Rows", "NewTable", each   Table.Combine ( { Table.FromRows({Record.FieldValues(Record.SelectFields (_, {"Level", "Main/Sub"}))}, {"Level", "Main/Sub"}), [Data] } ) ),
    #"Removed Other Columns" = Table.SelectColumns(Custom1,{"NewTable"}),
    #"Expanded NewTable" = Table.ExpandTableColumn(#"Removed Other Columns", "NewTable", {"Level", "Main/Sub", "Sub-Code"}, {"Level", "Main/Sub", "Sub-Code"})
in
    #"Expanded NewTable"

10 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    Hi PowerBI_Query ,

     

    Sounds like you want to just put the lot into a matrix visual.

    You'll need to create a measure to populate the matrix, something simple like COUNTROWS(yourTable) will do, but you should be able to adjust the matrix formatting to create this nested hierarchy.

     

    Pete

  • PowerBI_Query's avatar
    PowerBI_Query
    Helper II
    4 years ago

    I am using Powery Query for Excel 365. So no matrix visual. I have pasted the columns below you can copy them into a new workbook. Is it possible to zip respective B and C columns and unzip below the respectives values in A.

     

    Main
    4567744567
    4567744567
    4567744567
    456774TYGE
    456774TYGE
    4567744BTW
    4567744BTW
    4567744YTT
    4567744YTT
    4567744YTT
    4567744YTT

     

    Sub
    FT5545
    CT5546
    BC5547
    456774TYGE
    Ft5678
    Fg56567
    4567744BTW
    Y678y
    H678y
    JY78y
    EW78y

     

    Sub-Code
    123-456-6565
    123-456-6566
    123-456-6567
    123-456-6568
    123-456-6588
    123-456-6599
    123-456-6569
    123-456-6570
    123-456-6571
    123-456-6572
    123-456-6573

    • BA_Pete's avatar
      BA_Pete
      Super User
      4 years ago

      Hi PowerBI_Query ,

       

      You can use a pivot table instead, that's all a matrix visual is.

      jennratten has provided a very good example of doing what you want inside Power Query, but I'd say that you're not really using PQ as designed in this way and, depending on your use case, you'll find it very difficult to manage/update as and when required.

      Just my tuppence.

       

      Pete

      • PowerBI_Query's avatar
        PowerBI_Query
        Helper II
        4 years ago

        I need to upload the excel file to a portal as input file. Moreover, Pivot table is not able to nest the columns into one main column. 

        I see no challage to updated it in future.  

  • jennratten's avatar
    jennratten
    Super User
    4 years ago

    Hello - here is how this can be done with Power Query.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc6xDoQwCAbgd2HW5NQCddWol5tJTGM63wvc4ttL42A5FsIPXwLHAQGJOZQKDayCGFCbrh9anbWEhJCbPzYXRpaRZ9OsjC3jiknaFt2aUNHo6frTFA2LNQuT7IV9ke4XHjeO3plQXTY0ieg26d3TKH559faq8+qTnOq9WnanBsj5Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Main = _t, Sub = _t, #"Sub-Code" = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Main", "Main/Sub"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Main/Sub"}, {{"Level", each 1, Int64.Type}, {"Data", each Table.RenameColumns ( Table.RemoveColumns ( Table.AddColumn ( _, "Level", each 2, Int64.Type), {"Main/Sub"} ), {{"Sub", "Main/Sub"}} ), type table }}),
    Custom1 = Table.AddColumn ( #"Grouped Rows", "NewTable", each   Table.Combine ( { Table.FromRows({Record.FieldValues(Record.SelectFields (_, {"Level", "Main/Sub"}))}, {"Level", "Main/Sub"}), [Data] } ) ),
    #"Removed Other Columns" = Table.SelectColumns(Custom1,{"NewTable"}),
    #"Expanded NewTable" = Table.ExpandTableColumn(#"Removed Other Columns", "NewTable", {"Level", "Main/Sub", "Sub-Code"}, {"Level", "Main/Sub", "Sub-Code"})
in
    #"Expanded NewTable"

     

    • PowerBI_Query's avatar
      PowerBI_Query
      Helper II
      4 years ago

      There is an error in the final output. 4th row values are same in both columns I need to skip that row.

       However, I see it in the final table. Could you add an if statement to exclude it?