Forum Discussion

hxnwx's avatar
hxnwx
Frequent Visitor
2 years ago

API Query iteration

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:

Column1Column2Column3
value when parameter2=30value when parameter2=30value when parameter2=30
value when parameter2=31value when parameter2=31value when parameter2=31
value when parameter2=32value when parameter2=32value when parameter2=32

 

Is there a way to do this?

Thanks in advance.

 

 

 

 

 

Need Help

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

    • hxnwx's avatar
      hxnwx
      Frequent Visitor
      2 years ago

      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:

       

      ColumnAColumnBColumnC

      parameter1value1

      		parameter2value1parameter3value1
      parameter1value2parameter2value2parameter3value2
      parameter1value3parameter2value3 
      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's avatar
        lbendlin
        Super User
        2 years ago

        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.