Guide to power query editor

Are you spending a big chunk of your time cleaning the data in excel? If yes then this blog is for you. All the cleaning and transforming of data can be achieved in Microsoft Power BI. Power BI offers a wide variety of options to clean your data. Power BI comes with a power query editor where you can play around with the data and I consider it as an ETL tool where you can transform your data according to the needs.

We have discussed the data cleaning process in the last blog and in today's blog the focus will be on transforming data and the features that the power query editor offers. These steps are solely subjective according to the data type and to your needs. I am highlighting 6 steps to transforming the data. I am working on the sample superstore data. So let's get started!!!!

• Removing columns and rows- We are aware that the datasets can have a lot of unwanted columns and rows. To get better insights you need to get rid of it. To do so you need to select the column. If you need multiple columns at once then hold down the Ctrl key and select all the columns. On the home ribbon, you can find the remove column pane. Considering this dataset I am adding the returned data along with the order summary and you can see in the second picture that the headers are in rows. You need to select the tab "Use the first row as headers".

• Checking the data type of each and every column because there are times where the columns are assigned with the wrong data type and can hamper the visualization. To do so if you toggle around every column on the left corner of every column you can find the data type assigned to that whole column. To change it you need to click it and you will get different options to select from. 

• Splitting the columns- There are several instances where you need to split the column for eg. you need to separate the first name and last names of consumers then this is the befitted option. This is similar to text to columns in excel. In this dataset, I am finding out the acronym for different categories which is present in the Product_ID and we will split the acronym from there.

• Replacing and removing null values- It is quite common to find a lot of null values when you are dealing with such big datasets. To get rid of such values you need to select one of the null values in a column and right-click where you get options to replace and remove such values. In this dataset, I am replacing the null values with a text.

• Merging and appending the data- This feature is most discussed in the Power BI community and users can get confused at times whether to use merge or append. When we talk about the merge you need to combine the queries via a common column. It's not the case with appending queries if you need to add extra rows to your existing query then use append. In this case, we are merging the order summary query with return status, and Order_ID act as a common column for this. To do so you need to select the merge queries pane in the Home ribbon. It will open up a new toggle where you need the common column and the type of join you want. Normally I use left join.

• Unpivoting the column- This is one of the most widely used features of the power query editor. It is quite similar to the Transpose formula in excel. In this dataset, we are adding the US population numbers. To do so we are scraping the data from the web. 

You will land up with the population data where you can remove unnecessary columns as suggested previously. After that, you need to switch to the transform pane where you can find the unpivot column. In that tab select the unpivot other columns

These are some of the basic steps which I followed to transform the data and you can observe the difference in the quality of insights you are going to generate if you implement such techniques. I haven't explored many features such as creating a data query that will help you to utilize all the time intelligence functions. Also, one great piece of advice would just check all the applied steps in the right of every query and you can undo any step with just one click. Power BI is very intuitive in nature and you won't find it difficult to try out all the offered features.  

Thanks for Reading  Let's connect on  LinkedIn.