Format Clarion Date

[Adzam]

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 the text field (in clarion date format) then format the field to display the date.

How do i achieve this in power bi ?

[v-yulgu-msft · Accepted solution]

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]

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)