Power Query/Excel: Error with DateTime.ToText()

Question

In Excel 2016 - Query Editor - Advanced Editor.

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 ?

Edit: Error says: Expression.Error: We cannot convert a value of type List to type DateTime. Details: Value=List Type=Type

By clicking right from "error" in the cell the error-message will be shown in the preview-window. It is often helpful to copy this into your thread as well. — ImkeF

ImkeF ImkeF · Accepted Answer · 2016-04-21T15:05:19

DateTime.FromText expects a cell as a first argument instead of a column.

This added custom column would create a textstring that concatenates the 2 Dates with the desired format and "-" as a separator:

   String = Table.AddColumn(#"TimeRange", "String", each DateTime.ToText([From], DateFormatString)&"-"&DateTime.ToText([To], DateFormatString))

Power Query/Excel: Error with DateTime.ToText()

1 Answers