Forum Discussion
Unpivoting data with multiple rows
Hello PBI user:
I have the following structure of data,
| SCENARIO | AC | FC2 | FC3 | PL | AC | FC2 | FC3 | PL | |
| COLUMN1 | COLUMN2 | MO | MO | MO | MO | YTD | YTD | YTD | YTD |
| a | x | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| b | y | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| c | z | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
I want to unpivot them into three columns
| COLUMN1 | COLUMN2 | SCENARIO | MO | YTD |
| a | x | AC | 0 | 0 |
| b | y | AC | 0 | 0 |
| c | z | AC | 0 | 0 |
| a | x | FC2 | 0 | 0 |
| b | y | FC2 | 0 | 0 |
| c | z | FC2 | 0 | 0 |
| a | x | FC3 | 0 | 0 |
| b | y | FC3 | 0 | 0 |
| c | z | FC3 | 0 | 0 |
| a | x | PL | 0 | 0 |
| b | y | PL | 0 | 0 |
| c | z | PL | 0 | 0 |
I am just showing the portion of the data, so it is impossible to do some kind of transpose, because of PBI column limitation. I am wondering if there is a good way to make data transformation.
Thank you.
Best, Qianru
hi qsong
You could try these steps as below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRCnZ29XMM8vQHMh2dgYSbsxGYNAaSAT44RGN1opWc/X1Cff0MgQIQFkiBrz8mERnigoUEmZAIZFcAsQFeDFKZBGRVEqUyGciqIqwyFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type 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]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}}), #"Transposed Table" = Table.Transpose(#"Changed Type"), #"Filtered Rows" = Table.SelectRows(#"Transposed Table", each ([Column1] <> "")), #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"SCENARIO", type text}, {"COLUMN2", type text}, {"x", Int64.Type}, {"y", Int64.Type}, {"z", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"SCENARIO", "COLUMN2"}, "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[COLUMN2]), "COLUMN2", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute", "COLUMN2"}}), #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "COLUMN1", each if [COLUMN2] = "x" then "a" else if [COLUMN2] = "y" then "b" else "c"), #"Reordered Columns" = Table.ReorderColumns(#"Added Conditional Column",{"COLUMN1", "COLUMN2", "SCENARIO", "MO", "YTD"}) in #"Reordered Columns"Result:
and here is sample pbix file, please try it.
Regards,
Lin
5 Replies
Hi,
You may refer to my solution here - Rearrange a multi heading dataset into a single heading one which is Pivot ready.
Hope this helps.
Hi Ashish
Thank you for your suggestion. Currently, I am building an automized report, and I have to do data cleaning and transformation in PowerBi, so do you have a way to deal with it?
Best, Qianru
Hi,
I do not understand. Doesn't my soluion at the link shared earlier answer your question?
hi qsong
You could try these steps as below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRCnZ29XMM8vQHMh2dgYSbsxGYNAaSAT44RGN1opWc/X1Cff0MgQIQFkiBrz8mERnigoUEmZAIZFcAsQFeDFKZBGRVEqUyGciqIqwyFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type 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]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}}), #"Transposed Table" = Table.Transpose(#"Changed Type"), #"Filtered Rows" = Table.SelectRows(#"Transposed Table", each ([Column1] <> "")), #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"SCENARIO", type text}, {"COLUMN2", type text}, {"x", Int64.Type}, {"y", Int64.Type}, {"z", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"SCENARIO", "COLUMN2"}, "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[COLUMN2]), "COLUMN2", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute", "COLUMN2"}}), #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "COLUMN1", each if [COLUMN2] = "x" then "a" else if [COLUMN2] = "y" then "b" else "c"), #"Reordered Columns" = Table.ReorderColumns(#"Added Conditional Column",{"COLUMN1", "COLUMN2", "SCENARIO", "MO", "YTD"}) in #"Reordered Columns"Result:
and here is sample pbix file, please try it.
Regards,
Lin
I see you have to transpose the orginal table, as I told you that my dataset is longer, more than MAX #columns in PowerBi. So it is not feasible for my case.
Thank you.
