Forum Discussion

jfchartier's avatar
jfchartier
Frequent Visitor
7 years ago

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.

Need Help

6 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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.

    • jfchartier's avatar
      jfchartier
      Frequent Visitor
      7 years ago

      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.

      • bajimmy1983's avatar
        bajimmy1983
        Advocate V
        7 years ago

        Hi jfchartier ,

         

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

  • karo's avatar
    karo
    Advocate V
    4 years ago

    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