Excel with merge cells to Power BI tabular table

[twister8889]

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?

[v-juanli-msft]

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]

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.

[amitchandak]

@ImkeF , any suggestions on this