cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerBI_Query
Helper II
Helper II

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.

Tree1.png

Output after nesting under column A.

Tree2.png

 

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

View solution in original post

10 REPLIES 10
jennratten
Super User
Super User

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"

jennratten_0-1658343372436.png

 

There is an error in the final output. 4th row values are same in both columns I need to skip that row.

PowerBI_Query_0-1658348585973.png

 However, I see it in the final table. Could you add an if statement to exclude it?

PowerBI_Query_1-1658348839308.png

 

This should do it.

jennratten_0-1658352395647.png

 

 

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

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
Helper II
Helper II

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

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




I agree with @BA_Pete on this.

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
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Kudoed Authors