Power Query bug when accessing xlsb files

[James_Denman_Wall_GmbH]

Dear Community,

This is to report a bug in Excel (Windows 10, Excel build 11601.20204 click-and-go, Version 1904 - Office 365 monthly channel) as follows:

When opening a data source from the Power Query window (Home\New Source\File\Excel):

 - opening the same file, saved in xlsb and xlsm format produces different results. With a minimal file (one worksheet containing one 2x2 data table, no macro), the choice shown in the "Navigator" dialogue in Power Query is different for the xlsb file vs. the xlsm file (the xlsb file doesn't offer the data table, while the xlsm file does) -> this is clearly an unwanted difference in behaviour. I attach the same file ("1_Table_no_macro") in xlsb and xlsm format.

 - Power Query sometimes refuses to open xlsb files, stating "External table is not in the expected format.". While this can happen for large files, I have managed to reproduce the effect using two small, only slightly different files ("Mini file_NOK.xlsb" and "Mini file_OK.xlsb"). The difference is in the definition of the name "a" under Formulas\Name Manager.

Could you please look into this and fix it?

Thanks and best regards,

James Denman

[#2]

@James_Denman_Wall_GmbH 

Hi James

You are quite right, this was the situation and I complained about it to Microsoft when I was at the MVP Summit.

I'm pleased to say that this has been resolved, and tables show up quite clearly in .xlsx, .xlsm and .xlsb files. It is certainly working on the Insider fast channel  (hich I am running) and no doubt that will make its way into the Monthly channels and General Availability fairly soon.