Fixing different results in standalone query compared to joining in Power Query

We ran into an issue at a customer where we joined two queries in Power Query and got different results compared to when viewing the standalone query results.

One of the queries had several transformations that don't fold and rely on sorting, partitioning, and adding an index -- essentially a rank with GROUP BY (as described here). The rank values in the standalone Ranking query were correct, but when we joined it to another query, the joined rank values were different and incorrect.

Standalone Ranking query: top ProductKey values for date are 350, 348, 351


Rank values after joining Ranking query to another query: top ProductKey for date is 346, not expected 350

We didn't dig into why -- the code was correct as far as unit testing the components of the query -- but my guess was that it has to do with Power Query running things in a different sequence than we intended as human developers or skipping steps it thought were immaterial.

This was a bit of a guess, but to try to force the execution to be more sequential, I added a step at the end of the Ranking query to buffer the results (Table.Buffer), thinking this may get the Ranking query to run in its entirety before joining it to the other query, and it worked.

Table buffer step manually added as final step in Ranking query

Updated results after join: top ProductKey values for date are 350, 348, 351, consistent with the standalone Ranking query they were joined from

Contact Form


Email *

Message *