Forum Discussion

CHyde's avatar
CHyde
New Member
5 years ago

Error: Cannot apply field access to the type List

I'm attempting to follow this pagination guide in Power Bi:

https://medium.com/@marktiedemann/how-to-do-pagination-in-power-query-430460c17c78

 

and I keep running into the field access error where:

Value=[List]

Key = odata.count

 

I'll paste my code below, any help would be much appreciated.

 

let 
    BaseUrl         = "api url here",
    EntitiesPerPage = 100,
 
    GetJson = (Url) =>
        let 
             // Concatenates the Consumer Key & Consumer Secret and converts to base64
            authKey = "Basic " & Binary.ToText(Text.ToBinary(#"WorkZoneClientID" & ":" & #"WorkZoneClientSecret")),
            // Uses the Workzone POST oauth2/token method to obtain a bearer token
            GetauthJson = Web.Contents(#"WorkZoneURL" ,
                 [
                     RelativePath="/api/token",
                     Headers = [#"Authorization"=authKey,
                    #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
                    Content = Text.ToBinary("grant_type=client_credentials") 
                ]
            ),
 FormatAsJson = Json.Document(GetauthJson),
 // Gets token from the Json response
 AccessToken = FormatAsJson[access_token],
 AccessTokenHeader = "Bearer " & AccessToken,

            Options = [Headers=[Authorization=AccessTokenHeader]],
            RawData = Web.Contents(Url, Options),
            Json    = Json.Document(RawData)
        in  Json,
 
    GetEntityCount = () =>
        let Url   = BaseUrl & "$count=true&$top=0",
            Json  = GetJson(Url),
            Count = Json[#"@odata.count"]
            
        in  Count,
 
    GetPage = (Index) =>
        let Skip  = "$skip=" & Text.From(Index * EntitiesPerPage),
            Top   = "$top=" & Text.From(EntitiesPerPage),
            Url   = BaseUrl & Skip & "&" & Top,
            Json  = GetJson(Url),
            Value = Json[#"value"]
        in  Value,
 
    EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
    PageCount   = Number.RoundUp(EntityCount / EntitiesPerPage),
    PageIndices = { 0 .. PageCount - 1 },
    Pages       = List.Transform(PageIndices, each GetPage(_)),
    Entities    = List.Union(Pages),
    Table       = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    Table

1 Reply

  • artemus's avatar
    artemus
    Microsoft Employee

    Simply put, the result from the web server is an array instead of a property bag. It is hard to say what the correct solution is, since it is based on your data set. The part you need to change is

    Count = Json[#"@odata.count"]

     Some options that might work for you are:

    Count = List.Sum(Json[#"@odata.count"])
    
    count = Json[#"@odata.count"]{0}

    The first tries to sum up the returned values, while the second just takes the first result.