Fix Error When Reading XLSB File in Power Query #10

Power Query - Fix Error When Reading XLSB File in Power Query

Recently one of my clients built a Power Query solution. In one of the steps, the query ran into an unknown error. The query started with data from a CSV file. And then merges different Excel files to enrich the dataset. One of the merges results in the following error message.

“An error occurred in the ‘TBL_MAPPING_B2G’ query. DataFormat.Error: External table is not in the expected format. Details: TBL_MAPPING_B2G.xlsb”

This message indicates the name of the Query that causes an error. Yet it’s not very clear what’s going on. The table is not in the expected format…

Error Checking

So I did some basic error checking. This was my approach.

  1. First I examined the source data. Does the source data contain any errors? Strange formatting? Perhaps symbols that are not accepted? Inspecting the source file didn’t give me any indication of strange symbols or errors. Data seemed structured, without errors or strange symbols.
  2. Next, I looked at what the query looked like after importing it in Power Query. Are columns perhaps defined with the wrong data type? Do errors occur in any of the columns? Again, I found nothing noteworthy.
  3. The error message occurs in the step where the base query merged with ‘TBL_MAPPING_B2G’. To make sure nothing went wrong there, I perform the merge once more by deleting the old step and doing the merge again. The error message appeared again. The riddle continued.

I’m slowly running out of ideas. The query involves many different merges. So how is this particular merge different from the others?


The clue to the solution appeared when inspecting the source files. The merge resulting in an error is the only file saved with a .XLSB file type. All other files types are either .XLSX or .CSV. Perhaps this influences how Power Query reads the data. Since the file causing the error contains macro’s, I save it as .XLSM. Next I change the source file in Power Query, press refresh and VOILA! The error message is gone! That’s strange. The error message indicates the table is not in the expected format, yet changing the file extension solves the problem.

The exact reason of the error occurring is still unclear to me. It appears there is a bug in Power Query when reading the .XLSB file. Yet changing the file type seems to solve the issue. Checking Google for similar issues doesn’t give much input either. It’s not much, but on this page someone seems to have a similar problem. Until it’s fixed, the way to go is to change the file format.

Have you experienced similar issues? And what was your solution to it? Please let me know in the comments below. Also make sure to check out how to import files from a Folder if you want to expand your Power Query superpowers!



Rick de Groot

My name is Rick and my goal is to help you advance with Excel and Power BI. I believe learning is one of the great pleasures in life and I want to share my knowledge with you to help you improve your skills.

17 Responses

  1. This type of issue i usually face in .XLS file. I have to extract the data from ERP data is in .XLS file, It is arround 400 file. I am using the feature Import from Folder and Combining through Power Quey techniques of merging Excel Filed, During Merging the files in the last Step i,e “” Excel.Workbook([Content])”” .I always get an error.

    if you have any sol of it please share with us.

  2. Hi RIck, By coneverting into xlsx by “Save As”. Then in gives no error, but assume to my Query if i have 500 file of XLS that has been extracted from an ERP, I open each and every file and save it ,

    • Hi Muhammad. That’s very frustrating. I suspect this is due to a bug in Power Query 🙁 You could investigate the possibility of your ERP system saving files with a different extension. If that’s not possible, I’m afraid I don’t have the solution for you at this moment.

  3. Power Query does not load .xlsb files in Excel or Power BI, they need to be converted to .xlsx or .xlsm first. There is an option to vote this in as a future feature.

    • Power Query (Get and Transform) on my version of Excel (Office 365 64 bit, Version 1904, build 11601.20204) has the exact opposite problem. It will not load .xlsx, but will load .xlsb, very strange. The error I get when navigating from any workbook to pull in in data from any other .xlsx workbook is “DataFormat.error: The input couldn’t be recognized as a valid Excel document. Details: Binary”.

      I would expect this problem to happen when opening a .xlsb binary file. Instead, it happens when trying to connect to an .xlsx file (any .xlsx file, no particular one). So I have to work around this bug by changing all my queries to use .xls, .xlsm or .xlsb instead of .xlsx. I believe this bug was introduced in a recent version of Excel. Has anyone else seen this problem?

      • I solved this problem today. The issue had nothing to do with the source file being .xlsx, .xlsb or any other type. The cause was that I had inadvertently set the Maximum Allowed data cache to be 0 mb. The minimum recommended is 32 mb. I discovered the solution because, during testing, Power Query returned a meaningful message about “increasing cache size” instead of the meaningless message I had always gotten, “DataFormat.error: The input couldn’t be recognized as a valid Excel document. Details: Binary”. The DataFormat.error was not helpful, and I got the new helpful message as a surprise, and thus was able to solve the problem with the proper error message.

Share Your Thoughts!