How to do Rows calculation & Data cleaning Using Power Query in Power BI Tool | Power Query Explained

 Power Query is a data transformation and data preparation tool that is part of Microsoft Excel and Power BI (Business Intelligence) applications. It allows users to connect to various data sources, transform and shape the data, and load it into Excel, Power BI, or other data analysis tools for further analysis and reporting.

In this post, we are going to look at how can we do row calculations in the Power Query.
I took a simple Excel sheet to showcase this

First of all, we upload an Excel file to Power bi then we start cleaning of data.

After uploading the Excel file we will clean the data and remove unnecessary rows and columns.

To do that we click on the arrow beside the remove rows icon from the home ribbon and there we select the option of removing top rows then a dialog box opens where we enter 2 to remove the top 2 rows.
                                                           

After Removing the top two rows we need to make the first row as the header, to do that we click on use first row as the header from the home menu.

The first column Header should be renamed as Year, to do that we double-click on the header of the first column and rename it as Year.

Now if we look at the bottom rows there are many null and unnecessary values which are not required. So to remove these rows we click on Keep Rows and select Keep Top rows then a dialog box opens and in the dialog box we enter 19 and hit enter.

After removing unnecessary rows and columns query editor looks something like this.

Now we will add a custom column for Average Expenditure Per Recipient to do that we will go to add column ribbon and from there we enter in custom column. Inside the custom column window, we will add a name for the column and Put the formula for Average Expenditure Per Recipient. The formula is expenditure divided by recipients. 

Now we have this column Avg Exp Per Recipient.  In this column, we have average values but the values are not rounded up.

Now we will Round the values of column Average Expenditure Per Recipient, to do that we will select the column and right-click then round up. Then a dialog box opened, inside the dialog box e have to put the number of decimal places, here we don't want any decimal number, so I put 0 in the dialog box and hit enter.

Now we will add a column for %Expenditure Change From Previous Year and to do that we have to use the formula as you can see in the below image. In the formula bar, we can see that we have to achieve c5-c4 divided by c4.

To achieve this we will going to use a method in which, I will add two index columns starting from 0 and starting from 1. To add an index column we go to add column ribbon and there we select index column select start from 0 and select start from 1.

Now to achieve c5-c4.

we will merge queries. To do merge queries we go to the home ribbon and select merge queries. then a dialog box opened inside the box first table is expenditure and in the second table, we select the current table which is expenditure current. In join kind, we select left outer join and then press ok.

Now we will expand the expenditure and recipient columns and reorder them. For reordering of the columns, we will select the columns and press the cntrl button to drag the columns to the left or right as per our requirement.

we can see that Expanded Expenditure column values start from 0.

Now if we see the index which starts from 0 is upside down the number 0 is at the very bottom. So we will sort it, to sort it we will click the arrow button near the header of the column and sort it in ascending order. 

Now we have to achieve the column of % change exp, to do that we will go to add column's ribbon and there we will click on the custom column.  A dialog box opened there we will add the name as % change exp and inside the custom column formula, we will add the formula. The formula is c5-c4 divided by c4 according to the Excel sheet and here it is Expenditures - Expanded Expenditures divided by Expanded Expenditures and to make this value into percentage we multiply it by 100. 

Now we have the column but our values go for many numbers after decimal. So we have to round up our values, to do that we will follow a similar trick we will right-click on the column and then select round and inside the dialogue box we will enter 2 for decimal numbers.

Now we have our desired column.

Now we have to achieve the other column, which is the % change Rec, to do that we will go to add column's ribbon and there we will click on the custom column.  A dialog box is opened there we will add the name as % change Rec and inside the custom column formula, we put (Recipients - Expanded Recipients) divided by Expanded Expenditures, and to make this value into percentage we multiply it by 100. 

Like this in power query, we can clean our data in many ways.

For Practice download the data set from here
https://docs.google.com/spreadsheets/d/1YWxA0x9hfQlYwHewXRlvgzVAhhihnTzU/edit?usp=sharing&ouid=108443129400256986779&rtpof=true&sd=true

For a detailed explanation, you can visit this video 
Detailed Video