• 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

P

PaulFogel123

Member
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
 
AlanSidman said:
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
Click to expand...
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.
 
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.
 
Chihiro said:
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.
Click to expand...
"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.
 
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.
 
You must log in or register to reply here.
Back
Top