Problem combining all excel files from a folder into one table in Power Query Editor

[smallfires0628]

I have some excel files containing data from different years in one folder. After connecting PowerBI Desktop with the folder and combining them, I could only see data from the 2019 file and not the rest. I've looked through the files and made sure that the column names, column amount, sheet name, and file name formatting are the same, and yet I could never seem to get PowerBI to combine all the files. The only consistency problem I can identify is for the values under the columns. The accounts' naming and months' formatting are different, but I don't see why that would pose a problem, and I wanted to clean that in Power Query Editor anyway. 

[jolind1996 · Accepted solution]

Hi, Smallfires0628

Here are some suggestions I would try. You have probably tried a bunch of them already.

1. File Format: Double-check if all your files are in the same format. If one file is .xls and another is .xlsx, this might be causing the issue.

2. Data Structure: You mentioned that the values under the columns are inconsistent. While this shouldn’t typically be a problem, do ensure that the data types for each column are consistent across all files.

3. Hidden Rows/Columns: Sometimes, hidden rows or columns in Excel can cause unexpected results in Power Query. It might be worth checking if this is the case in your files.

4. Corrupted Files: It’s possible that one or more of your Excel files might be corrupted. Try opening each file individually to check for any issues.

5. Refresh the Query: This might sound simple, but sometimes, refreshing the query in Power Query can solve the issue.

6. Load Settings: In Power Query, under ‘Close & Load’, ensure ‘Close & Load To…’ is selected and then choose ‘Table’ in the ‘Load To’ dialog box.

7. Combine Binaries Settings: When combining files from a folder, Power Query provides several options. Make sure you’re using the correct option for your needs.

Best regards,

Johannes