Now do it in Power BI Desktop
Power Query in practice: Power BI Desktop exercise
Real shaping work in Power Query: custom columns, types and groups. You practised this in the browser; now do it in your own copy of Power BI Desktop (free from Microsoft), on the same data, and check your numbers below.
1. Get the data
In Power BI Desktop: Home → Get data → Text/CSV, pick each file, then Load. Check that numbers came in as numbers and dates as dates (Table view, column header icons).
2. The tasks
1. A custom column
Add Column → Custom Column Revenue =
[Units Sold] * [Sale Price], typed as Fixed Decimal Number. Load it and sum it.2. Size bands
Add a Conditional Column Deal Size: Large from 500,000 Sales, Medium from 100,000, else Small. How many Large rows?
3. Group and rank
Group By Product with the sum of Profit, sort descending and add an Index column from 1. Compare with these totals.
3. Check your numbers
Try every task first. Then open each answer: the numbers below come from the same data, so yours should match exactly.
1. A custom column
Revenue = SUMX(financials, financials[Units Sold] * financials[Sale Price])Result: 127,931,598.5
2. Size bands
Large Rows = CALCULATE(COUNTROWS(financials), financials[Sales] >= 500000)Result: 85
3. Group and rank
Total Profit = SUM(financials[Profit])| Product | Total Profit |
|---|---|
| Amarilla | 2,814,104.05 |
| Carretera | 1,826,804.89 |
| Montana | 2,114,754.88 |
| Paseo | 4,797,437.94 |
| Velo | 2,305,992.47 |
| VTT | 3,034,608.02 |
| Total | 16,893,702.25 |