in PowerQueryEditor, Convert DateTime to YYYMM

[Anonymous]

I am reading a table from SQL Server and I need to convert a datetime column to YYYYMM. or YYYY-MM.  Intellisense is not helping much.  My last failed attempt was this:

= Table.AddColumn(dbo_OLAPJobHistory, "dtRunMonth", format('dbo_OLAPJobHistory'[dtRunDate],"YYYYMM"))

Help?

[bcdobbs]

I think you want date.totext

syntax in:

https://docs.microsoft.com/en-us/powerquery-m/date-totext

Date.ToText([column], "yyyyMM")

[Anonymous]

That makes sense, except I am having trouble with the addcolumn syntax.  I stepped thru the "Custom Column" in the Power Query Editor and it built this, which throws an error:

= Table.AddColumn(dbo_OLAPJobHistory, "dtRunMonth", each DateTime.ToText(dbo_OLAPJobHistory[dtRunDate],"YYYYMM"))

What am I missing? 

[bcdobbs]

I'm not by a computer but I think your problem is power query doesn't want the table reference in front of the colum (that's dax syntax).

try 

[dtRunDate] instead of dbo_OLAPJobHistory[dtRunDate]

[Anonymous]

This worked for me:

= Table.AddColumn(dbo_OLAPJobHistory, "dtRunMonth", each DateTime.ToText([dtRunDate],"yyyyMM"))

Thank you

[Anonymous · Accepted solution]

Unfortunately, this leaves  you with a text field.  If you want it to be useable as a date:

From the Power Query Editor, add a new column as beginning of month:

Table.AddColumn(#"Filtered Rows", "dtStartOfMonth", each Date.StartOfMonth([dtRunDate]), type datetime)

From the Data View, format the date using yyyy-mm:

dtRunMonth = FORMAT(OLAPJobHistory[dtStartOfMonth],"yyyy-mm")

[bcdobbs]

Missed that you said datetime in question. Glad it's working.