Forum Discussion
Post Patronchanging date from text to date
Yeah, I'm pretty sure this is the issue. This uses the optional "culture" argument of Table.TransformColumnTypes to pick the appropriate way of interpreting the date format.
Another way to do this is to tweak the M code directly. If the date format is dd/mm/yyyy, then you can write
Table.TransformColumnTypes( #"Split Column by Delimiter", { {"Item.1", type text}, {"Item.2", type date}, {"Item.3", type text}, {"Sales", type number} }, "en-IN" )If the date format is mm/dd/yyy, then use "en-US" for the last argument instead.
(There are plenty of other cultural choices but these are the two I remember for the sake of date interpretation.)
Super UserHi,
I cannot see the information in Power Query Editor, since the source file is not accessible.
However, I assume that PBI desktop is setup as to show USA date structure, but the data shows different date structure.
I suggest try the below.
In Power Query Editor, select the column -> right click -> Change type -> Using locale -> Data type: Date -> Locale: English(Netherlands) or English(UK)
Super UserYeah, I'm pretty sure this is the issue. This uses the optional "culture" argument of Table.TransformColumnTypes to pick the appropriate way of interpreting the date format.
Another way to do this is to tweak the M code directly. If the date format is dd/mm/yyyy, then you can write
Table.TransformColumnTypes(
#"Split Column by Delimiter",
{
{"Item.1", type text},
{"Item.2", type date},
{"Item.3", type text},
{"Sales", type number}
},
"en-IN"
)If the date format is mm/dd/yyy, then use "en-US" for the last argument instead.
(There are plenty of other cultural choices but these are the two I remember for the sake of date interpretation.)