How to Run a Query against a Dataset Using Power Automate?

If you have a requirement to run a query against a dataset using Power Automate, then this Power Automate tutorial is for you.

Yes, running a query against a large dataset from the Power BI service is possible. Follow the steps explained in this tutorial, I’m confident you will also run a query against a large dataset using flow.

Scenario:

The flow uses the On Power BI button click trigger and runs a query against a dataset presented in the Power BI report. Finally, create a CSV file with the values in the SharePoint Document Library.

Run a query against a dataset using Power Automate

Let us see how to run a query against a dataset using Power Automate flow,

To achieve this, follow the below steps:

Step-1:

• Log in to the Power BI desktop to generate or select the Power BI report you want to get data from the datasets.
• Add a table visual from the visualization pane, and select the field from the fields pane. Here, I have selected my generated Financial Data Report; in the table visual, I have the below columns: Product, Country, Segments, Profit, and Date.

Step-2:

• From the ribbon tab, click on Performance Analyzer, and a new pane appears on the right-hand side. Select Start Recording, and from the table visual, select the Analyze option. It generates or writes a DAX query for us.
• Expand the line by clicking on the +, then click on Copy Query. The TOPN by default, returns 500 rows, to get large data, we can change the TOPN rows number value and pass the query in a run query against a dataset action in Power Automate flow.

The DAX query generated as below:

// DAX Query
DEFINE
  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        ROLLUPGROUP(
          'financials'[Product],
          'financials'[Country],
          'financials'[Segment],
          'financials'[Date]
        ), "IsGrandTotalRowTotal"
      ),
      "SumProfit", CALCULATE(SUM('financials'[Profit]))
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(
      502,
      __DS0Core,
      [IsGrandTotalRowTotal],
      0,
      [SumProfit],
      0,
      'financials'[Product],
      1,
      'financials'[Country],
      1,
      'financials'[Segment],
      1,
      'financials'[Date],
      1
    )

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  [IsGrandTotalRowTotal] DESC,
  [SumProfit] DESC,
  'financials'[Product],
  'financials'[Country],
  'financials'[Segment],
  'financials'[Date]

Step-3:

• Save the changes and Publish the Power BI report from the Power BI Desktop to the Power BI service,
• Select the workspace and click publish

Step-4:

Open the report in the Power BI service, add the Power Automate visual, and don’t add any fields to it. Directly click on the Ellipsis icon -> Edit option as shown below:

Step-5:

Once you click the edit option, it redirects to the Power Automate flow. Expand the +New option and choose instant cloud flow.

Step-6:

It will automatically select the “On Power BI button and clicked” trigger action. Enter the flow name.

Step-7:

Select +New step and choose the run a query against datasets action. Provide all the required parameters.

• Workspace -> Select the Workspace where your Power BI report and Datasets are presented.
• Datasets -> From the dropdown, select the dataset that is used in the Power BI report.
• Query -> Paste the copied DAX query that we copied in Step 2. Modify the DAX query based on the requirement.

Here I have modified my generated DAX query as the TOPN values as 1000, to get the large amount of data.

// DAX Query
DEFINE
  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        ROLLUPGROUP(
          'financials'[Product],
          'financials'[Country],
          'financials'[Segment],
          'financials'[Date]
        ), "IsGrandTotalRowTotal"
      ),
      "SumProfit", CALCULATE(SUM('financials'[Profit]))
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(
      1000,
      __DS0Core,
      [IsGrandTotalRowTotal],
      0,
      [SumProfit],
      0,
      'financials'[Product],
      1,
      'financials'[Country],
      1,
      'financials'[Segment],
      1,
      'financials'[Date],
      1
    )

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  [IsGrandTotalRowTotal] DESC,
  [SumProfit] DESC,
  'financials'[Product],
  'financials'[Country],
  'financials'[Segment],
  'financials'[Date]

Step-7:

Add +New step, choose the Create CSV table, and pass the first table rows from the previous action.

Step-8:

Select +new step and choose a create file action, Provide the SharePoint site address, Folder path, and File Name with extension and pass the dynamic file content value.

Step-9:

Our complete flow looks like below:

Step-10:

Click Save to save the flow, Select Save and Apply, and choose Back to Report.

Step-11:

Now save the Report, open the report in Reading View, and select the Export Large data button in the Power BI report, the flow will start triggering.

once the flow runs successfully like below:

Now you can see the file CSV file is created in the SharePoint Document Library.

When we open the CSV file you can see the large data fetched by running a query against a data set action from Power BI Report using Power Automate flow.

This is how to run a query against a dataset using Power Automate flow.

Conclusion

In this Power Automate tutorial, we learned how to run a query against a dataset get data from a Power BI report, and create a CSV file in the SharePoint document library using Power Automate flow.

You may also like the following tutorials:

• How to get more than 100 items using the Get items action in Power Automate?
• Export Power BI Report to Excel using Power Automate
• How to check if an array contains value using Power Automate?