GetTotalPage by m code function (Source data is API)

[chrisyan_manalu]

Hi All,

I'm stuck right now

I have code like this to get how much the total page of this API (source data)

(SelectedService as text) =>
    let
    BaseURL = "https://testing.com", //example API
    ConstantVar = "test/rest/CheckAPI/GetData?PageSize=""&PageNumber=""&Building=""&Category=""&Location=""&Status=""&StartDate=""&EndDate=""&StartTime={StartTime}&EndTime=", // example var
    StartDate = "2021-10-19",
    CurrentYear = Text.End("0000" & Number.ToText(Date.Year(Date.From(DateTime.LocalNow()))),4),
    CurrentMonth = Text.End("00" & Number.ToText(Date.Month(Date.From(DateTime.LocalNow()))),2),
    CurrentDay = Text.End("00" & Number.ToText(Date.Day(Date.From(DateTime.LocalNow()))),2),
    EndDate = CurrentYear & "-" & CurrentMonth & "-" & CurrentDay,
    response = Web.Contents(
    #"BaseURL",
    [
        Headers = 
        [
            Authorization= "test test" // username and pass (basic auth)
        ],
        RelativePath = #"ConstantVar" & SelectedService,
        Query = 
        [
            start_dt = StartDate,
            end_dt = EndDate,
            page = Number.ToText(1)
        ]
    ]
),
    jsonResponse = Json.Document(response),
    #"Converted to table" = Record.ToTable(jsonResponse),
    #"Filtered rows" = Table.SelectRows(#"Converted to table", each ([Name] = "total_page")),
    #"Removed columns" = Table.RemoveColumns(#"Filtered rows", {"Name"}),
    #"Changed column type" = Table.TransformColumnTypes(#"Removed columns", {{"Value", Int64.Type}}),
    #"Renamed columns" = Table.RenameColumns(#"Changed column type", {{"Value", "TotalPage"}})
in
    #"Renamed columns"

And when I try to invoke custom function, I got error like this

If the function running well, the output present how much the total page

Any solution ?

Please help

Thanks

[lbendlin]

Please read the documentation again, and do a cleaner separation of RelativePath and Query

Web.Contents - PowerQuery M | Microsoft Docs

After that please confirm if your API provides pagination ("nextPage"  until done)  or if it reports the "totalRecords"  value in each response.