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

Welcome to our Power Query blog.  Today, I look at how to resolve 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 last time 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.  Since the Power Query combine method has failed, I need to find another way to append my data together.

I start by removing the steps after ‘Filtered Hidden Files1’.  I can do this by right-clicking on step ‘Invoke Custom Function1’, and choosing to ‘Delete Until End’:

I receive a warning, and I choose to ‘Delete’:

I can also remove the queries that Power Query created by removing the Transform File from PQ_StandardExpenses_Excel group.

Again, I receive a warning, but I choose to ‘Delete’:

Now I have removed everything I no longer need; I can create the tables.  To see how I would do this, I click on the first ‘Binary’ object in the Content column:

This extracts the contents of the first Excel workbook:

To get the sheets for all my Excel files, I need to put this solution into a ‘Custom Column’, which I can create from the ‘Add Column’ tab:

This creates a new column Extract the Contents:

I can expand each Table by using the expand icon next to the heading on Extract the Contents:

I am only interested in the Data column, so as I did for Content, I select it and right-click to remove other columns:

I can now extract the data from the tables using the icon next to the Data heading.     

I wish to extract all the columns, and I don’t need to ‘Use original column name as prefix’:

This time, there are no errors!  I need to promote the headings using ‘Use First Row as Headers’ from the Home tab:

I can remove the extra headings by changing the datatype of Date to a date.  This gives me errors in the Date column for the unwanted headings rows:

I should also change amount to a decimal number: this too results in errors in the amount column.

I can right-click on the Date column and ‘Remove Errors’:

Now, I can fill down in the Name column, using the option on the right-click menu or the option on the Transform tab:

My query is complete:

Although Power Query is being updated to try to help users complete tasks more easily, sometimes things do not go as planned, and we need to go back to the method we used before the improvements had been added.  Next time, I will look at what went wrong when I combined binaries using the Power Query helper queries.

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