Fix XLSB File Errors in Power Query

A XLSB File error can come to you by surprise. 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?

Solution

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 XLSB File Error 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!