To import excel with merged column headers in PowerBI

[nripendrasinha]

I have a excelchart with Merged column headers for month as shown below.

I need to import in PowerBI desktop and then in PowerQuery I would like to convert the Query as shown 

I have tried to unpivot however couldn't get the required format.

It will be a great help for your suggestion

Rgds

Nripendra

[BjoernSchaefer · Accepted solution]

Hi Nripendra,

i had exact the same issue before and i've been able to solve it witch the function "Transpose" in combination with Pivot and Unpivot. 

First thing you can do ist to transpose the whole table, fill down the first column, merge the first two columns with a delimiter, transpose again, set first rows as headers and split by delimiter. 

Here's the M-Syntax (only available in German, sorry guys. But you get the concept) for the whole Transformation. If somebody knows a more elegant and effective way of doing so don't hesitate to mention it. I'm curios 'bout that.

let
Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Spalte1", type text}, {"Spalte2", type any}, {"Spalte3", type text}, {"Spalte4", type any}, {"Spalte5", type any}, {"Spalte6", type any}, {"Spalte7", type any}, {"Spalte8", type any}, {"Spalte9", type any}}),
#"Transponierte Tabelle" = Table.Transpose(#"Geänderter Typ"),
#"Nach unten gefüllt" = Table.FillDown(#"Transponierte Tabelle",{"Column1"}),
#"Zusammengeführte Spalten" = Table.CombineColumns(#"Nach unten gefüllt",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Zusammengeführt"),
#"Transponierte Tabelle1" = Table.Transpose(#"Zusammengeführte Spalten"),
#"Geänderter Typ1" = Table.TransformColumnTypes(#"Transponierte Tabelle1",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
#"Ersetzter Wert" = Table.ReplaceValue(#"Geänderter Typ1",";","",Replacer.ReplaceText,{"Column1", "Column2", "Column3"}),
#"Höher gestufte Header" = Table.PromoteHeaders(#"Ersetzter Wert", [PromoteAllScalars=true]),
#"Geänderter Typ2" = Table.TransformColumnTypes(#"Höher gestufte Header",{{"Production Office", type text}, {"Year", Int64.Type}, {"Material", type text}, {"January;Yarn/Fabric/Material px", Int64.Type}, {"January;Garn px", Int64.Type}, {"February;Yarn/Fabric/Material px", Int64.Type}, {"February;Garn px", Int64.Type}, {"March;Yarn/Fabric/Material px", Int64.Type}, {"March;Garn px", Int64.Type}}),
#"Entpivotierte andere Spalten" = Table.UnpivotOtherColumns(#"Geänderter Typ2", {"Production Office", "Year", "Material"}, "Attribut", "Wert"),
#"Spalte nach Trennzeichen teilen" = Table.SplitColumn(#"Entpivotierte andere Spalten", "Attribut", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Attribut.1", "Attribut.2"}),
#"Geänderter Typ3" = Table.TransformColumnTypes(#"Spalte nach Trennzeichen teilen",{{"Attribut.1", type text}, {"Attribut.2", type text}}),
#"Umbenannte Spalten" = Table.RenameColumns(#"Geänderter Typ3",{{"Attribut.1", "Month"}, {"Attribut.2", "Type"}}),
#"Pivotierte Spalte" = Table.Pivot(#"Umbenannte Spalten", List.Distinct(#"Umbenannte Spalten"[Type]), "Type", "Wert", List.Sum),
#"Neu angeordnete Spalten" = Table.ReorderColumns(#"Pivotierte Spalte",{"Production Office", "Year", "Month", "Material", "Yarn/Fabric/Material px", "Garn px"})
in
#"Neu angeordnete Spalten"

Regards

Björn

[BjoernSchaefer · Accepted solution]

Hi Nripendra,

i had exact the same issue before and i've been able to solve it witch the function "Transpose" in combination with Pivot and Unpivot. 

First thing you can do ist to transpose the whole table, fill down the first column, merge the first two columns with a delimiter, transpose again, set first rows as headers and split by delimiter. 

Here's the M-Syntax (only available in German, sorry guys. But you get the concept) for the whole Transformation. If somebody knows a more elegant and effective way of doing so don't hesitate to mention it. I'm curios 'bout that.

let
Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Spalte1", type text}, {"Spalte2", type any}, {"Spalte3", type text}, {"Spalte4", type any}, {"Spalte5", type any}, {"Spalte6", type any}, {"Spalte7", type any}, {"Spalte8", type any}, {"Spalte9", type any}}),
#"Transponierte Tabelle" = Table.Transpose(#"Geänderter Typ"),
#"Nach unten gefüllt" = Table.FillDown(#"Transponierte Tabelle",{"Column1"}),
#"Zusammengeführte Spalten" = Table.CombineColumns(#"Nach unten gefüllt",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Zusammengeführt"),
#"Transponierte Tabelle1" = Table.Transpose(#"Zusammengeführte Spalten"),
#"Geänderter Typ1" = Table.TransformColumnTypes(#"Transponierte Tabelle1",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
#"Ersetzter Wert" = Table.ReplaceValue(#"Geänderter Typ1",";","",Replacer.ReplaceText,{"Column1", "Column2", "Column3"}),
#"Höher gestufte Header" = Table.PromoteHeaders(#"Ersetzter Wert", [PromoteAllScalars=true]),
#"Geänderter Typ2" = Table.TransformColumnTypes(#"Höher gestufte Header",{{"Production Office", type text}, {"Year", Int64.Type}, {"Material", type text}, {"January;Yarn/Fabric/Material px", Int64.Type}, {"January;Garn px", Int64.Type}, {"February;Yarn/Fabric/Material px", Int64.Type}, {"February;Garn px", Int64.Type}, {"March;Yarn/Fabric/Material px", Int64.Type}, {"March;Garn px", Int64.Type}}),
#"Entpivotierte andere Spalten" = Table.UnpivotOtherColumns(#"Geänderter Typ2", {"Production Office", "Year", "Material"}, "Attribut", "Wert"),
#"Spalte nach Trennzeichen teilen" = Table.SplitColumn(#"Entpivotierte andere Spalten", "Attribut", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Attribut.1", "Attribut.2"}),
#"Geänderter Typ3" = Table.TransformColumnTypes(#"Spalte nach Trennzeichen teilen",{{"Attribut.1", type text}, {"Attribut.2", type text}}),
#"Umbenannte Spalten" = Table.RenameColumns(#"Geänderter Typ3",{{"Attribut.1", "Month"}, {"Attribut.2", "Type"}}),
#"Pivotierte Spalte" = Table.Pivot(#"Umbenannte Spalten", List.Distinct(#"Umbenannte Spalten"[Type]), "Type", "Wert", List.Sum),
#"Neu angeordnete Spalten" = Table.ReorderColumns(#"Pivotierte Spalte",{"Production Office", "Year", "Month", "Material", "Yarn/Fabric/Material px", "Garn px"})
in
#"Neu angeordnete Spalten"

Regards

Björn

[nripendrasinha · Accepted solution]

Hi Björn,

Thanks so much. It works 🙂

And here is the english version...in case anyone would like to check

let
Source = Excel.Workbook(File.Contents("C:\Users\ind38\Pictures\Price Excel\Price Chart1.xlsx"), null, true),
Turkey_Sheet = Source{[Item="Turkey",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Turkey_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type text}, {"January", type any}, {"Column5", type any}, {"February", type any}, {"Column7", type any}, {"March", type any}, {"Column9", type any}, {"April", type any}, {"Column11", type any}, {"May", type any}, {"Column13", type any}, {"June", type any}, {"Column15", type any}, {"July", type any}, {"Column17", type any}, {"August", type any}, {"Column19", type any}, {"September", type any}, {"Column21", type any}, {"October", type any}, {"Column23", type any}, {"November", type any}, {"Column25", type any}, {"December", type any}, {"Column27", type any}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column3] <> null)),
#"Demoted Headers" = Table.DemoteHeaders(#"Filtered Rows"),
#"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}}),
#"Transposed Table" = Table.Transpose(#"Changed Type1"),
#"Added Conditional Column" = Table.AddColumn(#"Transposed Table", "Custom", each if [Column1] = "Column5" then "January" else if [Column1] = "Column7" then "February" else if [Column1] = "Column9" then "March" else if [Column1] = "Column11" then "April" else if [Column1] = "Column13" then "May" else if [Column1] = "Column15" then "June" else if [Column1] = "Column17" then "July" else if [Column1] = "Column19" then "August" else if [Column1] = "Column21" then "September" else if [Column1] = "Column23" then "October" else if [Column1] = "Column25" then "November" else if [Column1] = "Column27" then "December" else [Column1], type text),
#"Reordered Columns" = Table.ReorderColumns(#"Added Conditional Column",{"Column1", "Custom", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}),
#"Merged Columns" = Table.CombineColumns(#"Reordered Columns",{"Custom", "Column2"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Column1"}),
#"Transposed Table1" = Table.Transpose(#"Removed Columns"),
#"Changed Type2" = Table.TransformColumnTypes(#"Transposed Table1",{{"Column2", type text}, {"Column1", type text}, {"Column3", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type2","Column1-","",Replacer.ReplaceText,{"Column1"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Column2-","",Replacer.ReplaceText,{"Column2"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Column3-","",Replacer.ReplaceText,{"Column3"}),
#"Promoted Headers1" = Table.PromoteHeaders(#"Replaced Value2", [PromoteAllScalars=true]),
#"Changed Type3" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Production Office", type text}, {"Year", Int64.Type}, {"Material ", type text}, {"January-Yarn/ fabric/ Material px", Int64.Type}, {"January-Garment px", Int64.Type}, {"February-Yarn/ fabric/ Material px", Int64.Type}, {"February-Garment px", Int64.Type}, {"March-Yarn/ fabric/ Material px", Int64.Type}, {"March-Garment px", Int64.Type}, {"April-Yarn/ fabric/ Material px", Int64.Type}, {"April-Garment px", Int64.Type}, {"May-Yarn/ fabric/ Material px", Int64.Type}, {"May-Garment px", Int64.Type}, {"June-Yarn/ fabric/ Material px", Int64.Type}, {"June-Garment px", Int64.Type}, {"July-Yarn/ fabric/ Material px", Int64.Type}, {"July-Garment px", Int64.Type}, {"August-Yarn/ fabric/ Material px", Int64.Type}, {"August-Garment px", Int64.Type}, {"September-Yarn/ fabric/ Material px", Int64.Type}, {"September-Garment px", Int64.Type}, {"October-Yarn/ fabric/ Material px", Int64.Type}, {"October-Garment px", Int64.Type}, {"November-Yarn/ fabric/ Material px", Int64.Type}, {"November-Garment px", Int64.Type}, {"December-Yarn/ fabric/ Material px", Int64.Type}, {"December-Garment px", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type3", {"Production Office", "Year", "Material "}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type4",{{"Attribute.1", "Month"}, {"Attribute.2", "Type"}}),
#"Pivot Columns" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Type]), "Type", "Value", List.Sum),
#"Reordered Columns1" = Table.ReorderColumns(#"Pivot Columns",{"Production Office", "Year", "Month", "Material ", "Yarn/ fabric/ Material px", "Garment px"})

in
#"Reordered Columns1"