Forum Discussion
API Query iteration
HI hxnwx,
I'd like to suggest you create a query table with parameter value list as @lbendlin mentioned, then you can add a new column with web connector to invoke api with current row field values as parameters.
AddColumn = Table.AddColumn(
#"Previous Step",
"NewTable",
each
Web.Contents(
"www.someapiurl.com/",
[
Headers = [
#"Authorization" = "Bearer " & "token strings",
#"Content-Type" = "application/json"
],
Query = [
parameter1 = [ColumnA],
parameter2 = [ColumnB],
parameter3 = [ColumnC]
]
]
)
)Regards,
Xiaoxin Sheng
Thanks Xiaoxin.
I went in a slightly different direction, using List.Accumulate which gave me a list of binary from passing in only one parameter for Query argument. I and was able to get the data in the form I need.
My question now is, how can I pass in a table (3 columns) of values for list in List.Accumulate so it can iterate over all combinations of all parameters?
Ex.
Table:
| ColumnA | ColumnB | ColumnC |
parameter1value1 | parameter2value1 | parameter3value1 |
| parameter1value2 | parameter2value2 | parameter3value2 |
| parameter1value3 | parameter2value3 | |
| parameter1value4 |
So my WebQuery would start with Column A's parameter1value1, then iterate thru ALL values of ColumnB for parameter2, then all values of ColumnC for parameter3. Then move to parameter1value2 and repeat process until end of ColumnA.
let
GetWebData = (statisticType as text) =>
let
Source =
Web.Contents("https://someapiurl?TIME=parameter1&POLYGON=parameter2&STATISTICS=parameter3",
[
Query=
[
STATISTICS=statisticType
]
]
)
in
Source
in
GetWebData
let
YourList = {parameter3value1, parameter3value2, parameter3value3},
Result = List.Accumulate(YourList, {}, (state, current) => state & {WebQuery(Text.From(current))}),
in
Result
List.Accumulate is not a good idea as it forces you to lug all the data around with you - multiple times. And no, a recursive function is not good either, for the same reasons.
