Transforming Data From HTML Tables

Question 1: Find the source of data based on below requirement and fetch them into Power Query

Question 2: Find the source “public data” regarding the amount of car sales in the local market.

Question 1 answer : https://web.archive.org/web/20251121112439/https://data.gov.my/dashboard/car-popularity

Question 2 answer: https://www.pcauto.com/my/sales-ranking

We managed to get the data but it is not in a ready-shape. We need to do some data transformation in order to prepare for our reporting.

Step 1 — Add Index Column

Go to Add Column

Click Index Column → From 1

Step 2 — Create GroupID

Go to Add Column → Custom Column

Name the column: GroupID

Enter this formula: Number.RoundUp([Index] / 2)

Click OK

Step 3: Identify Model vs Quantity

Add another custom column:

Add Column → Custom Column

Formula: if Number.Mod([Index], 2) = 1 then "Model" else "Quantity"

Name it: Type

Step 4: Pivot the Data

Now we reshape the table.

Select the Type column

Go to Transform → Pivot Column

Values column = Column1

Advanced options → Don't Aggregate

Click OK

Step 5: Fill Down + Remove duplicates

Remove other columns than Model and Quantity.

Download example:

https://archive.org/download/analytica/web-scrape-carmodel-quantity.xlsx

You can view the applied steps by following below example: