Handle Missing Values in Power Query (Visually Explained) | #Power BI Course 11

Handle missing values in Power Query (Power BI course step)

The cleanup focuses on removing/standardizing missing values of different kinds:

• null: represents unknown / missing with no value.
• Empty string (""): text type is known, but the value is empty (length 0).
• White-space strings (e.g., " " / multiple spaces): appear empty but still contain characters; text length is > 0 and requires cleaning.

Key rule for text cleanup

• Use Trim to remove extra spaces (converts whitespace-only strings to "").
• After trimming, replace empty strings with a standard missing marker (commonly NA / “Not available”).

---

Decisions by data type (and why they matter)

Text columns

Choose between:

• Replacing missing values with null, or
• Replacing with a dummy default like NA / "unknown" for reporting clarity and easier filtering.

Trade-off noted: dummy values consume more semantic model space; null uses none.

Numeric columns

• Missing values are typically null, but sometimes represent “no value” and may be converted to 0.
• Important business impact: averages and calculations differ significantly between null vs 0 (example: average price changes).

Date columns

• Typically use null, or sometimes a dummy date like 9999-12-31 to represent “open ended”.
• Warning: dummy dates can distort time-series visuals/charts (e.g., charts look wrong).

---

Tutorial walkthrough: applying replacements in the right order

Example cleanup:

• Order ID: no missing values.
• Product info: empty string replaced with NA.
• First/Last name: no blanks/nulls.
• Emails: empty string replaced with NA.

Critical implementation detail

• Trim first, then replace empty strings.
• Reason: email fields may contain both whitespace-only and empty strings; doing replacements in the wrong order can require multiple passes or miss cases.

---

Saving / applying Power Query changes (important workflow)

• Must save twice:
  • Use Apply / Close and Apply for Power Query changes.
  • Use Save for the overall Power BI project.
• After finishing cleanup, return to Power BI via Close and Apply.

---

Reshaping / deriving new information (next phase)

After cleaning, the video shifts to reshaping:

• Split columns
• Merge columns
• Extract substrings into new columns

---

Split columns (when one field contains multiple pieces of info)

• Product info contains combined data (product name and category) separated by a pipe (|).
• Use Split Column by Delimiter with the |.

Post-split quality checks:

• Trim new columns again (may introduce leading spaces).
• Convert new nulls (from missing categories) into NA to keep standards.
• Rename columns to match naming convention (e.g., product_category / category).

---

Don’t split when it breaks meaning

• Customer ID: last two letters are country codes.
• If you split the customer ID, you end up with fragments that are not meaningful alone.
• Correct approach: use Extract instead of split.

---

Merge columns (when multiple columns should be one)

• First name + last name merged into a single customer name column.
• Adds a space between values to keep formatting readable.

---

Extract parts of values (derive attributes without splitting)

• From Customer ID, extract last two characters → country_code.
  • Requires duplicating the original column so the original ID isn’t lost.
• From Email, extract domain:
  • Use text after delimiter (@) to get the domain.
  • Validate extracted results via length (detects empty string domain).
  • Replace empty result with NA.
• Dates: extracting year/month/day is mentioned but considered unnecessary because Power BI visuals support date drilldown/rollup directly.

---

Main speakers / sources

• The instructor / presenter of the “#Power BI Course 11” video (only voice implied by the subtitles; no additional identifiable names or guest sources mentioned).