Forum Discussion

NirH_at_BITeam's avatar
NirH_at_BITeam
Frequent Visitor
7 years ago
Solved

Convert DateTime into Date in Query Editor using M function

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

 

Data Modeling

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      after this advanced editing, right click the Date/Time columns to change type to Date! No errors ğŸ˜€

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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