PBI connected to dataflows - how to export power query result into file on SharePoint on schedule

Question

Hi!I have a power query connected to multiple dataflows in one of the workspaces. I connect to dataflow tables, do a lot of transformations and spit out one table as a result. I want to save this table into Sharepoint (excel file would be best). My end goal is to upload my pbi file to the workspace so I can schedule a refresh once a day and I would like it to result in new overwritten file on Sharepoint with query table inside.I have everything ready, just missing the part with saving the final query table as a file on SharePoint.Any directions would be much appreciated!Thank you!Blazej

jennratten · Answer

Hello - you can do this using a Power BI paginated report combined with Power Automate. I have included info and helpful links below.&nbsp;If you are trying to stay only within Power BI, then you can create a Power BI paginated report and create a subscription to the report and choose to have it emailed to you as an Excel file.&nbsp; If you use Power Automate as well, you can have it automatically added to Sharepoint or any other array of options.&nbsp;Paginated reports does require the workspace be assigned to a dedicated capacity, but if you are using multiple dataflows together, that shouldn't be a problem.&nbsp;&nbsp;&nbsp;Please let me know if you have any questions about the info below or if neither of these will work for you, please explain why so that I may try and find a different option.&nbsp;Paginated Report in a dayPaginated Reports FAQSave a Power BI Paginated Report to SharepointGetting Started with Power Automate&nbsp;Power BI Paginated Report + Power AutomateYou could build a Power BI paginated report&nbsp;which would reside in your Power BI workspace.&nbsp; Then in Power Automate, you can use the flow template named Save a Power BI Paginated Report to Sharepoint. It is very straight-forward and doesn't require coding.&nbsp; You can choose the schedule and frequency at which you'd like this to be done from within the flow, and you can also specify the type of file to be exported, such as Excel.&nbsp;Here are some screenshots of what the flow looks like in Power Automate.Choose the schedule and the Power BI workspace, report and export format.&nbsp; There are additional fields for the paginated report but if you don't have any parameters or row level security to specify then you can skip those.&nbsp;Then you select the Sharepoint site and folder, configure your file name and click save.&nbsp;&nbsp;&nbsp;

resty · Answer

Hi&nbsp;jennratten,Thank you for your detailed reply!I have explored this solution, but I don't have acccess to any premium workspaces and this option is not possible in my case - I can't publish the paginated report so I could subscribe to it.I use Power Automate for other purposes so grabing the file from email and putting it on SharePoint wouldn't be the issue, I am just missing getting a phisical file from query results step.Thank you,Blazej

v-jingzhang · Answer

Hi&nbsp;Resty&nbsp;
&nbsp;
You can export data from a table with Python or R Scripts when refreshing the dataset. But this requires Personal mode data gateway installed to work in the service.&nbsp;
How to Auto-Schedule Data Export From Power with Python
Schedule Automated Data Exports from Power BI using a simple R/Python Script
&nbsp;
Best Regards, Community Support Team _ Jing

Forum Discussion

PBI connected to dataflows - how to export power query result into file on SharePoint on schedule

Related content

connection to dataflows

Connection Sharepoint folders via Dataflow

Incremental Refresh on Dataflow sourced from SharePoint

dataflow connection

OData Error When Connecting to SharePoint Online