Follow these steps in the Power Query Editor (click Transform data on the Power BI Desktop ribbon).
1. Connect to Your Excel File
Start by connecting to your Excel workbook. When the Navigator window appears, select your sheet and click Transform Data.
2. Stop Automatic Header Promotion
Power Query will likely try to use the first row as headers. We need to stop this.
In the Applied Steps pane on the right, find the step named Promoted Headers and click the "X" to delete it.
Find the step below it named Changed Type and delete that as well.
Your table should now just have generic Column1, Column2, etc., as headers, with your data starting from the first row.
3. Transpose the Data
To easily work with the two header rows, we'll temporarily flip the entire table.
Go to the Transform tab and click Transpose. Your columns are now rows, and your rows are now columns.
4. Fill and Merge the Header Columns
Now your two header rows have become the first two columns.
Fill Down: Select the first column (which contains AOP, null, Forecast, null). On the Transform tab, click Fill > Down. This will fill the null values with the correct scenario name.
Merge Columns: Select the first column, then hold Ctrl and select the second column. Right-click one of the selected column headers and choose Merge Columns. You can use a separator like a pipe (|) or a colon (:). Click OK. You now have a single, clean header column (e.g., AOP|Jan, Forecast|Feb).
5. Transpose Back and Promote Headers
Now we flip the table back to its original orientation.
Go to the Transform tab and click Transpose again.
Now that your first row contains the correct, combined headers, go to the Home tab and click Use First Row as Headers.
6. Unpivot Your Data
This is the key step to transform your data into a proper format.
Select all of your initial project information columns (Project ID, Project name, etc.).
Right-click on any of the selected column headers.
Choose Unpivot Other Columns.
This will transform all your scenario/date columns into just two new columns: Attribute (containing your merged headers like AOP|Jan) and Value.
7. Split and Rename Columns
Finally, let's clean up the new columns.
Select the Attribute column.
On the Home tab, click Split Column > By Delimiter.
Choose the separator you used in Step 4 (e.g., the pipe |). Click OK.
Rename the newly created columns to Scenario and Month. Also, rename the Value column if needed. Check that your data types are correct (e.g., numbers, text, dates).
If this explanation and solution resolve your issue, please like and accept the solution.
