Calculate the difference between two rows in Power Query

[v-yiruan-msft]

Scenario: 

Staff need to calculate the difference of the number of products between every morning and every afternoon.

Table used: 

The table shows employees, products, periods, and the number of products on different days. If the employee's corresponding product is missing a morning or an afternoon row, the default will be 0 products for the corresponding time period over three days.

Expected Result:

Solution 1:

1. Right-click on the ‘Queries’ panel and create a blank query.

2. In the advanced editor, enter the following query statement.

3. Go back to the main table and insert a new step in the applied steps. Then enter the statement at the underlined line.

4. Expand the columns in the tables, and it is unnecessary to check ‘Use original column name as prefix’.

Solution 2:

1. Group the tables according to the Employee column and the Product column and generate tables containing the Period column and Days column in the group.

2. Transpose the Table column

3. Put the second row into the column name and fill in the missing columns.

4. Add a column to calculate the difference

5. Transpose the new custom column

6. Remove the columns we do not need and expand the columns we need.

7. Here is the final step to modify the column name.

Summary:

Comparing solution 1 with 2, solution 2 is more intuitive, because in addition to calculating the difference, it also completes the missing rows (e.g. some employees in the data source correspond to products missing the morning or afternoon rows).

Author: Stephen Tao 

Reviewer:  Kerry & Ula

Calculate the difference between two rows in Power Query.pbix