How to write an M formula to convert Julian dates using Power Query
This short blog explains how to convert Julian dates to "normal" ones, and how the formula used actually works!

Posted by Andy Brown on 08 December 2017

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Converting Julian dates in Power BI Desktop / Power Query

Imagine that you have an Excel workbook or other data source containing Julian dates:

Julian dates

I've shown the UK-style date alongside, but this is actually what we're trying to derive.

 

This blog explains the theory of how to convert the dates to a UK or US format, then shows how to do this in Power Query.

You can try this out yourself using this workbook.  I got the idea for this from this blog, and it was prompted by a question from James Cartney.

The theory behind the formula

To convert the date type to UK format, here are the steps to follow in Power Query (using the top date, 2017039, as an example):

No. Step What it does What it would give
1 Number.RoundDown([JulianDate]/1000) Gets the year number from a 7-digit Julian date 2017
2 #date( Step 1 output ),1,1) Gets the 1st January for this year 01/01/2017
3 Number.Mod([JulianDate],1000) Divdes the 7-digit date by 1000, and takes the remainder to give the number of days to add 39
4 Date.AddDays( Step 1 output , Step 3 output-1) Adds this many days on to the first of January - less one 08 Feb 2017

You need to subtract 1 because in the Julian date format the first day of January is day one, not zero.

Applying the formula

I'm showing this via Excel, but it would work equally well in Power BI Desktop.  First choose to import the Excel workbook containing your dates:

Import a workbook

Choose to import a workbook.

Choose the file, then choose the sheet within it:

Choosing worksheet

The data in our example is held in a sheet imaginatively called Sheet1.

Now choose to edit how you import this data:

Editing data

Click on this button at the bottom right.

 

Now choose to add a column:

Adding a column

Add a custom column to your query.

 

Create your formula:

Create custom formula

You can paste the formula from the box below.

Here's the formula to paste in:

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

Success!

The converted dates

OK, so I already had a column with the Excel dates in, but ...

My only remaining question is: who uses dates in the Julian format? 

 

This blog has 0 threads Add post