Forum Discussion
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.
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"
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