Skip to content
August 26, 2014 / Erik Svensen

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 www.dst.dk – 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.

 

 

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: