Forum Discussion

batmanadk's avatar
batmanadk
Icon for Helper I rankHelper I
6 years ago
Solved

Datatype format

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...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

     

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

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,select%20Change%20Type%2FUsing%20Locale.&text=Now%20you%20can%20choose%20the,the%20Locale%20and%20that%27s%20it.

 

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

Regards,

Pat

  • batmanadk's avatar
    batmanadk
    Icon for Helper I rankHelper I
    6 years ago

    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.