Several data connectors allow you to control the names assigned to relationship columns. Defining a custom relationship column naming format is easy. Ensuring that the generated names do not conflict with existing column names is trickier. Let’s look at how to do both.
Continue readingHave you ever stopped to think about relationship columns: how they work, when they’re automatically added, and in particular how they’re named?
On that last point: Did you know there is a latent danger where seemingly unrelated changes can break existing M code?
In a nutshell, a relationship column is an automatically added nested join between the table you’re working with and a related table. In the relationship column, for each row, there’s a nested table containing the associated rows from the related table. Thanks to M’s laziness, if the nested join isn’t used, fetching the related table’s row data will be skipped—so the presence of a relationship column whose values are unneeded does not incur an appreciable cost.
Continue readingSometimes, 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:
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!).
Continue readingPower 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.
What?!
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!
Continue readingIn the below record, when is Amount‘s value calculated?
[
FieldA = …,
Amount = ExpensiveToCompute("some", "arguments"),
…
]
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?
let
SomeExistingRecord = [
FieldA = …,
…
]
in
Record.AddField(
SomeExistingRecord,
"Amount",
ExpensiveToCompute("some", "arguments")
)
No! Whoa! Amount‘s laziness went good bye! Above, ExpensiveToCompute("some", "arguments") is executed whether or not Amount‘s value is ever needed.
Take the following M expression:
Table.Join(A, "ID", B, "ID", JoinKind.Left)
Does it behave like the below SQL (which is how a join between two tables on column ID would typically be coded in the database world)?
FROM A
LEFT JOIN B ON A.ID = B.ID
Perhaps surprisingly, no—at least, not when the simple, innocent null is involved.
Continue readingQuery 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.
Continue readingDid 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:
let
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)
in
TranslatedToM
The same identifier name (think: variable name, field name, etc.) can be defined more than once in the same set of Power Query expressions. If you reference an identifier name that’s been defined in multiple places, which of those definitions will your reference point to?
In this post, let’s learn how M sorts this out. We’ll also explore sections—the usually hidden “frame” at the core of organizing the different expressions that make up a Power Query program.
Let’s get to it…and have fun while we’re at it!
Continue reading