
A while back, I was working on a project involving getting data from Excel files. The Excel files contain the data in sheets and tables. Getting the data from the tables is easy. However, the data in the sheets have some commentaries on top of the sheet, then the column names and then the data itself. Something like below:
This approach is pretty consistent across many Excel files. The customer wants to have the commentary in the column names when the data is imported into Power BI. So the final result must look like this:
The business requirement though is to combine the first 3 rows of data and promote it as the column name.
The Challenge
Let’s connect the Excel file and look at the data in Power BI Desktop.
As