cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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?

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

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/

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

2 REPLIES 2
ImkeF
Super User
Super User

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/

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Greg_Deckler
Super User
Super User

@Anonymous Perhaps @edhans or @ImkeF can assist.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!