Ravindra BagaleCourses & study guides

5. Web Data, APIs and Google Sheets

5.2 Scraping an HTML Table (Worked Example)

By Last updated:

Goal: get a table of Maharashtra districts or cities from a public encyclopaedia page (for example a Wikipedia list of cities in Maharashtra) to enrich our DarkStore table with a Division column (Pune, Nashik, Nagpur, Aurangabad/Chhatrapati Sambhaji Nagar divisions etc.). We describe the process. The page's numbers are not quoted here, and pages change over time.

Steps in Power BI

  1. Open the page in your browser and confirm the table you want is a real HTML table (you can select its rows as text).
  2. Home › Get data › Web › Basic › paste the page URL › OK.
  3. Choose Anonymous access for a public page › Connect.
  4. The Navigator lists HTML Tables (Table 1, Table 2 …) and Suggested Tables. Click each one and use the Table View / Web View tabs to find the right table.
  5. Tick the table › Transform Data.
  6. Clean it: promote headers if needed, remove footnote markers like "[3]" (Transform › Replace Values, or Text.BeforeDelimiter([City], "[")), trim, and set types using Using Locale English (India) for numbers with Indian commas.
  7. Standardise names so they match our data ("Aurangabad" → "Sambhaji Nagar", Module 7.9) and Merge with DarkStore on City.

Add table using examples

When the data is not in an HTML table (for example cards or lists on a page), click Add table using examples in the Navigator:

  1. The page opens inside the dialog, with an empty grid below.
  2. Type the value you want in the first cell of Column1 (e.g. "Pune"). Power BI suggests matching values from the page. Pick one.
  3. Type one or two more examples until the column fills correctly. Add Column2 (e.g. the division name) the same way.
  4. Click OK. A new Table from examples appears in the Navigator. Tick it › Transform Data. Power Query uses functions such as Web.BrowserContents and Html.Table with CSS selectors.

Ghabru naka, pahilya vela scraping thoda confusing vatto. Don-teen pages var try kela ki ekdum sope vatel.

Ravindra Bagale's Tip

Mitrano, ithe chuk karu naka: web pages change their layout, and a scraped table can break or silently shift columns after a site update. Maza sangna aahe, add a check, udaharan mhanje keep only the expected column names, and clean footnote marks, merged headers and "—" explicitly. Review the numbers after every refresh. Dhyan rakho!

Practice task

Mhanje asa: scrape a public list of Maharashtra districts with their divisions. Clean it into two columns, District and Division, and merge it with DarkStore to add Division to our six cities.