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 ,
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
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.