Power Query - Need help on REST API call with Dynamic Start - End Date and Pagination

[viren2023]

Due to some limitation in third party REST API, I can access 50 reconds per page and allowed one month period. I need to access all records from 1/1/2023 to today's date.

fReport
= (Page as number) =>
let
Source = Json.Document(Web.Contents("https://XXXXorts?startdate=1-1-2023&enddate=2-1-2023&pagesize=50&pagenumber="&Number.ToText(Page),
[Headers=[authorization="Bearer "&GetAccessToken()]])),
in
.........

Query
= List.Generate(()=>
[Result = try fReport(1) otherwise null, Page=1],
each [Result]<>null,
each [Result = try fReport([Page]+1) otherwise null, Page=[Page]+1],
each [Result])

Current Output

[rubayatyasmin · Accepted solution]

Hi, @viren2023 

try this one from gpt

let
    // Get today's date
    TodaysDate = Date.From(DateTime.LocalNow()),

    // Create a list of month start dates from 1/1/2023 to today
    ListOfMonths = List.Dates(#date(2023, 1, 1), Duration.From(TodaysDate - #date(2023, 1, 1)) + 1, #duration(30,0,0,0)),

    // Define a function to retrieve data given a start and end date
    fReport = (StartDate, EndDate, Page) => 
        let
            Source = Json.Document(Web.Contents(
                "https://XXXXorts?startdate=" & Date.ToText(StartDate, "M-d-yyyy") & 
                "&enddate=" & Date.ToText(EndDate, "M-d-yyyy") & 
                "&pagesize=50&pagenumber=" & Number.ToText(Page),
                [Headers=[authorization="Bearer "&GetAccessToken()]]
            ))
        in
            Source,

    // Combine the data for each month-long period
    CombineDataForAllMonths = List.Accumulate(ListOfMonths, {}, (state, currentMonthStartDate) => 
        let
            // Define the start and end dates for each period
            PeriodStartDate = currentMonthStartDate,
            PeriodEndDate = Date.EndOfMonth(currentMonthStartDate),

            // Get the data for the current month-long period
            MonthlyData = List.Generate(()=>
                [Result = try fReport(PeriodStartDate, PeriodEndDate, 1) otherwise null, Page=1],
                each [Result]<>null,
                each [Result = try fReport(PeriodStartDate, PeriodEndDate, [Page]+1) otherwise null, Page=[Page]+1],
                each [Result]
            )
        in
            state & MonthlyData
    )
in
    CombineDataForAllMonths

[viren2023]

Only two updates required 
1.
ListOfMonths = List.Dates(#date(2023, 1, 2), ((Date.Year(TodaysDate)-Date.Year((#date(2023, 1, 2))))*12) + Date.Month(TodaysDate) - Date.Month(#date(2023, 1, 2))+2, #duration(30,0,0,0)),

Note: Due to 30 days preiods maybe we have duplication of the data.

2.

PeriodStartDate = Date.StartOfMonth(currentMonthStartDate),
PeriodEndDate = Date.EndOfMonth(currentMonthStartDate)

Thank you so much @rubayatyasmin for the solution. You are the best.

[rubayatyasmin · Accepted solution]

Hi, @viren2023 

try this one from gpt

let
    // Get today's date
    TodaysDate = Date.From(DateTime.LocalNow()),

    // Create a list of month start dates from 1/1/2023 to today
    ListOfMonths = List.Dates(#date(2023, 1, 1), Duration.From(TodaysDate - #date(2023, 1, 1)) + 1, #duration(30,0,0,0)),

    // Define a function to retrieve data given a start and end date
    fReport = (StartDate, EndDate, Page) => 
        let
            Source = Json.Document(Web.Contents(
                "https://XXXXorts?startdate=" & Date.ToText(StartDate, "M-d-yyyy") & 
                "&enddate=" & Date.ToText(EndDate, "M-d-yyyy") & 
                "&pagesize=50&pagenumber=" & Number.ToText(Page),
                [Headers=[authorization="Bearer "&GetAccessToken()]]
            ))
        in
            Source,

    // Combine the data for each month-long period
    CombineDataForAllMonths = List.Accumulate(ListOfMonths, {}, (state, currentMonthStartDate) => 
        let
            // Define the start and end dates for each period
            PeriodStartDate = currentMonthStartDate,
            PeriodEndDate = Date.EndOfMonth(currentMonthStartDate),

            // Get the data for the current month-long period
            MonthlyData = List.Generate(()=>
                [Result = try fReport(PeriodStartDate, PeriodEndDate, 1) otherwise null, Page=1],
                each [Result]<>null,
                each [Result = try fReport(PeriodStartDate, PeriodEndDate, [Page]+1) otherwise null, Page=[Page]+1],
                each [Result]
            )
        in
            state & MonthlyData
    )
in
    CombineDataForAllMonths

[viren2023 · Accepted solution]

= List.Dates(#date(2023, 1, 1), Duration.From(TodaysDate - #date(2023, 1, 1)) + 1, #duration(30,0,0,0))