Foreach loop in PowerQuery

[Anonymous]

Hi,

Maybe a stupid question but I'm kinda new to PowerQuery and was looking for something similar to a foreach. 

Basically what I'm trying to do is perform an API call and from results in the first query perform a number of second queries to fill a table with results. In the second query I'm also looking to change part of the URL with values from the first results. I have no idea where to begin. Had a look at a couple of articles but for a beginner like me it doesn't make a lot of sense. 

Here is the first results I got

[Chad_OB]

Hi @Anonymous 

I'm curious to know if you had any issues after publishing to the Power BI Service.

I have a similar solution to yours which refreshes successfully in Power BI Desktop, but not in the Power BI Service. If its the latter, then what did you do to solve this?

[Anonymous]

@Chad_OB, For me it worked perfectly after I built a connector. 

[Anonymous]

@PhilipTreacy 

Thanks for the help. I gradually learned how I was supposed to do this. In hindsight I now know how open my questions. Really needed some first step guidance :). I got it working by making a custom function and call that from the first query. Like in the line below. The part "each TestRuns([testId])" was crucial in this. With that, it uses the function for each test ID it finds and adds the results to the existing table.  

#"Invoked Custom Function" = Table.AddColumn(ExpandTests, "TestRuns", each TestRuns([testId])),

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

I've got a blog post that deals with just this situation but I don't know the rules here for linking out to such things so I won't.

Using a custom function may do the job but without knowing a lot more about what it is you are actually trying to achieve, it's very hard to give you a solution.

What API are you using?

What data does the 1st query return?

How do the 2nd queries get affected by this?

How do you want to change the URL in the 2nd query/ies.

Regards

Phil

[v-yingjl]

Hi @Anonymous ,

Based on your description, you may want to pass parameters in the URL using power query. 

You can refer this simliar issue which introduces it in details that could help you to try:

https://community.powerbi.com/t5/Desktop/Passing-a-parameter-to-a-URL/td-p/103363

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.