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!
Now one can extract data from PowerBI dataflow via MSFT Fabric dataflow gen2 .
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 : )
I am interested in that solution
could you please describe in a few steps what steps you use in PA (Power Automate} to manage this output
Once you publish the paginated report, you can use one of the predefined PA templates to save the file to a locaiton - these are the 3 standard templates that are showning for me - pls. see the attached image.
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
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.