cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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

How do i achieve this in power bi ?
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Format Clarion Date

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)

1.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Microsoft
Microsoft

Re: Format Clarion Date

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)

1.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
New Member

Re: Format Clarion Date

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)

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Top Solution Authors