fbpx

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?

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 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.

14 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.

Share Your Thoughts!