cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Import Power BI Dataset or Dataflow table into Excel data model

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.

5 REPLIES 5
Highlighted
Community Support
Community Support

Re: Import Power BI Dataset or Dataflow table into Excel data model

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

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.

Highlighted
Frequent Visitor

Re: Import Power BI Dataset or Dataflow table into Excel data model

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.

Highlighted
Advocate III
Advocate III

Re: Import Power BI Dataset or Dataflow table into Excel data model

Hi @jfchartier ,

 

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

Jaderson Almeida
Business Coordinator
Highlighted
Helper I
Helper I

Re: Import Power BI Dataset or Dataflow table into Excel data model

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?

 

 

Highlighted
Super User IV
Super User IV

Re: Import Power BI Dataset or Dataflow table into Excel data model

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

 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors