BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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:
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:
Choose to import a workbook.
Choose the file, then choose the sheet within it:
The data in our example is held in a sheet imaginatively called Sheet1.
Now choose to edit how you import this data:
Click on this button at the bottom right.
Now choose to add a column:
Add a custom column to your query.
Create your formula:
You can paste the formula from the box below.
Here's the formula to paste in:
Success!
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?