Labs · Power BI

Lab: Load a Public Web Table and a Published Google Sheet into Power BI, Then Refresh Both

By Ravindra Bagale · Last updated: 8 October 2026

Suppose we are…

Suppose we work for a Tata Motors dealer network planning team in Maharashtra. Two pieces of data live outside our laptop:

1. The list of districts with their population. It is on the public Wikipedia page "List of districts of Maharashtra". We want it in Power BI to plan where to open showrooms.
2. Monthly city targets. Our manager keeps them in a Google Sheet and changes them often. She is tired of emailing Excel files.

We will load both, then change one target in the Sheet and see it arrive in Power BI with Refresh. The targets are sample numbers made up for practice.

Goal of this lab

By the end you will have:

• A query Districts with 36 rows from a Wikipedia table, with the state-total row removed.
• A query Targets with 4 rows from your own Google Sheet, published as CSV.
• Changed Pune's target in the Sheet and seen it in Power BI after Refresh.

What you need (all free)

• Power BI Desktop and an internet connection.
• A Google account (for Google Sheets).
• The targets file: Download gsheet_targets.csv (4 city targets)
• 35 minutes.

The data: before and after

Before. The Targets as they first load from the Google Sheet.

After. We change Pune to 375,000 in the Sheet only, then click Refresh in Power BI. The total goes from 1,300,000 to 1,325,000.

The formula

Power Query writes one Source step for each query in its own language, M. You do not type these, but you should be able to read them in the formula bar:

Targets:   = Csv.Document(Web.Contents("https://docs.google.com/spreadsheets/d/e/…/pub?gid=0&single=true&output=csv"), [Delimiter=",", Columns=2, Encoding=65001])
Districts: = Web.BrowserContents("https://en.wikipedia.org/wiki/List_of_districts_of_Maharashtra")

• Web.Contents downloads whatever the link returns. Because the Google link ends with output=csv, the result is a CSV file, so Csv.Document reads it like any CSV.
• Web.BrowserContents opens the page like a browser, and the next step (Html.Table) cuts out the table you picked.
• Refresh simply runs these steps again, so it always fetches the current page and the current Sheet.

Steps

Part A: a public web table

1. In Power BI Desktop, click Blank report, then Home → Get data → Web.

2. Paste https://en.wikipedia.org/wiki/List_of_districts_of_Maharashtra into the URL box and click OK. If asked how to connect, choose Anonymous → Connect.

   👁 What you should see: the Navigator with a list of tables (names such as "Table 1", "Table 2" or "Districts") and some "Suggested tables".

3. Click each table name and look at the preview on the right. Pick the one whose columns start with No, Name, Code, Formed, Headquarters. Click Transform Data.

   👁 What you should see: the Power Query Editor with 37 rows. The last row has Name = Maharashtra: it is the state total, not a district.

4. Click the drop-down arrow on the Name column header, untick Maharashtra and click OK.

   👁 What you should see: 36 rows (the status bar at the bottom says 36 rows).

5. Hold Ctrl and click the headers Name, Administrative division and Population (2011 Census). Right-click one of them and choose Remove Other Columns.

6. Double-click each header to rename it: District, Division, Population. Then click the ABC icon at the left of the Population header and choose Whole Number.

   👁 What you should see: Population values aligned to the right, with no Error cells. Pune shows 9,429,408.

7. In Query Settings on the right, rename the query from its table name to Districts. Click Home → Close & Apply.

8. In Report view, make a Table visual with Division, Count of District (tick District, then in the Columns box click its arrow and choose Count) and Population.

   👁 What you should see: 6 divisions: Aurangabad 8, Konkan 7, Nagpur 6, Amravati 5, Nashik 5, Pune 5 districts. Total 36 districts and 112,374,333 people. (Wikipedia can be edited, so names or numbers may change a little over time.)

Part B: a published Google Sheet

9. Open https://sheets.new to create a new Google Sheet. Click File → Import → Upload, choose gsheet_targets.csv, select Replace current sheet and click Import data. Name the spreadsheet Targets 2026.

   👁 What you should see: columns City and Target with 4 rows.

10. Click File → Share → Publish to web. On the Link tab, choose the sheet name (not "Entire document") and Comma-separated values (.csv). Click Publish → OK and copy the link.

    👁 What you should see: a long link that ends with output=csv.

11. Back in Power BI, click Get data → Web, paste the link and click OK (choose Anonymous if asked).

    👁 What you should see: a preview with City and Target and 4 rows. Click Load, then rename the new query Targets (right-click it in the Data pane → Rename).

12. Make a Table visual with City and Target.

    👁 What you should see: Mumbai 400,000, Pune 350,000, Nagpur 250,000, Nashik 300,000, Total 1,300,000.

Part C: change and refresh

13. In the Google Sheet, change Pune's target to 375000 and press Enter. Wait about 5 minutes: Google republishes the CSV a few minutes after each change.

14. In Power BI, click Home → Refresh.

    👁 What you should see: Pune 375,000 and Total 1,325,000. The Districts table refreshes too (same 36 rows).

15. Save the file as Lab-05-web-and-sheets.

Common mistakes

Self-check checklist

Try-at-home challenge

Add a fifth row to your Google Sheet: Aurangabad, 150000. Refresh Power BI. Does the new city appear in the Targets table? Now look at the Districts table: can you find "Aurangabad" there? What does that tell you about joining these two tables later?

Samjla ka? Web.Contents + output=csv = a live Google Sheet; Refresh just runs the steps again. Aata pudhe jaauya: clean a really messy sales file in Power Query.