Date with time zone

Power BI Data Model
1

Our system is giving me this format 2025-03-14T00:00:00-04:00. I have tried the following multiple ways to transform to mm/dd/yyyy hh:mm:ss to no avail.

  1. Power Pivot
  2. Power BI
  3. M

Can any forum member assist?

Linda

2

Hi @llchoong,
Using the Mentor built within the EDNA Platform this what comes up to your problem
To transform the datetime format 2025-03-14T00:00:00-04:00 into mm/dd/yyyy hh:mm:ss, you can use different approaches depending on the tool you’re using. Below are the solutions for M code (Power Query), Power Pivot (DAX), and Power BI.

M Code (Power Query)

You can use the following M code to transform the datetime format in Power Query:

let
    Source = "2025-03-14T00:00:00-04:00",
    DateTimeValue = DateTime.FromText(Source),
    FormattedDate = DateTime.ToText(DateTimeValue, "MM/dd/yyyy HH:mm:ss")
in
    FormattedDate

Copy

Code Explainer

Run Code

Power Pivot (DAX)

For Power Pivot, you can create a calculated column with the following DAX formula:

FormattedDate = FORMAT(UTCNOW(), "MM/dd/yyyy HH:mm:ss")

Copy

Code Explainer

Run Code

You would need to parse your string to a datetime value first if it’s stored as text:

FormattedDate = FORMAT(DATEVALUE(MID([YourColumn], 1, 10)) + TIMEVALUE(MID([YourColumn], 12, 8)), "MM/dd/yyyy HH:mm:ss")

Copy

Code Explainer

Run Code

Power BI (DAX)

In Power BI, use the same method as Power Pivot since DAX functions work similarly:

FormattedDate = FORMAT(DATEVALUE(MID([YourColumn], 1, 10)) + TIMEVALUE(MID([YourColumn], 12, 8)), "MM/dd/yyyy HH:mm:ss")

Copy

Code Explainer

Run Code

Summary

These snippets will convert your datetime string into the desired format across different tools. Make sure to adjust [YourColumn] with the actual column name used in your reports.

Give Mentor a try to some of your questions.
thanks
Keith