Nest Column Values Under Main Column

[PowerBI_Query]

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.

[jennratten]

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]

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?

[jennratten]

This should do it.

[PowerBI_Query]

Could you paste the modified code. I am unable to see it from the screenshot. 

[jennratten · Accepted solution]

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"

[PowerBI_Query]

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]

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

[jennratten]

I agree with @BA_Pete on this.

[PowerBI_Query]

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.  

[BA_Pete]

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