Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.