If you go step by step, you’ll think that Power Query does the following: How can Power Query tell my database to only retrieve only that subset of data? Query Folding is the answer! Here’s the situation, our database has MILLIONS of records, but we only want a subset of them and that’s why we did the “Filtered Rows” step and then we removed some columns. Let’s use the same query that we previously saw and elaborate on that. What’s different about this post than the rest? we take a purely practical approach.
#Adventureworks2012 items table how to#
#Adventureworks2012 items table code#
In short, query folding is the process where the M code gets translated into the native query language of a data source. Now we head over to one of the coolest things about this Evaluation Model: Query Folding. And this is how you force the evaluation of a value. How do we force an evaluation? Using any of the Buffer functionsįor our case, we’ll be using the Table.Buffer function to force the evaluation of our codeĪnd voilá! we now get the result that we were looking for. What do we need to do? Force the evaluation! This happens because there’s a lazy evaluation and the actual folder hasn’t been evaluated whatsoever.
![adventureworks2012 items table adventureworks2012 items table](https://docs.devart.com/images/fusion-for-sql-server/object-viewer.png)
Which basically says that there’s no error. Source = Sql.Database("", "adventureworks2012"), Up to this point, we just used the mouse and we never had to use the keyboard other than to input the server and credential details.Īfter only using the UI, here’s the final code that Power Query created for us: let
![adventureworks2012 items table adventureworks2012 items table](https://www.mssqltips.com/tipImages2/1851_AlterpredefinedtriggertopreventaccidentalDML.jpg)
The ones that we want to keep are shown below
![adventureworks2012 items table adventureworks2012 items table](https://blog.sqlauthority.com/wp-content/uploads/2009/05/lastupdated.jpg)
The code that you see there was generated through your clicks and the language that it uses is called M. (Example of Applied Steps by transforming a numeric column using a round operation) You can see this piece of code in the “Advanced Editor” and also in the Formula bar. This piece of code is called a step, and you can even see, to your right, a timeline of all the steps that you performed. When you click on any of the buttons inside the Power Query Editor, the tool will automatically create a piece of code for you. Power BI / Power Query does an amazing job of providing what appears to be a seamless experience to the end-user where the end user can perform all the data transformations that he wants against pretty much any data source. The evaluation process is not visible to end users. How do Queries get evaluated in Power BI / Power Query? You can read the official documentation from the Microsoft team here, but in this post I’ll cover the basics, so you can have a quick overview of how things work with Power Query / Power BI and how you can optimize your queries to better harness their engine. In this case, we’re curious about how the Evaluation Model works for Power BI & Power Query when it comes to executing your queries and getting your data. We want to know how things work and perhaps harness them for our own purposes. How do things work in the world? Humans are curious beings.