Power Query Count Rows

You can edit to query and set the desired sort order in Power Query. Underscore character in Power Query = All items in Row 9. As per Microsoft documentation, Text. Power Query lets you perform a series of steps to transform your messy Excel data. This approach works well with websites that spread data over multiple pages, and have one or more query parameters to specify which page or range of values to load. Length function power query. Microsoft Power Query for Excel is an Excel add-in that enhances the self-service Business Intelligence experience in Excel by simplifying data discovery, access and collaboration. The STATISTICS IO output of this query shows that SQL Server is doing a lot of work! Over 100,000 logical reads, physical reads, and even read-ahead reads need to be done to satisfy this query. Power Query Function for Dense Ranking. I just started learning how to use Power Query and it is a very powerful tool. Introduction. It uses an Index, Modulo (to assign rows to a column column), Integer divide (to group the rows) and Pivot. Power BI or Power Query in Excel (or Get Data and Transform as the new name of it) can do many data transformations. Power query will then automatically create query steps to promote the first row to column headings (for CSV files) and change data types. I use this function in my Power Query Editor for COUNTIF but I haven't yet mastered COUNTIFS or SUMIFS: let countif = (tbl as table, col as text, value as any) as number => let select_rows = Table. Can I use regular excel commands in power query?. We want those to be combined into a single row. Accumulate is one of most undocumented and most underestimated functions in Power Query 'M' language. Plug Housing, Dual Row, 4 Circuits, UL 94V-2, with Panel Mounting Ears, Natural. Query folding works reliably when queries authored in Power Query reference relational database tables or views. This will group your table by Country value, and count the number of occurrences of each country. The problem with removing duplicates is that power query keeps the first instance of the record and removes the duplicates. I'd like a query that can give me the count per column of the values in the table. The following shows how to use ROW_COUNT() in a query after running an INSERT:. Let us say, for all rows that are not numeric, we set them to a negative value of -999. The following formula returns a count of all rows in the Product table that have a list price. As a data nerd, I’ve been having a lot of fun pulling data sets off the web and mashing them up. However, there is a way around this limit. Use Count Rows and All Rows. In Power Query use "From Table", select the "Parameter" table and right click one of the values and click on "Drill Down". Online Power Query Training Course; DAX function and audit that the number of accounts by account the user is limited to placing fields on Rows/Columns with. You create an Index column to show the row positions prior to removing any rows. Power Query. Many excellent BI bloggers have covered creating a basic date dimension using Power Query. Today I had a table where the column contained information about both the parent element and child element In the example the CategoryInfo column contains both the Category and sub Category information. Pingback from Power Query Decathlon – Beginner 04: General Transformations | Mark V SQL Time January 12, 2015 at 10:00 am […] shown in Figure 4. Data cleanup and transformation is one of the main purposes of the PowerQuery. By calling Table. Not limited by Power Query. There are also options for which number to start the sequence with and how much to increment each row. For my inaugural Power Query post, I’ll show how you can get data from the Twitter API, specifically the Twitter GET search/tweets method. Removes duplicate rows from the table table. An optional parameter, equationCriteria, specifies which columns of the table are tested for duplication. If you’ve not seen our first article on creating queries in Power Query, you might want to check it out. Feb 01, 2016 · Tables can be thought of as a list of records, where each record is a row. The COUNT() function returns the number of rows that matches a specified criteria. You’ll now be launched into the Power Query editor. Be sure to update the in statement with this variable name. Calculating values with Power Pivot. you can get approximate row counts too Would that heat-engine provide power. I was presented with an interesting problem last week; how to create running totals in Power Query. You can, however, "expand" these date ranges or durations to create rows for all the dates including start, end and those in between. Learn how to prepare your textual data and load it in Word Cloud custom visual in Power BI. The table contains records assessment attempts for staff. In the functions for Table. Introduction. But my files have 100,000 rows of data, and the first row of the next file and subsequent files will always be another header. To see what it looks like open Power BI Desktop (or Excel), and Get Data from the database and table above; DimCustomer, Click on Edit to open Query Editor window. I was answering a question on the Power BI Community Forum where a user was looking to get some data from an Excel spreadsheet into Power BI. If there is added a Limit, you will get at most as many rows as the number you specify. Counts up all the rows in a given column. What i want is the number of rows which is 3 and not the sum of count. Power Query. To Keep Top Rows. The technique he proposed uses Power Query to:. However, when researching this issue, I discovered that both columns contained null values, so I removed those rows. Maximum number of rows filled to worksheet. Where I am currently consulting there was a requirement to create a measure like you can in the Excel pivot tables for the % of Column Total or the % of Row Total. 50 Greatest Horror Movies of the 21st Century From topical zombie apocalypses to retro-slasher flicks, the best scary movies since the turn of the millennium. About this document. Here, ROWS limits the records within the partition by specifying start and end within the partition and UNBOUNDED PRECEDING means that the window starts from the 1st row till the current row. Save this query as Order Count. [email protected] Are those commands available in power query? Don't get me wrong, I'm really good at excel and and math, but M (I think that's the language) is not my strong side. 81 of the Power Query Add-in for Excel. In this example we are going to use the ROW NUMBER concept to find the fourth highest salary. Each transaction has a date and that date is liked to a dateDim table. Power Query allows you to extract and manipulate data from various sources. You can use the Power Query UI to generate the code to do this. Join function in Power Query is to use Merge Queries on two steps of one single query. The data source was a simple 100+ row worksheet in the same workbook. Developing queries for Power BI and Power Query I had to look into documentation or check my previous solutions from time to time in order to get answers to same questions again and again. This is the most basic DAX query (the brackets are optional):. In our formula, we supply the coordinates in the reverse order, and this is what actually does the trick! In other words, this part of the formula ADDRESS(COLUMN(A1),ROW(A1)) swaps rows to columns, i. How this formula works. For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet. To repeat the same process on the empty columns, we can transpose the table. Returns the number of items in the list list. With Power Query, working with data dispersed across worksheets or even workbooks has become easier. Each column of data can only hold boolean, numeric (including date/time types) or string values. Example: Let us say we have account data as shown below. By telling Power Query to add a column using the All Rows operation, it adds a Table object to each row of the resulting column. How to group rows and add a non-aggregation column with Power BI / Power Query ? This is an excellent example to showcase some of the hidden gems when using the Group by feature. Online Power Query Training Course Rows in a Time Period: Guest Post from Chris Campbell. How to apply row number based on certain fields in power pivot? A linkback table is the result set in Excel from a query of Power Pivot tables. In this article, we shall learn about implementing variables in a DAX query. Creates a small query in Power Query, such as importing a csv file. What I’d like to cover in this admittedly long post is how to create Power Query functions that you can spin up and configure quickly. Let us say, for all rows that are not numeric, we set them to a negative value of -999. This way I could create a Power Query/Power Pivot solution in Excel, refresh it on a timely fashion by an admin with access to the source data and then just publish that SNAPSHOT of data for consumption in Power BI through OneDrive for example and be able to refresh THAT FILE’s data only so it reflects changes I make in the Excel file. Welcome to our Power Query blog. This will group your table by Country value, and count the number of occurrences of each country. However, the number of functions in the graphical interface is very limited. Yes, It can definitely remove the data. Power Query is an amazing ETL tool (Extract, Transform, and Load). What is Power BI? It is a software to create and publish interactive, web-enabled reports & visualizations for your audience. When the conditional expression's logic is on a row-by-row basis, the best is doing it in Power Query rather than DAX (there are exceptions always), The Add Conditional Column in Power Query is a very helpful option, but often many people find…. Filtering rows by position is similar to filtering rows by value, except that rows are kept or discarded by their position in the table rather than by cell values. 1M characters. In this article, we will learn to sort data and remove duplicates with power query. The technique he proposed uses Power Query to:. Load the data into Power Query: Excel 2010 & 2013 Power Query tab/Excel 2016 - Data tab > From Table. To do this: i. To your questions > If you did have [UnitPrice] in a separate Products Table, the formula would change to. If you want to count logical values, use the COUNTAX function. Hi, Can you please help me how to know the number of rows in all the tables databsae. Feb 01, 2016 · Tables can be thought of as a list of records, where each record is a row. Now save the query function. As Power Query is a parallel language, each row gets same random number (unlike Excel's RAND() filled down a column). I was thinking of that project the other day and thought, hey, how we can do the same in SSAS Tabular when there is no Power Query (M) language available in SSAS Tabular 2016. My data has 40k+ rows. I use this function in my Power Query Editor for COUNTIF but I haven't yet mastered COUNTIFS or SUMIFS: let countif = (tbl as table, col as text, value as any) as number => let select_rows = Table. So instead of taking your table directly into Power Pivot, you drag it through PowerQuery and load it to the model. It can go into a worksheet in the workbook, into the data model, or into both. The data source was a simple 100+ row worksheet in the same workbook. Measures (also known as Calculated Fields in Excel 2013) are formulas/calculations that are added to a Pivot Table. There doesn't appear to be a built in method to do this, so I thought it would make an interesting use case study. 50 Greatest Horror Movies of the 21st Century From topical zombie apocalypses to retro-slasher flicks, the best scary movies since the turn of the millennium. If some pictures are hard to view, you can get the PDF of the article The Pivot option in Power Query. One point before I start: there really should be a straightforward way to create a running total in Power Query. One of the features offered by this tool is that it allows you to group different rows together in order to get the count of the entire group in a very easy and convenient fashion. RowCount function counts how many rows exist in the group. The approach I took was to merge the two tables so that each row contained both the old data for the row and the new data for the row. ) The Problem. This is a perfect task for Power Query. Solved: i am trying to group by in power query but i only want it to count distinct on one column. A reader asked if we can number rows by group in Power Query, similar to the PARTITION function in SQL of course there is, we just need a little code. Learn several methods to split words, ignore common words, and count word occurrences in your analysis. I have been using them in Excel 2013 for a while and one of my favorite features was Power Query’s ability to source files out of a folder, concatenate them together and then load them into a. Using Power Query to search the interwebs! then for each of those rows you are walking the table again 1 row at a time to evaluate. Category: DUPLICATES AND UNIQUES (using MS Query and Power Query) Since the number of rows here are quite many, i would suggest copying in batches i. As you can see below, the data had been loaded in the power query editor. Importing your data with Power Query is simple. Q&A for Work. April 9, 2013; have a column with just the month number (e. Go-to tools for many are PivotTables or formulas as SUMIFS, SUMPRODUCT, AGGREGATE and the like. SelectRows(table as table, condition as function) as table About. Reverse Rows It reverses the rows in a table in such a way that the last row becomes first row. function (table as table) as number. The post contained two queries - the GetData function, and a query to invoke it over a set number of pages. One of the most common steps I do, is to clean my data and remove rows, and Power Query gives you a lot of options to do so! STEP 1: Our sample data contains the Sales numbers for each month. I hope Power Query team will implement a distinct count option in the user interface. The table has four rows: Store number, Date, Metric and Result. If your connector has this non-delegation query limitation, and you know that the number of records you need from your data source is 2000 or less, you can set this value to 2000 and see all your records. The function is =COUNTIF(A:A,A1). Go-to tools for many are PivotTables or formulas as SUMIFS, SUMPRODUCT, AGGREGATE and the like. COUNTA function. In some applications, you execute queries that can return a large number of rows, but you need only a small subset of those rows. Add some space and copy/paste function code, give it a name. When date is one of the dimensions, any days without data are omitted from the result list. I'm talking about slightly more complicated cases. Power Query is an amazing ETL tool (Extract, Transform, and Load). I would like to create a column that is an incrementing number for each unique value of Col2. Power Query. We shall also learn what Keep Rows and Remove Rows are in Power BI. By calling Table. Number of Rows Select Part. Actually, the right myth should be that you can't use more than 1,048,576 rows, since this is the number of rows on each sheet; but even this one is false. Go-to tools for many are PivotTables or formulas as SUMIFS, SUMPRODUCT, AGGREGATE and the like. Let us begin and learn to sort data and remove duplicates with power query. AggregateTableColumn of Power Query M doesn't allow usage of type as 4-th parameter of an aggregation for last aggregation. Plug Housing, Dual Row, 4 Circuits, UL 94V-2, with Panel Mounting Ears, Natural. How to apply row number based on certain fields in power pivot? A linkback table is the result set in Excel from a query of Power Pivot tables. How this formula works. These structures are main value structures in Power Query and M. To better explain my code, you can think of it as a function that performs operations on all the cells in a. (Relationship in Power BI) The calculated measure "Record Count" is the count of records (Record Count = COUNTROWS('Sales Invoices')). And you will get the corresponding Beatle. There doesn't appear to be a built in method to do this, so I thought it would make an interesting use case study. ) In the screenshot below, you will see ROW 4 and 9 are hidden by the direct method of Hiding rows, i. Count(list as list) as number About. The SQL COUNT() function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. Select the columns you wish to unpivot. calculating a percent of total in a query. This function can be used to count the number of rows in a base table, but more often is used to count the number of rows that result from filtering a table, or applying context to a table. One of the most common steps I do, is I want to add an index column that serves as a row counter of my data. Level: Advanced Power Query. As you can see below, the data had been loaded in the power query editor. Recently I faced interesting Power Query problem. hi i use power query to aggregate all excel file in a folder that increase monthly my data fildes are name column1 column2 column3 reportB. For example, we want to count for only specific years of data. The table has four rows: Store number, Date, Metric and Result. (Note: What I mean “Filtered Rows” in this article is the row(s) hidden by Filter. To learn more about Power BI; read Power BI book from Rookie to Rock Star. Let us say we want records of March 2015 to Feb 2016. Values close to the 2000 limit may also adversely affect performance. Pingback from Power Query Decathlon – Beginner 04: General Transformations | Mark V SQL Time January 12, 2015 at 10:00 am […] shown in Figure 4. If the data is available for that column, it will be shown, else it will show null. The standard aggregations are Average, Median, Min, Max, Count Rows, Count Distinct Rows and All Rows. How to Use Values from Previous or Next Rows in a SQL Server Query The Row_Number() function enumerates rows in the order specified in the ORDER BY clause in the. Alas, that's not the purpose of this post. I have almost 450 Event IDs and for each event, speed was measured every 0. If the function finds no rows to count, it returns a blank. It allows you to summarize data and aggregate underlying values. SelectRows(table as table, condition as function) as table About. Power Query Count & Extract Customer Names for 8 Sales Coupon Groups. when you say query do you mean when you first load in data because I regularly demo 2-4 million rows of source data when showing this off at events. These results show me every unique number of rows that was returned and their average duration. So, we must ^Use First Row as Headers. The result of SUMMARIZE is always a table. Power BI Tutorial: How to Use Power Query to Load Data from a Formatted Report By Business Intelligist in Excel and Excel Services , Power BI , Power Query , Self Service BI , Tutorial on January 25, 2015 January 25, 2015. POWER QUERY is one of those things which can be a game changer for you. By Michael Alexander. The easiest way to achieve this in Power BI Desktop is to “Enter Data” which creates a new table by typing or pasting in new contents. Transform table column using own function in Power Query 2015-10-11 2016-12-12 Ivan Bondarenko Very often we come to situation when we need to somehow transform list elements or, what is more often, table column. Transpose in Power Query. Suppose someone has given you a list like the one shown below (which you can download here if. Evaluate in Power BI and Power Query This article describes the use of Expression. It¨s little similar to Pivot Tables in common Excel or to groupping in Power Query (Get and Transform). This function is also available in repository of helpful Power Query functions on Github - here. Choose Remove Bottom Rows from the Remove Rows drop-down. By telling Power Query to add a column using the All Rows operation, it adds a Table object to each row of the resulting column. Below you can find the function that I created. I hope Power Query team will implement a distinct count option in the user interface. Return Type. To get the records between two months with the year we have to change this query. 36459475 published The workaround to check for Total works if you are using only a few tables. If we glance at the Query Setting under the Applied Steps, we now have 4 steps. I've integrated Power BI and my Salesforce report but when importing the data it selects a random 1000 rows of information and then won't import the remaining 4000 rows. One of the most common steps I do, is I want to add an index column that serves as a row counter of my data. One of the most common steps I do, is to clean my data and remove rows, and Power Query gives you a lot of options to do so! STEP 1: Our sample data contains the Sales numbers for each month. Power Query is an amazing ETL tool (Extract, Transform, and Load). Prerequisites: Data consolidation using Power Query. Power Query. These tips will not only help you to save your time but also going to inspire you to use POWER QUERY for managing data. Soft limit of persistent cache. Chris also shares best practices for working efficiently with Power Query. It enables you to transpose your table, and switch between your columns and rows. Power Query Import. Yup, this was a bit of contrived example. I was answering a question on the Power BI Community Forum where a user was looking to get some data from an Excel spreadsheet into Power BI. upper function power query. So instead of taking your table directly into Power Pivot, you drag it through PowerQuery and load it to the model. Click the Group By button and set it up using the following criteria:. Introduction. Filtering rows is one of the most basic requirements in a data preparation tool such as Power Query, and Power Query do it strongly with an Excel like behavior for filtering. So, I ended up with a creation of cheat sheet for myself. Don't forget to add comma after "result". To be able to do that, the spreadsheet needed all the unique combinations possible for the data. Find the number of rows in the table. this is the current m code #"Grouped. upper function is. SUMMARIZE – groupping in data models (DAX – Power Pivot, Power BI) This article is about SUMMARIZE function, which groups rows in data models. Then Group By > Count. Power Query Unpivot Scenarios - Written Instructions: Straightforward Unpivot. Transpose in Power Query. Each row has been split into two (or sometimes more) rows. So do not read too much into the way data is structured. The icon appears elsewhere, in the user interface, but this is the easiest to find. The Microsoft Power BI product team told us the same late last week: Power BI Report Server data export is limited to 30k rows, while Power BI Service can export 150k rows. Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. You'll now be launched into the Power Query editor. More technical details on using the function as part of a more robust profiling query are provided later in this post but to just get started you can pass the source table to the function. When we click on the OK button, we get an extra column in the power query editor called ConvertToNumeric. In this article, we shall learn about implementing variables in a DAX query. Values close to the 2000 limit may also adversely affect performance. Let’s change it. The Transpose transformation is available in the Query Editor’s Transform tab, and as an M function Table. He then dives into Power Query, explaining how to leverage key query editing tools to connect and transform data. Now you can play around with this, by substituting the 0 by any other number up to three. April 9, 2013; have a column with just the month number (e. Power Query. Field(_, col) = value), count_rows = Table. If you want to work with the data, choose Keep. I am loading excel file that include of three columns of patient name, date of admission and duration of hospitalization (day) by power query. If you use the graphical user interface in Power Query for Power BI or Excel you have number of options to get some. Like if you want names to be the headers and particulars along the rows under each name then you simply have to select name column and pivot it with “Info” as value column. 20mm Pitch, Dual Row, 2 Circuits, without Snap-in Plastic Peg PCB Lock, Tin, Natural. However, when researching this issue, I discovered that both columns contained null values, so I removed those rows. Once in the Power Query Editor, the Group By feature is the first icon on the Transform tab: Transform -> Group By. Name this column “Random” But Power Query gives same random number in all rows … That is right. Home Power BI / Power Query Absolute and Relative References in Power Query: R1C1 Excel-style approach. Q&A for Work. They use a new set of keywords to define the boundaries within the partition, i. Confirmation dialog to delete a query when deleting a worksheet. These structures are main value structures in Power Query and M. Tip: Format the data in an Excel Table first and give it a useful name. When there are more than 999 rows I see an information like "30 COLUMNS, 999+ ROWS" after each step in my Query. As aggregation I have selected "All rows" and named the new column as "Data". One of this methods uses direct selection of unique parameter name. Each transaction has a date and that date is liked to a dateDim table. What i want is the number of rows which is 3 and not the sum of count. And you will get the corresponding Beatle. Converting a Query to a Function in Power Query You can turn a query into a function by adding a single line before your M query: That is a pair of parenthesis ( () ), followed by a goes-to symbol ( => ). RowCount function counts how many rows exist in the group. The actual limitation depends on the version of Microsoft Excel you are running and whether you are going to publish your spreadsheet to SharePoint. Once in the Power Query Editor, the Group By feature is the first icon on the Transform tab: Transform -> Group By. Power Query makes this an easy, two click operation. One point of confusion I see with Power Query is some people believe Power Query adheres to the Excel limit of a max of 1,048,576 rows that can be imported. If row number is greater than 1 means it is a duplicate row and to be deleted. Power Query. The arguments RowCount and ColumnCount represent the number of iterations for the outer and inner loops, respectively. How do I determine the number of rows that I have without waiting for Power BI to load them? Manually it takes forever to. This post explores how to do that. This article introduces these two concepts. There’s an Index Column button in the Add Column tab of the power query editor. The 1 becomes the "original" and the 0s are the "duplicates". Transpose is a very useful transformation. ROW_NUMBER can be non-deterministic: if sort_key is not unique, each time you run the query it is possible to get different row numbers assigned to any rows where sort_key is the same. A whole number. To learn which days have data, issue a query without filters grouped by date, for the date range of interest. Listing all of the rows in a table. Click on data tab in power query and then click on from table option. SelectRows(table as table, condition as function) as table About. Suppose someone has given you a list like the one shown below (which you can download here if. Find the number of rows in the table. And that’s where Power Query came to the rescue. But you need to always use ORDER BY clause so that the numbers are assigned to the specific order. Creating a serial number for each row of data in power query is easy. The next step is to use the Group By command from Transform tab, using the new column to group by, with Count Rows operation (Or Count Distinct Rows, or any operation, it's not really relevant, we se the grouping just to create a unique ID) The only thing left to do for this query is to add an index column, starting from 1. What I want to do is: - In a new column [Attempts. The Power Query (M) Builder is a new tool for the XrmToolBox. Power Query will automatically turn your pivoted data into an Excel data table and launch a Query Editor window. Retrieving the entire result table from the query can be inefficient. This week, I look at how to view the errors for each row. One of the features offered by this tool is that it allows you to group different rows together in order to get the count of the entire group in a very easy and convenient fashion. If row number is greater than 1 means it is a duplicate row and to be deleted. Array formulas can be recognised by the {} Excel puts around it automatically when you press Ctrl+Shift+Enter upon formula entry. Don’t worry if some of the Excel Tables have additional columns. The issue is that Power Query automatically changed the data types of my columns for me based on the first 1,000 rows. This was only an issue if you had a large number of tables. There are multiple rows for individuals. Specifically, his question was this: The other day I was asked if Power Query could delete all top rows up to a found value. A column reference intuitively means that you want to retrieve. Evaluate in Power BI and Power Query This article describes the use of Expression. RowCount(table as table) as number About. The second step is about to insert new (6) columns on the right based in certain conditions. Feb 01, 2016 · Tables can be thought of as a list of records, where each record is a row. Using Power Query, you can load this sheet, remove the first two rows and columns, unpivot the data, and you have the numbers you're looking for in tabular format. One of these transformations is grouping rows by number of fields. all records after the first instance of the record are. We will work on a simple example to show you how easy it is to use the COUNTROWS function in your Measure. When sort_key is unique, each row will always get a unique row number. Category: POWER QUERY The number of rows on each worksheet vary depending upon the number of products sold in that year (as can be seen in the two images below). Power Query – Controlling M Query Functions with User Driven Parameters April 3, 2015 devinknight Power Query 15 comments Have you ever had a user run a query against one of your largest tables only for them to immediately filter the results in Excel to show the last years worth of data?. Once you successfully place a random number in each row Access will sort the data based on that number, because you specified Ascending for the column’s sorting. Power Query lets you perform a series of steps to transform your Excel data. Using a ‘where’ clause to eliminate blank rows. 5M rows and 10 CSV files with 250K rows each merged using binary combine takes the …. Imagine having hundreds or thousands of rows for which you need to enter the row number. Number of Rows Select Part. There’s an Index Column button in the Add Column tab of the power query editor. In this example we want to unpivot columns B:D and remove the Grand Total, in column E. To save the query, choose File Save As in the Microsoft Query window and then name your query anything you want. Today I combine two list functions to show one method to achieve a running total. Duplicate the query, then unpivot the columns containing TRUE/FALSE. To Keep Top Rows. The post contained two queries - the GetData function, and a query to invoke it over a set number of pages. How to group rows and add a non-aggregation column with Power BI / Power Query ? This is an excellent example to showcase some of the hidden gems when using the Group by feature. 8/1/2019; 2 minutes to read; In this article Syntax Table. Maximum size of text in a preview cell. Click on the ABC123 icon and select Whole Number. Test your system to find the optimal value for your application. And please figure out how internally inside Power Query you can retrieve the first 2000 rows, then the next 2000 rows, etc. DistinctCount here, you reduce the number of rows in the table to a list of distinct count values, returning a correct value. The Row Count Spool operator is a simple component in an execution plan that can make an enormous difference to SQL query plans that would otherwise read a table on every row of the inner input to a nested loop.