topic Re: API Query iteration in Developer

API Query iteration

hxnwx — Wed, 14 Feb 2024 01:00:39 GMT

Hello.

I am hitting an API from PowerBi that does not offer a range option. So I am stuck doing multiple queries to get a complete dataset. Output is JSON.

For some random API:

www.someapiurl.com/&parameter1=A&parameter2=30&parameter3=C

Return all columns' data for parameter2 = 30 AND 31 AND 32, etc.

In other words, iterate thru values 30-32 for parameter2.

Expected output:

Column1	Column2	Column3
value when parameter2=30	value when parameter2=30	value when parameter2=30
value when parameter2=31	value when parameter2=31	value when parameter2=31
value when parameter2=32	value when parameter2=32	value when parameter2=32

Is there a way to do this?

Thanks in advance.

Re: API Query iteration

lbendlin — Thu, 15 Feb 2024 01:51:13 GMT

Use List.Generate or simply {30..32} to feed your API URL.

Re: API Query iteration

Anonymous — Thu, 15 Feb 2024 01:58:25 GMT

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] ] ] ) )

Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code (crossjoin.co.uk)

Regards,

Xiaoxin Sheng

Re: API Query iteration

hxnwx — Sat, 17 Feb 2024 22:23:01 GMT

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

Re: API Query iteration

lbendlin — Sat, 17 Feb 2024 22:34:07 GMT

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.