Incremental Power BI Dataflow Refreshes for API Calls

Okay, I want to preface this by saying I am far from an expert however hopefully this resolves any issues and if it does, winner winner chicken dinner!

For the sake of being generic I will be using an API call to a Magic: The Gathering website (lol nerd). This may be slightly different to the APIs you’re going to be calling however the notion of it should be similar. In order to do achieve this, we’re going to use Power BI Dataflows – essentially one for each API call. While this may be messy, it should work.

So first thing to do is create a dataflow and select “Add new tables”:

For this example, I will select Other, then Web API. This will in turn bring you to the below screen to enter your API info.

In this instance I don’t require credentials as I am using an open-source API however this is where you would enter any if required. Once these fields are filled out, click next. This will take you to Power Query for any under-the-bonnet data manipulation. Once this is done, give the query a name and click save and close, then give your dataflow a name.

Next is setting up a scheduled refresh. Click the three little dots next to the new data flow and go into settings:

Once there, expand Scheduled refresh, turn it on and configure the refresh frequency, time zone and the time you want your dataset to refresh. Once you’re happy, click apply:

And boom, we new have a dataflow that talks to a Web API (a simple one albeit)! Next thing to do is to set up a different dataflow for each API call you need, and ensure the scheduled refresh is staggered in such a way that it doesn’t overlap with another API call. For simplicity’s sake, I have just configured the two. One thing to keep in mind which could in turn be a limitation is that it doesn’t seem like you can select a custom refresh time, only on the hour or half past:

Once your dataflows are set up, all that’s left to do is connect to the dataflow tables themselves in Power BI Desktop! Open up the Power BI Desktop application, go to the Get Data dropdown and select Dataflows:

Expand workspaces and re-auth if prompted, then select and expand the workspace where you created your dataflows, then expand those data flows to access your tables.

Then all you need to do is load them in, and then you’re good to go and create any visuals you need to in order to tell the story of your data! I hope this helps in some way, it may be a bit simpler than the APIs you’re using however to break it down:

• For each API call, create a dataflow.
• Set up the scheduled refresh so they are staggered to your liking (I’d recommend overnight, that way you’re not poking the bear during business hours).
• There are a couple of limitations:
  • The scheduled refresh can only be set daily or weekly
  • It doesn’t seem like you can set a custom time – only o’clock or half past.

Again, I am no expert by any means, however I hope this explains it well enough and remember, knowledge is power!

Any questions or advice on how this could be done better, please feel free to get in touch with me on twitter at @AyyOhhDee.