One of the really nice features of working with Power Query is the ability to have the tool translate your M language steps into native code in the backend for some data sources. This is called Query Folding, and MSSQLTips has a good introductory article on it, Query Folding in Power Query to Improve Performance. Chris Webb has a more recent post on how they've added View Native Query so that you can see when query folding is taking place, or when it breaks.
One of the challenges I ran into when working with it is that some pretty straight forward queries to a SQL Server to bring in some tables and get only the columns I wanted. Easy enough. And when I tried to merge the two tables together, they would never fold. Today I finally figured out that even though I'm operating within the same SQL Server, each database within the SQL Server is treated as it's own data source.
With this insight I realized I could handle this a couple different ways. Write my own SQL query as part of connecting to the database, which breaks any query folding that may have otherwise taken place. Or since I was primarily working with one database, and just referenced a table from another, I ended up exposing the reference table as a view in the main database. From there, I only needed to change where the table reference was pointing, and suddenly query folding was taking place.
In hindsight this feels like it was pretty obvious if I had considered how any time you write your own SQL you must specify a database. When you write your own SQL, you're able to specify multiple databases as a three part name just like if you were writing it in SSMS (SQL Server Management Studio), and so I expected the query folding to just figure that out as well.
