How can I read data from a Power BI Dataset in a Power BI Dataflow or Microsoft Flow?

[KiranKr]

I would like to read transformed data from a published dataset in Power BI service (ex: daily snapshot of an aggregated data of the day such as number of patients with emergency, number of beds occupied by them in a certain room, number of doctors attended them etc,) in a Dataflow or a Microsoft Flow to store incrementally in a streaming dataset or some other data source for historical reporting. I do not want to connect to the original sources (ex: SQL server) from a dataflow or microsoft flow because I do not want to transform the data again since it requires complex transformations, which are already there in the Power BI dataset that is published. If you know or figured out a way, please let me know. If this feature is not available, please vote for this as an idea. I believe is a fundamental need. Thank you very much. 

[joko29]

Hello,
I have the same rewquirement. I transform Data in the context of Data Migration from Navision to D365,
I generated Tables di-pivoted (long vertical List), and I do a lot of calculations in DAX to apply the Migration Rules comming from process experts/consultants.
Now I would like to export these data to be used as inout files for D365.
a) there is no possibility to push converted Data directly from the Dataset/Dataflow Tables into D365 (guess there is no data adapter to D365)
b) I did not find an easy way to transform my verticall table in a pivoted table in DAX in my Dataset (if anybody has a solution, please tell)

c) My workaround at the moment is, to load the dataset into Excel as a vertival table and then read the table from another excel with Power Query to do the pivoting.  But this workaround with excel is a manual step and my goal ist to automate this process in PBI Service with refresing calendar. And I have some problems by loading in Excel (is another story)

My wish would be to read from a dataflow Data directly from a Dataset stored in PBI Service and put the refresh in the right order (or even the refresh logic would recognize this automatically). Then I could do the pivoting and other preparations for the INput-FIle for D365 very simple and load the D365 Excel File directly from PBI Serivce, which would be a complete automated process.
🙂 Has anybody an idea if this functionality is planned for the future?

[nickyvv]

Loading data from a PBI dataset is indeed not supported, and I doubt if it ever will be.
A dataflow is used for data prep, so ETL. As MS says and you can see in the below picture, dataflows are used to help organizations unify data from disparate sources and prepare it for modeling. So data modelling comes after a dataflow.

[desott]

Do you think it's possible to get data directly from the report or a dataset without using power automate to generate a .xls file? 

[stchln]

Hello KiranKr

There is a trick to write first to a .csv using R script but this is working only in Power BI Desktop (not in Power BI Service unfortunately). When the .csv is available then you can read from Power Automate

Example below: just creating a dummy table 

then you create a R Visual, allow R script, add the columns you want to export. From that you will see an editor with some comments. You just paste the 3 lines of R Script below (2 last lines are just to display what is written and also R script cannot end without displaying data)

write.table(dataset,file='C:/ML/test.csv', sep=",", append=F, row.names=F)
library(gridExtra)
gridExtra::grid.table(head(dataset))

you are done