Power Query Record as Dictionary: Part 2

This is the second post in the “Power Query Think Tank” series, initially written by Mikhail Muzykin for the Telegram channel “For those from the tank” (https://t.me/pbi_pq_from_tank), and it also was slightly rewritten and enriched for better reading experience.

Record.FieldOrDefault, Record.FromTable – Optimising “Record as Dictionary” trick in Power Query

In the previous blog post (Power Query Record as Dictionary: Part 1) we demonstrated the way to lookup values from the other table using the record as the dictionary.

A quick recap of what we did there:

We used the record data type (which is, as it stated in the Power Query Language Specification, “is an ordered sequence of fields”) to simulate the Dictionary behaviour and quickly retrieve the value by its name (key).

Additionally, “A field consists of a field name, which is a text value that uniquely identifies the field within the record, and a field value. The field value can be any kind of value”. This allowed us to store table row values in the record and refer to them by the key column value.

But what if the dictionary (lookup record) contains not all the keys required?

We know we can use the next syntax to get the null value if we refer to the field which is not exist in the record:

And we can use the coalesce syntax to replace that null to something meaningful:

If you don’t sure what is the last syntax purpose – it is another form of writing “if the value to the left of ?? is null, then give me the value which is to the right of ?? marks”, or, in the other words, “give me the value, but if it is equal to null, give me another value”.

Unfortunately, we cannot use these syntaxes with the Record.Field function. First of all, these syntaxes are not dynamic (we cannot pass the field name there, retrieving it from the other values), and if we try to get non-existing field with it we will get an error:

But there is the another (very useful!) function to rescue – Record.FieldOrDefault.

It has third optional argument – default value to be returned if the desired field name wasn’t found in the record:

• Using this function without the third argument (as in Record.Field) we’ll get the null value – as the equivalent of []? syntax.
• Using it with the third argument (default value) we’ll get the default value – the equivalent of  []? ?? value syntax.

Let us apply this function to the code from the previous post:

We want to add “Salary” and “Bonus” columns from LookupTable to this table, based on the relation between the “Occupation” column in TargetTable and the “Position” column in LookupTable.

I removed one of the rows in the LookupTable, so now it has no related values for the “Medic” position:

In the previous post in the series we used different approach to create the Dict record (with the help of List.Accumulate function). For relatively big dictionaries this function could slow down the performance if our lookup table is big enough (like, tens of thousands rows). Here we used another helpful function: Record.FromTable. It requires the table of two (and only two) columns – “Name” and “Value” – as its single argument. “Name” should be a key value to lookup, and “Value” should contain (in our case) the list of the values to return.

There are many ways to create such table from the LookupTable, but we selected a little bit tricky approach there:

1. convert the LookupTable to the list of rows (where each row is the list of its values),
2. a small list generator function which transforms each “row” in the list of two elements:
   1. first value in the row: row{0} and
   1. all other values from that row except the first: List.Skip(row)
3. convert this list of values with the help of the list generator function into a table of two columns with Table.FromList function.

Not usual, but in the most cases it is really performant way to generate tables (Table.FromList is one of the fastest, if not the faster, table generators in M language).

Finally, we used Record.FromTable to get the desired result.

Also take a closer look on the Generator step: we used the Record.FieldOrDefault function, and the third argument we used not the null value, but an empty list: {}.

Why we did it? This RowValues argument in the Generator function is the list of the current row values, and our goal is to combine this list with the values from the Dict record (which are also must be of list type). If the default value, returned by Record.FieldOrDefault, will be null, we will get an error “We cannot apply operator & to types List and Null”.

Of course, if we want to get not just an empty values in case of absent dictionary key, we can use the list of values as the third argument of the Record.FieldOrDefault: {0,0}, or {“not found”, “not applicable”} – anything you want/need. Just remember that in this case we need to get the list, not just a text or number value.

That’s all for now: we found how to create dictionaries from big enough tables, and how to manage missed keys in these dictionaries.

Time to try it, huh?