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

Ravi Sir's Cards › Power BI

7. Data Cleaning A–Z in Power Query: Cards

मराठी cards · Open the chapter

Revision cards

A cleaning workflow you can repeat. Cleaning in random order causes errors, like setting types before junk rows are gone.
Revision card 1 of 24: A cleaning workflow you can repeat · Download PNG · Open card page

Lesson: 7.1 A Cleaning Workflow You Can Repeat

Data profiling tools. You cannot fix problems you cannot see. Profiling shows bad values quickly.
Revision card 2 of 24: Data profiling tools · Download PNG · Open card page

Lesson: 7.2 Data Profiling Tools

Messy Excel exports: title rows, headers, footers. Exports made for printing have titles and totals. Loaded as is, they break columns and double totals.
Revision card 3 of 24: Messy Excel exports: title rows, headers, footers · Download PNG · Open card page

Lesson: 7.3 Messy Excel Exports: Title Rows, Headers, Footers and Transpose

Removing duplicates: full row and by key. Duplicate rows double your revenue. You must remove them, but carefully.
Revision card 4 of 24: Removing duplicates: full row and by key · Download PNG · Open card page

Lesson: 7.4 Remove Duplicates in Power Query with Example (Full Row and by Key)

Blanks and nulls: remove, replace, fill. Empty cells break grouping and totals. But a wrong fill can invent data.
Revision card 5 of 24: Blanks and nulls: remove, replace, fill · Download PNG · Open card page

Lesson: 7.5 Blanks and Nulls: Remove, Replace, Fill Down / Fill Up

Extra spaces and hidden characters. Hidden spaces make the same city show up twice in a chart and break merges.
Revision card 6 of 24: Extra spaces and hidden characters · Download PNG · Open card page

Lesson: 7.6 Extra Spaces, Non-printable Characters and Text Case

Indian date formats with Using Locale. Indian files use day-month-year. A wrong guess swaps days and months and breaks monthly totals.
Revision card 7 of 24: Indian date formats with Using Locale · Download PNG · Open card page

Lesson: 7.7 Wrong Data Types and Indian Date Formats (Using Locale)

Numbers stored as text: ₹ and commas. Text numbers cannot be summed. Your revenue card shows nothing or a count.
Revision card 8 of 24: Numbers stored as text: ₹ and commas · Download PNG · Open card page

Lesson: 7.8 Numbers Stored as Text, ₹ Symbol and Commas

Inconsistent spellings and standardisation. One city with 3 spellings splits its sales into 3 bars.
Revision card 9 of 24: Inconsistent spellings and standardisation · Download PNG · Open card page

Lesson: 7.9 Inconsistent Spellings and Standardisation

Split columns. Two facts in one column cannot be filtered separately.
Revision card 10 of 24: Split columns · Download PNG · Open card page

Lesson: 7.10 Split Columns (All Six Methods, Plus Split into Rows)

Merge columns. Reports often need one label made from two columns.
Revision card 11 of 24: Merge columns · Download PNG · Open card page

Lesson: 7.11 Merge Columns

Extract: length, first, last, range, between. Codes hide useful parts, like the city in an order ID.
Revision card 12 of 24: Extract: length, first, last, range, between · Download PNG · Open card page

Lesson: 7.12 Extract: Length, First/Last Characters, Range and Delimiters

Phone numbers, emails and PIN codes. Phone numbers stored as numbers lose leading zeros and can turn into 9.87E+09.
Revision card 13 of 24: Phone numbers, emails and PIN codes · Download PNG · Open card page

Lesson: 7.13 Phone Numbers, E-mails and PIN Codes

Dates and times: parts, split, UTC to IST. App data is often stored in UTC. Without converting, evening orders land on the wrong day.
Revision card 14 of 24: Dates and times: parts, split, UTC to IST · Download PNG · Open card page

Lesson: 7.14 Dates and Times: Parts, Split DateTime, UTC to IST, Age and Duration

Errors: remove, replace, keep, try otherwise. One error cell can stop a refresh or hide rows. You need to find the cause first.
Revision card 15 of 24: Errors: remove, replace, keep, try otherwise · Download PNG · Open card page

Lesson: 7.15 Errors: Remove, Replace, Keep and try … otherwise

Outliers and invalid values. One wrong value can make the average delivery time look terrible.
Revision card 16 of 24: Outliers and invalid values · Download PNG · Open card page

Lesson: 7.16 Outliers and Invalid Values

Unpivot and Pivot. Months as columns cannot be filtered or charted over time easily.
Revision card 17 of 24: Unpivot and Pivot · Download PNG · Open card page

Lesson: 7.17 Unpivot and Pivot in Power Query with Example

Keep or remove rows, sort and filter. Loading rows you never use makes the model bigger and slower.
Revision card 18 of 24: Keep or remove rows, sort and filter · Download PNG · Open card page

Lesson: 7.18 Keep / Remove Rows, Sort and Filter

Rounding in Power Query. Rounding too early changes totals. And Power Query's default rounding surprises people.
Revision card 19 of 24: Rounding in Power Query · Download PNG · Open card page

Lesson: 7.19 Rounding

Combine files from a folder (quick). Loading 12 monthly files one by one is slow, and next month you must do it again.
Revision card 20 of 24: Combine files from a folder (quick) · Download PNG · Open card page

Lesson: 7.20 Combine Files from a Folder (Quick Version)

Append queries: stack rows. Data split into tables with the same columns must be stacked to analyse together.
Revision card 21 of 24: Append queries: stack rows · Download PNG · Open card page

Lesson: 7.21 Append Queries (Stack Rows)

Merge queries and join kinds. Columns you need are often in another table. Merge brings them in.
Revision card 22 of 24: Merge queries and join kinds · Download PNG · Open card page

Lesson: 7.22 Merge Queries and All Join Kinds in Power Query with Example

Group By: basic and advanced. Sometimes you need a summary table, like one row per city.
Revision card 23 of 24: Group By: basic and advanced · Download PNG · Open card page

Lesson: 7.23 Group By (Basic and Advanced)

Case study: cleaning a messy Blinkit export. Real exports have many problems at once. A fixed order solves them cleanly.
Revision card 24 of 24: Case study: cleaning a messy Blinkit export · Download PNG · Open card page

Lesson: 7.25 Case Study: Cleaning a Messy Blinkit Export End to End

Interview cards

What join kinds are there in Power Query Merge? Six: Left outer (all left rows), Right outer, Full outer (all rows from both), Inner (only matches), Left anti (left rows with no match) and Right anti.
Interview card 1 of 4: What join kinds are there in Power Query Merge? · Download PNG · Open card page

Lesson: 7.22 Merge Queries and All Join Kinds in Power Query with Example

Why does Remove Duplicates sometimes leave duplicates? In Power Query, Remove Duplicates is case-sensitive and space-sensitive. “Pune”, “PUNE” and “Pune ” are three different values.
Interview card 2 of 4: Why does Remove Duplicates sometimes leave duplicates? · Download PNG · Open card page

Lesson: 7.4 Remove Duplicates in Power Query with Example (Full Row and by Key)

Why can column profiling in Power Query be misleading? By default column profiling uses only the top 1000 rows. Errors or empty values further down are not shown.
Interview card 3 of 4: Why can column profiling in Power Query be misleading? · Download PNG · Open card page

Lesson: 7.2 Data Profiling Tools

What is the difference between null and blank text in Power Query? null means no value. Blank text "" is a real text value with zero characters. Fill Down and many null checks skip "".
Interview card 4 of 4: What is the difference between null and blank text in Power Query? · Download PNG · Open card page

Lesson: 7.5 Blanks and Nulls: Remove, Replace, Fill Down / Fill Up

Comparison cards

Merge vs Append in Power Query. Both combine two tables. Append stacks rows (one under the other). Merge joins columns side by side on a key.
Comparison card 1 of 3: Merge vs Append in Power Query · Download PNG · Open card page

Lesson: 7.21 Append Queries (Stack Rows)

Trim vs Clean in Power Query. Both are under Transform > Format and fix hidden problems in text.
Comparison card 2 of 3: Trim vs Clean in Power Query · Download PNG · Open card page

Lesson: 7.6 Extra Spaces, Non-printable Characters and Text Case

Pivot vs Unpivot. Both reshape a table in Power Query. Unpivot turns columns into rows. Pivot turns rows into columns.
Comparison card 3 of 3: Pivot vs Unpivot · Download PNG · Open card page

Lesson: 7.17 Unpivot and Pivot in Power Query with Example