Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
hxnwx
Frequent Visitor

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.

 

 

 

 

 

4 REPLIES 4
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

 

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
Super User
Super User

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.