Power Query empty list is not recognized as empty

Question

I'm currently developing a PowerBi connector. I get the data from a REST-webservice of a company. Tt's about the pagination now. The Problem is that if the pageSize is 100 and there are 101 records in the database (first call i get 100, second i get 1 record), i can't stop the call cause List.Count gives me not zero on an empty list.

Example data:

{"records":[{"firstname":"...","lastname":"..",}]}

Code:

json = Json.Document(Web.Contents(url, [
    Content = Text.ToBinary(body)])),

    records = Table.FromRecords({json}),

    recordsExpaned = Table.ExpandTableColumn(records, "records", {"firstname", "lastname"}), 
    recordsTable = Table.ToList(recordsExpaned), 
    result = 
        if(List.Count(recordsTable) < 1) then
            Data.Contacts(json) meta [NextPage = null]
        else
            SData.Contacts(json) meta [NextPage = page + 1]

I expect that the List.Count(recordsTable) is 0 or null, if the records are

{"records":[]}

But this isn't the case.

{"records":[]}
{"records":[{"firstname":"...","lastname":"..",}]}

gives the same Count value.

This is driving me crazy. How can i check if the list is really empty like

{"records":[]}

If i check it like this

if(List.Count(acd) < 2) then

then it stops on the empty list but also on a list with only one argument(thats right). This means to me that the empty list is not really empty?!

EDIT: Thanks to @MarcelBeug this is working

json = Json.Document(Web.Contents(url, [
Content = Text.ToBinary(body)])),

data = Table.FromRecords({json}),

recordsExpaned = Table.ExpandTableColumn(data, "records", {"firstname", "lastname"}), 
recordsTable = Table.ToList(recordsExpaned), 
result = 
    if(List.IsEmpty(json[records]) = true) then
        Data.Contacts(json) meta [NextPage = null]
    else
        Data.Contacts(json) meta [NextPage = page + 1]

the following line was the game changer

if(List.IsEmpty(json[records]) = true) then

it seems that the IsEmpty-Function is looking for the element "records" in the json although i never declared "records". It seems that the function is parsing the element to search for it, but im not an expert in Power Query M.

MarcelBeug MarcelBeug · Accepted Answer · 2017-11-09T02:31:26

You first need to parse the string as a JSON value, resulting in a record. Then you can check if the "records" field of that record contains an empty list.

Example (returns true):

let
    Source = "{""records"":[]}",
    #"Parsed JSON" = Json.Document(Source),
    Custom1 = List.IsEmpty(#"Parsed JSON"[records])
in
    Custom1

Power Query empty list is not recognized as empty

1 Answers