September 14, 2021
I have source files with some worksheets that are formatted as tables and some worksheets that are in ranges. Additionally, the column names that I want to target on match, but the order does not (new columns have been added over time).
What I want to do is consolidate all worksheets into one connection and then create pivot tables using only the columns that I want to keep.
I thought I could just create a connection to the folder, append the files, and then I could create my pivot using only the fields needed.
Below is the error I'm receiving. Any suggestion?
An error occurred in the Transform File' query. Expression.Error: The key didn't match
any rows in the table.
Item = Export Worksheet
July 16, 2010
This error is caused by files that do not contain the column headers specified in the query, which is in line with how you've described your data. You'll need to either create a list of the column headers and write a custom function that can be fed into the query, or make sure the sample file you choose contains all the headings.