5. Web Data, APIs and Google Sheets
5.2 Scraping an HTML Table (Worked Example)
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
- Open the page in your browser and confirm the table you want is a real HTML table (you can select its rows as text).
- Home › Get data › Web › Basic › paste the page URL › OK.
- Choose Anonymous access for a public page › Connect.
- 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.
- Tick the table › Transform Data.
- 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. - 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:
- The page opens inside the dialog, with an empty grid below.
- Type the value you want in the first cell of Column1 (e.g. "Pune"). Power BI suggests matching values from the page. Pick one.
- Type one or two more examples until the column fills correctly. Add Column2 (e.g. the division name) the same way.
- Click OK. A new Table from examples appears in the Navigator. Tick it › Transform Data. Power Query uses functions such as
Web.BrowserContentsandHtml.Tablewith CSS selectors.
Ghabru naka, pahilya vela scraping thoda confusing vatto. Don-teen pages var try kela ki ekdum sope vatel.
Ravindra Bagale's Tip
Friends, don't make a mistake here: web pages change their layout, and a scraped table can break or silently shift columns after a site update. My advice: add a check – for example, keep only the expected column names – and clean footnote marks, merged headers and "—" explicitly. Review the numbers after every refresh. Keep this in mind!
Ravindra Bagale's Tip – मराठी
मित्रांनो, इथे चूक करू नका: web pages त्यांचा layout बदलतात, आणि site update नंतर scrape केलेला table तुटू शकतो किंवा गुपचूप columns सरकू शकतात. माझं सांगणं आहे, एक check जोडा, उदाहरणार्थ फक्त अपेक्षित column names ठेवा, आणि footnote marks, merged headers आणि "—" स्पष्टपणे clean करा. प्रत्येक refresh नंतर आकडे तपासा. लक्षात ठेवा!
Ravindra Bagale's Tip – हिंदी
दोस्तों, यहाँ गलती मत करना: web pages अपना layout बदलते रहते हैं, और site update के बाद scrape की गई table टूट सकती है या चुपचाप columns खिसक सकते हैं. मेरी सलाह है, एक check जोड़ो, जैसे सिर्फ़ अपेक्षित column names रखो, और footnote marks, merged headers और "—" को साफ़ तौर पर clean करो. हर refresh के बाद आँकड़े जाँचो. ध्यान रखना!
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.