Ravindra BagaleCourses & study guides

7. Data Cleaning A–Z in Power Query

By Last updated:

Mitrano, real data kadhich saaf nasto – extra spaces, chukiche spellings, ₹ symbols, dd-mm-yyyy dates, blank cells, duplicate rows... sagla asta. Ya module madhe pratyek problem sathi Before aani After table, exact steps, M code aani practice task dila aahe. Ha module thoda motha aahe, pan ghabru naka – ek ek section karat gela ki sope hote.

What you will learn in this module

  • A repeatable cleaning workflow and the data profiling tools
  • Every common cleaning problem – duplicates, blanks, spaces, case, spellings, types, ₹ amounts, phones, e-mails, PIN codes, dates, errors and outliers
  • Reshaping: headers, top/bottom rows, unpivot, pivot, transpose, keep/remove rows
  • Combining data: folders, append, merge (all six join kinds) and Group By
  • A complete case study: cleaning a messy Blinkit export from start to finish

Real data is never clean. Exports from the Blinkit store app, the Amazon Now seller panel and Excel sheets typed by store staff usually have extra spaces, mixed spellings of city names, ₹ symbols inside numbers, dates in dd-mm-yyyy format, blank cells and duplicate rows. In this module, each problem is shown as a Before (messy) table and an After (clean) table. You also get the exact Steps in Power BI, the M code Power Query writes for you, a Ravindra Bagale's Tip box on the mistakes students make most often, and a small practice task.

All rows are fictional

Mhanje asa: every table in this module is made-up practice data in the style of Blinkit and Amazon Now orders. The values are not real company data.

Where to find the commands

Every command in this module is inside the Power Query Editor. To open it, click Home › Transform data in Power BI Desktop. When a command is on the Transform tab it changes the column you select. When it is on the Add Column tab it creates a new column and leaves the original as it is (see Module 8).

Concepts in this chapter

  1. 7.1A Cleaning Workflow You Can Repeat
  2. 7.2Data Profiling Tools
  3. 7.3Messy Excel Exports: Title Rows, Headers, Footers and Transpose
  4. 7.4Removing Duplicates (Full Row and by Key)
  5. 7.5Blanks and Nulls: Remove, Replace, Fill Down / Fill Up
  6. 7.6Extra Spaces, Non-printable Characters and Text Case
  7. 7.7Wrong Data Types and Indian Date Formats (Using Locale)
  8. 7.8Numbers Stored as Text, ₹ Symbol and Commas
  9. 7.9Inconsistent Spellings and Standardisation
  10. 7.10Split Columns (All Six Methods, Plus Split into Rows)
  11. 7.11Merge Columns
  12. 7.12Extract: Length, First/Last Characters, Range and Delimiters
  13. 7.13Phone Numbers, E-mails and PIN Codes
  14. 7.14Dates and Times: Parts, Split DateTime, UTC to IST, Age and Duration
  15. 7.15Errors: Remove, Replace, Keep and try … otherwise
  16. 7.16Outliers and Invalid Values
  17. 7.17Unpivot and Pivot
  18. 7.18Keep / Remove Rows, Sort and Filter
  19. 7.19Rounding
  20. 7.20Combine Files from a Folder (Quick Version)
  21. 7.21Append Queries (Stack Rows)
  22. 7.22Merge Queries and All Join Kinds
  23. 7.23Group By (Basic and Advanced)
  24. 7.24Conditional Column, Index Column and Column From Examples in Cleaning
  25. 7.25Case Study: Cleaning a Messy Blinkit Export End to End

The chapter recap is at the end of the last concept page.