We cannot apply field access to the type List.

[Shamatix]

Hi there fellow users of Power Bi,

We have now gotten a bit further and is now stumbling upon a new error:
"We cannot apply field access to the type List."

Below is our M code

let
    // Få authorization token
    Kilde = Web.Contents("https://api.bilagscan.dk/oauth2/token",
[Headers=[#"Content-Type"="application/x-www-form-urlencoded"],
Content=Text.ToBinary(Uri.BuildQueryString([grant_type="password", client_id="XXXXXXXXXXXXXXXXXXXXXXXXXX", client_secret="XXXXXXXXXXXXXXXXXXXXXXXXXX", email="XXXXX@YYYYYYYYYY.com", password="XXXXXXXXXXXXX"]))]),
    KildeTekst = Text.FromBinary(Kilde),
    KildeSegmenter = Splitter.SplitTextByDelimiter("&")(KildeTekst),
    KildeKvps = List.Transform(    KildeSegmenter, 
                                       (value) => { 
                                          List.First((Splitter.SplitTextByDelimiter("=")(value))),
                                          List.Last((Splitter.SplitTextByDelimiter("=")(value)))
                                        }
                                  ),
    KildeKvpTabel = Table.FromList(KildeSegmenter, Splitter.SplitTextByDelimiter("=")),
    #"Filtrerede rækker" = Table.SelectColumns(Table.SelectRows(KildeKvpTabel, each [Column1] = "access_token"), {"Column2"}),
    Token = Text.From(List.First(Table.SelectColumns(Table.SelectRows(KildeKvpTabel, each [Column1] = "access_token"), {"Column2"})[Column2])),

    EntitiesPerPage = 100,
    BaseUrl = "https://api.XXXXXXXXXXX.dk/v1/organizations/XXXX/vouchers",
    Options = [Headers=[ #"Authorization" = "Bearer " & Token ]],


        GetJsonCount = (Url) =>
        let 
            result = Web.Contents(Url, Options),
            #"Temp" = Json.Document(result,1252),
            JsonCount = Record.Field(#"Temp"[meta],"count")
        in  JsonCount,

 

        
        GetJsonList = (Url) =>
        let 
            resultList = Web.Contents(Url, Options),
            #"TempList" = Json.Document(resultList,1252),
            JsonList = #"TempList"[data]
        in  JsonList,
        
        GetEntityCount = () =>
        let
            Url   = BaseUrl & "?count=11",
            Json = GetJsonCount(Url),
            Count = Json
        in  Count,

 

        
        GetPage = (Index) =>
        let     
            Skip  = "offset=" & Text.From(Index * EntitiesPerPage),
            Top   = "?count=" & Text.From(EntitiesPerPage),
            Url   = BaseUrl & Top & "&" & Skip,
            Json  = GetJsonList(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

I really hope someone out there can help us out 🙂

Best regards

[artemus]

I don't know what your dataset is like, so the error cound be in many places.

In the error message you should see:

Value=[List]

Key=****

the **** is an indicator where the error is. Somewhere in your source should be [****], if it isn't there and key is "count" then it is on the line with

Record.Field(#"Temp"[meta],"count")

The error message basicly means:

You tried to get the column of a table/row, but instead you gave us a column/collection.

[Shamatix]

Hi Artemus,

The error is:

We cannot apply field access to the type List.
Details: 
Value=[List]

Key=value

So I guess it's in this part:

 GetPage = (Index) =>
        let     
            Skip  = "offset=" & Text.From(Index * EntitiesPerPage),
            Top   = "?count=" & Text.From(EntitiesPerPage),
            Url   = BaseUrl & Top & "&" & Skip,
            Json  = GetJsonList(Url),
            Value = Json[#"value"]
        in  Value,

The error must lie?

How can I fix it :S?

[artemus · Accepted solution]

In this case, it looks like it is trying to extract a list when it already got one. In that case try removing the final step in that function:

 GetPage = (Index) =>
        let     
            Skip  = "offset=" & Text.From(Index * EntitiesPerPage),
            Top   = "?count=" & Text.From(EntitiesPerPage),
            Url   = BaseUrl & Top & "&" & Skip,
            Json  = GetJsonList(Url)
        in  Json,

if that doesn't work due to the file having both forms try:

 GetPage = (Index) =>
        let     
            Skip  = "offset=" & Text.From(Index * EntitiesPerPage),
            Top   = "?count=" & Text.From(EntitiesPerPage),
            Url   = BaseUrl & Top & "&" & Skip,
            Json  = GetJsonList(Url),
            Value = if Json is record then Json[#"value"] else else Json
        in  Value,

[Shamatix]

Hi Artemus,

Thanks a ton for the solution!

However I have another major issue now:S

By chance you have skype and I can try descibe my issue there?

I am getting this:

There were too many elements in the enumeration to complete the operation.

Best regards