none
Excel Power Query Error RRS feed

  • Question

  • I am using the get and transform to pull some google maps json data on call of a M function in PowerQuery. The table I’m using has [ Start | Finish ] and I use powerquery to create a custom column that will contain the pulled data. However; I am receiving this error with a mix bag of errors and tables:

     

    An error occurred in the ‘OrigDrivingTime’ query. Expression.Error: There weren't enough elements in the enumeration to complete the operation.

    Details:

        List

     

    No clue why. Here is my code, with a generic key:

     

    let OrigDrivingTime = (start as text, finish as text)=>

     

    let

        Source = Json.Document(Web.Contents("https://maps.googleapis.com/maps/api/directions/json?origin="&start&"&destination="&finish&"&key=key")),

        #"Converted to Table" = Record.ToTable(Source),

        Value = #"Converted to Table"{1}[Value],

        Value1 = Value{0},

        legs = Value1[legs],

        legs1 = legs{0},

        duration = legs1[duration],

        #"Converted to Table1" = Record.ToTable(duration),

        #"Removed Columns" = Table.RemoveColumns(#"Converted to Table1",{"Name"}),

        #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Columns",1)

    in

        #"Removed Bottom Rows"

    in

        OrigDrivingTime

     


    Sunday, July 15, 2018 5:23 PM

Answers

  • Hi Javier. My guess is the error is coming from one of the places you're indexing into a set of results (e.g. Value{0}). My recommendation would be to create another version of this query that is not a function. Just hard-code the start and finish values where you're seeing errors, then click through each step to determine where the problem is originating. After fixing the source of the error, you can then update the function version.

    Ehren

    Monday, July 16, 2018 11:26 PM
    Owner