Refresh local query using MS Flow

[chriswragge]

Hi there, 

I am looking to automatically refresh a local query based on a tigger in MS Flow, but am having difficulty.

Here's my current workflow

1. Data is emailed to me weekly as an attachment
2. As soon as the Email hits my inbox, a MS Flow rule saves this attachment to OneDrive (good so far)
3. I then open PowerBI and manually refresh my query using that newly modified file in OneDrive (which is synced to my desktop) as a data source, plus one other file needed in the transformation
   • The query pings an API and returns a list of JSON values that I perform further transformation on
   • A bit of R script at the end of my query exports the transformed data as a new file out to OneDrive
4. Microsoft Flow detects the exported file from PowerBI in OneDrive, and the FTPs the fully transformed dataset to a server

In essence, what I am trying to remove my manual intervention from Step 3. 

My Issues

1. How do I get Step 3 triggered via Flow?
2. I require a file to be exported out of PowerBI when my query ends ready for FTP-ing.

Where I've got to so far

• A change in OneDrive will automatically refresh my dataset on the hour, but this requires my PowerBI query to use a Live Connection instead of a External Data Source. However I have x2 External Data Sources, plus I need to export a data file from PowerBI. This does not work with a Live Connection. 
• This blog here outlines how to get a Live Connection refreshing using MS Flow, but as a Live Connection seemes to be the wrong route given my requirements, then I feel like there is no further reason to proceed here https://medium.com/@Konstantinos_Ioannou/refresh-powerbi-dataset-with-microsoft-flow-73836c727c33
• This looks promising (using an on prem-gateway), but the refresh of Power BI would be scheduled and not triggered by MS Flow https://docs.microsoft.com/en-us/power-bi/refresh-excel-file-onedrive. 
  • Though this also look interesting https://flow.microsoft.com/en-us/blog/flow-of-the-week-local-code-execution/

Can anyone lend a hand?

[Greg_Deckler]

I think that one issue with your approach is going to be that there are not a lot of options for automating Power BI Desktop via code just yet versus what people have done in the past with things like Excel, Word, etc. You can check out this here: https://community.powerbi.com/t5/Desktop/Automation-for-Power-BI-Desktop/m-p/334108#M149441 I do not see much here other than streaming data sets as far as a destination: https://flow.microsoft.com/en-us/search/?q=power+bi

[chriswragge]

Thanks @Greg_Deckler. I may explore the local code side of things a little further, but good to know I am not missing something obvious. Cheers!