Pivoting Nested Columns

[corporalph]

Hello! So I need to create a dashboard based on multiple excel files that involve multiple nested columns which look like this (mock data for privacy purposes).

Obviously, this data is not ready for dashboarding because of the multiple nested columns. I'm fairly new to the service so I need help transforming it to look like this.

I think this is what it should look like for it to be usable in Power BI. I tried tansposing the data first and filling down the columns to make up for the merged cells. But I'm having a problem turning the "food spending" and "leisure spending" into separate columns. 

Here is what it looks like on Power Query (again, mock data). The months seem to work correctly

With that, I would like to ask for guidance on perhaps an outline of steps I should try exploring to achieve the desired result.

[mahoneypat · Accepted solution]

Please see this article/video for how to do this.

Transform ANY nested table to Pivot Table with function query - DataChant

Pat

[corporalph]

Hi Pat!

Thanks for this. I wasn't allowed to edit the excel file itself but the whole solution still works as long as the data is already on Power Query.

Thank you so much!

R