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.

     

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!