Pagination API

[Elipo]

I need Help!
Power query returns this error:

Expression.Error: We cannot convert a value of type List to type Record.
Details:
Value=[List]
Type=[Type]

Here is my code for getting paginated API data:

let
access_token = access_token,

// Function to fetch a page of data
FetchData = (page as number) as record =>
try
Json.Document(Web.Contents("https://api.qgenda.com/v2/schedule",
[
Headers = [
Authorization = "bearer " & access_token,
#"Content-Type" = "application/json"
],
Query = [
startDate = "2025-01-01",
endDate = "2025-04-30",
page = Text.From(page),
limit = "100"
]
]
))
otherwise [data = {}],

// Generator that creates a list of records
Pages = List.Generate(
() => [PageNum = 1, Result = FetchData(1)],
each List.Count([Result][data]) > 0,
each [PageNum = [PageNum] + 1, Result = FetchData([PageNum])],
each [Result]
),

// Convert each page's data list to a table
PagesTables = List.Transform(Pages, each Table.FromList(Record.Field(_, "data"), Splitter.SplitByNothing(), {"Record"})),

// Combine all tables into one
CombinedTable = Table.Combine(PagesTables),

// Expand fields from the record (adjust to your actual fields)
#"Expanded Record" = Table.ExpandRecordColumn(CombinedTable, "Record", {
"StartDateUTC", "EndDateUTC", "CompName", "TaskName", "StaffFName", "StaffLName"
}),

// Apply Text.Combine to combine text fields
#"Combined Text" = Table.TransformColumns(#"Expanded Record", {
{"CompName", each Text.Combine(List.Transform(_, Text.From)), type text},
{"TaskName", each Text.Combine(List.Transform(_, Text.From)), type text},
{"StaffFName", each Text.Combine(List.Transform(_, Text.From)), type text},
{"StaffLName", each Text.Combine(List.Transform(_, Text.From)), type text}
}),

// Convert dates
#"Changed Type" = Table.TransformColumnTypes(#"Combined Text", {
{"StartDateUTC", type datetime}, {"EndDateUTC", type datetime}
})
in
#"Changed Type"

[v-veshwara-msft]

Hi @Elipo ,

We’re following up once more regarding your query. If it has been resolved, please mark the helpful reply as the Accepted Solution to assist others facing similar challenges.

If you still need assistance, please let us know.
Thank you.

[v-veshwara-msft]

Hi @Elipo ,

Following up to see if your query has been resolved. If any of the responses helped, please consider marking the relevant reply as the 'Accepted Solution' to assist others with similar questions.

If you're still facing issues, feel free to reach out.

Thank you.

[Elipo]

Hi, the provided code is functioning correctly. However, the table returns empty due to restrictions outlined in the REST API documentation.

[v-veshwara-msft]

Hi @Elipo ,

Thanks for confirming the code is working correctly. If the table returns empty due to API restrictions, that’s an important limitation to keep in mind.

Could you please share the specific REST API documentation or page where these restrictions are outlined? This would help to better understand the API behavior.

Meanwhile, if any of the suggestions shared here have been helpful, please consider marking the response as a solution. It helps others in the community as well.

Let us know if you need any further assistance.

Thank you.

[v-veshwara-msft]

Hi @Elipo ,

Just checking in to see if you query is resolved and if any responses were helpful. If so, kindly consider marking the helpful reply as 'Accepted Solution' to help others with similar queries. 

Otherwise, feel free to reach out for further assistance.

Thank you.

[v-veshwara-msft]

Hi @Elipo ,
Thanks for using Microsoft Fabric Community and sharing the issue you're facing with the QGenda API pagination in Power Query.

The error you're seeing:

Expression.Error: We cannot convert a value of type List to type Record.

usually happens when Power Query expects a record but receives a list instead. In this case, FetchData function assumes the API response always includes a "data" field inside a record, but at least one response likely returns a different structure.

Thanks @kushanNa  for providing a helpful response. Your update correctly checks if the response has a "data" field and confirms it's a list before proceeding:

safeResponse =
if Record.HasFields(response, "data") and (response[data] is list)
then response
else [data = {}]

This ensures that each page passed to List.Generate follows the expected structure and prevents the type conversion error.

Here are a few links that explain similar issues and solutions in more detail:

[Expression.Error] We cannot convert a value of ty... - Page 2 - Microsoft Fabric Community

Solved: Power Query Expression error: We cannot convert a ... - Microsoft Fabric Community

Error handling - Power Query | Microsoft Learn

Please try the updated version shared above. If you're still running into problems after that, feel free to share the exact error for further review.

Please consider marking the helpful reply as Accepted Solution to assist others with similar issues and a kudos would be appreciated.

Thank you.

[Elipo]

Thank you, the error 

We cannot convert a value of type List to type Record.
Details:
Value=[List]
Type=[Type]

is solved but I get an empty table.

[v-veshwara-msft]

Hi @Elipo 

Thanks for confirming that the original error is resolved.

Since you're now seeing an empty table, that typically indicates:

1. The API didn’t return any actual records for the given date range.

2. The "data" field’s structure varies across pages - for example, it might return a list in some responses and a record or something else in others.

As @kushanNa  rightly suggested earlier, the use of 'try ... otherwise' helps avoid breaking the query when unexpected structures occur. However, do note that this may drop rows with valuable data if the API returns inconsistent structures, silently skipping over responses that don’t match the expected format. This can lead to the query completing without error but returning an empty or partial table.

To handle this better,

Instead of relying on 'try', consider using a structure-aware approach like the one shared in this similar discussion: Re: Hi Re: [Expression.Error] We cannot convert a ... - Microsoft Fabric Community. This checks each value with 'Value.Is(...)' and then transforms it accordingly based on whether it's a list or a record.

You can adapt this idea to fit into your pagination logic.

Additionally,

->Try calling the API outside Power Query (e.g., Postman) with the same parameters (startDate=2025-01-01, endDate=2025-04-30, limit=100) to confirm whether data is returned.

->You can also temporarily output the raw Pages list in Power Query to inspect the actual content per page.

->Finally, verify that your API key or role has access to the expected data for the given filters.

Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to give a kudos and Accept as the solution to help the other members find it more quickly.

Thank you.

[kushanNa · Accepted solution]

Hi @Elipo 

Try this code 

let
    access_token = access_token,

    // Function to fetch a page of data
    FetchData = (page as number) as record =>
        try
            let
                response = Json.Document(Web.Contents("https://api.qgenda.com/v2/schedule",
                    [
                        Headers = [
                            Authorization = "bearer " & access_token,
                            #"Content-Type" = "application/json"
                        ],
                        Query = [
                            startDate = "2025-01-01",
                            endDate = "2025-04-30",
                            page = Text.From(page),
                            limit = "100"
                        ]
                    ])),
                safeResponse = if Record.HasFields(response, "data") and (response[data] is list) then response else [data = {}]
            in
                safeResponse
        otherwise
            [data = {}],

    // Generator that creates a list of page results
    Pages = List.Generate(
        () => [PageNum = 1, Result = FetchData(1)],
        each Record.HasFields([Result], "data") and List.Count([Result][data]) > 0,
        each [PageNum = [PageNum] + 1, Result = FetchData([PageNum])],
        each [Result]
    ),

    // Convert each page's 'data' list to a table of records
    PagesTables = List.Transform(
        Pages,
        each Table.FromList(Record.Field(_, "data"), Splitter.SplitByNothing(), {"Record"})
    ),

    // Combine all tables into one
    CombinedTable = Table.Combine(PagesTables),

    // Expand fields from the record (adjust fields as per your data structure)
    #"Expanded Record" = Table.ExpandRecordColumn(CombinedTable, "Record", {
        "StartDateUTC", "EndDateUTC", "CompName", "TaskName", "StaffFName", "StaffLName"
    }),

    // Apply Text.Combine to handle lists of text
    #"Combined Text" = Table.TransformColumns(#"Expanded Record", {
        {"CompName", each Text.Combine(List.Transform(_, Text.From)), type text},
        {"TaskName", each Text.Combine(List.Transform(_, Text.From)), type text},
        {"StaffFName", each Text.Combine(List.Transform(_, Text.From)), type text},
        {"StaffLName", each Text.Combine(List.Transform(_, Text.From)), type text}
    }),

    // Convert date columns to datetime
    #"Changed Type" = Table.TransformColumnTypes(#"Combined Text", {
        {"StartDateUTC", type datetime},
        {"EndDateUTC", type datetime}
    })
in
    #"Changed Type"

this has a similar pages step to above but a public api , i have a feeling you must having this issue in this step

let
    // Search query
    searchTerm = "harry potter",

    // Function to fetch paginated results
    FetchData = (page as number) as record =>
        try
            let
                response = Json.Document(Web.Contents("https://openlibrary.org/search.json",
                    [
                        Query = [
                            q = searchTerm,
                            page = Text.From(page)
                        ]
                    ])),
                safeResponse = if Record.HasFields(response, "docs") and (response[docs] is list) then response else [docs = {}]
            in
                safeResponse
        otherwise
            [docs = {}],

    // Generate all pages until no results
    Pages = List.Generate(
        () => [PageNum = 1, Result = FetchData(1)],
        each Record.HasFields([Result], "docs") and List.Count([Result][docs]) > 0,
        each [PageNum = [PageNum] + 1, Result = FetchData([PageNum])],
        each [Result]
    ),

    // Convert each page to a table
    PagesTables = List.Transform(Pages, each Table.FromList([docs], Splitter.SplitByNothing(), {"Record"})),

    // Combine all pages
    CombinedTable = Table.Combine(PagesTables),

    // Expand fields of interest
    Expanded = Table.ExpandRecordColumn(CombinedTable, "Record", {"title", "author_name", "first_publish_year"})
in
    Expanded