Forum Discussion

Sumit_Bhokare's avatar
Sumit_Bhokare
Regular Visitor
1 year ago
Solved

Date format issue

hello, I need one help on date format. I have a dataset within which date format's are different & want to know is there any way I can correct that within power query. below snapshot of same 0...
Need Help
  • p45cal's avatar
    p45cal
    1 year ago

    Try this (using US locale (the date from which you're converting) (the last argument of the Table.TransformColumnTypes) to change both kinds of date successfully):

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrDQNTDUNTIwMlHSAXEMjZA4BpbIHDMIJ1YHpEvfyEgfLqdvZIaTYw7hxMYCAA==", BinaryEncoding.Base64), Compression.Deflate))),
    ChangedType = Table.TransformColumnTypes(Source,{{"Column1", type date}, {"Column2", type date}, {"Column3", type date}, {"Column4", type date}},"en-US")
in
    ChangedType

     

     

    This:

    becomes this:

    (I'm in the UK so these dates are showing as dmy, but they're proper dates so will show properly in your locale, that is, they're all August dates)

     

jgeddes's avatar
jgeddes
Super User
1 year ago

If you are experiencing issues with date formatting you may be able to leverage the Date.From() function specifying the culture you require.

Date.From() - Microsoft Learn 

= Table.TransformColumns(previousStep, {{"date", each Date.From(_, "en-US"), type date}})

would put the date in month/day/year.

= Table.TransformColumns(previousStep, {{"date", each Date.From(_, "de-DE"), type date}})

would put the date in day/month/year.

 

Hope this helps.