Recursive function in Power Query to iterate results from a public REST API

[Fatau2130]

Hi all,

I am relatively new to using Power BI and have been running into an issue when trying to call a lot of data from my database public REST API. Currently I am doing a separate query for each month but would like to find a way to loop the query to iterate through each month.

Each row of data has a unique identifier so could iterate through that way? The API has a standard return of 200 rows but you can specify limit in the URL up to 10,000. The API does not have an offset available so a lot of options for this I see online are not viable. The API also returns the data as a List of Records.

I have tried to create a recursive loop to get this working as follows using a blank query (User Key and Secret are set as parameters):   

let

    fetchData = (url, headers) =>

        let

            response = Json.Document(Web.Contents(url, headers)),

            data = response[Data]

        in

            data,

    url = "my_API_URL",

    headers = [

        #"User" = User,

        #"Key" = Key,

        #"Secret" = Secret

    ],

    result = List.Generate(

        () => [url = url, headers = headers, data = {}],

        each List.Count([data]) > 0,

        each [url = url, headers = headers, data = fetchData([url], [headers])],

        each [url = url, headers = headers, data = {}]

    ),

    allData = List.Combine(List.Transform(result, each [data]))

in

    allData

This doesn't return any errors but is not producing any results so not sure where I am going wrong. Any additions to the current function or another method to loop the query would be greatly appreciated 🙂

[AlienSx]

Hi, @Fatau2130 regardless of anything else, this is what stops List.Generate to produce any result.

() => [url = url, headers = headers, data = {}],
        each List.Count([data]) > 0

Your first item has zero values in data list.  

[ppm1]

How are you passing the filter for each month into the API? Also, if your API doesn't have skip or offset, does it offer pagination and gives you a link to the next set of results?

Pat