API Query iteration

[hxnwx]

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.

[v-shex-msft]

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 An...

Regards,

Xiaoxin Sheng

[hxnwx]

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

[lbendlin]

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.

[lbendlin]

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