Multi-level Column Unpivoting with Power Query

An interesting challenge today come up while mentoring finance users in Power BI. Consider the following Excel layout.

The user wants to unpivot the data by rotating the three header rows (Scenario Type, Month, and Year) from columns to rows. The issue is that the headers span three rows. If you just select these columns and unpivot, you’ll end up with a mess. And Power Query operates on row at the time so you can’t reference previous rows, such as to concatenate Scenario, Month, and Year. We can do the concatenation in Excel so we have one row with column headers, such as Actuals-Jan-2018, Actuals-Feb-2018, and so on, which we can easily unpivot in Power Query. But if we can’t or don’t want to modify the Excel file, such as to avoid the same steps every time a new file comes in?

One way to achieve a Power Query-only solution is to transpose the rows. Then, we can apply the required transforms, such as filling down, creating a new column that concatenates the three columns and so on.

Then, we transpose back and unpivot. You can download the Excel file with the Power Query transforms using this link.
UnpivotBudget