API query error "Cannot convert the value false to Type list".

[Petsnell]

Hello, everyone.

I am new to API calls in Power Query and got this code from a colleague who is unavailable for some time. To me this is quite complex M-code that I am having a hard time debugging. It is probably unsolvable with the provided information, but I am giving it a shot.

The error code we are getting is 

let

  startDate = DateTimeZone.FromText(FromDate),

  endDate = DateTimeZone.FromText(ToDate),

  #"Api url" = "",

  #"Raw data from api"  = Json.Document(Web.Contents(#"Api url",

    [

      Headers = [#"Content-Type"="application/json",#"Authorization"="Bearer "&AuthToken&"" ]

    ])),

  GetChunk = (ChunkFrom as datetimezone, ChunkTo as datetimezone) =>

    #"Api url" = ApiUrl ="&DateTimeZone.ToText(ChunkFrom, "yyyy-MM-ddTHH:mm:ssZ")&"&toDate="&DateTimeZone.ToText(ChunkTo, "yyyy-MM-ddTHH:mm:ssZ"),

  GetChunks = (chunkStartDate as datetimezone) =>

    let chunkEndDate = Date.AddDays(chunkStartDate, 14),

    actualStartDateTime =  if chunkStartDate = startDate then chunkStartDate else chunkStartDate + #duration(0,0,0,1),

    actualEndDateTime =  if (chunkEndDate > endDate) then endDate else chunkEndDate

    in

      GetChunk(actualStartDateTime, actualEndDateTime),

ExpandResult = (result as list) =>

    if List.Count(result) < 1

      then

        #table({"StartDateTime"},{{endDate}})

      else

        let

          table_converted = Table.FromList(result, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

          expanded = Table.ExpandRecordColumn(table_converted, "Column1", {"id", "userId", "externalId"})

        in

          Table.TransformColumnTypes(expanded,{{"StartDateTime", type datetimezone}}),

#"Chunks" = List.Generate( () => GetChunks(startDate), (result) => List.Count(result) > 1, (result) => GetChunks(List.Max(ExpandResult(result)[StartDateTime])), (result) => ExpandResult(result) ),

  result = Table.Combine( #"Chunks")

in

  #"Chunks"

[Petsnell]

After isolating the code it seems the issue is related to 

  GetChunk = (ChunkFrom as datetimezone, ChunkTo as datetimezone) =>

    #"Api url" = ApiUrl ="&DateTimeZone.ToText(ChunkFrom, "yyyy-MM-ddTHH:mm:ssZ")&"&toDate="&DateTimeZone.ToText(ChunkTo, "yyyy-MM-ddTHH:mm:ssZ")

as it returns "False", and not a list. 

[BA_Pete]

Hi @Petsnell ,

My initial guess is that you've got extra equals signs in there that shoudn't be. These are the only sections where Power Query can resolve to a boolean output.

My second guess is that it should look something like this instead:

GetChunk = (ChunkFrom as datetimezone, ChunkTo as datetimezone) =>
#"Api url" =
ApiUrl
    & "DateTimeZone.ToText(ChunkFrom, "yyyy-MM-ddTHH:mm:ssZ")"
    & "DateTimeZone.ToText(ChunkTo, "yyyy-MM-ddTHH:mm:ssZ")"

Pete

[Petsnell]

Much appreciated, BA_Pete. From what I gather, the issue did not seem to be the equals signs but rather the location of the API-call. Instead of iterating the function with the new variable name it reiterated the renaming process.