Power Query - Change DateTime to Text without altering format

0
votes

I'm trying to convert a DateTime field to Text in Power Query.

Currently the DateTime format is dd/mm//yyyy hh:mm:ss (e.g. 02/04/2006 00:00:00)

When I change the type in Power Query to Text, the output is formatted as mmmm d yyy hh:mm AM/PM (e.g. Apr 2 2006 12:00AM)

I want to retain the original format when I convert to text, how can I do this?

Thanks

1
datetimepowerbipowerquerypowerbi-desktop

1 Answers

0
votes

There isn't really an "original format" when it's stored as DateTime, it's just how your program chooses to represent it (which can vary regionally).

That said, you should be able to specify exactly the format you want. When you use the GUI to transform the data type it will produce a step that looks like this:

= Table.TransformColumnTypes(#"Previous Step",{{"Column1", type text}})

Modify that a bit so it looks like this instead:

= Table.TransformColumns(#"Previous Step",
    {{"Column1", each DateTime.ToText(_, "MMM d yyyy hh:mm tt"), type text}})