Forum Discussion

twister8889's avatar
twister8889
Icon for Helper V rankHelper V
6 years ago

Excel with merge cells to Power BI tabular table

Hello guys,

 

I have a table from excel, with some cells merged, I would like to transform this cells/table in the tabular table into Power BI, how can do I do this? What kind of options to solve this, do I have here?

 

 

 

 

Need Help

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi twister8889 

    After importing, in Transform data,

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PcrNCoJAFMXxV4m7ltH7YeADtGzXbphVDhiMGmFQb9+NM7o5fzj8YqTlXQo1ey6fZ75veTxd12Wbytev22POnnl9ZUpNJG65lY4H/4agvsxB/pFgHtOqZFfcgQmYglllejAGUzAD6yuzgwmYgfVgZ0rpBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Expected = _t, hours = _t, Column4 = _t, Column5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"Expected", type number}, {"hours", type text}, {"Column4", type text}, {"Column5", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"hours", "Expected Monthly"}, {"Column4", "Time"}, {"Column5", "more"}}),
    #"Removed Top Rows" = Table.Skip(#"Renamed Columns",1),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Top Rows", {"Month", "Expected"}, "Attribute", "Value"),
    #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "hours"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "hign level", each "hours")
in
    #"Added Custom"

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • twister8889's avatar
      twister8889
      Icon for Helper V rankHelper V
      6 years ago

      First of all, thank you v-juanli-msft 


      I understood that your solution, as to use in the table into power bi, right? I have other sheets in excel with more complexity merge cells, so I would like to have the table in the Editor, like a tabular model, because I need to use this data to the relationship with another table and use this data to create some pie/bar charts.

       

      In the Editor, my table will be like this:

       

       

      I would like to have this as the final result in the Editor model, this is possible?

       

      Thank you, so much.