Tag Archives: Data Transformation

M Language Proposal: Cleaning Up Function Chains with the Pipeline Operator

, , ,

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.

The Problem

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:

  1. Filter the nested orders table to Status = "Completed".
  2. Sort by Total, descending.
  3. Take the top 3 results.
  4. Average their Totals.
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

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

Migration Thoughts: Raw Data vs. Real Data

, ,

A challenge when migrating data to a new system is that the legacy database may only contain a representation of the old system’s data. Arguably, the actual to-be-migrated data is what a user of the old system sees when using its user interface, reports or APIs. This data isn’t always exactly what is present in the database. Whether purposefully or inadvertently (think: bugs), the legacy system may apply transforms, filtering and such to the database’s raw data before displaying or outputting it. In order to create an accurate export for migration, these data manipulations need to be factored in.

From one migration, I remember an email address table along the lines of the following. This table’s design allows a customer to have multiple email addresses, including multiple primary email addresses (as the example below shows).

EmailId  CustomerId  EmailAddress       IsPrimary
-------  ----------  -----------------  ---------
1        15835       joe@example.com    1
2        15835       jsmith@work.com    1
3        15835       jsmith@home.com    0
4        321163      bob@elsewhere.com  1

If this system’s database were the exhaustive, authoritative source for its data, it would seem reasonable to conclude that all of the table’s email addresses should be included in the dataset to be migrated.

However, in the old system’s user interface, a customer could only have two email addresses: one primary and one secondary. Due to a bug, some email address edits resulted in new address rows being created instead of existing rows being updated.  The system, perhaps inadvertently, was smart enough to ignore these old rows, so their presence didn’t cause it problems. Even though these rows were physically present in the database, they weren’t a part of the system’s authoritative data because from the user’s perspective they did not exist; instead, they were data corruptions which needed to be ignored during the export extraction process.

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  

Speaking of things not validated during ascription: Don’t forget what we learned last time about how child component names (record field names, table column names and function parameter names) are not checked, even though bad things can happen when the names on the new type don’t align with the names on the value. M will allow you to, say, ascribe a table type whose first column is named Amount onto a table whose first column is named Amt, even though code that later works with that table may misbehave because of the name mismatch.

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

Editing Report/Spreadsheet Mashups in VSCode

, , , , ,

Data Mashup Cmdlets import and export Power Query from most Microsoft Power BI and Microsoft Excel files; however, they don’t include a GUI editor. Microsoft’s new Power Query / M Language extension for Visual Studio Code provides a graphical editing experience for mashups, with syntax highlighting, validation, and IntelliSense, but doesn’t read or write PBIX, PBIT or XLSX files.

What if the two could be married? Imagine…if this were possible, you could use VSCode to externally edit the Power Query code saved in your Power BI and Excel files….

Now, stop imagining. Try Edit-DataMashup!

Screenshot showing VSCode being used to edit Power Query from a report file
Continue reading