Error with Table.FromRecords() - Column - The field '[FIELDNAME]' of the record wasn't found.

[jimbob2285]

Hi

I've copied some M code from one table in my API data set (that's working fine on that table) to another table and it;s throwing up an error:

let
    // Base URL and parameters
    BaseUrl = "[URL]/[Table]?",
    ApiToken = "[APIToken]",
    Limit = 500,
    InitialStart = 0,

    // Function to fetch one page of results
    GetPage = (Start as number) =>
        if Start = null then
            [Data = {}, More = false, NextStart = null]
        else
            let
                Url = BaseUrl & "start=" & Text.From(Start) & "&limit=" & Text.From(Limit) & "&api_token=" & ApiToken,
                Response = Json.Document(Web.Contents(Url)),
                Data = Response[data],
                More = try Response[additional_data][pagination][more_items_in_collection] = true otherwise false,
                NextStart = try Response[additional_data][pagination][next_start] otherwise null
            in
                [Data = Data, More = More, NextStart = NextStart],

    // Loop through all pages using List.Generate
    AllPages = List.Generate(
        () => [Result = GetPage(InitialStart), Continue = true],
        each [Continue],
        each [
            Result = GetPage([Result][NextStart]),
            Continue = [Result][More]
        ],
        each [Result][Data]
    ),

    // Flatten all results into one list
    Combined = List.Combine(AllPages),
 
    // Convert to table, automatically detecting columns
    RawTable = Table.FromRecords(Combined)
in
    RawTable

I borrowed the above code, which @DataNinja777 helped me perfect on this forum yesterday, as I'm very new to M code.  It works fine on the original table, but when I've applied it to to a new table, it's throwing up errors in some rows, but not others:

If I step through the steps in the RHS pane, Its the last step "RawTable = Table.FromRecords(Combined)" that it's failing on, which I have overcome by removing this step and expanding the records from the main editor window, but I'd really l like to understand what's causing the error

I'm guessing the specified field just isn't in the selected record, but when I fetch the table without pagination, that record shows a null in the specified field.  which is the same for lots of fields in lots of records in the original table that this code works on

So I'm a bit lost, should I be handling nulls somehow, and if so, why didn't I have to handle them for the orginnal table that this M code worked on?

Unfortunately, there's no point sharing my PBIX file with you, as I can't share my API Key, but hopefully this is a common issue that someone can help me with.

Thanks

Jim

[v-kpoloju-msft · Accepted solution]

Hi @jimbob2285,

Thank you for reaching out to the Microsoft fabric community forum and sharing the context and M code. Based on the error and screenshot, the issue is that the records returned from your API do not have a consistent schema some fields are missing entirely in certain records, not just null.

In Power Query, Table.FromRecords() expects a uniform structure across all records. To resolve this, you can dynamically compute the union of all field names and ensure each record has all those fields, inserting null where missing. I have modified your M code to handle this please see the updated version below.

M Query:

let
    // Your existing steps
    BaseUrl = "[URL]/[Table]?",
    ApiToken = "[APIToken]",
    Limit = 500,
    InitialStart = 0,

    GetPage = (Start as number) =>
        if Start = null then
            [Data = {}, More = false, NextStart = null]
        else
            let
                Url = BaseUrl & "start=" & Text.From(Start) & "&limit=" & Text.From(Limit) & "&api_token=" & ApiToken,
                Response = Json.Document(Web.Contents(Url)),
                Data = Response[data],
                More = try Response[additional_data][pagination][more_items_in_collection] = true otherwise false,
                NextStart = try Response[additional_data][pagination][next_start] otherwise null
            in
                [Data = Data, More = More, NextStart = NextStart],

    AllPages = List.Generate(
        () => [Result = GetPage(InitialStart), Continue = true],
        each [Continue],
        each [
            Result = GetPage([Result][NextStart]),
            Continue = [Result][More]
        ],
        each [Result][Data]
    ),

    Combined = List.Combine(AllPages),

    // STEP 1: Get all unique field names
    AllKeys = List.Distinct(List.Combine(List.Transform(Combined, each Record.FieldNames(_)))),

    // STEP 2: Add missing fields with null
    Normalized = List.Transform(Combined, each Record.SelectFields(_, AllKeys, MissingField.UseNull)),

    // STEP 3: Convert to table
    RawTable = Table.FromRecords(Normalized)

in
    RawTable

This Power BI Community thread explains the root cause and solution very clearly:
Expression.Error: The field '[field]' of the record wasn't found

Also here I provided few official Microsoft documents below mentioned for more information:
Record.SelectFields - PowerQuery M | Microsoft Learn
Table.FromRecords - PowerQuery M | Microsoft Learn

Let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Community Forum.

[jimbob2285]

Hi @v-kpoloju-msft 

Thanks for this, it worked a treat, and thanks for explaining what the issue was too.  I do have another query but I'll post in a fresh question and tag you, if you can help please

Cheers

Jim