Previously, I shared how to transform date/time values in Power BI using DAX. But I’m working on building more using M formulas as those are less resource-intensive and improve report performance.
The following was my solution for correcting a time zone issue in which the original data’s date/time column was 5 hours ahead.
=[#"Date/Time"] - #duration(0, 5, 0, 0)
That example subtracts 5 hours from a column called Date/Time. You can change the column name to your own, of course. Also update the numeric values in the #duration part to add or subtract days, hours, minutes, and seconds, respectively.
#duration(days, hours, minutes, seconds)
And just change the – to + to add instead of subtract.
Examples
Add seven days (a week)
=[#"Date/Time"] + #duration(7, 0, 0, 0)
Correct a time zone – Add hours
=[#"Date/Time"] + #duration(0, 5, 0, 0)
Correct a time zone – Subtract hours and minutes
=[#"Date/Time"] - #duration(0, 2, 30, 0)
Learn more about M formulas and functions
Check out “Add a custom column in Power BI Desktop” from Microsoft Docs to get started with creating custom columns using M formulas instead of DAX.
This Power Query M function reference guide is super helpful for learning more about available functions to use in your M formulas in Power Query Editor.