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

Re: 7 Digit Julian Date conversion

Is there a way to remove the error if there is no number in the field? 

Highlighted
Community Champion
Community Champion

Re: 7 Digit Julian Date conversion

I would prefer preventing the error, e.g. if "no number" means null

 

= if [JulianDate] = null then null else Date.AddDays(#date(Number.RoundDown([JulianDate]/1000),1,1),Number.Mod([JulianDate],1000)-1)

 

If the error can not be prevented, then you can use try .. otherwise, like

 

= try Date.AddDays(#date(Number.RoundDown([JulianDate]/1000),1,1),Number.Mod([JulianDate],1000)-1) otherwise null

 

Note: I adjusted the code out of my head, so not tested. Hopefuly it's correct.

Specializing in Power Query Formula Language (M)
Highlighted
Regular Visitor

Re: 7 Digit Julian Date conversion

I thought there was no number. It actually had a 0 so i modified it

if [DRTDAT] = 0 then " " else Date.AddDays(#date(Number.RoundDown([DRTDAT]/1000),1,1),Number.Mod([DRTDAT],1000)-1)

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

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.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors