Power Query to Convert Data Range to a Table

Hi,

I have very recently discovered Power Query and it looks like it could make a huge difference to what I do in Excel.

In the attached workbook I have some fictitious sample data that arrives in a workbook in a format that's not so easy to work with (for me, anyway) - for example, there are merged cells (rows 8 and 9), dates are in text format and the headers in row 11 are the same whatever the period measured.

With this in mind I was hoping that someone could please tell me how to achieve, using Power Query, the following separate but related tasks:

1. Convert the data in these two worksheets into separate Tables in which the merged cells are separated and it's clear to which period each set of "Sales Growth" and "Sales Rank" belongs.

2. Combine the data in both sheets into a single Table, along the same lines as the Tables described in point 1.

It's only the data in rows 8 and below that I'm interested in.

Any advice would be very much appreciated.

Thanks!

For step 2, please mock up what you would want the solution to look like. Combining them as you have asked could connote various types of combinations and since I no longer at adept at mind reading (old age as crept in), I need you to show us what it should look like.

Here is the Mcode for what I think you are looking for in Step 1

Please Login or Register  to view this content.

Please Login or Register  to view this content.