Group headers to create one master table

[Klokx]

Hi Guys

I'm a novice when dealing with power query, I'm trying to create one master table from my set of data but the extracted data has mutiple tables grouped together, with misaligned header rows. Is there a easy way to reorganise the data to align similar headers together to form one master table. e.g. data is of three tables but normally have way more (+20) thanks.

Excel link: Excel document 

 

[HotChilli · Accepted solution]

If you edit the Excel to insert a row between each 'table' then save it.

You can then connect to the Excel from powerbi.  A 'suggested tables' option will appear in the bottom left of the powerbi->Excel workbook connection screen. You can select all the suggested tables and Power Query will import them as separate queries/tables.

You can then Append Queries (from the Power Query interface - use the 2 or more tables option). Power Query will try and append all queries selected and union the column headings.

Give it a go.