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
Dear edhans and Anonymous
thank you a lot for your most valuable advice.
Both of your suggested solution do work, but not for me.
I think there are other problems in that column originating from the cube.
I spent more than one day to investigate what and where the problem is without coming to an answer.This is what happens.
When I preview that column I seeIf I sort it in descending order I see
I remove the "Unknown" value and I appy your suggested conversion method (both of them do work)
And this is what I getAnd if I sort this coulumn descending this is what I get
The only suggestion I've got from the guys administering that data source is to change the storage mode to DirectQuery, but this is an heavy compromise I cannot accept for my project.
On the other hand, using PowerQuery in Excel that Data column is correctly reccognized and you can play with the pivot table without any issue.
Thank you again for your advice.
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.
