Import Power BI Dataset or Dataflow table into Excel data model

[jfchartier]

Is it possible to import a Power BI Dataset or Dataflow table into an Excel data model? I've looked at using the Web.Content function with the Power BI REST API, but I keep running into an "Access to the resource is Forbidden" error and I haven't found any step by step directions on how to make this work in Excel. Before I spent much more time trying to figure this out I thought I'd ask if this is viable approach or if there is a better way. Having access to these tables would alleviate my need to reprocess all of my data when testing incremental data changes in my Excel data model.

[karo]

Hi,

Any updates?

I am looking a way to build "composite models" in Excel using connection to Power BI Dataset or

to import data from Power BI Dataset into Excel ...

Regards,

Karo

[v-juanli-msft]

Hi @jfchartier 

Based on my experience, connect to Power BI in excel is possible.

Thanks to contributors, You could refer to the following articles.

• In the Power BI Service:
  Analyzing in Excel, described in the official documentation.
• From Power BI Desktop:
  Export to csv or txt files using R-script. Read article here (only in German available).
  Export to an SQL database using an R script. Read article here.
  use Excel functions, 

        Import data from a Power BI Desktop file into Excel

• From Power BI Desktop/Service:
  official document: export data to excel

As for connecting to Power BI dataflow directly, i find no articles saying this.

You could firstly connect to Power BI dataflow with Power bi desktop, then try methods above.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jfchartier]

Hi @v-juanli-msft,

Thank you for all of your suggestions. It's nice that there are so many ways to get data in and out of Power BI/Excel. What I'm really looking for is a direct way to pull data from the Power BI Service into an Excel data model. I would like to be able to click Refresh Data in Excel and pull the latest data from a dataset/dataflow without any intermediate steps. The Analyze in Excel option gets close, but as far as I can tell, it only allows access to the data through PivotTables, which unfortunately don't seem to be accessable through Power Query. I know that I can create the PivotTable and then create a data table that references that PivotTable and then pull that data table in through Power Query, but that means I have to keep two copies of my data directly in Excel and it's a bit slow. It also appears that if we had set up data storage for our Power BI tenant I may be able to set up my dataflow to store on Azure Data Lake, which I think I could query, but it looks like that feature is still in preview and I'm not ready to try to push that through yet.

[RakeshSinghr]

Is any of these features being worked on for future releases:

- Connect Excel directly to Power BI Dataflows to build excel models

- Connect to Power BI Dataset as table ( not as pivot table)

there used to be a excel publisher download from power bi service which provides a ribbon within excel.

Is that been dicontinued? Is the only way to connect to Power BI Dataset is to download the odc file and use it to create a pivot table?

[Mariusz]

Hi @RakeshSinghr 

In the below video from this years, MBAS Will demonstrates a preview of how excel will be able to connect to PBI datasets, its towards the end of the video.
https://mymbas.microsoft.com/sessions/e19fedb9-97b7-41ea-83b8-d01f61ec6a77?source=sessions

Don't know if dataflows will follow or not 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[bajimmy1983]

Hi @jfchartier ,

Today I started looking for the same approach and ended up here and it seems there isn't a solution yet. 😞