Unpivot multiple headers in a data

[vishwanathans]

Hello,

I am trying to figure out if there is way to unpivot multiple header data.

Tried few steps in M query but i couldn't get it to achieve the results.

Here is my data.

		MTD	MTD	MTD	MTD	MTD	MTD
		Actual	Actual	Actual	Actual	Actual	Actual
		Group A	Group A	Group A	Group A	Group A	Group A
		Jan	Feb	Mar	Apr	May	Jun
		FY19	FY19	FY20	FY20	FY21	FY21
Total CostType	Revenue	100000	100000	100000	100000	100000	100000
DirectCost	Salary	50000	50000	50000	50000	50000	50000
DirectCost	Bonus	2000	2000	2000	2000	2000	2000
DirectCost	Healthcare Cost	1500	1500	1500	1500	1500	1500

I need the end result to be:

Thanks in advance for your help!

[Anonymous · Accepted solution]

Hi @vishwanathans 

Try transpose and merge in power query to achieve your goal.

Firstly we need to duplicate your original table and transform them as below.

Sample Table(Table1):

Duplicate Sample to Table2 and Remove all column except Column1,2,3. And only keep last 4 rows in this table.

Remove Column1 and Column2 in Table1.

Transpose all columns in Table1.

Then select column6,7,8,9 and use unpivote function. 

Finally, merge Table1 and Table2 by number column and expand table to get result.

Merge:

Result:

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

What if the values are not all the same across the rows? This creates an issue with the matching during merge.

[vishwanathans]

Hi Rico,

This helps big time. Thank you for your help.

Thanks
Srini

[amitchandak]

@vishwanathans , refer if this can help

https://prologika.com/multi-level-column-unpivoting-with-power-query/

[vishwanathans]

Hi Amit,

Thank you so much for the help.

With that query i tried with my data set but i am getting the following error:

Can you be kind enough to help me with this please?

This is my code:

let
    Source = Excel.Workbook(File.Contents("C:\Users\vishwanathans\Desktop\Power BI files\CRS\Monthly detail P&L - IPG - Power BI.xlsx"), null, true),
    #"Clin Ops_Sheet" = Source{[Item="Clin Ops",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"ICR", type any}, {"ICR_1", type any}, {"ICR_2", type any}, {"ICR_3", type any}, {"ICR_4", type any}, {"ICR_5", type any}, {"ICR_6", type any}, {"ICR_7", type any}, {"ICR_8", type any}, {"ICR_9", type any}, {"ICR_10", type any}, {"ICR_11", type any}, {"ICR_12", type any}, {"ICR_13", type any}, {"ICR_14", type any}, {"ICR_15", type any}, {"ICR_16", type any}, {"ICR_17", type any}, {"ICR_18", type any}, {"ICR_19", type any}, {"ICR_20", type any}, {"ICR_21", type any}, {"ICR_22", type any}, {"ICR_23", type any}, {"ICR_24", type any}, {"ICR_25", type any}, {"ICR_26", type any}, {"ICR_27", type any}, {"ICR_28", type any}, {"ICR_29", type any}, {"ICR_30", type any}, {"ICR_31", type any}, {"ICR_32", type any}, {"ICR_33", type any}, {"ICR_34", type any}, {"ICR_35", type any}, {"ICR_36", type any}, {"ICR_37", type any}, {"ICR_38", type any}, {"ICR_39", type any}, {"ICR_40", type any}, {"ICR_41", type any}, {"ICR_42", type any}, {"ICR_43", type any}, {"ICR_44", type any}, {"ICR_45", type any}, {"ICR_46", type any}, {"ICR_47", type any}, {"ICR_48", type any}, {"ICR_49", type any}, {"ICR_50", type any}, {"ICR_51", type any}, {"ICR_52", type any}, {"ICR_53", type any}, {"ICR_54", type any}, {"ICR_55", type any}, {"ICR_56", type any}, {"ICR_57", type any}, {"ICR_58", type any}, {"ICR_59", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Transposed Table",{{"Column8", type text}}),
    #"Filled Down1" = Table.FillDown(#"Changed Type1",{"Column1"}),
    #"Added Custom" = Table.AddColumn(#"Filled Down1", "Custom", each [Column1] & "-" & [Column2] & "-" & [Column3] & "-" [column4] & "-" [column5] & "-" [column6] & "-" [column7] & "-" [column8]),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Column1", "Column2", "Column3","Column4", "Column6", "Column7","Column8", "Custom", "Column9","Column10"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8"}),
    #"Transposed Table1" = Table.Transpose(#"Removed Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Column1"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3", "Attribute.4", "Attribute.5", "Attribute.6", "Attribute.7","Attribute.8"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}, {"Attribute.3", type text}, {"Attribute.4", type text}, {"Attribute.5", type text}, {"Attribute.6", type text}, {"Attribute.7", type text}, {"Attribute.8", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Attribute.1", "Column1"}, {"Attribute.2", "Column2"}, {"Attribute.3", "Column3"}, {"Attribute.4", "Column4"}, {"Attribute.5", "Column5"}, {"Attribute.6", "Column5"}, {"Attribute.7", "Column5"}, {"Attribute.8", "Column8"}})

in
    #"Renamed Columns"