Forum Discussion
Solved
Looping through a list using Power Query M
Hello,
I am trying to make a series of calles to a particular API endpoint and pass it a different variable with each call. Once we receive and parse the response from the API, we need to insert a related variable as a custom column.
I have set up a separate Query using the direct entry of data that contains both of these variables. I am looking for a simple way to repeat the request for each entry in the table. Code and table are both included below. I've tried to make it obvious where each field goes.
What's the easiest way to do this?
Query ParkBranchMatrix Table
| BranchID | ParkCode |
| 123456 | Example1 |
| 456789 | Example2 |
| 147258 | Example3 |
Query GetEmails M Code
let
// Define the endpoint
url = API_Endpoint,
// Define the JSON body
jsonBody = Text.ToBinary("{ ""network"": { ""network_id"": "& Network_ID &" }, ""branch"": { ""branch_id"": [===BranchID Goes Here===] } }"),
#"Added custom" = Table.AddColumn(expandedUserInformation, "Park Code", each "[===ParkCode Goes Here===]"),Hey,
Maybe you can turn the query into a function.
func_GetEmails
(vBranchID as text, vParkCode as text) =>
let
...
Then call the function by adding a column
Table.AddColumn(PreviousStep, "getEmails", each func_GetEmails([BranchID], [ParkCode]))
Make the function return a nested table/record, which then can expand.
1 Reply
Replies have been turned off for this discussion
Hey,
Maybe you can turn the query into a function.
func_GetEmails
(vBranchID as text, vParkCode as text) =>
let
...
Then call the function by adding a column
Table.AddColumn(PreviousStep, "getEmails", each func_GetEmails([BranchID], [ParkCode]))
Make the function return a nested table/record, which then can expand.