Forum Discussion

alvinyuda's avatar
alvinyuda
Copper Contributor
Jul 21, 2023

Power Query Error

Hi, I have a problem about power query. When I want to query many files on the same folder.

 

The Error shows up like this:

 

ps: I'm using Microsoft Excel 2016

excel
Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jul 22, 2023

    alvinyuda 

    The error message you encountered in Power Query indicates that the file you are trying to load could not be recognized as a valid Excel document. This issue could be caused by various reasons, such as corrupted files, incorrect file extensions, or incompatible Excel formats.

    Here are some potential solutions to try and resolve the issue:

    1. Verify File Extensions: Double-check that all the files you are trying to load have the correct file extensions (.xlsx for Excel files). Ensure there are no hidden file extensions or incorrect extensions like .xls or others.
    1. Check File Integrity: Verify that the files you are trying to load are not corrupted. Open them manually in Excel to ensure they open without any issues. If a file is corrupted, try to recover a previous version or use a backup.
    2. Use Folder Path in Power Query: Instead of selecting individual files, use the folder path as the source in Power Query to load all files within that folder. This can help simplify the process and ensure all files in the folder are included.
    3. Check File Permissions: Ensure you have the necessary permissions to access the files in the folder. Make sure the files are not protected or encrypted with passwords that prevent Power Query from reading them.
    4. Check Compatibility: If you are using an older version of Excel 2016, make sure the files you are trying to load are compatible with your version. Check for any features or functions used in the files that might not be supported in your version of Excel.
    5. Use "Binary" Option: If you suspect the file format might be the issue, you can try using the "Binary" option in Power Query to load the files. In the Power Query Editor, go to "Home" > "Advanced Editor" and add , BinaryFormat.Binary at the end of the file path in the source step. For example:

    Source = Folder.Files("C:\YourFolderPath", BinaryFormat.Binary)

    1. Update Excel and Power Query: Make sure you are using the latest version of Excel and Power Query. Microsoft regularly releases updates that may resolve known issues.
    2. Test with a Single File: Try loading individual files from the folder one by one using Power Query. This will help you identify if a specific file is causing the problem.

    If none of the above solutions work, it's possible that the issue might be more complex or specific to the files you are working with. In such cases, you might consider seeking help from technical support or forums dedicated to Power Query and Excel. Since no one has answered it for at least one day or more, I entered your question in various AI’s. The text and the steps are the result of various AI's put together.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

Resources