Power Query in Excel So Simple

Data! Data! Data! Everywhere!

Data is basically a collection of facts or information. Dirty Data is a big problem in mainly Data Analysis situations. In this article we will go through some of the important automatic Excel functionality POWER QUERY to clean data. This comes in handy when the data is very messy when the cleanup process is more complicated.

Let us see an Example how to use :

In our example will load the data already present in the excel sheet. Here are the steps.

1. Select the data range/ table you want to load into power query to transform into a clean data.

2. Under Data tab ---> From Table/ Range

Power Query Editor with Data loaded,

I want to split the DateJoined column(containing date and time together) into separate date and time column,

In order to split the column, simply select the column you want to split and select Split Column ( here you have lot of options like By Delimiter, By Number of Characters ect..,)

For our example, I am selecting the-- Split Column-->By Delimiter-->Space

Now, our column is split into two DateJoined.1, DateJoined. 2. You can rename the column by right click on the column header--> Rename to Date and Time respectively.

we created two columns in just a matter of few clicks.

At anytime you can remove the changes done through the applied steps on the right hanad side of the window as shown below,

we can use other functionalities like choose columns, remove columns, Sort data, change data Type, Replace Values, Merge two Tables ect...,

Finally, to load the data back into the excel click close & Load on the top left hand corner.

No wonder it is called Power Query, any user with zero code experience can transform the data with few clicks.

Power Query is Grand Dad of Data Cleaning.

#Power Query #powerQuery #Data Cleaning