Fixing Improper Dates in Power Query

Welcome to Excel Jet Consult blog post. In this tutorial, I will share with us how to solve the problem of improper date in Power Query. Let’s get started

Quite often, I do get questions from people who have challenge of working with dataset that wasn’t correctly entered on Excel. Hence, I write this blog post to address the problem.

In the caption below, I have an Excel sheet with over four thousand records. In column H, we discovered that the dates in that column are not recognized as proper date format. The General Number formatting attest to the fact that the dates are not property entered. We want to ultimately have the values in column H to display the proper date format using Power Query.

The first thing we need to do is format the data in Excel Table.

Click inside the data and press CTRL + T. My table has headers is automatically checked

Click OK.

In the Get & Transform Data group of the Data tab, click on From Table/Range. The data will automatically be loaded into the Power Query editor.

That’s all we need to do! In the caption below, we can see that the improper dates we saw earlier has been converted to Dates courtesy of Power Query. This is amazing!

Load To Excel

To load the data back to Excel, click on Close & Load. From the caption below, we can see that Dates has been transformed and recognized as proper dates value as seen in the Date Number formatting

Conclusion

Power Query is the fantastic data cleaning and transformation tool that enhances efficiency and productivity

 

 

Posted in Blog and tagged , .

Leave a Reply

Your email address will not be published. Required fields are marked *