none
Strange Data Type Conversion RRS feed

  • Question

  • Hello,

    I'm able to import a table from Google Finance, but when I try to convert the "Date" field from text to date, I get an error only for the date range of March whilst other months date are correctly converted. I.e., the error I get for each row is: "DataFormat.Error: Impossible to analyze specified input as Date value. Details: Mar 31, 2015". Here is a link as example:

    https://www.google.com/finance/historical?q=EPA:AC&startdate=01/1/2015&enddate=10/22/2015&num=200&ei=u3AoVvjaKMaDsgHb4YyQCw

    Thanks in advance,

    Franco


    Thursday, October 22, 2015 9:06 AM

Answers

  • Ok....so here is my code (automaticaly generated by PQ)

    let
        Źródło = Web.Page(Web.Contents("https://www.google.com/finance/historical?q=EPA:AC&startdate=01/1/2015&enddate=10/22/2015&num=200")),
        Data1 = Źródło{1}[Data],
        #"Zmieniono typ" = Table.TransformColumnTypes(Data1,{{"Date", type date}, {"Open", type text}, {"High", type text}, {"Low", type text}, {"Close", type text}, {"Volume", type text}})
    in
        #"Zmieniono typ"

    Could you paste it into advanced editor window (in PQ) ?
    if an error still occurs, change the last step (below)

    #"Zmieniono typ" = Table.TransformColumnTypes(Data1, {{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Volume", type number}}, "en-US")
    

    • Marked as answer by Franco Fornari Thursday, October 22, 2015 11:55 AM
    Thursday, October 22, 2015 11:26 AM

All replies

  • Hi Franco,

    Do you have a latest version of PQ?

    For me everything is ok. Data import looks good in PQ (except last row which should be removed)

    Regards

    Thursday, October 22, 2015 10:08 AM
  • Hi Bill,

    I have Office 365 Small Business and then Excel 2016 already working. If for you it's all ok, then I fear I'm getting that error because I installed the italian version. But it is a strange behavior anyway, since the other 11 months are correctly recognized and date correctly converted. Only March don't, why?

    Best, Franco

    Thursday, October 22, 2015 10:30 AM
  • Ok....so here is my code (automaticaly generated by PQ)

    let
        Źródło = Web.Page(Web.Contents("https://www.google.com/finance/historical?q=EPA:AC&startdate=01/1/2015&enddate=10/22/2015&num=200")),
        Data1 = Źródło{1}[Data],
        #"Zmieniono typ" = Table.TransformColumnTypes(Data1,{{"Date", type date}, {"Open", type text}, {"High", type text}, {"Low", type text}, {"Close", type text}, {"Volume", type text}})
    in
        #"Zmieniono typ"

    Could you paste it into advanced editor window (in PQ) ?
    if an error still occurs, change the last step (below)

    #"Zmieniono typ" = Table.TransformColumnTypes(Data1, {{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Volume", type number}}, "en-US")
    

    • Marked as answer by Franco Fornari Thursday, October 22, 2015 11:55 AM
    Thursday, October 22, 2015 11:26 AM
  • Hi Bill,

    yes, it works changing tha last step, where you added "en-US" culture value. I still don't understand why the issue was affecting March only, but it works and that's all.

    Thank you very much for the help,

    Franco

    Thursday, October 22, 2015 11:55 AM