Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |