Forum Discussion

ucmaoTWG's avatar
ucmaoTWG
Icon for Helper I rankHelper I
2 years ago

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

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 f...
General Comment
Need Help
SumitChandra's avatar
SumitChandra
Frequent Visitor
2 years ago

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's avatar
    ucmaoTWG
    Icon for Helper I rankHelper I
    2 years ago

    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,