Fully Automating a Data Refresh

[bonjourposte]

I'd like to fully automate a data upload/refresh.  This process would have to:

1) connect PBI Service to SQL to upload our addresses into a table

2) send an API call to get the lat and long for those addresses

3) then send another API call to get the walkscore of those locations

4) update/refresh our visualizations in the desktop dashboard.  

I'd like to do this in Service because the Walkscore API is very slow and I don't want to have to wait ten minutes every time I add a calculated column to fetch the data.  Is this possible?  If so, how does the system know to do one API first and then the next API?  Is this a Power Automate thing, an M code thing, or a schedule refresh thing where I manually time the API calls to be one after the other?

[otravers · Accepted solution]

You'll probably end up using a combination of these things, for instance you can trigger dataflow then dataset refreshes from Power Automate. The best place to handle the timing of your API calls would be in your M code, typically using Invoke.After:

https://docs.microsoft.com/en-us/powerquery-m/function-invokeafter