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

Welcome to our Power Query blog.  Today, I look at 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 does not go so smoothly.  In a new blank workbook, I choose to extract a folder of Excel files:

I browse to the folder location, and choose to ‘Open’ the folder:

I choose to ‘Transform Data’:

Note that if this were a real-life scenario, I would add a lowercase and filter transformation to the Extension column, to ensure only the required file types were loaded, but here I am concentrating on the issue and how to solve it.  I only need the Content column, so I select it and choose ‘Remove Other Columns’ from the right-click menu:    

I can then use the icon next to the Content heading to combine the Binary files.

I am then prompted to check my data, and everything looks fine, so I click ‘OK’:

Power Query generates the ‘Helper Queries’ folder and creates some steps for me, but there is a problem:

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 error message mentions “the ‘Transform File’ Query”.  This function has been generated by Power Query and uses the steps in Transform Sample File.

This appears to be a very simple query, so this doesn’t help me.  If I look at the steps that Power Query has generated, the first step with an error is  ‘Invoke Custom Function1’:

This has failed to extract the second and third tables.  Since the Power Query combine method has failed, I need to find another way to append my data together.  Next time, I will create the steps I need to solve this issue.

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