上QQ阅读APP看书，第一时间看更新

Comparing the current and previous rows

The goal of this example is to compare the values of one row with the next or previous row to compute a value for a variance or status column. In this example, the output of a factory for a given date needs to be compared to its previous days' output:

Retrieve the essential columns into a query.
- In this case, there are four factories, with each row representing the quantity output of a factory by date.

Figure 106: Source Data - Factory Qty by Day

Use the Table.Sort() function to sort the table by Factory ID and then by Date.

Figure 107: Sorting Expression Applied Source

The order of columns specified from left to right drives the sorting precedence.

Figure 108: Sorted Table

Observe the 1 and 2 indicators added to the sort by columns in the Query Editor.

Use the Table.AddIndexColumn() function to add two different index columns to the table.
- Row Index and PrevRow Index have seed values of 1 and 0, respectively.

Figure 109: Two Index Columns Added

The index function is wrapped inside Table.TransformColumnTypes() to convert the column to a whole number data type.

Figure 110: Index Columns in Query Editor

The new index columns provide an ability to self-join the table.

Use a Table.NestedJoin() function to join the table to itself based on the index columns created in step 3.
Use a Table.ExpandTableColumn() function to add Prev Factory ID and Prev Qty columns to the table.

Figure 111: Join and Expand Expressions

See "Integrating and Working with Multiple Queries" for details on joining queries.

Figure 112: Previous Row Columns Added

Add a column with an if...then expression that compares Qty and Prev Qty.

Figure 113: Variance Column Expression

The expression checks whether the Factory ID matches with the Prev Factory ID and sets the new column as a whole number.

Finally, use Table.SelectColumns() to retrieve only the columns needed.

Figure 114: Final Table with Daily Qty Var

The source data starts at 3/12/2017; this causes the nulls in Figure 114.