Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
Solved! Go to Solution.
Hi,
This should help.
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
Hi,
This should help.
after this advanced editing, right click the Date/Time columns to change type to Date! No errors 😀
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
71 | |
56 | |
39 | |
35 |
User | Count |
---|---|
66 | |
66 | |
59 | |
53 | |
45 |