Forum Discussion

smallfires0628's avatar
smallfires0628
Helper I
2 years ago
Solved

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

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 r...
Need Help
  • jolind1996's avatar
    jolind1996
    2 years ago

    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

     

jolind1996's avatar
jolind1996
Resolver II
2 years ago

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