REST with Bearer Token - Successfull refresh in advanced editor, failure in apply changes

[sceccolini]

Hello,

I need to invoke REST APIs with Bearer token authentication to retrieve the data for my model; same queries work in Postman.

I defined the following functions/queries in the Power BI advanced editor which are working fine in the advanced editor area, but when I close and try to apply the changes in my dashboard, the process is failing.

First of all, I defined a GetBearerToken function which returns my access_token.

let
    GetBearerToken = () =>
    let
        baseUrlToken = "BASIC URL",
        relativePathToken = "login",
        bodyToken = "grant_type=password&username=USER&password=PWD&client_id=CLIEND_ID&client_secret=CLIENT_SECRET",
        headersToken = [#"Content-Type"="application/x-www-form-urlencoded"],
        responseToken = Json.Document(Web.Contents(baseUrlToken, [RelativePath=relativePathToken, Headers=headersToken, Content=Text.ToBinary(bodyToken)])),
        accessToken = responseToken[access_token]
    in
        accessToken
in
    GetBearerToken

Then in my queries, I recall this one and I retrieve the data

let
    // Recupera il token
    accessToken = GetBearerToken(),

    // Funzione per recuperare una singola pagina
    GetPage = (PageNumber as number) =>
    let
        baseUrl = "BASE URL",
        relativePath = "RELATIVE PATH",
        query = [
            dql = "PARAMETER", // Modifica i campi qui
            page = Text.From(PageNumber)
        ],
        headers = [Authorization="Bearer " & accessToken],
        response = Json.Document(Web.Contents(baseUrl, [RelativePath=relativePath, Query=query, Headers=headers])),
        entries = response[entries],
        nextPage = if List.NonNullCount(List.Select(response[links], each _[rel] = "next")) > 0 then PageNumber + 1 else null,
        result = [Entries = entries, NextPage = nextPage]
    in
        result,

    // Recupera tutte le pagine
    GetAllPages = List.Generate(
        () => [PageNumber = 1, Results = GetPage(1)],
        each [Results][Entries] <> null and List.Count([Results][Entries]) > 0,
        each [PageNumber = [PageNumber] + 1, Results = GetPage([PageNumber] + 1)],
        each [Results][Entries]
    ),

    // Combina i risultati di tutte le pagine
    CombinedResults = List.Combine(List.Transform(GetAllPages, each _)),
    ResultTable = Table.FromList(CombinedResults, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(ResultTable, "Column1", {"content"}, {"content"}),
    #"Expanded content" = Table.ExpandRecordColumn(#"Expanded Column1", "content", {"properties"}, {"properties"}),
....

Everything works great in the advanced editor preview area, but when I click on "Close & Apply Changes", all the functions give an error, even the GetBearerToken. The error is the same for all the executions, an example from the GetBearerToken and the one reported above:

GetBearerToken
Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [Expression.Error] The field 'entries' of the record wasn't found.. OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E. The current operation was cancelled because another operation in the transaction failed. OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E. OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E. OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E. OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E. '.

I cleaned the cache but nothing.

Do you have any suggestions on this?

Thanks

Simone

[sceccolini]

Hello,

it seems in the data model load Power BI is doing a different operation. It was an issue in the data processing, and I solved it with a "try" statement

...

debugResponse = response,
entries = try response[entries] otherwise null,
nextPage = if List.NonNullCount(List.Select(response[links], each _[rel] = "next")) > 0 then PageNumber + 1 else null,

...

Thank you
Simone

[v-yangliu-msft · Accepted solution]

Hi  @sceccolini ,

You can try the following link solutions: Find any step that references a field named “entries” in Power Query's “APPLIED STEPS” and look for the specific error message. Alternatively, delete all columns other than the one in question and add columns incrementally to narrow down the scope of the error. This will allow you to locate and resolve the problem more efficiently.

Solved: OLE DB or ODBC error: [Expression.Error] The field... - Microsoft Fabric Community

This is the related document, you can view this content:

Solved: OLEDB or ODBC error: [Expression Error] The field... - Microsoft Fabric Community

Solved: Request: Expression.Error: The Field of the record... - Microsoft Fabric Community

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.