This tutorial will discuss how to use selection and projection inside the Power Query Editor. Selection is for selecting a row; projection is for selecting a column. You’ll learn and understand how to use them to obtain data and information from a table.
How To Use Power Query Row Selection
The positional index operator is the zero-based index number within a set of curly brackets. The lookup operator is a field name in a set of square brackets. These operators can be combined to access an individual cell.
To demonstrate, this is a sample table query that will be used.
If you want to retrieve the Table1 value in the Item column, click fx in the formula bar and add a manual step. Then, use the positional index operator to refer to its zero-based position and the field access operator to refer the column name.
From the Sheet1 cell in the Item column, count from 0 until the cell of Table1. You’ll find that the position of the Table1 cell is 2. Therefore, input 2 inside the curly brackets. For the field access operator, input the name of the column that the Table1 cell belongs. After that, you’ll see that the preview pane will return Table1.
If you right-click the space in the Table1 cell and select Drill Down, you’ll see that the user interface generates the same results and syntax.
The reason is that the sample table doesn’t have a primary key. Tables with primary keys use key match lookup.
This is another sample table query with a primary key.
If you right-click on the space in the Table1 cell and select Drill Down, you’ll see the same results but with different syntax.
This is because key match lookup is applied to the positional index operator. It lists all the primary key columns and their values for the selected record. You can also use the key match lookup method in the first sample table query above even if it doesn’t have a primary key.
Remove the last step of that table query and click fx in the formula bar to add a manual step. To get the same result, you need to match the item value to Table1.
You’ll see that it generated the same result. The method works as long as the search criteria doesn’t match multiple rows in the table.
To illustrate, this is a manually created code using the sample table query.
If you press Enter, a single row can’t be returned and you’ll get an error message saying that the key matched multiple rows in the table.
Using optional selection by adding a question mark doesn’t suppress the error because it only affects the not-found behavior. In this case, multiple records were found that meet your criteria.
This is something that you need to be aware of when applying the key match lookup. The user interface will only use key match lookup when a table has a primary key. Otherwise, it will always use the positional index operator.
You can check if a table has a primary key using the Table.Keys function. In this third sample query, you’ll see that the function returns a record.
When you click on the space inside the Record cell, you’ll see a preview of its contents below the preview pane.
You can see that Primary states TRUE for the table. This means that the table has a primary key and contains a list with the key columns for the table.
How To Use Power Query Column Projection
Projection produces a new table that contains a reduced number of columns. The projection operator is a set of square brackets with the field or column selection. To demonstrate, this sample table query will be used.
If you only want to keep the Data column, first add a manual step by clicking the fx in the formula bar. For the syntax, input Data inside the square brackets.
You can select multiple columns by adding a comma after the first column and then creating new square brackets with the added columns. You can see that projection also rearranges the column order depending on the syntax.
Referring to a column that doesn’t exist will cause an error. If you add a new column in the syntax, an error message will appear in the preview pane.
By adding a question mark, any non-existent columns will be added but their values will be set to null.
Power Query is case-sensitive and that’s something to be aware of when using selection or projection.
Conclusion
***** Related Links *****
M Code In Power Query Custom Columns | Power BI
Power Query/M Masterclass #1 – New Course On Enterprise DNA
Extract Values From Records And Lists In Power Query
Selection and projection help you bring out and obtain specific information from a large table or source. They help you single out the data you need. Once you can understand how the two of them operate, you’ll see a great improvement in your data modeling skills.
Melissa
