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.
Hi, I have following data in an unusual nested matrix shape. Can anyone share methods to flatten this data to a normal tabular shape?
As-is:
Product | CY2020-Q1 | CY2020-Q2 | CY2020-Q3 | CY2020-Q4 | CY2021-Q1 | |||||
Bikes | Sales | 1000 | Sales | 1000 | Sales | 1000 | Sales | 1000 | Sales | 1000 |
Bikes | Book | 3000 | Book | 3000 | Book | 3000 | Book | 3000 | Book | 3000 |
Bikes | Units | 2000 | Units | 2000 | Units | 2000 | Units | 2000 | Units | 2000 |
Cars | Sales | 1000 | Sales | 1000 | Sales | 1000 | Sales | 1000 | Sales | 1000 |
Cars | Book | 3000 | Book | 3000 | Book | 3000 | Book | 3000 | Book | 3000 |
Cars | Units | 2000 | Units | 2000 | Units | 2000 | Units | 2000 | Units | 2000 |
Boats | Sales | 1000 | Sales | 1000 | Sales | 1000 | Sales | 1000 | Sales | 1000 |
Boats | Book | 3000 | Book | 3000 | Book | 3000 | Book | 3000 | Book | 3000 |
Boats | Units | 2000 | Units | 2000 | Units | 2000 | Units | 2000 | Units | 2000 |
To-be:
Product | Time period | Sales | Book | Units |
Bikes | CY2020-Q1 | 1000 | 3000 | 2000 |
Bikes | CY2020-Q2 | 1000 | 3000 | 2000 |
Bikes | CY2020-Q3 | 1000 | 3000 | 2000 |
Bikes | CY2020-Q4 | 1000 | 3000 | 2000 |
Bikes | CY2021-Q1 | 1000 | 3000 | 2000 |
Cars | CY2020-Q1 | 1000 | 3000 | 2000 |
Cars | CY2020-Q2 | 1000 | 3000 | 2000 |
Cars | CY2020-Q3 | 1000 | 3000 | 2000 |
Cars | CY2020-Q4 | 1000 | 3000 | 2000 |
Cars | CY2021-Q1 | 1000 | 3000 | 2000 |
Boats | CY2020-Q1 | 1000 | 3000 | 2000 |
Boats | CY2020-Q2 | 1000 | 3000 | 2000 |
Boats | CY2020-Q3 | 1000 | 3000 | 2000 |
Boats | CY2020-Q4 | 1000 | 3000 | 2000 |
Boats | CY2021-Q1 | 1000 | 3000 | 2000 |
Solved! Go to Solution.
Here's another way to do it in the query editor. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlHSUXKONDIwMtANNASyFZD4Rmh8YzS+CTLfEKY/VidaySkzO7UYyAtOzAHThgYGBhRxkQ11ys/PBlLGEGXk8ZDNC83LLAHRRhBl5HNBhjonFlHd41AzqeVvqHFU9bZTfmIJ9SMcaijVIhxqHvV8HgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", Int64.Type}, {"Column4", type text}, {"Column5", Int64.Type}, {"Column6", type text}, {"Column7", Int64.Type}, {"Column8", type text}, {"Column9", Int64.Type}, {"Column10", type text}, {"Column11", Int64.Type}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Product", type text}, {"CY2020-Q1", type text}, {"Column3", Int64.Type}, {"CY2020-Q2", type text}, {"Column5", Int64.Type}, {"CY2020-Q3", type text}, {"Column7", Int64.Type}, {"CY2020-Q4", type text}, {"Column9", Int64.Type}, {"CY2021-Q1", type text}, {"Column11", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index", "Product"}, "Attribute", "Value"),
#"Lowercased Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Attribute", each if Text.Start(_,6) = "Column" then null else _, type text}}),
#"Filled Down" = Table.FillDown(#"Lowercased Text",{"Attribute"}),
#"Added Index1" = Table.AddIndexColumn(#"Filled Down", "Index2", 1, 1, Int64.Type),
#"Divided Column" = Table.TransformColumns(#"Added Index1", {{"Index2", each Number.Mod(_,2), type number}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Divided Column", {{"Index2", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Divided Column", {{"Index2", type text}}, "en-US")[Index2]), "Index2", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
#"Pivoted Column1" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"1"]), "1", "0"),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column1",{{"Attribute", "Time Period"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Sales", Int64.Type}, {"Book", Int64.Type}, {"Units", Int64.Type}})
in
#"Changed Type2"
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you both @mahoneypat @wdx223_Daniel for your input. I am able to use Pat's solution now. While Daniel your solution can probably work too, it's much easier for me to follow Pat's workflow and troubleshoot/maintain. By the way Pat, I had to change the step #"Lowercased Text" to make it work, and removed the earlier Type Setting steps to avoid static column names. Pls see my new code below:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlHSUXKONDIwMtANNASyFZD4Rmh8YzS+CTLfEKY/VidaySkzO7UYyAtOzAHThgYGBhRxkQ11ys/PBlLGEGXk8ZDNC83LLAHRRhBl5HNBhjonFlHd41AzqeVvqHFU9bZTfmIJ9SMcaijVIhxqHvV8HgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "Index", 0, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index", "Product"}, "Attribute", "Value"),
#"Lowercased Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Attribute", each if Text.Start(_,1) = " " then null else _, type text}}),
#"Filled Down" = Table.FillDown(#"Lowercased Text",{"Attribute"}),
#"Added Index1" = Table.AddIndexColumn(#"Filled Down", "Index2", 1, 1, Int64.Type),
#"Divided Column" = Table.TransformColumns(#"Added Index1", {{"Index2", each Number.Mod(_,2), type number}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Divided Column", {{"Index2", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Divided Column", {{"Index2", type text}}, "en-US")[Index2]), "Index2", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
#"Pivoted Column1" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"1"]), "1", "0"),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column1",{{"Attribute", "Time Period"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Sales", Int64.Type}, {"Book", Int64.Type}, {"Units", Int64.Type}})
in
#"Changed Type2"
Here's another way to do it in the query editor. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlHSUXKONDIwMtANNASyFZD4Rmh8YzS+CTLfEKY/VidaySkzO7UYyAtOzAHThgYGBhRxkQ11ys/PBlLGEGXk8ZDNC83LLAHRRhBl5HNBhjonFlHd41AzqeVvqHFU9bZTfmIJ9SMcaijVIhxqHvV8HgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", Int64.Type}, {"Column4", type text}, {"Column5", Int64.Type}, {"Column6", type text}, {"Column7", Int64.Type}, {"Column8", type text}, {"Column9", Int64.Type}, {"Column10", type text}, {"Column11", Int64.Type}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Product", type text}, {"CY2020-Q1", type text}, {"Column3", Int64.Type}, {"CY2020-Q2", type text}, {"Column5", Int64.Type}, {"CY2020-Q3", type text}, {"Column7", Int64.Type}, {"CY2020-Q4", type text}, {"Column9", Int64.Type}, {"CY2021-Q1", type text}, {"Column11", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index", "Product"}, "Attribute", "Value"),
#"Lowercased Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Attribute", each if Text.Start(_,6) = "Column" then null else _, type text}}),
#"Filled Down" = Table.FillDown(#"Lowercased Text",{"Attribute"}),
#"Added Index1" = Table.AddIndexColumn(#"Filled Down", "Index2", 1, 1, Int64.Type),
#"Divided Column" = Table.TransformColumns(#"Added Index1", {{"Index2", each Number.Mod(_,2), type number}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Divided Column", {{"Index2", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Divided Column", {{"Index2", type text}}, "en-US")[Index2]), "Index2", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
#"Pivoted Column1" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"1"]), "1", "0"),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column1",{{"Attribute", "Time Period"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Sales", Int64.Type}, {"Book", Int64.Type}, {"Units", Int64.Type}})
in
#"Changed Type2"
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@chaz2jerry when we generate Power Query code, Excel will change data area to Super Table automatically. So, when you add new data, just keep those data into this super table, and ensure each time adding 2, 4, 6 ....columns.
then refresh the query.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Custom1 = let a=Table.ToColumns(Table.DemoteHeaders(Source)),
b=Table.FromPartitions(
"Time Period",
List.Transform(
List.Split(List.Skip(a),2),
each let aa=List.Zip({a{0}}&_)
in {aa{0}{1},Table.FromRows(List.Skip(aa),{"Product","x","y"})}
)
)
in Table.Pivot(b,List.Distinct(b[x]),"x","y")
in
Custom1
Thanks Daniel, checking the code, seems this is a static/hard-coded approach to create the new table, in terms of the "time periods". Unfortunately the incoming data will change over time (adding new time periods), is it possible to modify the code to handle this data change?
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.