Datatype format

[batmanadk]

Hello! I have downloaded a file that has the dates formated as DD/MM/YYYY, the thing is, that when getting te data to PBID and trying to transform it in the PQuery, I can't format the table as a date column. 

I've tried changing the regional settings to spanish but nothing changed. What I want to achieve is formatting the column "Fecha" as a date column (formatted as a text column currently). I get errors in the column right now, because it is trying to format the column as MM/DD/YYYY, as you can see in the picture below... I haven't found a button to format a date with dates or months in the beggining (in the powerquery). Any ideas?? Thanks a lot. Here is the picture:

[Pragati11]

Hi @batmanadk ,

Try chnaging date formation "Using Locale" for your date column.

Refer following article on how to do this:

https://radacad.com/flawless-date-conversion-in-power-query

Thanks,

Pragati

[v-alq-msft · Accepted solution]

Hi, @batmanadk 

Based on your description, I created data to reproduce your scenario. The date column's data type is set as text.

The pbix file is attached in the end.

Table:

When you modify the data type as 'date', the error appears because the format of the input data doesn't match the format which is used to parse data. 

I'd like to suggest you fill the third parameter of 'Table.TransformColumnTypes()' with 'ar-DZ' as the culture.

=Table.TransformColumnTypes(Source,{{"Date", type date}},"ar-DZ")

Finally it works properly.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[batmanadk]

Hi @v-alq-msft , finally your solution worked perfectly. The other suggestions kept the column as text type, and even if I changed locale, I couldn´t make it work with date formulas like DATEDIFF and TODAY. So thanks!

[mahoneypat]

You can change to the data type "Using Locale" to get around this error.  Please see this link for details - https://www.oraylis.de/blog/local-date-formats-in-power-bi#:~:text=Here%20comes%20the%20easy%20way,s...

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[batmanadk]

I'm almost sure that I tried that before posting and it didn't work. Any way, just tried it again and it got solved. Thanks

*Edit: Reason why it wasn't working before came up later (I just didn't realize it atm). The problem is that even using locale, the column appeared as a text type column and problems like connecting to a date lookup table or using dates with dax would have been impossible if the locale format would've remained. Thanks anyway, for your time and everything.