cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
michaelshparber
Helper V
Helper V

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

2 ACCEPTED SOLUTIONS
v-frfei-msft
Community Support
Community Support

Hi @michaelshparber ,

 

Based on my test, it is not supported yet currently.You can come up a new idea about that and add your comments there to improve Power BI and make this feature coming sooner.


https://ideas.powerbi.com/forums/265200-power-bi-ideas

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

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 NavigationPower 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.

View solution in original post

18 REPLIES 18
mikesmith_bp
Frequent Visitor

Looks like you have the same build I do (2108).  I think we need to wait for our next Excel update before this will work.

hugo50
Frequent Visitor

Hi, 

 

I read it is now possible to connect Excel PQ to dataflows, do you guys know how to ? I can't find "dataflow" as data entry option in excel (it says I have the latest version).

 

Regards

Hugo

Did you ever figure this out?  I'm also very interested in finding a way to connect Excel to a DataFlow.  I'm getting very tired of using different data sources when I have to use Excel rather than Power BI.  

hugo50
Frequent Visitor

In Power BI's "Get Data" dialog there's an entry for "Power BI dataflows" and "Power Platform dataflows". The link only mentions Power Platform dataflows. I wonder if this will include both?

Same boat here - would like to be able to consume powerbi dataflow data in excel, appears that the option should be present, but cannot find anywhere that explains how to do it.

v-frfei-msft
Community Support
Community Support

Hi @michaelshparber ,

 

Based on my test, it is not supported yet currently.You can come up a new idea about that and add your comments there to improve Power BI and make this feature coming sooner.


https://ideas.powerbi.com/forums/265200-power-bi-ideas

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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 NavigationPower 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,
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

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.

@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".

 

DataInsights_0-1652638056514.png

 

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

 

DataInsights_1-1652638211228.png

 

----------

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

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.

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)

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

Did anyone work out when this will be implemented or a work around?

 

i ahve tried to use the suggested: =PowerPlatform.Dataflows(null) - but this doesnt work and just errors.

fjamB
Regular Visitor

I am having the same problem, it shows an error when connecting.

 

I have the following excel version:

Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.20900) 64-bit

Version 2108 (Build 14326.20910)

 

My steps are:

1. Blank query

2. Paste code in advanced editor

 

let
    Source = PowerPlatform.Dataflows(null)
in
    Source

 

 

Then it shows this error:

 

fjamB_0-1652821033878.png

 

Any help is welcome

 

Regards,

F.

 

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors