Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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"
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"
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?
This should do it.
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"
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
Proud to be a Datanaut!
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.
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
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.