Pull data from a Power BI table using Power Automate

This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Pull data from a Power BI table using Power Automate
Use Power Automate to pull data from Power BI using a DAX query.

Posted by Sam Lowrie on 23 September 2024

Power BI can connect to, transform and visualise data. Which is great but how do we get that modified data out?

One way is using a Power Automate flow to connect to the published Power BI dataset.

Take this Power BI table visual, I want to extract this data.

First thing we need is to extract the DAX behind the visual. To do this go to View | Performance analyzer and toggle this on.

When you click Performance analyzer a new pane will appear on the right.

The analyzer lets you see information about each visual on the page like load times. Click on Refresh visuals button.

Clicking Refresh visuals loads all visuals on the current page.

Click on the + icon by the table visual and choose Copy Query to add the DAX code to your clipboard.

We now have the code Power BI uses to generate the visual.

Now we can open Power Automate in the browser and create a flow to extract the data using the copied script.

Create a new flow by clicking on New flow | Instant cloud flow.

You can choose from several triggers but here we will choose Instant cloud flow.

Give your flow a suitable name and choose Manually trigger a flow to create your new flow.

Manually triggered flows run when you choose but there are other options.

This will open up your designer where you create all the steps of the flow. Click on the + to add a new step.

Choose Add an action to see a list of the available options.

Search for Run a query against a dataset and you should see it under the heading Power BI.

We want to run the query copied earlier.

Select the Workspace and Dataset of your published report, then paste the code from your clipboard into Query Text.

Power Automate will run the code against the specified dataset.

Now in the top right choose Save and then click on Test to run your flow.

You must save before selecting the Test option.

If you have run this flow before, you can choose Automatically to re-run with previous data.

For now choose Manually and select Test to try your new flow.

You can run the flow by clicking Test.

We should now see each step with a tick next to it. You can now select the Power BI step to see the returned data.

Click on the last step to see more information.

Each step has an Inputs and Outputs section and by choosing Show raw outputs we can see our data.

Clicking here will show you a JSON file of the returned information.

Now you can see all the returned data in the body section of the output.

The headers section includes useful information like run Date.

From here you could email this data or store it somewhere like a SharePoint list!

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

Head office

Kingsmoor House

Railway Street

GLOSSOP

SK13 2AA

London

Landmark Offices

99 Bishopsgate

LONDON

EC2M 3XD

Manchester

Holiday Inn

25 Aytoun Street

MANCHESTER

M1 3AE

Visual C# training

Excel training courses

Power Automate training

Power BI / DAX training

Python training courses

SQL training courses

VBA (macros) training

Site map

Terms & Conditions

BLOGS BY TOPIC

BLOGS BY AUTHOR

Head office

London

Manchester