How to convert data from multiple merged columns and sub columns to rows using power query

[sreejitsnair]

Hi all,

Have recently started exploring powerBI and have been fascinated with the power query functions.

I managed to use unpivot finction to transform my data from columns to rows. While working with multiple merged columns and sub columns, I am unable to get the desired results. I have been trying to transfrom this data:

                                       PTM Data(Cell B to E)                      SMC Data (Cell F to I)

School Name	Nos. of PTM	Male	Female	Total	Nos. of SMC	Male	Female	Total
School 1	1	3	5	8	1	2	3	5
School 2	1	2	3	5	1	2	3	5
School 3	5	16	33	49	3	10	19	29

(Can't copy the merged columns above hence headers mentioned in text)

To this:

School Name	Event	Event Number	Male	Female	Total
School 1	PTM	1	3	5	8
School 2	PTM	1	2	3	5
School 3	PTM	5	16	33	49
School 1	SMC	1	2	3	5
School 2	SMC	1	2	3	5
School 3	SMC	3	10	19	29

Can this be done using unpivot or any other function in PowerBI ?

Regards,

Sreejit

[Anonymous · Accepted solution]

Result: 

What I used for the source code:

PTM and SCM are merged + centered cells 

M Query Code and steps taken: 

 

Explanation: 
After loading the data, make sure no headers were promoted.

Your first step is to transpose the table under the 'Transform' tab. This will bring down our PTM and SCM Event types.

From there we select Fill --> Fill Down from the 'Transform' tab. This will fill in the null values left by the merged excel cells. 

After that we ctrl + select both [Column1] aka Event Type and [Column2] aka Gender, then go to 'Add Column' tab and select Merge Columns. I choose not to add a separator but you can use whatever, this will come into play later. 

After merging, transpose the table again.

Congratulations, Headers, you're being promoted.

From there ctrl + select all of the columns except for [School Name] and [Event #]. Go back to the 'Transform' tab and select Unpivot Columns --> Unpivot Only Selected Columns. 

Now we're getting somewhere. Select the [Attribute] aka merged column and choose Split Column --> Split by Position under the 'Transform' tab. You'll need to split by delimiter if you used one during the merge column step. Just split by whatever delimiter you choose. I just like splitting by position. 

Finally, select [Attribute.2] column and select Pivot Column under the 'Transform' tab. Add finishing touches like header names and type changes. 

While this is method is possible it's a little cumbersome and will run much slower on larger datasets. Personally, I would either try to restructure the data you pull or organize it differently. For example I would ditch the total column all together and let DAX take care of it on the back end. I like to have my data organized like the following as I think you'll find it easier to manipulte with DAX:

Special thanks to the following website for guidance, this was fun problem to solve!
https://www.newtechdojo.com/unpivot-columns-in-power-bi/#dualrow

[Anonymous · Accepted solution]

all steps excuted from GUI:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRCgjxxUoGO2MjY3WilYKTM/LzcxT8EnNTgaJ++cV6CvlpChBdvok5IEG31FwIIyS/JDEHSVWwrzNOVUhmGwIFQdgYiE2B2ALKN4KLISk2wpDEoxiuwAwkC+KZWELVGRqACBDPyFIpNhYA", 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]),
    #"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}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Merged Columns" = Table.CombineColumns(#"Promoted Headers",{"PTM", "PTM_1", "PTM_2", "PTM_3"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"PTM"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"SCM", "SCM_4", "SCM_5", "SCM_6"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"SCM"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Merged Columns1", {""}, "Attribute", "Value"),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Unpivoted Other Columns", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"School Name", type text}, {"PTM", type text}, {"Nos. of PTM:Male:Female:Total", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Nos. of PTM:Male:Female:Total", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Nos. of PTM:Male:Female:Total.1", "Nos. of PTM:Male:Female:Total.2", "Nos. of PTM:Male:Female:Total.3", "Nos. of PTM:Male:Female:Total.4"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Nos. of PTM:Male:Female:Total.1", type text}, {"Nos. of PTM:Male:Female:Total.2", type text}, {"Nos. of PTM:Male:Female:Total.3", type text}, {"Nos. of PTM:Male:Female:Total.4", type text}}),
    #"Promoted Headers2" = Table.PromoteHeaders(#"Changed Type2", [PromoteAllScalars=true]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Promoted Headers2",{{"School Name", type text}, {"SCM", type text}, {"Nos. of SMC", Int64.Type}, {"Male", Int64.Type}, {"Female", Int64.Type}, {"Total", Int64.Type}})
in
    #"Changed Type3"

[Anonymous · Accepted solution]

Result: 

What I used for the source code:

PTM and SCM are merged + centered cells 

M Query Code and steps taken: 

 

Explanation: 
After loading the data, make sure no headers were promoted.

Your first step is to transpose the table under the 'Transform' tab. This will bring down our PTM and SCM Event types.

From there we select Fill --> Fill Down from the 'Transform' tab. This will fill in the null values left by the merged excel cells. 

After that we ctrl + select both [Column1] aka Event Type and [Column2] aka Gender, then go to 'Add Column' tab and select Merge Columns. I choose not to add a separator but you can use whatever, this will come into play later. 

After merging, transpose the table again.

Congratulations, Headers, you're being promoted.

From there ctrl + select all of the columns except for [School Name] and [Event #]. Go back to the 'Transform' tab and select Unpivot Columns --> Unpivot Only Selected Columns. 

Now we're getting somewhere. Select the [Attribute] aka merged column and choose Split Column --> Split by Position under the 'Transform' tab. You'll need to split by delimiter if you used one during the merge column step. Just split by whatever delimiter you choose. I just like splitting by position. 

Finally, select [Attribute.2] column and select Pivot Column under the 'Transform' tab. Add finishing touches like header names and type changes. 

While this is method is possible it's a little cumbersome and will run much slower on larger datasets. Personally, I would either try to restructure the data you pull or organize it differently. For example I would ditch the total column all together and let DAX take care of it on the back end. I like to have my data organized like the following as I think you'll find it easier to manipulte with DAX:

Special thanks to the following website for guidance, this was fun problem to solve!
https://www.newtechdojo.com/unpivot-columns-in-power-bi/#dualrow