Forum Discussion

Adzam's avatar
Adzam
Regular Visitor
8 years ago
Solved

Format Clarion Date

Hi, I am importing data from SQL server 2010. Several columns are in clarion date format. How do I format the field to display the date correctly. In excel its easy, just subtract 36161 from ...
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Adzam,

     

    In Power BI, you can still use the same logic to clarion date, please refer to below M code. Open Query Editor mode, add a custom column:

     

    =Date.AddDays(DateTime.FromText("1900-01-01"), [Clarion Date]-36161)

     

    Best regards,
    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi Adzam,

 

In Power BI, you can still use the same logic to clarion date, please refer to below M code. Open Query Editor mode, add a custom column:

 

=Date.AddDays(DateTime.FromText("1900-01-01"), [Clarion Date]-36161)

 

Best regards,
Yuliana Gu

  • viniciusdias's avatar
    viniciusdias
    New Member
    8 years ago

    Hello guys,
    I was with this same problem and I followed your advice, however, in my case when I subtracted the value of -36161 in Powerbi my dates were always two days apart from the real. So instead of subtracting -36161 I subtracted -36163 and ready worked! My question is if I made it right by making this change and if that solution could affect me in the future? I thank the attention.

     

    Follow the code used

    =Date.AddDays(DateTime.FromText("1900-01-01"), [Clarion Date]-36163)