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 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:

 

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.

     

5 Replies

  • 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.

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    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.

     

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

      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!