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”
NOTE: The limitation is 100,000 rows or 1 Million Values, which ever comes first
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.
Hi Gilbert,
Do you know any way how to workaround limitation?
Hi Dejzi
Could I ask what limitation are you referring to?
NOTE: The limitation is 100,000 rows or 1 Million Values, which ever comes first
Thanks Dejzi, good point
I will update the original blog post.
[…] 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 […]
Thanks for sharing such an information on Exporting a Power Bi Visual data to a CSV File in Sharepoint , it was very helpful. Keep posting such blogs.
Pleasure, thanks for the awesome comment.
Hi Gilbert,
Thank you for sharing, it is very useful. I always get the results as one column and the data is separated by “,”
Any thoughts how to get the results as a table with many columns ?
Thank you.
Hi Ahmad
That is to be expected because the results are in a CSV file which is comma separated.
If you open the CSV file in Excel it should automatically put the data into separate columns.
Hi Gilbert,
Thank you so much for the quick reply. Do you know a way to get it separated automatically? without the need to import it in an Excel file, as I want to have a fully automatically process with a table at the end. I want it also from Power-Automat, so the button in Power-Bi isn’t a good way for me as it requires to open the file and press the button.
Thank you very much.
Hello there!
I am trying to use the connector Run a query against a dataset but it gave me an error that says PowerBIFeatureDisabled & DatasetExecuteQueries is not enabled for the tenant. Do you happen to know why? Is this something to do with my company configurations in PowerBI?
Hi Jane,
Please chat to your Power BI Admin and ask them to allow you access to the following section in the Tenant settings
Dataset Execute Queries REST API
Thank you
Hi and thank you for this post.
I was wondering wheather we are able to do the export into .xlsx and avoid a slow method using the loop ‘Apply to each’? This is really great and quick way how to get the .csv but if we would like to work further with a table in PA we would appreciate such output. Thank you
Hi Martin
I am pretty sure that you could change it to use an Excel file in Power Automate. What I have done in the past is to create an Excel Template file saved in SharePoint.
I then copy this template file into a new location in SharePoint. Once copied I then insert the data into the copied Excel file.
This is awesome.
Question though: is it possible to query a dataset that is shared with you, but you’re not a member of the workspace where that dataset is located?
Hi Peter,
As far as I know you would need to have read permissions to the dataset in order to run the DAX query?
Do I need to be an Admin of the workspace where the dataset is located? Or will Viewer be enough?
Hi David,
I am not 100% sure but I do think having read permissions on the underlying dataset should work?
The Viewer role grants you access to view the report, but not necessarily the dataset permissions which are a different set of permissions.
Thanks for the steps, i tried replicate the same. But, i was unable to get the data to CSV partially. I have 80K rows to export to an csv file, but it can only about 20k or so. Is there a limitation to the csv file size. In my case, it not move more than 4MB. Any solution here?
Hi Keerthi
As far as I know that is roughly the file size limit I would suggest trying to split the file or reduce the amount of columns?
Thank you, Gilbert, as suggested I was able to split the file into multiple and got it uploaded to the SharePoint. Can you help on how i can convert a csv file to xlsx in a table format.
Hi Keerthi,
If you are looking to convert it I am not aware of any way to automate this. If you are using Power BI, I would just consume the CSV file?
Hi Gilbert,
Yes, i am using PBI, I tried import the CSV file directly from the sharepoint, but it’s not loading properly. Some data were imported to dataset unevenly. Any suggestion?
{“error”:{“code”:”PowerBIFeatureDisabled”,”pbi.error”:{“code”:”PowerBIFeatureDisabled”,”parameters”:{},”details”:[{“code”:”DetailsMessage”,”detail”:{“type”:1,”value”:”‘DatasetExecuteQueries’ is not enabled for the tenant.”}}],”exceptionCulprit”:1}}}
I am getting this error, could you please help
Hi Pankaj ,
From the error this feature is currently disabled in the Power BI Tenant settings.
Please chat to your Power BI tenant admin and ask them to enable it for your login.
Hello how are you?
I have a question, when I have filters to be applied to filter the table, how to make it work in Automate.
Here’s an example: Note that in the first query I can get exactly the data for that period, but in the other, it doesn’t work.
First query:
DEFINE
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES(‘dCalendario'[data])),
AND(‘dCalendario'[data] >= DATE(2022, 8, 1), ‘dCalendario'[data] = 0.5
)
VAR __DS0FilterTable3 =
FILTER(
KEEPFILTERS(VALUES(‘pFiltroSaidaRenda'[Value])),
‘pFiltroSaidaRenda'[Value] >= 0.5
)
Query not working:
DEFINE
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES(‘dCalendario'[data])),
AND(
‘dCalendario'[data] >= MIN(dCalendario[data]),
‘dCalendario'[data] = MIN(pFiltroEntradaRenda[Value])
)
VAR __DS0FilterTable3 =
FILTER(
KEEPFILTERS(VALUES(‘pFiltroSaidaRenda'[Value])),
‘pFiltroSaidaRenda'[Value] >= MIN(pFiltroSaidaRenda[Value])
)
Hi Rafael,
What you would need to do is to then put the filters you need as a variable in Power Automate which you can then pass through to the DAX query?
Hi, Thank you for the tutorial. Instead of creating a new file each time, will it be possible to append the data onto an existing Excel online file sitting in a sharepoint site? Thanks.
Hi Jim,
Yeah that can be done in Power Automate just look to change it from Create an existing file to appending rows to an existing table.
Hello Gilbert,
This is working for me perfectly, but there is one issue that I am trying to solve, and it appears in your excel snippet as well. I am trying to have the resulting csv column names be exactly what I have in PowerBI, but it is returning the table name as well. Everything I seem to be attempting keeps returning errors.
Hi Scott,
Unfortunately there is no way to change the output as it stands today.
You would need to use another application to rename the columns.
Hi, thanks for sharing. Was quite helpful for being able to actually use the Run a query against a dataset.
I have a question that I guess should happen to a lot, when you dont want to create a file, but to add rows onto a file. I tried it with the built-in feature from Excel to add rows to a table, but does not work as adding row. Ideally attaching a timestamp to it.
Hi Matias,
I think that this could possibly be done but it would require you having to first save the CSV and then look at appending that to an existing table.
The difference is that the initial file is a CSV and I think that is where the challenge comes in.
Thanks Gilbert, I solved it diferent. In case this is useful for someone. I connect my power bi to a folder (one of the built-in options). Then as a result you get the CSV files ordered by the dynamic name, as in the example above. Using Power Query you can combine and clean the data. BR
Hi Matias,
That is a great way to solve it well done. And thanks for letting me know.
Hello, thank you very much for your contribution to the community. I have a doubt if in the case I need the end user could download the file on his computer?
Hi Heric,
If that is the case what you can do is to use PowerShell to download the file to your computer?
Ok thanks for answering, it would not be feasible because there are more than 50 different end users and we all handle PowerShell, it occurs to me if there is a way to integrate a flow of PowerApps for the end user with a button to open a form to enter your email and then through power Automate send the file to the user’s email?
Hello Gilbert,
This works exactly how I need it to without using R script and downloading locally…except…the output column names has the table name. Is there a way to remove the table name?
Hi there,
Unfortunately that is the way it exports via the API at the moment. There are other ways to change the export, but you would need to use DAX Studio or PowerShell to achieve this.
Hi Gilbert,
Thanks for the posting! I have tried the method but it took forever in the last step (create file in the Sharepoint). Have you had this issue before?
Hi Ivy,
It could depend on the DAX used to export the data. As well as how much data their is to export?