Forum Discussion

Rufyda's avatar
Rufyda
Icon for Super User rankSuper User
10 months ago
Solved

Date column not recognized as Date in Data View despite setting UK locale

I have a dataset from a UK bank that includes a date column. I tried setting the timezone/locale to the United Kingdom, but the column is still not recognized as a Date. As a result, in the Data View, the column does not appear as a date type, and I cannot perform date-based operations on it.

Dataset source: UK bank

Column format: Standard date strings (e.g., DD/MM/YYYY)

Action attempted: Set timezone/locale to UK

Issue: Column still not recognized as Date

Could you advise how to make the system recognize this column as a Date?

Thank you!


Need Help
  • Anonymous's avatar
    Anonymous
    10 months ago

    Hi Rufyda,

    Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to danextian and anilelmastasi  for prompt and helpful responses.

    Try as a work around by unabling the auto date time feature in current file (or) If Power BI doesn’t recognize your UK style date (DD/MM/YYYY), open Power Query and right-click the date column then choose Change Type to Using Locale, then set the type to Date and locale to English (United Kingdom). If that still doesn’t work, create a new column using a formula like Date.FromText([Date Joined], "en-GB") to force it into a proper date format. After applying and loading the data, check the Model or Data View to make sure the column’s data type is set to Date.

     

    Thanks & Regards,

    Prasanna Kumar

  • danextian's avatar
    danextian
    10 months ago

    It already is a date as per your pbix. 

    If you're looking for the calendar icon before the column name, that shows up only if auto date hierarchy is enabled which automatically adds a date hierarchy to most date columns. 

  • Rufyda's avatar
    Rufyda
    10 months ago

    Hi every one
    Thank you for you helpe
    After trying different settings, I solved it by creating a new column using this DAX formula
    CorrectDate = DATE(
    RIGHT('Table'[DateColumn],4),
    MID('Table'[DateColumn],4,2),
    LEFT('Table'[DateColumn],2)
    )


    This extracts the day, month, and year correctly for UK formatted dates.
    Now the column is recognized as a proper Date type, and I can perform all date-based operations normally.

    Hope this helps anyone facing the same issue.



10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 months ago

    Hi Rufyda,

    Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to danextian and anilelmastasi  for prompt and helpful responses.

    Try as a work around by unabling the auto date time feature in current file (or) If Power BI doesn’t recognize your UK style date (DD/MM/YYYY), open Power Query and right-click the date column then choose Change Type to Using Locale, then set the type to Date and locale to English (United Kingdom). If that still doesn’t work, create a new column using a formula like Date.FromText([Date Joined], "en-GB") to force it into a proper date format. After applying and loading the data, check the Model or Data View to make sure the column’s data type is set to Date.

     

    Thanks & Regards,

    Prasanna Kumar

    • anilelmastasi's avatar
      anilelmastasi
      Icon for Super User rankSuper User
      10 months ago

      If I have to guess, in Table View select the Date Joined column and in Data Type section select Date too.

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    10 months ago

    Hi Rufyda 

     

    It already shows as date type in Power Query. If the date column is on the many side of and is being used in a relationship, it won't show a date icon before it but the data type should show as date.

    • Rufyda's avatar
      Rufyda
      Icon for Super User rankSuper User
      10 months ago

      I have one Table 
      no relationship!

      it appears in power query as data but text in data visu 

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        10 months ago

        It already is a date as per your pbix. 

        If you're looking for the calendar icon before the column name, that shows up only if auto date hierarchy is enabled which automatically adds a date hierarchy to most date columns. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 months ago

    Hi Rufyda,

     

    Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

     

    Best regards,
    Prasanna Kumar

     

  • Rufyda's avatar
    Rufyda
    Icon for Super User rankSuper User
    10 months ago

    Hi every one
    Thank you for you helpe
    After trying different settings, I solved it by creating a new column using this DAX formula
    CorrectDate = DATE(
    RIGHT('Table'[DateColumn],4),
    MID('Table'[DateColumn],4,2),
    LEFT('Table'[DateColumn],2)
    )


    This extracts the day, month, and year correctly for UK formatted dates.
    Now the column is recognized as a proper Date type, and I can perform all date-based operations normally.

    Hope this helps anyone facing the same issue.



  • ANVS's avatar
    ANVS
    Icon for Advocate II rankAdvocate II
    10 months ago

    Hi Rufyda 

    Thanks for reaching out to the Fabric Community for help.

    U can solve this issue by chaning the Data type by going to power Query from Power BI Desktop and select the Date Column and choose the Data Type as Just "Date". And also you can try enabling the Auto/Date Time Feature in Power Bi Desktop by going to the Options and Settings In Power BI Desktop.