Excel source with Multiple Level Header

[urpalani]

Hi Experts,

I have a excel with Multiple level Headers,

Want to upload it and do some analysis,As you see below

My H1 is Asia,Europe ...

My H2 is East Asia,South Asia,...

My H3 is EA1,EA2,SA1,...

Want to upload in same format and refresh the excel whever necessary,

Thanks for your time,

[v-danhe-msft]

Hi @urpalani,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[v-danhe-msft]

Hi @urpalani,

Based on my research, you could not merge the multiple header in Power BI, you could submit a feature idea:

https://ideas.powerbi.com/forums/265200-power-bi-ideas

Regards,

Daniel He

[Stachu]

to analyse you will need to put the hierarchy from columns to rows, similar to what I did here

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRcizOTARSEORaWpRfkArlxuqAFbgmFpcoQFWFpyLYIKlQQxjDCKzcJbEEpNsRJOxoBCKMQYQJkEg0BRFmYGUGhvoGRvpGBoYWCgYGVmAElARpAukxhmIzILaAqDfCph6mFmQ6yHBzhHpjEtWbkKjelAT1sQA=", 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]),
    #"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}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}),
    #"Kept First Rows" = Table.FirstN(#"Replaced Value",3),
    #"Transposed Table" = Table.Transpose(#"Kept First Rows"),
    #"Removed Top Rows" = Table.Skip(#"Transposed Table",1),
    #"Filled Down" = Table.FillDown(#"Removed Top Rows",{"Column1", "Column2"}),
    Custom1 = #"Replaced Value",
    #"Removed Top Rows1" = Table.Skip(Custom1,2),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type datetime}, {"A1", Int64.Type}, {"A2", Int64.Type}, {"A3", Int64.Type}, {"A4", Int64.Type}, {"a5", Int64.Type}, {"a6", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Date"}, "Attribute", "Value"),
    #"Merged Queries" = Table.NestedJoin(#"Unpivoted Other Columns",{"Attribute"}, #"Filled Down",{"Column3"}, "Custom",JoinKind.LeftOuter),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Merged Queries", "Custom", {"Column1", "Column2"}, {"Column1", "Column2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom",{"Date", "Column1", "Column2", "Attribute", "Value"})
in
    #"Reordered Columns"

if you replace the red syntax with your import and adjust the names it should return the data in the easily analysed format

[urpalani]

Thanks  @Stachu

Can you share me the sample file for your code to load and see how it works,

[Stachu]

you can just copy the code directly to PowerBI as a new blank query, using Advanced Editor
the table is JSON and not linking to Excel, so it will work for you