Lesson learned at the customer: Merging two tables to calculate On Time In Full (OTIF). [Part II]
Bringing everything together in one table
Note: this is the second part of the blog. Missed the first part? Read it here.
Select the new Orders table you just made and click “Append Queries as New”
Now append the newly created Orders table with the newly created Deliveries table
If you have more than 2 tables to append, select “Three or more tables” at the top of the Append window.
Remove all columns except for the key column (SalesDocumentSalesItemKey).
Now you have one column containing the values of SalesDocumentSalesItemKey from both the Orders table and Deliveries table.
It is expected to now have duplicates in this new appended column so we remove the duplicates again from this column. This leaves us with a table that contains unique key values that allow us to merge with the new Orders and Deliveries tables to extract the needed columns and their related values.
Make sure you are now on the appended table and merge the appended table with the new Orders table to add the Requested Delivery Date column.
It’s not necessary to make a new table from this unless you want to segment your steps in different tables.
Go ahead and select the first table you want to merge with. In this case we will merge with the new Orders table.
Select the two key columns over which the join relationship will travel. Here we select SalesDocumentSalesItemKey for both tables.
After doing this you still need to make sure that the correct kind of join is selected. We chose for “Left Outer (all from first, matching from second)”.
Right now your values are joined and you can expand the table again. It’s handy to include all columns so you can later on verify whether your join was made correctly. It’s good to check the box for “Use original column name as prefix” so you later know where the column comes from and which ones you can delete at the end.
Repeat step 1-5 but with the new Deliveries table instead of Order.
Now the columns from the new Orders table and the new Deliveries table are added in one big table.
Check if the values look correct. To be certain that you don’t have duplicates you can use the Group By again on the SalesDocumentSalesItemKey column (from the append step) to verify. Don’t forget to delete this navigation step.
Remove the SalesDocumentSalesItemKey columns that you merged from the Orders and Deliveries tables and rename the date columns that are left to something more easy to work with.
Since you have two tables now that won’t be used for reporting nor will they need to be added to the model, it’s good to uncheck “Enable Load” for these tables.
This blog will continue in part 3 where we discuss the measures that have to be made.
Tibo is a BI consultant with a mere focus on Power BI and data modelling. His interests mainly lie in DAX writing, development of Paginated Reports and Power Apps.