How do I Loop in M to Query Last 40 Days a Day at a Time Using Web URL API

[Anonymous]

Hi all,

So as fairly new to using M and Power Query, I was trying to adapt things from this post:
https://community.powerbi.com/t5/Desktop/Iterating-over-start-and-end-dates-for-multiple-API-calls/m...

I haven't got very far! Basically what I want to achieve is to create one table with the previous 40 days worth of data rather than last 12 months as the post above is tailored for. I currently pull this data using a web URL API, but in the API you can only call 1 day at a time. So I would need to create some kind of loop that advances the date that is specified in the API string by one, up to 40 times and have this reside in this one table.

How do I achieve that?

[ImkeF · Accepted solution]

Hi @Anonymous ,
the simplest loop in Power Query is to add a column to a table: The command for the column will be applied for each row.
So you can create a table with the last 40 days in one column and then you add a custom column that makes the web call. There you reference the day-column to make a reference to each day.
But if you want to refresh that in the Power BI Service, you have to move parts of your URL to query parameters unfortunately: 

http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/

[Greg_Deckler]

@Anonymous Perhaps @edhans or @ImkeF can assist.