Data transformation with Power Query

Transform date input as “Excel-unrecognized” text string into date (number) usable by #Excel

This is a continuation of the previous post – How to turn “1st January, 2017” into #Excel recognizable date?   …using Get & Transform (better known as #PowerQuery).

Excel Tips - Date format from text to number with PQ

Note: All screenshots and steps in this post are based on Excel 2016.  The ribbon of Power Query for Excel 2010/2013 may be different a bit… but the steps and interfaces should be more or the same.

The problem was discussed in the previous post.  So let’s go straight to the solution:

You may download a Sample FIle – Reversed Text Date to Number Date to follow through.

Step 1 – Load the data to Power Query

Select the range of dates to be transformed, then go to

Data –> Get & Transform Data (group) –> From Table/RangeExcel Tips - Date format from text to number with PQ 1

Now we have opened the Query Editor, with the data loaded into it…

Excel Tips - Date format from text to number with PQ 2.0

Be curious!  Let’s explore a bit by clicking the “ABC 123” icon on the column header “Date”… wow, there is a list of different data type.  It looks promising!  So I clicked on it and selected Date hoping for the magic instantly.

Excel Tips - Date format from text to number with PQ 2

Oooooops…. all Error.

Excel Tips - Date format from text to number with PQ 2.1

Apparently, Power Query fails to recognize this kind of “date” too.

Don’t be frustrated.  We need just a few more steps to achieve our goal.

Now, let’s undo the “Changed Type” step.  Unlike in Excel, Ctrl+Z won’t undo the step applied in Query Editor.  We need to remove the “Applied Steps” in the pane of Query Settings.  See below:

Excel Tips - Date format from text to number with PQ 2.2

Step 2 – Split Day from Month and Year

Now let’s go to

Transform –> Split Column –> By Delimiter

Excel Tips - Date format from text to number with PQ 3

Select Space as delimiter; Split at “Left-most delimiter” as we are trying to separate the day portion from month and year…  (Think about why we need this step)

Excel Tips - Date format from text to number with PQ 4

The Date is now split into Date.1 and Date.2.  And Power Query recognizes Date.2 “January 2017, February 2017, etc…” as date and automatically changes it to date… Well, this is helpful most of the time but not in our case here.  So we need to undo this “Changed Type” from “Applied Steps” under Query Settings.

Excel Tips - Date format from text to number with PQ 4.1

Note: After removing “Changed Type”, both columns are recorded as “text” indicated by the “ABC” icon on column header.

Excel Tips - Date format from text to number with PQ 4.2

Step 3 – Replace Values

In order to remove all “st”, “nd”, “rd” and “th” from Date.1, go to

Home –> Transform (group) –> Replace Values

Excel Tips - Date format from text to number with PQ 5Excel Tips - Date format from text to number with PQ 5.1

The step above remove all “st” from the column Date.1

Excel Tips - Date format from text to number with PQ 5.2

Now repeat the steps to remove “nd”, “rd” and “th”

Excel Tips - Date format from text to number with PQ 5.3

We should get the following as a result:

Excel Tips - Date format from text to number with PQ 5.4

Step 4 – Combine “trimmed” Day with Month and Year

First of all, select both columns Date.1 and Date.2, then go to

Transform –> Merge Columns

Excel Tips - Date format from text to number with PQ 6

Select Space as Separator, and give the new column a meaningful name:

Excel Tips - Date format from text to number with PQ 6.1

Step 5 – Change Type

Now the “Transformed” date is ready for changing type as it’s now in a text pattern recognized as date by Excel.

Click the “ABC” on the column header,  

Excel Tips - Date format from text to number with PQ 7

Then select “Date”

Excel Tips - Date format from text to number with PQ 7.1

Here we go…

Excel Tips - Date format from text to number with PQ 8.0

Final Step – Load to worksheet

To load the “transformed” data back to Excel worksheet, go to

Home –> Close and Load To… —> Table –> Existing worksheet –> Destination –> OK

 

Output

Excel Tips - Date format from text to number with PQ

And the best is yet to come…

When new data comes, a simple (right) click of “Refresh” does the magic:

Excel Tips - Date format from text to number Ending

Yes, that’s what we all do in office day by day: Repetitive and tedious tasks, again and again…

With Power Query, we can say GOODBYE to repetitive, tedious, and probably boring tasks.

You may wonder why we need to split day and then combine it with month and year later.   Why we need such extra step?  Simply because one of the replacing values “st” also appears in “August”.  If we do not split day from the month first, “August” will become “Augu” after replacing “st” and it results in error of all dates in August.  Try it and you will see.

Want to learn more about Power Query?

Power Query Course

Disclosure: I earn a small commission if you finally take the course via my site.

Advertisements
This entry was posted in Power Query and tagged , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

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