When creating a related record in Microsoft Dynamics/Power Apps, ever notice how the “quick create” form for the new entity may be displayed with some fields prepopulated?
For example, on an account, go to related contacts and click “New Contact.” By default, the create contact form that pops up shows account, phone and address pre-filled in with data from the account. The user can leave these values untouched or edit them, at their preference, before clicking “Save” to actually create the new entity record.
If you’re working programmatically—say using the Dataverse Web API—and would like to prepopulate fields like this from another entity, how would you go about pulling this off?
You could maintain your own mappings, like “When creating a contact for an account, set fields x, y and z on the new contact to the corresponding values from the related account.” While technically valid, this requires you to own the responsibility of configuring and storing these mappings. What if, instead, you could simply use the exact same template that the Dynamics/Power Apps UI uses?
Sometimes, a chain of M function calls reads as a dense blob of code, yet refactoring to the clearer structure of a let statement is an overkill. Let’s look at an alternative, a new operator to consider for inclusion in the M language.
You’d like to add a column to your customers table that holds the average amount of the given customer’s three largest completed orders. The needed data is already available in the table, thanks to a nested orders table. All that’s needed is for you to define logic that uses this data to calculate the desired average.
To pull this off, your new column’s logic needs to:
Filter the nested orders table to Status = "Completed".
As we learned last time, normally, M code is evaluated in a global identifier resolution scope consisting of all shared members + the standard library. Also, normally, we can’t inject additional identifiers into this global environment. Normally isn’t always. Today, we learn about the exception: where both of these normalities do not apply.
That’s not all: Did you know that M has a mechanism for remembering how to access variables that later go out of scope? Closures open up powerful options, particularly when generating functions…and even enable building an object-like programmatic construct that maintains internal private state and is interacted with through a public interface (kind-of, sort-of somewhat like an object from object-oriented programming!).
Power Query is great for reading, combining and computing data, but it’s not meant for writing data modifications—like inserts, updates or deletes—back to the source. Correct?
Yes and no.
If you are a non-internal user, then yes, Power Query is intended to be read only: it does not expose functionality meant for inserting, updating or deleting data on remote systems. But this doesn’t mean Power Query lacks this capability: to the contrary, it has hidden, internal support for performing data modifications!
Only if needed. Why? Power Query’s record field values are lazily evaluated. A field’s expression is only evaluated if its value is needed. If it’s not, the cost of computing the value isn’t expended. Nice!
Let’s say, instead, you’d like to dynamically add Amount to an existing record. Is something like the following effectively equivalent to the above?
SomeExistingRecord = [
FieldA = …,
No! Whoa! Amount‘s laziness went good bye! Above, ExpensiveToCompute("some", "arguments") is executed whether or notAmount‘s value is ever needed.
Query folding is supposed to be transparent, as far as results go. Whether or not a Power Query expression is folded should have no effect on the data returned. You should receive back identical results either way. At least, that’s the theory.
Unfortunately, this is not always the case!
The fact that query folding sometimes changes the results that are returned can bite unexpectantly. You have an M expression that produces exactly what you want. Then you make what should be an innocuous edit, but behind the scenes the change affects whether or how the query is folded. The results you now receive back are no longer what you expect, and puzzlingly the divergence seems to have no obvious relation to your edit. Or, maybe you didn’t edit anything at all: instead, a Power Query update changed the foldability of your query without you touching it. You made no changes, yet the data returned is now different.
Did you know that Power Query can convert (some) SQL into M code?!
No, I didn’t mean that the other way around. It’s true that Power Query can query fold M into SQL (M -> SQL), but that’s not what I’m referring to here. M also has some capability to take a SQL statement and translate it to Power Query code (SQL -> M).
(Disclaimer: As fun as SQL -> M may look, this technique is best not relied upon in production, for reasons that will be explained. However, exploring this functionality—especially the possible why behind its presence—may have educational benefits.)
Without further ado, let’s convert SQL to M:
ReferenceTables = [Department = Department, Company = Company],
SqlQuery = "
SELECT d.dept, c.name
FROM Department d
LEFT JOIN Company c ON d.id = c.id
TranslatedToM = SqlExpression.ToExpression(SqlQuery, ReferenceTables)