Ravindra BagaleCourses & study guides मराठी Track your progress

29. Practice Exercises and Final Assignment

29.2 Modules 3–5: Getting Data, Databases, Web and Google Sheets

By Last updated:

  1. Connect to an Excel workbook (DarkStores.xlsx) containing two sheets and one formatted table. Which items appear in the Navigator?
  2. Write three situations where DirectQuery would be preferred over Import (hint: think of a live order-tracking screen).
  3. Explain the difference between DirectQuery and Live connection in your own words and fill a 5-row comparison table from memory.
  4. Connect to SQL Server (Express) QuickCommerceDB with Windows authentication. Load Orders and DarkStore via the Navigator, filter to the six Maharashtra cities and check View Native Query.
  5. Write a native SQL query with a JOIN between Orders and DarkStore and WHERE City IN (...). Explain why later Power Query steps may no longer fold.
  6. Connect to a local MySQL database. Note the driver you installed and one error you saw and fixed.
  7. Change the SQL server name from QCTEST01 to QCSERVER01 using Data source settings › Change Source.
  8. Scrape an HTML table of Maharashtra districts from a public page (following its terms of use), clean footnote marks and merge a Division column into DarkStore.
  9. Use Add table using examples to extract two columns from a page that has no HTML table.
  10. Load a Google Sheet of daily targets with the Google Sheets connector, then the same sheet via a published CSV link. Which method would you use for customer data, and why?
  11. Convert a single-page web query into a function and load pages 1–3 using {1..3} and Invoke Custom Function.
  12. Load a small JSON API response into a table with Json.Document.

Practice files (fictional): dark_stores_maharashtra.csv, visuals_orders_apr_may_2026.csv, folder_orders/Orders_2026_04.csv

Hints
  • Navigator shows sheets and formatted tables separately.
  • DirectQuery fits when data must be near real-time or is too large to import.
  • For the web exercises, check the site's terms first.
Solutions (checked on the practice files)
  1. Three items: the two sheets and the formatted table (table icon). Load the table, not the sheet, when one exists.
  2. Live order-tracking screen; very large tables that cannot be imported; a policy that data must stay in the source database.
  3. DirectQuery: Power BI model, queries sent to a database, you can add measures and relationships. Live connection: connects to an existing semantic model or Analysis Services; you add only report-level measures. Rows: where the model lives, modelling allowed, Power Query allowed, refresh, typical source.
  4. Check: after filtering to six cities, View Native Query is enabled on the filter step (it folded).
  5. A native SQL query usually stops folding for later steps, because Power Query cannot rewrite your SQL safely. Put joins in a view instead.
  6. Typical answer: MySQL Connector/NET (or the driver Power BI asks for); a common error is driver not found until it is installed.
  7. Data source settings › select the server › Change Source › QCSERVER01 › OK › refresh. Queries keep their steps.
  8. Practice check: number of district rows equals the table on the page; footnote marks removed with Replace Values or Text.Remove.
  9. Add table using examples builds a table from typed examples when the page has no HTML table.
  10. Use the connector (signed-in, private) for customer data; a published CSV link is public.
  11. Three pages appended: row count = rows per page × 3 (check against the site).
  12. Json.Document(Web.Contents(url)) → To Table → expand records. Practice: load visuals_orders_apr_may_2026.csv with the Web connector (Module 5.1) and check 8 rows, ₹2,100.

Ravindra Bagale's Tip

A common mistake is connecting to a source and loading it without checking the data types and row counts. For every getting-data exercise, write down the number of rows you expect and compare it with what loaded. Practise, and it will feel very easy.