Week 5

Starting off with Power Query.

What is a Power Query? Sounds complicated. Let me explain.

Here, I’m gonna extract a table from this page which contains data for the 2024 Summer Olympics.

So, I open Excel, go to the “Data” section.

Select “Get Data”.

Under “Get Data” you can find various sources to fetch data from . For our eg, I’m gonna go to “From Other Sources” and select “From Web”

Alternatively, you can select “From Web” which is available right next to “Get Data”

Once I select “From Web”, paste the URL and click “Ok”.

This loads the data from the website.

Now, a “Navigator” window shows up which shows the list of all the tables from the source (i.e.) website.

For our eg, let’s select “2024 Summer Olympics medal table” and select “Transform Data” at the end of the window.

This allows us to open Power Query Editor and load the data into the Power Query window.

This part is interesting in Power Query Editor.

The circled part captures the actions taken in Power Query so if I get lost, I can always go back to the “Applied Steps” menu & check my previous actions.

Now, this data needs some cleaning to be done. For eg, there’s an “*” symbol after France which needs to be removed. This can be done using “Find and Replace” option.

Now where is it?

Right click on the column where the data is located and select “Replace Value”.

In this case, I want the “*” to be removed so the “Replace With” box is empty.

Click OK and you’re done.

Now, the “null” needs to be filled as we’re undergoing the process of data cleaning. But why it’s showing “null”?

Well, if we looked at our source, we can see a shared value, hence the “null” is showing up.

Select the “Rank” column, go to the Transform tab and select “Fill”

Under Fill, select “Down”. This takes the value from the cell above and fills the entire column

Let’s say I want to know the percentage of Gold Medals from the total number of medals.

But before that, make sure to convert it into whole numbers so that you’re able to calculate.

To do that, select all the columns with the numbers, right click on them, go to Change Type and select Whole Number.

Now, that’s done, let’s focus on calculating.

Select the column “Gold” 1st, hold down Ctrl and select “Total”.

Now, we need to add a new column with the calculated values.

With the columns selected, go to Add Column and select “Standard” which performs basic math operations like add, subtract, divide etc…

In this case, select “Divide”.

But I want to see the values in percentage so I select the column, right click, go to Change Type and select “Percentage”

But there’s one more issue with the data if you scroll to the bottom.

Now, we don’t need the Totals (91 entries) and remove it. This is called “Filtering” kind of like adding a WHERE clause in SQL.

To filter this, select any column, click on the arrow next to the column.

A tiny window appears.

Scroll all the way down and uncheck (or) click on the tick box called “Totals (91 entries)” and click “OK”.

Now, I need to close Power Query and load this into Excel. To do that, go to Home tab and select “Close & Load”

Now, it’s loaded as a table in Excel.

But the values in “Division” are still showing as numbers. To convert to percentage, select the column → go to Home tab → select the “%” icon to convert.

This table is a direct connection to the source (i.e.) the wikipedia page which means whatever changes happens in the source will reflect here.

To view those changes, select any value within the table → right click → select “Refresh”

And the rest will be continued towards next week….