563 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
|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!|
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.
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.
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:
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?
25 Aytoun Street