Forum Discussion
Help needed for a date format conversion
Use the "with Locale" setting. Click on the ABC data type in the upper left of the column, then select "using locale" at the very bottom of the list, then set it like below:
I just use Bahamas because I know it uses DD/MM/YY as the format and will work. Use whatever locale your data is coming from. It returns this:
If you need further help, please post data we can use in a table format per links below.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
Hi maclura,
alternatively, you can use Table.TransformColumns instead of Table.TransformColumnTypes. This is particularly helpful when dealing with multiple date formats in a single dataset:
Table.TransformColumns(Source,{{"Column1", (x) => Date.ToText(Date.From(x, "en-GB"), "yyyy-MM-dd")}})Replace the formula in your type transforming step in the Advanced editor to the above and replace Source in the formula to the name of the preceding step in your query.
Kind regards,
JB
The problem is maclura , what is 9/9/30? That isn't a date format I am aware of. Is it Sept 30, 2009, Sept 9, 2030, or what? While the first to parts can be switched depending on location, the 3rd part is always year when there are slashes. So:
- MM/DD/YY
- DD/MM/YY
- never YY in the 1st or 2nd position in that format. It can be YYYY/MM/DD or YYYYMMDD or YYYY-MM-DD, but then it is always a 4 digit year and leaves nothing to the imagination.
I don't think it is that my solution or Anonymous 's solution doesn't work. It is that your source data isn't really a date. You can use the following logic in a new column to split the data out and rearrange as necessary:
#date(
Number.From(Text.AfterDelimiter([Column1], "/", 1)) + 2000,
Number.From(Text.BetweenDelimiters([Column1], "/", "/", 0, 0)),
Number.From(Text.BeforeDelimiter([Column1], "/"))
)
This could be done in place with out an extra column, but I'm not going to fool with tinkering the code at that level because honestly I don't know what the data your system is calling a date is really giving us.
