Ravindra BagaleCourses & study guides

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.

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.