none
Need help with error handling RRS feed

  • Question

  • Folks,

    This function takes a security symbol as input.  It appears that the api returns no data when the symbol is invalid (there is no data).  This function is used to retrieve data for multiple symbols.  Two questions here, please:

    1. How to capture this condition.  Table.TransformColumnTypes throws the error because the column doesn't exist (i.e. no returned table)

    2. Why does the query stop working at the first error?!  What can be done to relax this condition and allow me to clean up the errors after all of the symbols have been processed?

    MSFT (valid symbol)

    XYZZ (Invalid Symbol)

    Many thanks.

    let GetEODOptions = ( Symbol as text ) as table =>
        let
            Source = Csv.Document(Web.Contents("http://ondemand.websol.barchart.com/getEquityOptions.csv?apikey=ac18dcd0119cd78beb42c9a9633cdbb6&underlying_symbols="
                &Symbol &"&fields=volatility,delta,gamma,theta,vega,rho,bid,bidSize,bidDate,ask,askSize,askDate,premium,settlement,lastTradeDate,openInterest"),[Delimiter=",", Columns=31, Encoding=1252, QuoteStyle=QuoteStyle.None]),
            #"Promoted Headers" = Table.PromoteHeaders(Source)
            #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"underlying_symbol", type text}, {"symbol", type text}, {"exchange", type text}, {"type", type text}, {"strike", type number}, {"expirationDate", type date}, {"expirationType", type text}, {"date", type date}, {"volatility", type number}, {"delta", type number}, {"gamma", type number}, {"theta", type number}, {"vega", type number}, {"rho", type number}, {"bid", type number}, {"bidSize", Int64.Type}, {"bidDate", type date}, {"ask", type number}, {"askSize", Int64.Type}, {"askDate", type date}, {"open", type number}, {"high", type number}, {"low", type number}, {"last", type number}, {"previous", type number}, {"change", type number}, {"percentChange", type number}, {"premium", type text}, {"settlement", type text}, {"lastTradeDate", type text}, {"openInterest", Int64.Type}})
        in
            #"Changed Type"
    in
        GetEODOptions

    Monday, October 31, 2016 12:55 PM

Answers

All replies

  • You can use  "try ... otherwise" on different places here (either when calling the function or in the Transform-step).

    Have a look at this article: http://blog.gbrueckl.at/2013/12/error-handling-in-power-query/


    Imke Feldmann TheBIccountant.com

    • Marked as answer by Nin Sute Tuesday, November 1, 2016 9:10 AM
    Monday, October 31, 2016 3:04 PM
    Moderator
  • If there a chance that the source can return an empty table, then there's probably no point calling any other steps, just to generate an error.

    For example, in the changed type step, an "if" statement can prevent any further processing if the source is an empty table. In either case ("if" or error handing), what do you return? One possibility:

    #"Changed Type" = if IsEmpty(Source) then #table({"InvalidData"}, {{"Invalid security symbol - "&Symbol}}) else Table.TransformColumnTypes(.......)



    Monday, October 31, 2016 8:14 PM
  • Thanks Imke.  Along with the try/otherwise clauses I first created a dummy table to be utilized in lieu of the empty table. :)
    Tuesday, November 1, 2016 9:10 AM