powerquery: importing & transforming data from all Excel files in a folder where two tables on the same sheet

Question

I am trying to use PowerQuery to load and transform data contained in each "Sheet1" of a number of files in a folder.

The issue i am having is that there are two tables in "Sheet1" and I don't know how to combine the two tables, and keep the shared Date/Weekday titles.

I have searched to no avail on the internet. I have found no reference to PowerQuery importing and transforming data, from all Excel files in a folder, where there are TWO tables in the same sheet.

I include an image of the two tables and the desired output (First two rows for layout).

Note: Range Sheet1!"A2:B2" is merged.

Any help gratefully received.

Answer 1

I would change the 2 tables to proper Excel Tables (e.g. select cells, Ctrl-T or Insert / Table). This will unmerge your column headings.

You will then see both tables as entries in the Navigator (when you use Get External Data / From File / From Excel).

Answer 2

Thank you. That was part of my VBA solution below.

I came up with two solutions in the end.

Solution 1: VBA script: Looped through Excel files in a folder and found the relevant table headings. then used current region to copy paste to an added sheet e.g.

''Code to loop files then...

 Set rngAdm = Worksheets("Figures").Range("A:B").Find("Daily Admissions", lookat:=xlPart)

 If Not rngAdm Is Nothing Then

    'Add sheet for creating Admissions table

    Sheets.Add.Name = "Adm"

   'Grab data and paste across

   rngAdm.CurrentRegion.Copy Sheets("Adm").Range("A1")

   'Clear merged cell

   Range("A1:B2").UnMerge

   'Code to transform......

I then wrote some script to transform into a clean data set and make into a table.

Solution 2: Powerquery -Loop over files in folder with two queries, using the remove rows>remove alternate rows feature to specify the pattern to pull the tables of interest out separately,then transforming the data, then merging the two queries. It was the ability to specify patterns for start row and number of rows to keep i was unaware of, hidden behind the remove rows heading.

Notes: I used VBA to loop through the source folder and rename the files so they were in the correct order for loading. I then used the following two excellent posts to identify and remove duplicates:

http://forum.powerpivotpro.com/forums/topic/power-query-remove-duplicates-keep-highest-id/ http://www.excelguru.ca/blog/2015/12/09/identify-duplicates-using-power-query/

By grouping on ID, combining sort [Date] asc, sort [Index] desc and removing duplicates [ID], i was able to keep just the last imported row of each duplicate set.

Q