Forum Discussion

michaelshparber's avatar
michaelshparber
Advocate V
7 years ago
Solved

Connect to a Dataflow with Excel Power Query

Hello friends, How do I connect to a Dataflow table from Excel Power Query? I don't see the same connectors as I see in Power BI - maybe I can install smth.? Thanks! Michael
Need Help
  • AJMcCourt's avatar
    AJMcCourt
    4 years ago

    It hasn't been properly rolled out yet, but I've figured out how it can be done (and it's really easy!). Not sure if this has been fully rolled out inside excel yet, I'm using excel 365 and it's working for me.

     

    In excel, do Get Data -> Other Sources -> Blank Query.

     

    The first line of your query needs to be:

     

    =PowerPlatform.Dataflows(null)

     

    If you've ingested a dataflow into Power BI before, this navigation will start to look very familiar. You'll need to sign in with your organisational account, and then you should see a table in the previous window show the records "Workspaces" and "Environments". Click "Workspaces", then under the "Data" field select "Folder" and it will drill down to the next level. You can keep navigating down in the same way, but I find the easiest way to continue is to then click the Navigation Cog in the "Applied Steps"  box and navigate exactly the same way that you would do in Power BI.

    Power Query Dataflow Navigation

     

    Congratulations! You've just connected Excel Power Query to your Power BI Dataflow! You can now interact with the dataflow in PQ exactly as you would any other source, and once you're done you can Load your data directly into your data model or a tab as usual.

AJMcCourt's avatar
AJMcCourt
Advocate I
4 years ago

It hasn't been properly rolled out yet, but I've figured out how it can be done (and it's really easy!). Not sure if this has been fully rolled out inside excel yet, I'm using excel 365 and it's working for me.

 

In excel, do Get Data -> Other Sources -> Blank Query.

 

The first line of your query needs to be:

 

=PowerPlatform.Dataflows(null)

 

If you've ingested a dataflow into Power BI before, this navigation will start to look very familiar. You'll need to sign in with your organisational account, and then you should see a table in the previous window show the records "Workspaces" and "Environments". Click "Workspaces", then under the "Data" field select "Folder" and it will drill down to the next level. You can keep navigating down in the same way, but I find the easiest way to continue is to then click the Navigation Cog in the "Applied Steps"  box and navigate exactly the same way that you would do in Power BI.

Power Query Dataflow Navigation

 

Congratulations! You've just connected Excel Power Query to your Power BI Dataflow! You can now interact with the dataflow in PQ exactly as you would any other source, and once you're done you can Load your data directly into your data model or a tab as usual.

  • DataInsights's avatar
    DataInsights
    Super User
    4 years ago

    AJMcCourt,

     

    Thank you for this awesome discovery! This will make a lot of Excel users happy. 🙂

     

    Community: here's the full query and screenshots to assist.

     

    1. Query:

     

    let
    Source = PowerPlatform.Dataflows(null)
in
    Source

     

    2. In the Data column for Workspaces, click "Folder".

     

     

    3. Click the gear icon on the Navigation step and navigate to the dataflow entity.

     

     

    ----------

     

    Another way to use Power BI data in Excel is to connect a pivot table to a published dataset. You can connect from Excel, or use the "Analyze in Excel" option in Power BI Service. Connecting to a dataset will enable you to use calculated tables, calculated columns, and measures. It's great to have the option to use dataflows or datasets.

     

    If you need to use formulas to pull dataset data into another sheet, configure your pivot table to use a table format:

     

    1. Show in Tabular Form

    2. Repeat All Item Labels

    3. Remove subtotals

  • M_Ulloa's avatar
    M_Ulloa
    Frequent Visitor
    4 years ago

    I can confirm that this works in Office 365. It's not exposed in the UI, but you can navigate to the Dataflows you have access to. I tried this same approach months ago (writing M code directly) and got an error message instead.

    • mikesmith_bp's avatar
      mikesmith_bp
      Frequent Visitor
      4 years ago

      Not working for me.  Which build of Excel do you have?  I have Version 2108.  The M code results in an error.  =PowerPlatform.Dataflows(null)

      • AJMcCourt's avatar
        AJMcCourt
        Advocate I
        4 years ago

        Microsoft® Excel® for Microsoft 365 MSO (Version 2202 Build 16.0.14931.20128) 64-bit

  • kaavyam's avatar
    kaavyam
    New Member
    4 years ago

    Hi, 

     

    I have office 365 but I still get error when I try to use your method to connect to dataflows. 

     

    is it still working for you?

     

    thanks, 

     

    Kaavya

  • mike_honey's avatar
    mike_honey
    Memorable Member
    4 years ago

    This worked well for me - thanks so much for the tip!

     

    So odd that they still haven't bothered to add it to the UI.

  • carlosrojaspdx's avatar
    carlosrojaspdx
    Frequent Visitor
    4 years ago

    AJMcCourt,
    Thank you so much for this post, I've been looking for months how to do this, it worked very well.

    Thank you, thank you

    Carlos

    • fcerullo's avatar
      fcerullo
      Advocate I
      4 years ago

      This is a Game Changer for me, THANKS!

  • hourir2's avatar
    hourir2
    Advocate I
    3 years ago

    Bravo!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    this is only available in MS 365 how about the other version of Excel like Excel 2021 it does't show or the =PowerPlatform.Dataflows(null) is not supported.

  • PBell's avatar
    PBell
    New Member
    1 year ago

    This is working extremely well, many thanks for this very good tip!