In this blog post I am going to demonstrate how to use the new Power Automate Flow to extract data from a DAX measure into a SharePoint CSV file.

I got this idea after reading the blog post from the Microsoft Power BI Team: Unlocking new self-service BI scenarios with ExecuteQueries support in Power Automate | Microsoft Power BI Blog | Microsoft Power BI

The great news is that this works on Power BI Pro, Premium Per User and Premium.

Creating the DAX Query to extract the data

The first step is to either write or create the DAX query which will be used to extract the data into a CSV.

To do this I created my Power BI dataset with the report and uploaded it to the Power BI Service. I did this first because when I later run the query I want to run it against this dataset.

I am going to get the DAX query to export the data from the table “Sales by Sales Territory”

As learnt from Patrick Le Blanc and I quote, “I am not lazy just efficient”

What this means instead of me having to write the DAX query I will use the Performance Analyzer built into Power BI desktop to get the query I need.

I click on View in the ribbon and then clicked on Performance analyzer.

Then in the visual I clicked on the “Analyze this visual” which would then refresh the visual.

Next, in the Performance analyzer window I expanded the “Sales by Sales Territory” where I could then see the option to copy query as highlighted below.

I then copied this query to my notepad. This allowed me to get the DAX I required without having to write any DAX.

Using Power Automate to extract the DAX query to a CSV file in SharePoint

Now that I have my DAX query, I can move to Power Automate and create a flow which will allow me to extract the data into a CSV file using the DAX query.

Before I started the Power Automate flow, I made sure that I had access to a SharePoint site and folder where I wanted to extract the data to.

I then went to Power Automate and clicked on Create, I then Scheduled cloud flow. This allows me to run it on a schedule.

I then created my flow with the following details, which will run every Sunday

Next is where I now wanted to extract the data from my Power BI report/dataset into the CSV file.

To do this I selected New Step, searched for Power BI and then clicked on “Run a query against a dataset”

From the drop down I selected the App Workspace and dataset name where I can going to run my DAX query

Now, in the Query text I copied my DAX query I had copied earlier from the Performance analyzer.

The next step was to then create the CSV file which I did by clicking on New Step and searching for CSV, and selected “Create CSV table”

When I had the “Create CSV Table” and I clicked in the From box I then selected “First table rows” from the list as shown below.

This is then what my “Create CSV Table” looked like once I had selected the “First table rows”

Then I clicked on New Step and selected Create File in SharePoint

I then put in the Site Address, Folder Path.

Then for the File Name, I wanted to put in the date as part of the filename and I did it by doing the following.

I first put in the name I wanted which was “Sales Extract -” I on the right hand side window I clicked on Expression as highlighted below.

I then put in the following expression to give me the date in the format I required ‘MM-dd-yyyy’

formatDateTime(utcNow(),’MM-dd-yyyy’)

I then clicked Ok.

I then added .csv at the end of the File name

This is what the File Name looked like once completed.

Finally for the File Content I clicked in the box and selected Output as shown below.

I then clicked on Save.

Testing the Extract process

The final step is to test the extract process to make sure it works as expected.

I clicked on Test on the top right-hand side.

Next, I selected Manually and clicked “Test”

After clicking on Test I then clicked on Run Flow

I could then see in Power Automate that my flow ran successfully.

To double check this, I also went to my SharePoint folder and found the file.

I could then open the CSV file and see the details.

Summary

In this blog post I have demonstrated how to create the DAX measure in a quick and easy way which is then later used in the Power Automate flow which then extracts the data to a CSV file which is stored in SharePoint.

I hope you found this useful. If you have any questions or comments, please leave them below.