How to handle merged cells in Excel in Power BI

[ericjo]

I'm trying to develop a report by loading an Excel file with merged cells (by year and month) into Power BI.

When I load the Excel data, the merged cells are marked as Null, and I don't know how to fill them in before pivoting.

I want to configure it in the following format.

[danextian · Accepted solution]

Hi @ericjo 

This is more than just merged cells but a case of nested hierarchy as well. You would need to combine the upper and lower hierarchy and use that as the column names, unpivot and then split the combine hierarchy names.  There's also a need to fill down due to merged cells.  This isn't exactly for beginners but try the attached files.