Skills level: Advanced
I am using Power Query more and more often to automate tasks by setting up the steps that it takes to clean up certain reports that I receive on a monthly basis. As soon as the latest report is out, Power Query knows exactly what to do with it, and cleans it up exactly the same way it did last time. It saves a lot of time and eliminates tedious processing and gets the file pivot-ready or ready to analyse in no time.
Very soon after learning the basic functions of Power Query, I got stuck with an error in my date column. What can possibly be wrong with a date? I spent some time reading up (no, Googling) for solutions and found the reason why power query cannot process the dates (in some instances) in an imported file.
In my sample file, I have a list of all dates in December 2016. Column A you will notice is also formatted as Date. You would think that Power Query would know how to deal with this very simple data.
When I import this file into power query, this data is treated as text at first.
If I change the formatting to Date, this is what happens:
For some obscure reason, Power Query only works with dates in the MM/DD/YYYY format, not in the DD/MM/YYYY format which is what my computer is set at. It therefore ignores your computer settings and just assumes that your date is written as MM/DD/YYYY and processes it in that way. It therefore actually processes 1/12/2016 (1 Dec) in my Excel file as 12/1/2016 (12 January) and 2 Dec as 12 Feb and so on. It is for this reason that after 12/12/2016 in my data file, Power Query gives an error, since there is no 13th month.
Note that if your computer is set to work with dates in the MM/DD/YYYY format, you will most likely not have this problem.
So, until the developers somehow change how power query sees or processes dates, you are going to need a workaround. Here are the steps:
Split your date into columns by clicking on Transform, Split Column, By Delimiter.
Select the Custom delimiter and enter forward slash on your keyboard. Make sure the split is At each occurrence of the delimiter. Select OK.
Label your columns accordingly (this is important).
Then, add a custom column where you will then combine the date in a format that Power Query recognises. Select Add Column, Custom Column.
Use the below formula to combine the date in a format that Power Query understands and processes correctly. Formulas in Power Query is similar to that in Excel, although it is a completely different language (informally known as M language).
Select OK and see how the date is now combined in a separate column.
Your new column will be in text format at first. You need to change it to date.
Now you have dates that Power Query can understand and deal with. You can now continue processing without any errors.
You do not need to keep the first 3 columns, if they are taking up your space, you can remove them. Unlike Excel, deleting these columns will not result an error in the last column (like when you would reference these columns in a formula in the last column). Power query processed the information, and no longer needs the columns there to know what to do.
Hi, and welcome to my blog :) I am an Excel enthusiast and want to help others keen on improving their own Excel skills. I hope you learn something useful on my blog.