Forum Discussion
Nest Column Values Under Main Column
- 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"
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_Pete4 years agoSuper User
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_Query4 years agoHelper II
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.
- jennratten4 years agoSuper User
I agree with BA_Pete on this.