API Pagination Help - List.Dates

l0ganBI — Wed, 21 Dec 2022 18:37:43 GMT

Hello,

I am hoping someone knows a solution to this. I am trying to connect to a REST API that I need to paginate JSON data using date timestamps instead of page #s. Normally, the solution here is to use 'List.Generate' and create a loop of page numbers for the API call to use - as shown here in this video

But since I need to loop through a list of dates instead of numbers, I need to use 'List.Dates' instead of 'List.Generate'. The problem I am running into is with my List.Dates query (see 'Query1' below). I should note that I am not attempting this in PowerBI but in Excel PowerQuery.

Function

= (Page as date)=>
let
Source = Json.Document(Web.Contents("https://api-website.com//data/v1/available?key=000-001001A0A&lastModifiedStart="&Date.ToText(Page,[Format="YYYY-MM-DD"]))),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"datacolumn1", "datacolumn2", "datacolumn3", "datacolumn4"})

in
#"Expanded Column1"

Query1

= List.Dates(()=> [Result = try Function(#date(2022,1,1)) otherwise null, Page=#date(2022,1,1)],
each [Result]<>null, each [Result = try Function(Date.AddDays([Page],1)) otherwise null, Page=Date.AddDays([Page],1)],
each [Result])

Query1 returns the following error. Does anyone know what might be causing this? Really appreciate any help I can get here.

Expression.Error: 4 arguments were passed to a function which expects 3.
Details:
Pattern=
Arguments=[List]

Re: API Pagination Help - List.Dates

ChainsawMan — Fri, 23 Dec 2022 04:24:41 GMT

Hi,

"List.Dates" needs three parameters but you provided four. Here is the document about this funtion.

Best Regards.

topic Re: API Pagination Help - List.Dates in Power Query

API Pagination Help - List.Dates

Re: API Pagination Help - List.Dates