Unpivoting data with multiple rows

[qsong]

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 

[v-lili6-msft · Accepted solution]

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

[qsong]

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. 

[Ashish_Mathur]

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.

[qsong]

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

[Ashish_Mathur]

Hi,

I do not understand.  Doesn't my soluion at the link shared earlier answer your question?