none
Function Date.ToText fails with partial date format

    Question

  • Hi,

    I have a problem with the Power Query function Date.ToText:

    • I add a new column based on a date column, by using Date.ToText([my-date-column],"YYYY-MM").
    • I am expecting getting the year and month number as result, like: "2013-05", but I get: "YYYY-05"!
    • However, when I specify the format "YYYY-MM-DD", I've get the right value: "2013-05-31".
    • Either by forcing a date, as a test purpose only, it return the same error:
      Date.ToText(Date.From("2013-05-31"),"YYYY-MM"))  gives "YYYY-05".

    Frank

    Environnement:

    • Excel 2013 32 bits - Version: 15.0.4420.1017
    • Power Query 32 bits - Version: 1.5.3296.1161
    • Windows Server 2008 R2

    François LESSARD

    Monday, September 16, 2013 5:59 PM

Answers

All replies

  • The second argument is case sensitive, you have to use "yyyy" instead.

    Use this:

    Date.ToText([my-date-column],"yyyy-MM")

    Marco Russo
    http://www.sqlbi.com


    Tuesday, September 17, 2013 4:35 AM
  • Thanks for the hint Marco.  That resolved my issue. 

    Still it's strange that the full format in uppercase is working: "YYYY-MM-DD"!  In the document Power Query Formula Library Reference (July 2013), the examples are in upercases.


    François LESSARD

    Tuesday, September 17, 2013 2:29 PM