This tutorial will discuss about record values inside the Power Query Editor. You’ll learn how to create and use records with different expressions to generate the results you want to achieve.
Creating Record Values
A record value is an ordered sequence of fields. It is made up of a set of fields that consists of a field name and an associated field value. The field name is a text value that uniquely identifies the field within a record and it can be of any primitive or structured type. However, the field order isn’t considered when comparing records.
The record initializer is a set of square brackets. If you input square brackets ([ ]) in the formula bar and press Enter, you’ll create an empty record without any fields and values.
If you look at the Query pane, you’ll see the icon associated with the record values.
To create a record with fields, first create a new query. Right-click on the Query pane, click New Query, and select Blank Query.
Records With Field Values
In the formula bar, enter a field name and assign a value. To add more fields to the record, put a comma between each name and value. You’ll then see the names and values in the Preview pane. Both field values in the record are primitive.
Records can also contain structure types. Create a new record and assign a record value. You’ll then have a nested record value in the Preview pane.
If you click the space beside the nested record value, you’ll see a preview of the contents of that record below the Preview pane.
Now, you can drill down on the record value. Click Record and you’ll see the value in the pane.
You can also do this by right-clicking in the space beside Record and selecting Drill Down.
Other Ways To Create Records
There are other ways to create records. You can use M functions that return records or use the underscore in a custom column to return a record with all the field names and field values for the current row of a table. You can also use the item access operator to return a specific record from a table.
If you open the advanced editor window, you can see that Power Query added the let expression.
The expression isn’t really required so you can remove everything except for the record value. When you click Done, nothing changes and the result is still the same.
If you turn the record into a table and go to the advanced editor window, you’ll see that Power Query automatically brings the let expression back.
Moreover, there are facts about records that you must know. First, every field name in a record needs to be unique and field names are compared using an ordinal comparison. If you input the same field name, you’ll get an error message.
Second, the field name in a record and the look-up operator, which is the field name inside square brackets, are written without the quoted notation.
You can see that there are no quoted notations used in the field names.
However, there is a specific case when quoted notations are required. Double-quote signs are needed when you include a leading or trailing space in the field name. This is because M assumes that a wide space occurring at the start or at the end of an unquoted field name can be ignored.
You can see that the notations are added because of a space after the First value field name.
Referring Other Fields Values
The expression for a field value can also refer to other fields within the record. Here’s an example:
If you look up a field value that doesn’t exist, you’ll get an error.
Adding a question mark to a field access or look-up operator changes the error into a null. This is referred to as performing an optional field selection.
Combining And Equating Record Values
There’s a set of operators that you can use with records: the combination and the equation operators.
Records can be merged with a combination operator using the ampersand (&).
If you combine records and the field names aren’t unique, the field values from the right operand override the fields values from the left.
Aside from combining, you can also compare records.
Remember that the field order isn’t considered when comparing records. So even if you switch the position of the field names, the result will still yield the same.
***** Related Links *****
Extract Values From Records And Lists In Power Query
Delimited Data – Extracting Records Using Power BI
Power Query/M Masterclass #1 – New Course On Enterprise DNA
Conclusion
Record values are one of the structured values in Power Query. They help shape your data report and show information based on the expressions and syntax you use in the Power Query Editor. If you want to make your data report more efficient in generating data, you can use record values.
Melissa
