Power Query: Excel Files from a Folder Fiddle – Part 1
8 November 2023
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!