This article will teach you how to streamline your query transformation process by eliminating the need to duplicate a table for rejoining purposes.
When handling data, you might find yourself in situations where it’s necessary to divide a query into two segments. These segments are then operated on separately, each at its own level of granularity, before being merged again. This strategy can be required in a multitude of circumstances, particularly when you need to deal with varying degrees of detail within the same table.
From the image above, Table and Table (2) are identical. They require different levels of granularity before being rejoined. However, there are several issues with this method:
• It creates a lengthy list of queries in the Queries pane, complicating matters when multiple data sources are involved.
• Duplicating a query means making two calls to the same data source, impacting performance.
Instead of duplicating tables, you can conduct the same transformations using the advanced editor with some M code. Here’s the M code solution:
Table.NestedJoin(#"MSTEP1", {"JoinKey"}, #"Grouped Rows", {" JoinKey"}, " MSTEP1", JoinKind.LeftOuter)
Let’s illustrate this with a simple use case:
A global company with multiple stores sells various products and wishes to apply product-specific discounts. Management has decided on initial discounts, but wants to offer an additional 5% if a store sells more than three types of products. Therefore, if a store’s product count is > 3, the premium discount = raw discount + 0.05.
To determine whether the additional 5% applies, we need to count the number of products each store sells. After this count, the rule can be implemented.
One option is to duplicate the table to group the stores, as shown in the image:
This grouped table can then be rejoined to the original table to acquire the required information for each row.
Now, each row displays the count of products sold for a specific store. With a conditional column, we can implement the logic: if store > 3, premium discount = raw discount + 0.05. This method involves creating two tables, which isn’t ideal.
An alternative approach conducts everything under a single query, calling steps from applied steps.
First, group the table by stores within the same query:
Note that the last applied steps is titled Grouped Rows. Next, apply the previously shown M code:
This step, labelled Autojoin, uses the Table.NestedJoin function to call a different step of the same query rather than a separate query.
Finally, expand the query and apply the premium discount rules:
To apply the rule, create a new column with an IF condition logic:
In conclusion, it’s more efficient to make all transformations within a single query rather than creating multiple queries and then combining them. The Table.NestedJoin function helps to achieve this by calling on previous steps of the same query.
