Combine Files button does not work for valid Excel files in Power BI

[mohowzeh]

Hi, 

I have a SharePoint folder with two Excel files called 'File 1.xlsx' and 'File 2.xlsx'. 

The folder contains no other files of any type, and it contains no hidden files. 

In Power BI, when I use Power Query to navigate to the SharePoint folder, I can see the two Excel files. 

But when I click the "Combine Files" button, I get an error message:

"We didn't recognize the format of your first file (). Please filter the list of files so it contains only supported types (Text, CSV, Excel workbooks, etc.) and try again."

However, those files are Excel files without any doubt:

* I can open both files without issues in Excel 
   (my Excel version = Microsoft® Excel® for Microsoft 365 MSO (Version 2209 Build 16.0.15629.20256) 64-bit)

* I can drill into the content of each Excel file separately in Power BI without any issues

* When using Power Query from within Excel, the "Combine Files" button does work without issues 

So it is just the "Combines Files" button in Power BI that somehow does not work for these files.

I have tried creating a completely new Power BI model and trying to combine only those two files, but I received the same error. 

The files can be found here

• 'File 1.xlsx'
• 'File 2.xlsx'

My Power BI desktop version is '2.112.603.0 64-bit'

There is a Power BI Community post here which mentions this YouTube video. This explains how to create a custom function in Power Query and this approach has indeed allowed me to solve the problem. 

However, I am still looking to understand why the standard "Combine Files" button does not work.

Any insights into the possible causes and remedies would be greatly appreciated.  

[bgreiner]

I have the same problem. I am able to copy old queries from a few months ago, and they work fine to combine my list of 9 Excel files into one table, even if I use a Sharepoint Folder as the source. The issue seems to be in actually using the "combine files" button, which is somehow failing to recognize that Excel files are indeed Excel files, and so it refuses to create the transformation sample file, and associated function and parameter. 

[nickvanmaele]

I have the exact same problem and solved it by following the steps explained by Wynn Baum in this YouTube video and summarised below:

1. create a normal query which reads and transforms the first file,
2. turn the filename of the first file into a parameter and insert the parameter into the query
3. turn the query into a function 
4. then create a new query which has one column showing the list of Excel files in the appropriate folder
5. to this list of Excel files, add a new column via "Invoke Custom Function" and choose the created function
6. expand the column to reveal all data of all files

That worked like a charm.

In essence, you manually re-create what the "Combine Files" button is doing automatically. 

[ppm1]

Please see this video with another way to combine your files. It will at least help you troubleshoot a little more (e.g., by adding a custom column with the Excel.Workbook function to explore the binary content).

How to Create and Modify Custom Functions in Power BI (or Excel) - YouTube

Pat

[nickvanmaele]

Thank you so much for sharing that video! Watched it entirely and it's a great technique to simplify the query window.