none
Error with DateTime.ToText() RRS feed

  • Question

  • In Excel 2016 - Query Editor - Advanced Editor.

    I am not familiar with Power Query language (I regularly use C#, JavaScript, SQL, ...)

    Here is my code:

        let
            SettingsSheet = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
            #"TimeRange" = Table.TransformColumnTypes(SettingsSheet,{{"From", type datetime}, {"To", type datetime}}),
            From = #"TimeRange"[From],
            To = #"TimeRange"[To],
            DateFormatString = "yyyy-MM-dd-THH:mm:ssZ",
            FormattedFrom = DateTime.ToText(#"TimeRange"[From], DateFormatString ),
            FormattedTo = DateTime.ToText(To, DateFormatString ),
            ...
            (Further in the code, I will need to concart formatted datetimes in a URL string.)

    If I finish with

        ...
        in
           #"TimeRange"

    I get a table with DateTimes, as expected.

    If I finish with
        ...
        #"testTable" = { From, To, FormattedFrom, FormattedFrom}
        in
           #"testTable"

    I get a table displaying
    1 List
    2 List
    3 Error
    4 Error

    while I expected
    3 and 4 to be date formatted as `DateFormatString` suggests.

    I have also tried without `DateFormatString` as in

        FormattedFrom = DateTime.ToText(#"TimeRange"[From]),

    and with `DateFormatString = "YYYYMMDD",` as shown in example on https://msdn.microsoft.com/en-us/library/mt253497.aspx
    But I got the same result.

    How am I supposed to format dates ?


    Thursday, April 21, 2016 2:25 PM

Answers

  • You're getting the error because you're attempting to concatenate text with lists.

    The form table[column] represents all of the values in "column" of the table "table"

    The form [column] represents the value in the current row of "table"

    Below is an example script up to url building step (the source is an Excel table with "From" and "To" date columns:

    let
       SettingsSheet = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
       TimeRange = Table.TransformColumnTypes(SettingsSheet,{{"From", type datetime}, {"To", type datetime}}),
       DateFormatString = "yyyy-MM-dd-THH:mm:ssz",
       FormattedFrom = Table.AddColumn(TimeRange, "FromDate", each DateTime.ToText([From], DateFormatString)),
       FormattedTo = Table.AddColumn(FormattedFrom, "ToDate", each DateTime.ToText([To], DateFormatString)),
       url = Table.AddColumn(FormattedTo, "URL", each "http://myurl/something/?from=" & [FromDate] & "&to=" & [ToDate])   
    in
       url

    Friday, April 22, 2016 2:56 PM

All replies

  • A few things to note:

    1) If you don't have a space in a variable's name, you can remove the # and " characters from the name (easier to read and less error prone).

    2) The correct format for your DateFormatString is "yyyy-MM-dd-THH:mm:ssz" Uppercase "Z" is not valid for the time zone.

    3) "YYYYMMDD" is NOT a valid date format in M. The documentation is incorrect. The year format must be lowercase "y" and the day format must be lowercase "d." Uppercase "Y" and "D" are treated as literals.

    4) It's not clear why you're creating From and To lists.


    Thursday, April 21, 2016 4:47 PM
  • On reviewing my point (4), I think I see what you're attempting to do. Instead of creating lists, create custom columns for the formatted "From" and "To" dates instead, e.g.

    let
       TimeRange = Table.TransformColumnTypes(SettingsSheet,{{"From", type datetime}, {"To", type datetime}}),
       DateFormatString = "yyyy-MM-dd-THH:mm:ssz",
       FormattedFrom = Table.AddColumn(TimeRange, "FromDate", each DateTime.ToText([From], DateFormatString)),
       FormattedTo = Table.AddColumn(TimeRange, "ToDate", each DateTime.ToText([To], DateFormatString)),

    Thursday, April 21, 2016 5:58 PM
  • For some reason I thought that #"Something" was a a temporary table and just Something was a single variable.

    The problem I get now it when I try to build my URL next steps are:

        url = "http://myurl/something/?from=" & FormattedFrom[FromDate] & "&to=" & FormattedTo[ToDate],
        Source = OData.Feed(url),
    (URL validity here doesn't matter, I can't write the real on on a forum.)

    For now I get

    Expression.Error: We cannot apply operator & to types Text and List. Details: Operator=&

    Left= [the starrt of URL]

    Right=List


    I also tried with "Text.From(FormattedFrom[FromDate]))"
    Friday, April 22, 2016 8:58 AM
  • One step further, I need to use

    FormattedFrom[FromDate]{0}

    Friday, April 22, 2016 9:41 AM
  • You're getting the error because you're attempting to concatenate text with lists.

    The form table[column] represents all of the values in "column" of the table "table"

    The form [column] represents the value in the current row of "table"

    Below is an example script up to url building step (the source is an Excel table with "From" and "To" date columns:

    let
       SettingsSheet = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
       TimeRange = Table.TransformColumnTypes(SettingsSheet,{{"From", type datetime}, {"To", type datetime}}),
       DateFormatString = "yyyy-MM-dd-THH:mm:ssz",
       FormattedFrom = Table.AddColumn(TimeRange, "FromDate", each DateTime.ToText([From], DateFormatString)),
       FormattedTo = Table.AddColumn(FormattedFrom, "ToDate", each DateTime.ToText([To], DateFormatString)),
       url = Table.AddColumn(FormattedTo, "URL", each "http://myurl/something/?from=" & [FromDate] & "&to=" & [ToDate])   
    in
       url

    Friday, April 22, 2016 2:56 PM