Convert DateTime into Date in Query Editor using M function

[NirH_at_BITeam]

Hi,

I'm looking for the right function to replace a DateTime to a Date in M.
The challenge is that the DateTime length may vary based on month and day number, so for example I have: 8/5/2017 12:00:00 AM OR 10/21/2019 12:00:00 AM

In both cases I need to keep only the date without the time and change it to: "mm/dd/yyyy".

I used the split function but that requires 2-3 steps in query. Not a big deal but I’m after adding a custom column with the right M formula that will get me the right result.

Thanks!
NH

[Anonymous]

Hi @NirH_at_BITeam 

Try wrapping the original DateTime data with the 'DateTime.Date()' function

Also, make sure the date you are referring to is actually a date and not a string

For example:

DateTime.Date(
    Date.AddDays( 
    Date.StartOfMonth( DateTime.LocalNow())
    ,-1))

You may find this link on how to use the function useful

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

Best,

Eric

[Ashish_Mathur · Accepted solution]

Hi,

This should help.

[Anonymous]

after this advanced editing, right click the Date/Time columns to change type to Date! No errors 😀

[v-qiuyu-msft]

Hi @NirH_at_BITeam, 

You can go to Query Editor, click on the left icon of the column name, then select the Date to change DateTime type to Date type. The corresponding M query uses the Table.TransformColumnTypes() function. 

Best Regards,
Qiuyun Yu