Skip to content
Tutorials Center
My learning

Topics

Lesson 54 of 56 ยท Practice

Exercises with Solutions

Twelve Power BI exercises across Power Query, data modelling and DAX, each with a worked answer in M or DAX and a pandas check you can run in the browser.

Reading about Power BI is not the same as doing it. These twelve exercises cover the three skills that decide how good your reports are: shaping data in Power Query, building a sound model, and writing DAX measures. Each task has a small data set, a worked solution and a runnable pandas check.

Power Query and DAX cannot run on this site, so the M and DAX solutions are read-only. The pandas stand-ins run in your browser and produce the numbers the Power BI solution must give; use them to confirm your own answer. Try each task on paper or in Power BI Desktop before you read the solution. The lessons in brackets tell you where the idea is taught.

Twelve tasks, three skills

Power Query Tasks 1 to 4. Clean, unpivot, merge, group.
Modelling Tasks 5 to 8. Star schema, keys, dates.
DAX Tasks 9 to 12. Measures, context, time.

Attempt first, then compare with the solution.

1 / 2

Part 1: Power Query

Task 1: Clean a text file

An orders file has a region column with stray spaces and mixed capitals (" north", "SOUTH ", "south"), an amount column where one value is the text n/a, and one row that appears twice. Produce a clean table: regions trimmed and in proper case, amounts as numbers with n/a becoming null, and no duplicate rows. (Cleaning Data)

Solution in M:

M
let
    Source = Csv.Document(File.Contents("C:\data\orders.csv"), [Delimiter = ",", Encoding = 65001]),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    Trimmed = Table.TransformColumns(Promoted, {{"region", Text.Trim, type text}}),
    Proper = Table.TransformColumns(Trimmed, {{"region", Text.Proper, type text}}),
    Typed = Table.TransformColumnTypes(Proper, {{"amount", type number}}),
    NoErrors = Table.ReplaceErrorValues(Typed, {{"amount", null}}),
    Distinct = Table.Distinct(NoErrors)
in
    Distinct

Changing n/a to a number produces an error value, and Table.ReplaceErrorValues turns that into null. The order matters: trim before you remove duplicates, or "SOUTH " and "SOUTH" count as different rows.

Python Check: clean the orders

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Task 2: Make a wide table tidy

A table has one row per product and one column per month (Jan, Feb, Mar). Turn it into three columns: product, month, units. (Unpivot, Pivot and Transpose)

M
Unpivoted = Table.UnpivotOtherColumns(Source, {"product"}, "month", "units")

Using other columns rather than selecting the month columns means that a new Apr column is unpivoted automatically at the next refresh.

Python Check: unpivot months

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Task 3: Merge and find orphans

Join Orders to Customers on customer_id, keeping every order, and then list the orders whose customer does not exist. (Merge and Append Queries)

M
Joined = Table.NestedJoin(Orders, {"customer_id"}, Customers, {"customer_id"}, "Customer", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Joined, "Customer", {"name"}),
Orphans = Table.NestedJoin(Orders, {"customer_id"}, Customers, {"customer_id"}, "Customer", JoinKind.LeftAnti)

A left outer join keeps all orders with a null name for orphans; a left anti join returns only the orders with no match.

Python Check: left join and anti join

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Task 4: Total per customer with a rank

From the order lines, produce one row per customer with the total amount and the number of orders, sorted from highest to lowest total, plus a rank starting at 1. (Group By and Aggregations)

M
Grouped = Table.Group(Source, {"customer"},
    {{"total", each List.Sum([amount]), type number},
     {"orders", each Table.RowCount(_), Int64.Type}}),
Sorted = Table.Sort(Grouped, {{"total", Order.Descending}}),
Ranked = Table.AddIndexColumn(Sorted, "rank", 1, 1, Int64.Type)

An index column numbers rows, so tied totals get different ranks. If ties matter, use the Rank column command or compute the rank in DAX.

Python Check: group and rank

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Part 2: Data modelling

Task 5: Split a flat table into fact and dimension

A flat sales table repeats product_name and category on every row. Split it into a Product dimension (one row per product) and a Sales fact table that keeps only product_id and the measures. Prove that no rows or money were lost. (Star Schema)

Solution: in Power Query, reference the flat query twice. In the first, keep product_id, product_name, category and remove duplicates; in the second, remove the descriptive columns. Load both and relate Product[product_id] (one) to Sales[product_id] (many). The check is that the row count and total of the fact table equal those of the flat table.

Python Check: split into fact and dimension

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Task 6: Why a one-to-many relationship fails

After a refresh, Power BI reports that it cannot create a one-to-many relationship because the dimension column has duplicates. Find the duplicates, and explain what would go wrong if the tables were joined anyway. (Relationships)

Solution: sort or group the dimension by key and look for counts above one; in Power Query use Keep duplicates on the key column. A duplicated key in a dimension makes each matching fact row appear once for every duplicate, so measures such as total revenue are overstated. The fix is to deduplicate (or correct the source), not to change the relationship to many-to-many.

Python Check: find the duplicate key

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Task 7: Build a date table

Create a calendar for 2025 with the columns Date, Year, Month Number, Month, Quarter and a fiscal year that starts in July (name it by the year in which it ends). Mark it as the date table. (Date Tables)

DAX
Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2025, 1, 1 ), DATE ( 2025, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month Number", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Quarter", "Q" & ROUNDUP ( MONTH ( [Date] ) / 3, 0 ),
    "Fiscal Year", YEAR ( [Date] ) + IF ( MONTH ( [Date] ) >= 7, 1, 0 )
)

Then set Month to sort by Month Number, and use Mark as date table on Date. A date table must have one row per day with no gaps.

Python Check: the calendar

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Task 8: Two dates, one date table

Orders have an order_date and a ship_date. The date table is related to order_date (active). Report sales by the month in which orders shipped, without a second date table. (Relationships)

DAX
Sales by Ship Date =
CALCULATE (
    [Total Sales],
    USERELATIONSHIP ( Orders[Ship Date], 'Date'[Date] )
)

Create the second relationship on ship_date and leave it inactive; the measure switches it on. Placing Month on rows and both measures in a table shows the difference between the two views.

Python Check: order month versus ship month

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Part 3: DAX

The next tasks use one sales table with order_id, customer, category, qty and price.

Task 9: Total sales and average order value

Write Total Sales, the number of orders and the average order value. Avoid summing price and multiplying. (Aggregators and Iterators)

DAX
Total Sales = SUMX ( Sales, Sales[Qty] * Sales[Price] )
Orders = DISTINCTCOUNT ( Sales[Order ID] )
Average Order Value = DIVIDE ( [Total Sales], [Orders] )

SUMX multiplies row by row and then adds. An order can have several lines, so the number of orders is a distinct count of order IDs, not a count of rows.

Task 10: Percent of total by category

Show each category's share of total sales, which must still work when a slicer filters the page. (CALCULATE and Filter Context)

DAX
Category % of Total =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], REMOVEFILTERS ( Sales[Category] ) )
)

REMOVEFILTERS clears the category filter for the denominator only, while other filters (a customer slicer, say) stay in place.

Python Check: tasks 9 and 10

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Task 11: Year to date and growth

Monthly sales for January to April are 100, 110, 120 and 130 in 2024, and 120, 115, 150 and 140 in 2025. For April 2025, return the year-to-date figure, last year's year-to-date and the growth. (Time Intelligence)

DAX
Sales YTD = TOTALYTD ( [Total Sales], 'Date'[Date] )

Sales PY YTD =
CALCULATE ( [Sales YTD], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

YTD Growth % = DIVIDE ( [Sales YTD] - [Sales PY YTD], [Sales PY YTD] )
Python Check: year to date and growth

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Task 12: Top customers

Using the same sales lines, rank customers by sales and return the total of the top two. (Ranking, Top N and Percent of Total)

DAX
Customer Rank = RANKX ( ALLSELECTED ( Sales[Customer] ), [Total Sales], , DESC )

Top 2 Sales =
CALCULATE ( [Total Sales], TOPN ( 2, ALLSELECTED ( Sales[Customer] ), [Total Sales] ) )

ALLSELECTED keeps the filters the user chose but removes the row-level filter of the visual, so each customer is ranked among the visible customers.

Python Check: rank and top two

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

More project ideas

Once the twelve tasks feel easy, build one whole report on a data set you pick yourself. Public data from open-data portals and sample files you can create in Excel work well. Each idea below names the question, the tables and a feature to practise.

Project Questions it answers Practise
Movie sales Which genres and release months earn most? Which films beat their budget? Star schema (film, genre, date), profit measures, a scatter of budget against revenue
Airline operations Which routes and airlines have the worst delays? When do delays peak? Date and time tables, DIVIDE for on-time rate, matrix with conditional formatting
Social media posts Which topics get the most engagement and when to post? Text cleaning in Power Query, a word-count column, trend lines, a time slicer
Retail inventory Which products will run out first? What is overstocked? Calculated columns, KPI cards, a gauge for stock against target
Public health or population How do regions compare per 100,000 people? Filled map with normalized values, a drill-through page (Maps, Gauges and Visual Formatting)
Personal finance Where does the money go each month? Categories, running totals (Time Intelligence), a waterfall

For every project, write down the question first, sketch the page on paper, choose the grain of the main table, then model, measure and design in that order. Publish it, add row-level security if different people should see different rows (Row-Level Security), and write three sentences explaining what a manager should do after reading it.

How to review your answers

If your result differs, check these in order: is the grain what you think it is, do the keys in the dimensions have unique values, are you counting rows or distinct entities, and which filters reach the measure? Most wrong numbers in Power BI come from one of those four.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which join kind returns only the orders that have no matching customer?

    Left anti keeps the rows of the first table with no match in the second. A left outer join keeps all rows and fills the missing values with null.
  2. 2. Why is Average Order Value defined as Total Sales divided by DISTINCTCOUNT of order IDs?

    Each row is an order line. Counting distinct order IDs counts orders, which is the right denominator for an average per order.
  3. 3. In the percent-of-total measure, what does REMOVEFILTERS ( Sales[Category] ) do?

    The denominator becomes the total across all categories under the remaining filters, so each category shows its share of what the user has selected.
  4. 4. A date table must have:

    Time intelligence functions need a continuous range of dates, one row per day, marked as the date table.

Summary

  • Power Query tasks are about order of steps: trim before deduplicating, unpivot other columns, and choose the join kind that matches the question.
  • A sound model has unique keys on the one side of every relationship; duplicates multiply fact rows and inflate totals.
  • Use inactive relationships with USERELATIONSHIP when one fact table has several date columns.
  • Iterators such as SUMX multiply row by row; count distinct IDs for entities, and use REMOVEFILTERS or ALLSELECTED to shape the denominator and rank groups.
  • Always check a measure against an independent calculation, such as the pandas checks above.

Next, practise explaining it all in Power BI Interview Questions.