none
Create Date Column out of Full Month Name and Year RRS feed

  • Question

  • I am trying to use Power Query to create a new column of data as a Date.  My data contains two columns: Month as the full text name and Year, for purposes of this example, I will set all days to the 15th.  In Excel, I would simply use a formula to convert the text month to the proper date part.

    Example:  [Month] = "January" and [Year] = 2013

    DATEVALUE([Month]&" 15, "&[Year])

    What is the syntax to Add a Column in Power Query with this conversion? 


    Thanks, Barbara

    Wednesday, January 15, 2014 10:16 PM

Answers

  • For an existing table in the editor with columns Month and Year, click "Insert Custom Column" and use this as the formula:

    = Date.FromText([Month] & " 15," & Number.ToText([Year]))

    This will create a formula which looks something like this:

        InsertedCustom = Table.AddColumn(Source, "Custom", each Date.FromText([Month] & " 15," & Number.ToText([Year])))

    Note that one difference between Excel and Power Query is that Excel will let you concatenate text and numbers with "&" but Power Query requires that you first convert the number to text.


    • Proposed as answer by Curt Hagenlocher Wednesday, January 15, 2014 10:30 PM
    • Edited by Curt Hagenlocher Wednesday, January 15, 2014 10:31 PM added detail
    • Marked as answer by Barbara Raney Wednesday, January 15, 2014 10:34 PM
    Wednesday, January 15, 2014 10:30 PM

All replies

  • For an existing table in the editor with columns Month and Year, click "Insert Custom Column" and use this as the formula:

    = Date.FromText([Month] & " 15," & Number.ToText([Year]))

    This will create a formula which looks something like this:

        InsertedCustom = Table.AddColumn(Source, "Custom", each Date.FromText([Month] & " 15," & Number.ToText([Year])))

    Note that one difference between Excel and Power Query is that Excel will let you concatenate text and numbers with "&" but Power Query requires that you first convert the number to text.


    • Proposed as answer by Curt Hagenlocher Wednesday, January 15, 2014 10:30 PM
    • Edited by Curt Hagenlocher Wednesday, January 15, 2014 10:31 PM added detail
    • Marked as answer by Barbara Raney Wednesday, January 15, 2014 10:34 PM
    Wednesday, January 15, 2014 10:30 PM
  • Perfect!  That is Exactly what I needed!

    Thanks, Barbara

    Wednesday, January 15, 2014 10:34 PM
  • I used your technique and it works fine, but I can't get it to sort correctly on the report. In the query, I've sorted the date column to ascending- and it appears that it is in order. But on the report the custom date I created with your method is out of order. Seems like I'm doing something small wrong, and would appreciate some redirection.


    Thanks!

    Thursday, May 26, 2016 11:41 PM
  • Try applying the sort-order in the Data-Editor (DAX) instead (the M-engine isn't reliable when it comes to keeping sort-order).

    Imke Feldmann TheBIccountant.com

    Saturday, May 28, 2016 5:37 PM
    Moderator