• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using .xlsb format with Power BI

Hello,

I want to experiment with Power BI as a companion to my Excel forecasting model. But, just as the source tables for Power Query must be in xlsx format, my understanding is that the source data for Power BI must also be in xlsx format (if the source data is Excel tables). My forecasting model is in xlsb format to limit file size, and this model and its tables would be the source data for Power BI to import.

Is there a workaround for this? One that would be turnkey for users, without manual steps or automated? Could I export the model's data to SQL and then import it from SQL to Power BI? Or is there a more direct and easier route?

Paul
 
Have you read this link. https://community.powerbi.com/t5/Desktop/is-Xlsb-file-stilll-doesn-t-support-in-power-BI/m-p/580751

What about exporting your XLSB file to an Access Table and then bringing that table into PQ or PBI
I think that could work but I don't know Access well enough. I need an automatic, turnkey solution so that Power BI could be used in tandem with Excel--a user would update his forecast and then open Power BI for some graphical analysis. If possible, maybe VBA that would export the needed tables from the Excel model and place them somewhere in another format for importing upon opening Power BI.

What I'm really after is simply those interactive Power BI graphs. My users don't need to construct them in Power BI and wouldn't know how, anyway. I'd build them and make them available. I'd like to have them right inside the Excel model or just be a click away.
 

Chihiro

Excel Ninja
Have you tried it? I routinely connect to *.xlsb files without issue. Using both PowerBI and PowerQuery in Excel.

If you have, what is the exact error message that you receive? And what is the version number of your PowerBI desktop?

Edit: If you mean that you can't query Named Table. That is because OpenXML sdk can't be accessed for *.xlsb file.
By keeping individual tables in separate sheet, you can query it without issue.
 
Have you tried it? I routinely connect to *.xlsb files without issue. Using both PowerBI and PowerQuery in Excel.

If you have, what is the exact error message that you receive? And what is the version number of your PowerBI desktop?

Edit: If you mean that you can't query Named Table. That is because OpenXML sdk can't be accessed for *.xlsb file.
By keeping individual tables in separate sheet, you can query it without issue.
"By keeping individual tables in separate sheet, you can query it without issue."

Are you saying that a named table in its own separate sheet in an xlsb file can, in fact, be accessed?
 
I tried it in Excel, from an xlsb source file to an xlsb destination file. It works! I assume that Power BI would the same way.
 

Chihiro

Excel Ninja
It doesn't matter what type of destination file you are using (xlsx, xlsm, xlsb i.e. as long as it's in format where PQ is supported). Just the source file data structure matters when using Power Query to query external workbooks.
 
Top