Replicating Qlik Sense Keep Operations in Power Query turns out to be fairly straightforward. I was recently asked by a client whether the Keep operations present in Qlik Sense could be replicated in Power Query. The Keep functionality allows report developers to import two tables which each observe a join but where the two tables are not actually joined and will be stored in Qlik Sense as two separately tables.
Although Power Query does not have an equivalent command, by using a combination of Merge Query, referencing and disabling query load, it is possible to replicate the Keep command in Power Query.
Here is the data set which will be used in this example.
It contains two tables of project information: the first listing public sector projects, the second private sector. And, let us attempt to import both of these tables using the equivalent of Qlik Sense’s Inner Keep, with each table only keeping those rows containing Staff IDs which appear in both tables.
The first step is to import both tables. Next, we right-click on each table and use the toggle command “Enable Load” to disable table loading. This means that neither of these two tables will be loaded into the data model when we click “Close and Apply” and that the names of the two queries will be displayed in italics.
In the interests of clarity, we have also added the word “Keep” in brackets after the name of each query and placed the queries in a group (folder) named “Keep”. Thus, we have two queries named “Private (Keep)” and “Public (Keep)”.
Next, to create the queries which will actually be loaded into the data model, we right-click on each of the load-disabled queries and choose “Reference”. The Reference command duplicates a query and creates a copy which is linked back to the original and will inherit any changes to it.
We rename these queries simply “Private” and “Public” and move them out of the “Keep” group and into “Other Queries”.
And now for the joins. We highlight the “Private” query and click “Merge Queries”; then, choose “Public (Merge)” as the second or right table, specify “Staff ID” as the match column and “Inner” as the join type.
In the resulting query, we can see that only three records are visible, those with Staff IDs 28, 31 and 4.
Next, we highlight the “Public” query and click “Merge Queries”; then, choose “Private (Merge)” as the second or right table, specify “Staff ID” as the match column and “Inner” as the join type.
In the resulting query, we again see that only three records are visible, those with Staff IDs 28, 31 and 4. These are the only three records where the Staff ID occurs in both tables.
Using the Query Dependencies command from the View tab of the Ribbon gives us a clear picture of how the four queries required to simulate Qlik Sense’s Keep functionality relate to one another.