Power Query: Excel Files from a Folder Fiddle – Part 3

Welcome to our Power Query blog. Today, I look at the reason behind an issue I encountered recently when importing Excel files from a folder.

In Power Query: Returning to the Folder, I looked at how the process to import files from a folder had been made easier by using the ‘Combine Files’ functionality:

This worked successfully for a folder of CSV (comma delimited) files. 

The files and functions in the ‘Helper Queries’ folder worked, and my data was successfully combined.  However, when I have some Excel files in a folder, the process in Part 1 did not go so smoothly. 

I have a red wavy line under my headings, and a row of errors instead of the rest of the data in the folder.  I only have one sheet of data from the first Excel file.  If I click on the error, the message is not helpful:

The first step with this error is ‘Invoke Custom Function1’:

This has failed to extract the second and third tables.  Last time, I described how I could resolve this without the Power Query ‘helper’ queries.  This week, I will look at what went wrong with the ‘Combine Binaries’ process.  Since the error occurred in the ‘Invoke Custom Function1’ step,  I need to look at the steps that this function is using.  The steps are in the Transform Sample File query.

Since there are only three [3] steps to this query, I can look at each in turn.  The ‘Source’ step is using a parameter to access the current Excel file:

I can see from the ‘Queries’ tab that Parameter1 is currently pointing to the Sample File.  As the Transform File function is applied to each row, i.e. each Excel file in turn, this will access the current Excel File.  The next step is the ‘Navigation’ step:

Here, we have a problem.  The M code for this step is hard-coded to the name of the sheet:

To check that this is the problem, I can test the Transform File function.  Since this accepts Parameter1, which is set to the binary value of the Sample File query, I can amend the Sample File query:

I change the Navigation1 step to point at the second Excel file, by changing the M code from:

to 

This changes the output:

When I change the Sample File, this breaks the Transform Sample File query:

This error looks familiar.  The ‘Combine Binaries’ functionality generated by Power Query will only work for Excel files if we want to combine a sheet with the same name in each Excel file.  To prove this, I create an example where this is true:

If there is one sheet name which is always the same, I am able to combine those sheets for Excel files from one folder.  Other sheets will be ignored. Whilst this may be true for some scenarios, this is a limitation of the ‘Combine Binaries’ functionality, which needs to issue clear messages so that the user understands this limitation.

Come back next time for more ways to use Power Query!