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:

  1. 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
  1. 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.
  1. 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.
  1. Use a Table.NestedJoin() function to join the table to itself based on the index columns created in step 3.
  2. 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
  1. 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.
  1. 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.
上一章目录下一章