29. Practice Exercises and Final Assignment

29.3 Modules 6–7: Power Query Essentials and Data Cleaning

By Ravindra Bagale · Last updated: 25 September 2026

1. Rename every applied step of your Orders query meaningfully and add descriptions to two steps. Open View › Query Dependencies and describe the diagram.
2. Remove 3 title rows and a Total row from a store export, promote headers and set types.
3. Remove full-row duplicates, then duplicates by Order ID + Product ID keeping the latest status (sort + Table.Buffer).
4. Fill down Category; replace null Discount with 0; replace null Partner ID with "Unknown". Explain why a null Delivery Time Mins should not become 0.
5. Clean City values " pune", "PUNE ", "Pune" with Clean, Trim and Capitalize Each Word. Remove double spaces inside customer names.
6. Standardise Aurangabad / Chh. Sambhaji Nagar / Sambhajinagar and Kolhapur typos with a CityMap merge.
7. Convert OrderDateTime text in DD-MM-YYYY HH:MM to Date/Time with Using Locale English (India).
8. Convert "₹1,250", "₹ 86.50", "2,40,000" and "Rs. 499" to Fixed Decimal Number.
9. Split "BLK-PUN-KOT-01" by delimiter, "500ml" by digit to non-digit, and an Items column into rows.
10. Extract the e-mail domain, the City Code with Range, and the text between brackets in "Blinkit Baner (Pune)".
11. Clean phone numbers to 10 digits with a Phone OK flag; lowercase e-mails with an E-mail OK flag; keep Pincode as text.
12. Convert UTC order times to IST and check which orders move to the next date.
13. Replace errors with null, and build an Error Reason column with try.
14. Add a Data Quality conditional column for negative quantity and delivery time 0 or above 180 minutes, and count issues per city with Group By.
15. Unpivot a monthly target table (City, Jan … Dec) into City, Month, Target Orders. Then pivot it back.
16. Use all six join kinds on the small Orders/Product example. Use Left Anti and Right Anti to find bad product codes and products never sold.
17. Group Orders by City with Total Sales, Order Lines and distinct Orders.
18. Complete the Module 7.25 case-study checklist on your own messy Amazon Now export.