11/08/2025

Still continuing with Power Query but using a Business Example.

In this example, there’s a lot of cleaning to be done so let’s go through it one by one.

In the name column, you can see that there’s extra space. In order to remove this, we undergo a process called “Trimming” similarly how we go to the barber to cut our hair whenever we have a lot of hair.

To do this, select the column where we want to remove extra spaces (in this case, the “Name” column), right click → Transform → Trim.

This way, Power Query removes the extra spaces so that the data looks clean.

Moving on to the next column, there’s a lot of data that’s been mentioned as null in the column. Now, we don’t want to remove the data, we just want to flag the data as “Missing”.

There’s another thing to note.

When you hover over the little green line just below the column’s heading, a small window pops up.

This is called as “Column Quality Indicator”. This shows the quality of data that we have.

So, to fix the issue of null , select the column “Gender” → right click → Replace Values.

Enter the value you want to find in the 1st box & enter the value you want to replace with in the 2nd box.

Click “OK” and the values get replaced with “Missing”.

But let’s say you got a change of mind, don’t want to add “Missing” and replace it with “Need to check”. You can do that by going to the “Applied Steps” on the right side.

This column records the steps that you did while performing actions using Power Query.

Select the little gear icon in the last performed step (in this case, Replaced Value)

This opens the last performed action (in this case, Replace Values). Enter the value to replace with.

In the “Salary” column, you can see that there’s 0’s and null values. But why is this present in the data? Cause sometimes, whoever entered the data might’ve been lazy & missed a few details here and there. Hence, it’s important for us to make sure to clean the data.

So, how do we remove the 0’s and nulls? Simple.

Select the “Salary” column, click on the drop box → unselect “0” and “null”.

Now that we’ve cleaned the data, let’s explore more functions in order to understand the functionality of Power Query.

I want the “Name” column to be split into 2 columns - First Name and Last Name. In order to do that, select the column → go to Transform Tab → select Split Column → under Split Column, select By Space.

Select the following options in the attached image and click OK.

But what if someone has a middle name? Like the eg below?

Again, Split Column → By Delimiter. Select the option in the below attached image.

In the salary column, there’s all kinds of salaries. Now, I want to tag the employees in 3 different buckets → <50k, ≥50k and ≤100k, >100k.

Let’s add the salary bucket column. For this, go to the “Add Column” tab → Conditional Column

You can add a Custom Column Name as per your preference. This is similar to using the “if else” statements in programming languages like Python.

Now, I want to know the tenurity of employees. To do that, select the respective column. Once selected, under Add Column → Date → Age. This tells you how long the employee has been within your organization till date. Results may vary depending upon when you run it.

I want to see this in a different format as it’s confusing. To do that, go to Transform Column. Under Transform Column → Duration → select the duration that you want it in.

Let’s use a minor example of how you can use M language (which is the language that shows in Power Query) to add a Custom Column.

To add a Custom Column, go to Add Column → select Custom Column.

17/08/2025

Shortcut for Filter → Ctrl + Shift + L (or) under “Home” tab, select “Sort & Filter”

Another way of filtering is to select this tiny arrow mark

Using Total Row in Excel:

If you want to know the sum, average, max value of a certain column, enable this option under “Table Design”.

After enabling this option, an additional row called “Total” gets added at the end of the table.

The best part is, it’s dynamic. For eg, if you use the filter option to select certain values, it automatically reflects in the “Total Row”.

Slicer - a visual filter option which shows the options as buttons. It shows up like a floating box.

Cell Referencing:

Let’s look at an example.

I made a simple currency converter. Nothing fancy. If I copy the formula in the cell and paste it somewhere else, it gives a different value.

It contains the same formula but why is this happening?

Cause Excel considers the formula as multiply the values from two cells above and one cell above. If we add some value, the formula works but it gives a different value cause Excel considers it as “multiply the values from two cells above and one cell above”.

So, let’s lock in one cell. To do this, select any cell value in the formula and press “F4”. It will come out like this:

This locks the cell value so wherever you copy this formula, the 1st cell won’t get changed.

Let’s look at our previous example.

Ideally 12*5=30 but it’s showing up as 5000. So, what’s happening here? Because we locked the cell value of C6, no matter where we copy the formula, Excel understands that the 1st value should be from the cell C6. You can apply the same to the next cell value C7.

Use $ for absolute referencing.

For eg, $C$6 means the reference row is absolutely locked. This is absolute.

C4 means the reference is relative.

$C4 means the reference is locked to the column. The row remains relative.

C$4 means the reference is locked to the row. The column remains relative.

Dynamic Array: A feature that allows a single formula to return multiple values

For eg, look at this image below

You select all the values from C46 to C51 and from D44 to H44. This makes sure to fill all the values with the necessary formula (in this case, multiplication)

Structural referencing - referring the data in your table based on their names.

Let’s add a new column next to the last column of the table.

Now, see what happens when I hit enter:

Excel adds a new column within the table cause Excel recognizes that it isn’t a separate value but adjacent (or) next to the column.

Here’s a bonus → I want to add a bonus of 5% to every employees salary. So 1st a column called “Bonus” is added. Next, I select the cell * 5%

But why is it showing as [@Salary] and not as a cell like H5? Apparently this is how it works within a table. So when you point out/reference a value within a table, it will always show up as [@ColumnName].

Now as soon as I hit enter, it fills each and every cell in that column.

So I don’t have to type in the formula in each and every cell in that column.

Tables provide consistency when it comes to calculating data so you don’t have to worry about your numbers getting screwed.

Next, let’s look at the IF formula → similar to the IF conditions in programming languages like Python which returns values based on a true or false condition.

Eg: =IF([@Tenure]<3,2%,IF([@Tenure]<5,5%,10%))

But the IF statements are nested and I don’t want to nest them. How do I do that?

Simple!

Use the IFS formula.

Eg: =IFS([@Tenure]<3,2%,[@Tenure]<5,5%,TRUE,10%)

Pivot Table - a quick way to generate reports & a tool that allows you to analyze & explore large data sets in a user-friendly manner.

Let’s create a Pivot Table from an existing table.

To do this, select any value from the table, go to the Insert tab and select PivotTable

Select OK and Excel creates a new worksheet in order to create a Pivot Table.

If you notice on the left side, Excel has given a space to create Pivot Tables but if move away from the space, you won’t be able to create one so make sure to be in that space.

Here’s an example:

I dragged the Department column into the Rows field. This creates a column and fills each Row with the values from the Department column. Also, you’ll notice that there’s no duplicate values.

Now I want to know the average of each salary from each department. How do I do that?

Simple!

Again, drag the “Salary” column to the Values field.

This creates a new column with the same values as the Sum of Salary.

Now, right click on any value from Sum of Salary → go to “Summarize Values By” → select “Average”

Now, my boss wants to know how many people are working in Hyderabad based on this data. So now I have to filter this data in order to find employees from Hyderabad.

To do that, drag the column ”Work Location” to the “Filters” field.

From here, you can select the intended city your boss asked for.

Alternatively, right click on “Work location” and select “Add as Splicer”

Now, let’s explore Box Plot.

Box Plot → It uses boxes and lines to depict the distributions of one or more groups of numeric data.

To create a Box Plot, go to the “Data” worksheet, select the “Salary” column → go to Insert tab → select the Bar Graph icon → Box and Whisker

And that’s it for this week. Next week, I’ll be exploring more Functions & Pivot Tables in Excel & hopefully get started with Power BI by the end of this week.