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

7. Data Cleaning A–Z in Power Query

By Last updated:

Friends, real data is never clean – extra spaces, wrong spellings, ₹ symbols, dd-mm-yyyy dates, blank cells, duplicate rows... it is all there. In this module every problem has a Before and After table, exact steps, M code and a practice task. The module is a bit long, but do not worry – go one section at a time and it becomes easy.

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

Live story: Cleaning before the monthly review

Suppose it is the last day of March and the Maharashtra monthly review is tomorrow at 10 am. The Mumbai file has duplicate orders. The Nashik file has "nashik " with a space. Dates are in Indian format. Sales look ₹3 lakh too high because one file was appended twice. This chapter is the A–Z cleaning kit: duplicates, blanks, spaces, types, split, unpivot, append, merge and group.

All numbers in this story are fictional practice data.

Real data is never clean. Data comes from the Blinkit store app, the Amazon Now seller panel and Excel sheets typed by store staff. It usually has extra spaces, different 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

Review and practice

  1. ★Practice and Answer Key: Data Cleaning in Power Query

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

Ravi Sir's Cards: 31 revision, interview and comparison cards for this chapter · मराठी cards