Date format issue

[Sumit_Bhokare]

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

08-01-2024	08-12-2024	08-09-2024	08-06-2024
08/22/2024	08/26/2024	08/26/2024	08/27/2024

actual dates are

01st Aug 24	12th Aug 24	09th Aug 24	06th Aug 24
22nd Aug 24	26th Aug 24	26th Aug 24	27th Aug 24

[v-stephen-msft]

Hi @Sumit_Bhokare ,

Thanks for reaching out to our community.

Pls has your problem been solved? I think the above users have already provided the great solutions. If so, accept the reply as a solution. This will make it easier for the future people to find the answer quickly.

If not, please provide a more detailed description, preferably some virtual sample data, and the expected results.

Best Regards,

Stephen Tao

[ZhangKun]

Maybe your sample is not enough, there is no problem in China. There are three ways to solve this problem:

1. When you set the type by clicking the table title, the Table.TransformColumnTypes function will be called to implement it. The third parameter of the function can specify the region (if not specified, the region in the computer settings will be used), for example: "en-US" for the United States, "zh-CN" for China, etc.

Note: If you use the cloud, it is best to specify this parameter because the local and remote server regions are usually different.

let
    源 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrDQNTDUNTIwMlHSAXEMjZA4BpbIHDMIJ1YHpEvfyEgfLqdvZIaTYw7hxMYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [d1 = _t, d2 = _t, d3 = _t, d4 = _t]),
    更改的类型 = Table.TransformColumnTypes(源,{{"d1", type date}, {"d2", type date}, {"d3", type date}, {"d4", type date}}, "zh-CN")
in
    更改的类型

2. In the sample you provided, the style of each row is the same. You can transpose it first, convert the format, and then transpose it back. But if there is a lot of data, it is best not to use this method.

3. Write a custom function to handle this conversion, but this method requires the data to be very regular, otherwise there is no way to determine the position of the month and day. For example, the following example:

let
    源 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrDQNTDUNTIwMlHSAXEMjZA4BpbIHDMIJ1YHpEvfyEgfLqdvZIaTYw7hxMYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [d1 = _t, d2 = _t, d3 = _t, d4 = _t]), 
    fx = (dt) => Date.FromText(dt, [
        // just for demonstration
        //assuming include-use day-month-year format, otherwise use month/day/year format.
        Format = if Text.Contains(dt, "-") then "d-M-yyyy" else "M/d/yyyy"
    ]), 
    // the column name to be converted
    Names = {"d1", "d2"}, 
    Result = Table.TransformColumns(源, List.Zip({Names, List.Repeat({fx}, List.Count(Names)), List.Repeat({type date}, List.Count(Names))}))
in
    Result

[p45cal · Accepted solution]

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]

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.