Importing a column into power query in a dataset without changing any formatting.

[ucmaoTWG]

Hello,

I have a column that is a date/time field specifically formatted to show like the below.

2023-08-14 02:40:28.097

When i try and load this into power query, it consistently modifies the formatting so that it changes the field entirely. Is there any way to make it so that when it loads into power query it remains unformatted in the original form?

Thanks,

[SumitChandra]

Hi,

Depending on the locale settings or default formatting rules, Power Query may automatically format date and time fields when they are loaded. You can take the following actions to maintain the date/time column's original format:

Load the Data: Load the data into Power Query as you normally would.

Change the Data Type: Locate the column in the Power Query Editor that has the date and time values. Select the column heading by clicking the drop-down arrow. 
"Change Type" will appear; select "Text."

Hope this will works for you 😊

[ucmaoTWG]

This doesn't work, it formats it as 'NOV 4, 2023 at 6:34 PM' for example.

The way that i solved this is by fixing it in sql with a convert to NVARCHAR with formatting 121. 

CONVERT(NVARCHAR(50),date,121)

Power query then reads this as a string and doesn't convert to date, even though it is technically a date.

Thanks,