The field-access expression is used to select a value from a record or to “project” a record or table to one with fewer fields or columns, respectfully. In this Power Query M Language tutorial, we are going to delve on Projections, its meaning, and how to use it. You can watch the full video of this tutorial at the bottom of this blog.
Power Query M Language Tutorial: BaseRecord
To start out our tutorial in the Power Query M/Language, let’s go to the Power Query editor, prepare a query, and look at our BaseRecord. As shown in the image below, the BaseRecord contains two fields, A and B, and two values, 1 and 2.
For projection, you can use the field-access expression, a set of square brackets. Inside those square brackets, you can refer to the field to select from the base record in another set of square brackets.
Thus, if we want to project field B, we append that with a set of square brackets for projection and a set of square brackets to refer to the field name. This syntax performs the required projection.
Power Query M Language Tutorial: Record.SelectFields
So what happens when you call a field name that doesn’t exist inside the record? In that case, it returns an error, as shown in the image below, after projecting field C.
You might conclude that this behavior is equal to selecting fields from a record using a standard library function, and you’re right.
So instead of projection, were now using the standard library function Record.SelectFields. If you call a field that doesn’t exist inside the record, you’ll get the same error, as shown in the image below.
Multiple Fields
You can select multiple fields by separating the field names with commas. And for performing optional projection, all you have to do is add a question mark at the end.
In the image above, we have one set of square brackets with the field names we want to project from that record. Calling a field name that doesn’t exist when appending that question mark at the end returns a null, resolving the error.
The same holds for a standard library function that allows a missing field type parameter, as shown in the image below.
Again we’re looking at the Record.SelectFields function, and this allows for that missing field type parameter. You can resolve the error by adding a null instead.
Power Query M Language Tutorial: BaseTable
You can expect the same behavior when dealing with tables instead of records. The image below shows our base table.
We can use the field access expression instead of square brackets to project fields from this table and refer to the columns to select from the table in another set of square brackets.
Thus, if we project Column B inside a set of square brackets, we can refer to the column name in another set of square brackets. This action is required projection.
Calling a column name that doesn’t exist returns an error. You can expect the same for the standard library function, which, in this case, is the Table.SelectColumns function.
You can select multiple columns by separating the column names with commas. And for performing optional projection, add the question mark.
You can resolve it in the same manner for standard library functions that allow the missing field type parameter.
***** Related Links *****
Power Query Editor Tutorial: Extract Record Field Values From Lists
What Is Power Query & M Language: A Detailed Overview
Power BI Parameters Via Query Edit
Conclusion
And that concludes our Power Query M Language tutorial. Now you know what projection in the Power Query/M Language is, it’s meaning, and how to use it properly. Using the field access expression, you can make projections with various functions, including BaseRecord, Record.SelectFields, and BaseTable.
Projection is an easy-to-use shorthand with similar behavior to the standard record or table library functions that allow for that optional missing field type parameter. Mix Projection and standard library functions when coding for the best results.
[youtube https://www.youtube.com/watch?v=MlBHesx10kU&t=82s&w=784&h=441]
