Expression.SyntaxError: Token Eof expected.

[anwernuman]

Getting Expression.SyntaxError: Token Eof expected. error in the below code. I have checked everything and it seems correct to me. What could be the issue here?

= let
    GoogleDistance = (origin as text, destination as text) =>
    let
        apiKey = key,
        url = "https://maps.googleapis.com/maps/api/distancematrix/json?origins=" & Text.Replace(origin, " ", "+") & "&destinations=" & Text.Replace(destination, " ", "+") & "&key=" & apiKey,
        attemptedSource = try Json.Document(Web.Contents(url)) otherwise null,
        isValid = attemptedSource <> null and "rows" in attemptedSource,
        Rows = if isValid then attemptedSource[rows] else null,
        Elements = if Rows <> null and List.Count(Rows) > 0 and Record.HasFields(Rows{0}, "elements") then Rows{0}[elements] else null,
        Distance = if Elements <> null and List.Count(Elements) > 0 and Record.HasFields(Elements{0}, "distance") then Elements{0}[distance][text] else "Unavailable",
        Duration = if Elements <> null and List.Count(Elements) > 0 and Record.HasFields(Elements{0}, "duration") then Elements{0}[duration][text] else "Unavailable"
    in
        [Distance = Distance, Duration = Duration]
in
    GoogleDistance

Expression.SyntaxError: Token Eof expected. 

[danextian · Accepted solution]

This line of code is causing a problem. M doesnt have a direct  equivalent of IN operator in DAX.

"rows" in attemptedSource

If you're checking whether attemptedSource has a field called rows, use

Record.HasFields(attemptedSource, "rows"),

[anwernuman]

You are amazing. This worked

[anwernuman]

Key is defined correctly but I am still getting the error

[DataNinja777]

Hi @anwernuman ,

The Expression.SyntaxError: Token Eof expected. error in Power Query typically suggests a missing or misplaced character, such as a missing closing parenthesis, missing in keyword, or an improperly formatted variable reference. One of the potential issues in your code is that the variable key is undefined. In the line:

apiKey = key,

if key is not assigned anywhere, Power Query will throw an error. Ensure key is properly defined, or replace it with an actual API key string.

Another issue is the = before let at the beginning of your code. The let statement should not have an = before it. The correct syntax is to define the function directly without an equals sign:

let

instead of

= let

Additionally, ensure that Web.Contents(url) is returning valid JSON. If it is not properly formatted, Json.Document(Web.Contents(url)) will fail. You can test the API response by using Text.FromBinary(Web.Contents(url)) to inspect the raw response.

Here is a corrected version of your code:

let
    GoogleDistance = (origin as text, destination as text) =>
    let
        apiKey = "your_api_key_here", // Ensure apiKey is defined properly
        url = "https://maps.googleapis.com/maps/api/distancematrix/json?origins=" & 
               Text.Replace(origin, " ", "+") & "&destinations=" & 
               Text.Replace(destination, " ", "+") & "&key=" & apiKey,
        attemptedSource = try Json.Document(Web.Contents(url)) otherwise null,
        isValid = attemptedSource <> null and "rows" in attemptedSource,
        Rows = if isValid then attemptedSource[rows] else null,
        Elements = if Rows <> null and List.Count(Rows) > 0 and Record.HasFields(Rows{0}, "elements") then Rows{0}[elements] else null,
        Distance = if Elements <> null and List.Count(Elements) > 0 and Record.HasFields(Elements{0}, "distance") then Elements{0}[distance][text] else "Unavailable",
        Duration = if Elements <> null and List.Count(Elements) > 0 and Record.HasFields(Elements{0}, "duration") then Elements{0}[duration][text] else "Unavailable"
    in
        [Distance = Distance, Duration = Duration]
in
    GoogleDistance

This version removes the unnecessary =, ensures apiKey is assigned correctly, and keeps the syntax properly structured. Try this and check if the issue is resolved.

Best regards,

[anwernuman]