AnalystReps

Power Query practice: Master Power Query & ETL Capstone

Chapter 20 · 5 questions · included in full access

  1. Question 1 · medium

    Big data, relationships, measures

    You have 3 million rows and want relationships and DAX measures. Where should Power Query load the data?

    Solve it in your browser

  2. Question 2 · hard

    Revenue league table

    Add Revenue (Qty × UnitPrice), group by Region summing it into Revenue, then sort largest first.

    Solve it in your browser

  3. Question 3 · hard

    Margin per order

    Merge Products on Product (Left Outer), expand Cost (no prefix), then add Margin = (UnitPrice − Cost) × Qty.

    Solve it in your browser

  4. Question 4 · hard

    Capstone Part 1: Data ingestion and text sanitization

    Build the ingestion pipeline: remove duplicates with Table.Distinct, strip blank rows with Table.SelectRows, split RegionRep by | into Region and Rep, and apply Text.Proper to Customer.

    Solve it in your browser

  5. Question 5 · hard

    Capstone Part 2: Master ETL revenue and profit scorecard

    Build the master scorecard: merge Products to look up Cost, compute Revenue as [Qty] * [UnitPrice], compute Profit as [Revenue] - ([Qty] * [Cost]), group by Region for Total Revenue and Total Profit using List.Sum, and sort by Total Revenue descending.

    Solve it in your browser

These questions are part of full access.

The first chapters of every track are free. Full access is a one-time payment.

See pricing