Exporting a Power BI Visual data to a CSV File in SharePoint
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.
Thank you so much for your awesome post. I was working on the similar business requirement and I have used this method. I have encountered with two issues. from 113 columns (that is a large dataset for comparing purpose) it has exported just 107 columns and the order of the exported column is different from the original table. By any chance if you know any technical reference for that would be much appreciated 🙂
Hi Saman thanks for the kind works and comment!
I am not sure of any limitations with regards to the number of columns. I would just ensure if you run the same query in DAX Studio that you get the number of columns.
I would once again use DAX studio to make sure that the order of the columns are in the order you expect?
This is really awesome, had been looking for this since long.
Thanks Akash for the kind words 🙂
Hi, thank you, it is very useful. As far I understand there will be extracted all data.
I just wonder if that would be possible to respect the extra filters of the data coming from slicers. For example when triggering the flow manually I woudl like to extract and generate csv file which would contain only sales of selected Sales Representative/s.
Cheers.
Hi Lukasz,
If you would want the slicers to be applied you would have to put that in as part of the query.
As I mentioned in my blog post I would suggest getting the data with the slicers being applied. Then use the performance analyzer to generate the DAX query for you.
Also in theory you could pass through the slicer options/details to a DAX query dynamically in Power Automate. To do this it would be a lot more complex but it can be done.
{“error”:{“code”:”DatasetExecuteQueriesError”,”pbi.error”:{“code”:”DatasetExecuteQueriesError”,”parameters”:{},”details”:[{“code”:”DetailsMessage”,”detail”:{“type”:1,”value”:”Failed to execute the DAX query.”}},{“code”:”AnalysisServicesErrorCode”,”detail”:{“type”:1,”value”:”3242524690″}}]}}}
My flow failed ? Is it because this method does Not work with Live Dataset /
Hi Vuong,
Yes that is expected because it only works on imported datasets.
[…] Gilbert Quevauvilliers builds a process: […]
It’s not a measure. The result of the DAX query is a table. Always. Sometimes it happens to be a single row single column table.
Hi Lutz
If it is a single row you can still return this as a table with the syntax
EVALUATE(ROW("Column Name',[Measure Name]))
Prior to finding your solution, I was never successful attempting to use the “Run a query against a dataset’ flow.
Assuming a misconfiguration issue existed within our company’s Power Automate/Power BI Online Service platforms, I’d given up ever again attempting to use this flow.
Very happy to have found your solution and successfully implemented it!
Hi Brian
Thanks for the kind words and I am so happy that you got it working!
For me, the output is not saving in Sharepoint as a csv file extension but instead as “#######Z_FILENAME File” extension (although Excel can still open it and parse it like a normal csv). And even though I tried exporting 85,000 rows, the file (once opened in Excel) only has around 65,000 rows. I cannot see any difference at all in my Power Automate script as compared to yours. Any theories as to what could be going on to explain this?
Hi Rob,
Thanks for the comments.
I can confirm that I can extract up to 100,000 in my query (NOTE: The limitation is 100,000 rows or 1 Million Values, which ever comes first)
Can you make sure that the TOPN is not set to a particular amount in your DAX Query.
[…] In my previous blog post I explained how to export data from a Power BI report to a CSV file here: Exporting a Power BI Visual data to a CSV File in SharePoint […]