Tag Archives: SQL Server

Equals Is Not Always Equivalent: When Query Folding Does Not Produce Identical Results

, , , ,

Query folding is supposed to be transparent, as far as results go. Whether or not a Power Query expression is folded should have no effect on the data returned. You should receive back identical results either way. At least, that’s the theory.

Unfortunately, this is not always the case!

The fact that query folding sometimes changes the results that are returned can bite unexpectantly. You have an M expression that produces exactly what you want. Then you make what should be an innocuous edit, but behind the scenes the change affects whether or how the query is folded. The results you now receive back are no longer what you expect, and puzzlingly the divergence seems to have no obvious relation to your edit. Or, maybe you didn’t edit anything at all: instead, a Power Query update changed the foldability of your query without you touching it. You made no changes, yet the data returned is now different.

Continue reading

For a Future Version: Re-Engineer Microsoft Access to Use a SQL Server Backend

,

At the core, both Microsoft Access and Microsoft SQL Server are relational database management systems (RDBMS), yet each uses a separate file format and code base. These differences add friction to the process of integrating the two technologies. Developers interacting with both back ends need to learn the idiosyncrasies of two systems. For administrators, the fact that each uses a different driver requires maintaining multiple driver configurations on systems that need to communicate with both. Power users are faced with the pain of figuring out how to cross this technology divide when growth dictates switching a home-grown Access application to a SQL Server backend. Continue reading