Change Type locale not working in query editor for Date

[ashish18]

After convering it to Date/Time using Change Type or Change Type using Locale (en-US),it displays wrong as below

Please advise if you have any leads..Thanks in advance for your help !!

[v-saisrao-msft]

Hi @ashish18,

We haven’t heard back from you in a while regarding your issue. let us know if your issue has been resolved or if you still require support.

Thank you.

[v-saisrao-msft]

Hi @ashish18,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

[v-saisrao-msft]

Hi @ashish18,

Have you had a chance to review the solution we shared by @MasonMA ? If the issue persists, feel free to reply so we can help further.

Thank you.

[MasonMA]

@ashish18 

Hi, one of the workarounds would be creating a function using below M, 

(DateText as text) as datetime =>
let
    Day = Text.Start(DateText, 2),
    Month = Text.Middle(DateText, 3, 3),
    Year = Text.Middle(DateText, 7, 2),
    Time = Text.End(DateText, 8),
    FullDate = Day & " " & Month & " 20" & Year & " " & Time,
    ConvertedDate = DateTime.FromText(FullDate)
in
    ConvertedDate

then invoke it in Table.TransformColumns()

let
    Source = Source,
    ConvertedTable = Table.TransformColumns(
        Source,
        {{"Column1", each Convert(_), type datetime}}
    )
in
    ConvertedTable

[ashish18]

hi @MasonMA  is there a way to convert text to date format with correct date,

I need same value in date format i.e. text value 

18/Feb/25 12:15 PM 

 should appear as 2/18/2025 in date format. Currently its displaying as 

2/25/2018 12:15:00 PM

 which is wrong. Date is 18th Feb 2025

[MasonMA]

Hi, did you give my solution a try? Your original 'Create' column is of Text type. 

[ashish18]

Hi @MasonMA it did not work 😞

[MasonMA]

@ashish18  Could you show the error? or share some screenshots how they look?

[ashish18]

Hi @MasonMA  I tried pulling this code uisng blank query but "Created" column not accessible in in blank query in advanced editor

[MasonMA · Accepted solution]

@ashish18 

Hi, or you can try below M. Replace with your table and column name. I put this as function only because it's good for reuse later 

let
    Source = YourTable, //YourTable to be reploaced
    ConvertedTable = Table.TransformColumns(
        Source,
        {
            {"Column1", each //Column1 to be replaced
                let
                    Day   = Text.Start(_, 2),
                    Month = Text.Middle(_, 3, 3),
                    Year  = Text.Middle(_, 7, 2),
                    Time  = Text.End(_, 8),
                    FullDate = Day & " " & Month & " 20" & Year & " " & Time
                in
                    DateTime.FromText(FullDate),
             type datetime}
        }
    )
in
    ConvertedTable

[tayloramy]

Hi @ashish18, 

What date format are you wanting it to display as? 
You can select different date formats in the format dropdown, or use a custom format using a pattern. 

If you found this helpful, consider giving some kudos. If I answered your question or solved your problem, mark this post as the solution to help future users find it quickly. 

[ashish18]

Hi @tayloramy   I need mm/dd/yyyy format .EVen date in text format is good but when convert type,its mess up

[tayloramy]

@ashish18, 

Once the column is in a date type,  you can change the format by clicking on the column outside of the power query editor and selecting column tools, then format: 

If you found this helpful, consider giving some kudos. If I answered your question or solved your problem, mark this post as the solution to help future users find it quickly. 

[ashish18]

Hi @tayloramy  At first place itself Date is coming wrong so formating doesnt help once it is in date format. I tried with this option but year is taking as day and day is taking as year. 

Value in Text format is correct. I need same value in date format i.e. text value 

18/Feb/25 12:15 PM 

 should appear as 2/18/2025 in date format. Currently its displaying as 

2/25/2018 12:15:00 PM

 which is wrong. Date is 18th Feb 2025

[tayloramy]

Hi @ashish18, 

In this case, give what @MasonMA posted a try, that should properly extract the different elements of the date and rebuild a proper date field. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.