Clean sales with Power Query and add model measures

In this lesson

Example

How it works

Power Query saves the cleaning steps and applies them again on refresh. Edit the source records or query steps rather than repairing the loaded output by hand.

A DAX measure calculates within a Data Model report’s filters. Put it in Power Pivot, not a worksheet cell. The same measure can return a regional or product total depending on the report layout.

Steps

1. 1

   Convert the sales source to a table named RawSales. In a supporting edition, choose Data > Get Data > From Table/Range, or the available table query command, to open Power Query Editor.

2. 2

   Set OrderID and ProductID to Text, Quantity to Whole Number and UnitPrice to a decimal numeric type. Select Region and apply Trim, then Capitalize Each Word. The results should be West, East and West.

3. 3

   Add a custom column named LineAmount with the expression below. Check 60, 180 and 90 and sum the output to 330.

   [Quantity] * [UnitPrice]

   Copy code
4. 4

   Name the query SalesModel and use Close & Load to place its output in a worksheet table. On Mac or Web, use the load and refresh options supported for your edition and source. You can continue with the worksheet result if Power Pivot authoring is unavailable.

5. 5

   For the Windows Power Pivot extension, make Products a table and choose Power Pivot > Add to Data Model. Under Data > Queries & Connections, right-click SalesModel > Load To and select the Data Model option; you can retain its worksheet output too. Open Power Pivot > Manage > Diagram View and relate Products[ProductID] to SalesModel[ProductID]. Check that each Products key occurs once.

6. 6

   Choose Power Pivot > Measures > New Measure. Set Measure Name to Total Sales and Formula to =SUMX(SalesModel,SalesModel[Quantity]*SalesModel[UnitPrice]). Use Check Formula and place the measure in a model PivotTable by Region.

7. 7

   Create West Sales with Formula =CALCULATE([Total Sales],SalesModel[Region]="West"). It replaces a filter on the same SalesModel[Region] column, so a report selection on that field will not narrow this measure to another region.

8. 8

   Create Region Share with Formula =DIVIDE([Total Sales],CALCULATE([Total Sales],ALL(SalesModel[Region]))). Put SalesModel[Region] in Rows and check West 150/330 and East 180/330.

9. 9

   Add O4 P200 2 180 West to RawSales. Refresh the query, model and reports, then compare the record count and totals. The new line adds 360; leave the loaded output to the query.

Try it yourself

1. Inspect the original model by Product and explain why Keyboard totals 150 while Monitor totals 180. On editions without Power Pivot, reproduce those product totals with a worksheet PivotTable and identify that as the alternative method.
2. After the added row, test Region Share under the fact Region field and compare the two rows with the grand total. Keep an audit note describing the source record count, query output count and refresh date.

If something goes wrong

A join or relationship gives an unexpectedly large total.

Check Products[ProductID] for duplicate keys and inspect the relationship or join. The products side should identify one product per key in this model.

The DAX formula gives a name error in a worksheet cell.

Create it under Power Pivot > Measures > New Measure using the stated table names. A worksheet formula and a model measure run in different contexts.

The query stops refreshing after the workbook moves.

Inspect the source step, connector and credentials for the current platform. This exercise uses workbook tables; an external path or connector needs its own setup. Keep credentials out of shared workbooks.

Quick check

Try these questions after the exercise. Check the result in your app before marking the lesson complete.

More from Microsoft

These Microsoft guides cover the tools used in this lesson.

• Power Query for Excel Help ↗
• Create a Measure in Power Pivot ↗