Video summary

Power Query | Data Transformation | Power BI

Main summary

Key takeaways

Technology

Summary of the Video (Power Query / Power BI Data Transformation)

This walkthrough tutorial focuses on Power Query transformations inside Power BI. It contrasts “simple English” explanations of common operations and highlights what to watch for during interviews and with real datasets, especially around data types and date/locale formats.

1) Column Selection and Column Removal

  • Uses Choose Columns to keep only selected columns (e.g., “top four columns”) and remove the rest.
  • Similar to Excel column hiding, but implemented through Power Query steps.

2) Row Filtering: Keep/Remove Ranges and Top/Bottom Rows

Demonstrates multiple Remove/Keep Rows options:

  • Keep Top Rows: keeps the first N rows (e.g., top 15).
  • Keep Bottom Rows: keeps the last N rows.
  • Keep Range of Rows: keeps rows from a start index over a computed range (e.g., “starting from 5, keep 7 rows…”).
  • Remove Top Rows / Remove Bottom Rows
  • Remove Alternate Rows
  • Keep Errors / Remove Errors:
    • Filtering out rows containing errors may remove data needed to fix/correct issues later.

3) Removing Duplicates, Blanks, and Handling Errors

  • Mentions Remove Duplicates.
  • Mentions Remove Blank Rows, noting that “formatting can be removed,” while blank cells may still carry structure.
  • Warns that removing errors can break correction workflows.

4) Splitting Text Columns (Text to Columns Equivalent)

Core section on Split Column:

  • Split by delimiter (e.g., comma).
  • If the delimiter is missing, it may require splitting by space instead.
  • Advanced split options include:
    • Quote character behavior (advanced setting concept)
    • Number of columns to split into
    • By number of characters (extract left/right portions by position)
    • Digit vs non-digit splitting (e.g., PAN-like patterns such as “digit to non-digit” / “non-digit to digit”), which can produce nulls when patterns don’t match.

Practical failure modes are also highlighted:

  • Using the wrong split method (e.g., expecting a comma delimiter when none exists) can lead to incorrect results and unexpected null outputs.

5) Group By (Aggregation Behavior on Text vs Numeric)

Explains Group By with examples like grouping by State / Type.

Key behavior:

  • When grouped data is text-only:
    • Count works (e.g., frequency of states, such as ~28 states + UT).
    • Mean/median/max/min may be meaningless or may not appear, since they require numeric interpretation.
  • Power Query’s results depend on the aggregation selected and what data types are present.

It also discusses “pivot-like” outcomes:

  • Count per group, then filtering further (e.g., applying filters like “male” vs “female”) after grouping.

6) Appending Queries (Stacking Tables) vs Preserving Originals

Major section on Append Queries:

  • Goal: combine multiple similar tables one below another under headers (stacking datasets).
  • Emphasizes using Append as new (or creating a new query) so the original data/query is not spoiled.

What must be checked:

  • Column alignment (same columns and order) to avoid nulls/errors.
  • Use Enable load / Include in report refresh appropriately, then follow the Close & Apply workflow to refresh safely.

Also notes that appended data can appear:

  • Like duplicates, or
  • Like unexpected totals, if steps are misapplied.

7) Merge Queries (Joining Tables) and Join Types

Explains Merge as a database-style join:

  • Join types similar to SQL:
    • inner, left, right, full/outer, anti-joins
  • Example concept:
    • An inner join restricts rows to those matching on both sides, changing row counts (e.g., “only shows days that exist in both population tables” concept).
  • Join results can vary depending on:
    • Which columns match,
    • Whether data types align correctly.

The section also covers how merge impacts:

  • The number of rows vs. columns in the output compared with input tables (merge can expand results).

8) Append/Merge Interview Pitfalls: Column Order, Schema Mismatch, Dates & Locale

Calls out common traps:

  • Append errors due to different number of columns:
    • If tables have extra columns, Power Query may paste based on schema positions, creating nulls.
  • Schema alignment:
    • Ensure you append only the intended matching columns (e.g., append only “same four columns” such as Gender/GenID/etc.).
  • Date format issues:
    • Power BI/Power Query interprets dates using the system locale (regional settings).
    • This can differ for US vs UK/India style formats.
    • Interview datasets may intentionally “trap” date parsing, requiring format/locale correction.
  • Whitespace/spacing problems:
    • Extra spaces in text can cause unexpected mismatches.

9) Transformations: Transpose, Rename, Fill Down/Up, Pivot/Unpivot

Additional functions and reshaping tools:

  • Transpose: convert columns into rows.
  • Rename: edit column names (via rename steps).
  • Fill Down / Fill Up: propagate values into blanks (Excel-like behavior).
  • Pivot / Unpivot:
    • Pivot turns values into columns.
    • Unpivot converts columns back into rows (normalization).
    • Unpivot is often important for interviews to reach an expected normalized structure.

10) “Advanced” Features: Conditional Columns, Index, Column Quality

Covers:

  • Custom Column / Conditional Column:
    • if/then logic to create derived fields.
  • Index Column:
    • start from 0 or 1 and adjust the increment.
  • Column Quality / error visualization:
    • Turning off “Column Quality” can hide error metrics to view data more clearly.

11) Scripting / Extra Functions

Briefly mentions:

  • R script support (Power Query can run scripts).
  • Ideas involving M-type transformations (static-only functions for certain text parsing contexts).
  • Text parsing operations such as first/last characters and left/right/mid concepts.

Main Speakers / Sources

  • Subtitles suggest one main instructor/host (not clearly identified by name in the subtitles).
  • Names like Nishant and Mayank appear only as references, not distinct speakers.
  • Main source: the video’s instructor/presenter (unnamed in subtitles).

Original video