I have Power BI dataflows (with scheduled refreshes) on my Power BI services. I want to export data from the tables of these dataflows to csv files. These csv files reside in SharePoint. This activity should happen on weekly basis.
What is the best solution for it ? We have been trying couple of things like creating new dataset from dataflow in import mode and adding R/Python script to do this job. But I feel it's not optimal.
Thank you in advance!
Have you tried paginated reports - you can build a dataset from the dataflow and then a paginated report on top of the dataset that can be exported to a .csv from within power automate - there are several hops indeed but that is the solution which I'm currently using which ironically I was looking for ways to optimize that brought me into this discussion : )
Somebody on the forum decoded the way that dataflow CSVs* are stored in the Azure cloud, and pulled these via Power Automate (I think). But for regular users dataflows cannot be accessed directly. You need to use a dataset to connect to the dataflow, and then you can run DAX or XMLA queries against that dataset (as you seem to be doing) but that's highly inefficient.
It may be easier to talk to the people who fill the dataflows, and ask them for the souce data instead. Who knows, their source data may already be in CSV format.
*) Yes, dataflows are CSV files!!!
Thank you Ibendlin for your reply, but dataflow is created with different sources and those are not in CSV format.
I am still trying to find the solution. Currently we are using R script to export to excel that just works fine on desktop version but not working on Power Bi services. In the older community posts I read that this is not possible due to security reasons on Power Bi services, not sure if that's still the case.
Dataflows are now an available connector in the GA Excel versions. You can write power query from within the excel workbook, and retrieve like any other data source query. Using excel as the vehicle introduces excel's limits.
If you can access the dataflow, you should be able to build a dataset. I also have had success consuming dataflow data via a published datasets using DAX / XMLA as suggested above
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!