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"
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