How to change a year column into a date in Power Query

I must admit I am a big fan of Power Query – thinking back on how hard it has been to import data into Excel from various sources and then transforming it into something useful using VBA, Microsoft Query, Web import etc. – then Power Query makes this so easy.

I am working on a model to analyze data from Statistics Denmark – and had a column containing the year. In order to use play axis in Power view and Power Map as well – I wanted the year to be a date.

And it turned out that is actually very easy

Here is the data

In this case Power Query has actually discovered the year to be a whole number in the previous step called “Changed Type” – this is done by Power Query to help but in this case we do not want the help.


Therefore, I change the Data type to Text instead in the formula line.

Then when I change the data type AFTER renaming the column to a date type

Power Query can convert it into the first date of the year

Then you can use the Add Column tools to create a Column with End of Year, Year.


And then you have columns containing both year, StartOfYear, EndOfYear in a few steps even without using M’s date and string functions.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s