Forum Discussion
Date format issue
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 ChangedTypeThis:
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)
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