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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
Anonymous
Not applicable

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

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.