Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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:
I think you want date.totext
syntax in:
https://docs.microsoft.com/en-us/powerquery-m/date-totext
Date.ToText([column], "yyyyMM")
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?
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]
This worked for me:
= Table.AddColumn(dbo_OLAPJobHistory, "dtRunMonth", each DateTime.ToText([dtRunDate],"yyyyMM"))
Thank you
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: