Tag Archives: Power Query M

M Mysteries: The Mysterious Type Action—An M-Internal Means to Write Data Modifications to External Systems

• , ,

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.

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 reading →

Dynamic, Lazy Records

• , ,

In 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.

Continue reading →

Equals Is Not Always Equivalent: Power Query Joins vs. SQL Joins

• , , , ,

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 reading →

Equals Is Not Always Equivalent: When Query Folding Does Not Produce Identical Results

• , , , ,

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.

Continue reading →

M Mysteries: SQL -> M (SqlExpression.ToExpression)

• , , ,

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:

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
Continue reading →

Power Query M Primer (Part 21): Identifier Scope & Sections

• , , , ,

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 →

Power Query M Primer (Part 20): Metadata

• , , , ,

Power Query allows information about a value to be attached to a value.

Ordinarily, these descriptive tidbits are invisible. Their presence does not change the mashup engine’s behavior. However, code (yours or others’, such as code in the standard library or in the host application) can intentionally choose to read and set these annotations, known as metadata, using them as a way to communicate extra information relevant to a particular value. These details can simply be for informational or diagnostic purposes; they can also be factored in by expressions as they determine how to behave.

Take parameters as an example. Parameters get special treatment in Microsoft Power BI’s user interface, yet in Power Query itself, they are stored as ordinary values.

This is good. You shouldn’t need to do anything special to consume a parameter; it should just be a value to your code. Yet parameters are special, but where is that “specialness” (like their configuration settings) hiding? From Power Query’s perspective, the answer is—yes, you guessed it—in metadata.

Continue reading →

Adding an Error Details Column

• , , ,

Errors here and there, scattered throughout a table—unfortunately, sometimes this is the reality, especially when source data quality is low. To help monitor issues, you decide you want an error details column. At minimum, it should show the name of each column in the row with an error. This will allow you to, say, filter to all rows where column “Item ID” is in an error state. Taking it a step further, it would be really nice if the error column also contained the error message associated with each erroneous column. With this, you could compute statistics like “how many times did we struggle to convert strings into dates”—information which could help identify the most frequently occurring data quality issues and so guide you to which you might want to consider addressing first.

Screenshot of table with error details column added
Continue reading →

Power Query M Primer (Part 19): Type System IV – Ascription, Conformance and Equality’s “Strange” Behaviors

• , , , ,

At first glance, it seems logical to expect the mashup engine to error if you attempt to ascribe a seemingly incompatible type to a value. Surprisingly, this doesn’t always occur.

During ascription, the engine validates that the type’s base type matches the value’s base type (so, for example, you can’t ascribe type logical onto a date value because the two base types are different). However, for types with children, those child types (list’s item type, record’s field types, table’s column types and function’s argument and return type assertions) have no effect on mashup engine behavior and are not validated for compatibility. What?!

For example, you can ascribe an item type of text onto a list of numbers, and the mashup engine won’t complain. Ascribe a column type of logical onto a column containing dates and M will comply. Similar holds true for records: a field type of duration can be ascribed onto a field containing a function and no error is raised.

Value.ReplaceType({1, 2, 3 }, type { text })
Value.ReplaceType(#table({"Col1"}, {{ #date(2020, 6, 24)}}), type table [Col1 = logical])
Value.ReplaceType([FieldA = () => ...], type [FieldA = duration])

For functions, the newly ascribed type’s argument and return type assertions aren’t validated or enforced; instead, the assertions specified when the function was originally defined continue to be what the engine follows. Take a function argument originally defined as number and ascribe as text to it. Despite the ascription, the mashup engine will expect the argument’s value to be compatible with number, not text, when the function is invoked.

let
  Func = (input as number) as number => input,
  NewType = type function (input as text) as text,
  Ascribed = Value.ReplaceType(Func, NewType)
in
  Ascribed("hi") // errors
  // Ascribed(1) // works fine

These behaviors seem strange—and they aren’t the only strangeness related to Power Query’s types. Comparing type values may also not work the way you expect. Think TypeValueA = TypeValueB will return true if the two types are identical? Maybe. Maybe not!

Fasten your seat belt. We’ll try to define and then clear up a bit of this confusion. It will be a journey! Here we go….

Continue reading →