Forum Discussion

urpalani's avatar
urpalani
Helper II
8 years ago

Excel source with Multiple Level Header

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,

 

 

Need Help
Show and Tell

5 Replies

  • Stachu's avatar
    Stachu
    Community Champion
    8 years ago

    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's avatar
      urpalani
      Helper II
      8 years ago

      Thanks  Stachu

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

       

      • Stachu's avatar
        Stachu
        Community Champion
        8 years ago

        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

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    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