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.