Tag Archives: Data Transformation

The Oldest Is Not Always the Minimum: Power Query Date-Based Values May Not Compare the Way You’d Expect

, , ,

The task seemed simple enough: Determine the earliest date between two columns, so that this value could be used when generating a date dimension.

A little M code that took the minimum of each column, then the minimum of those minimums seemed to meet the need until—ouch—I discovered it wasn’t finding the earliest date. Why?

let
  OrderCreatedMin = List.Min(SomeTable[OrderCreated]),
  TransactionReceivedMin = List.Min(SomeTable[TransactionReceived]),
  OverallMin = Date.From(List.Min({ OrderCreatedMin, TransactionReceivedMin }))
in
  OverallMin

What do you think? Do you see any problems with the above?

Continue reading

The Elusive, Uncatchable Error?

, , , ,

The error seems to escape catching. When the expression is evaluated, Query Editor displays the error. Try loading the query’s output into Microsoft Power BI or Excel and the operation dies with an error. Clearly, there’s an error—but if you wrap the expression with a try, the error isn’t caught! To the contrary, the record output by try reports HasError = false, even though if you access that record’s Value field, Query Editor again shows the error.

What’s going on?! Have you discovered an uncatchable error? Is this a Power Query bug?

Continue reading

Zero Rows Can Bite (part 1): The Mysterious Missing Column

, , , , ,

Your Power Query expression is happily skipping along, fetching data from a web API. Happily, that is, until one day its refreshes mysteriously die with the complaint that “column 'Column1' of the table wasn't found“. You haven’t changed any M code. You’ve verified that no schema changes occurred on the external web service. How, then, could a column come up missing?

Power Query error message - Expression.Error: The column 'Column1' of the table wasn't found. Details: Column1

Or, maybe it’s not a missing column, but rather your fetch data code starts outputting an unexpected table row with a null value in each column. You manually try the web API using a tool like Postman or Insomnia and don’t find any all-null objects in the API’s response. Where is this all-null table row coming from?

Both of these unexpected occurrences potentially stem from the same underlying cause. As common M code patterns tend to not properly handle this situation, it is possible (even probable!) that M code you use may leave you susceptible to being bitten by one or the other of these bugs.

Continue reading

Combining Query String Parameters—a.ka. Inclusive Record Field Combining

, ,

Your expression is building a query string for use in a Web.Contents call. Different parts of your code each separately compute parameters that should be included in the final query string. These are provided as “query fragment” records, which all need to be combined into a single, consolidated record that’s then passed to Web.Contents:

ProductCodeFragment = GetProductCodeQueryFragment(), // might return [productId = 123]
LimitFragment = GetLimitQueryFragment(), // might return [limit = 100]
FinalQueryParams = (single, consolidated record containing query parameters from all fragment records, such as ProductCodeFragment and LimitFragment),
Result = Web.Contents("some-url", [Query = FinalQueryParams]

Using Power Query’s built-in functionality, combining the fragment records into a consolidated record is easy, so long as the fragment records each have different fields. If they do, a record merge can be performed using the combination operator (&) or the records can be combined using Record.Combine, both of which produce the same output.

[productId = 123] & [limit = 100] // outputs [productId = 123, limit = 100]]
Record.Combine({[productId = 123], [limit = 100]}) // outputs [productId = 123, limit = 100]

The challenge comes if multiple records contain the same fieldsay, one fragment record contains [productId = 123] while another contains [productId = 456]. Record.Combine and & are exclusive in how they compute the field values they output. When the same field name is present in multiple input records (e.g. both input records contain field productId), the value for that field that’s output will be the value from the last/right-most input record (in this case, productId = 456). The other input record value(s) for that field will be ignored (so in this case, productId = 123 is ignored).

// notice that productId 123 is *not* included in the outputs
[productId = 123] & [productId = 456] // outputs [productId = 456]
Record.Combine({[productId = 123], [productId = 456]}) // outputs [productId = 456]
Continue reading

Privacy Level Descriptions Revised

, , ,

Last week, Microsoft Power BI’s privacy level descriptions were revised.

Previously, Microsoft documentation implied that the data protection firewall imposed unilateral, bidirectional isolation between data sources having incompatible privacy levels. In actuality, the direction of data flow is factored in when determining whether data folding can take place between sources. Privacy level documentation now reflects this.

Continue reading

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