This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

In this blog

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.

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