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

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

Dataverse Web API Tip #12: Lookup Property Annotations

,

In Dataverse Web API responses, you’ll sometimes see properties named along the lines of _{navigation property name}_value (like _owerid_value). These are lookup properties: system-computed, read-only values that appear on the many side of a many-to-one relationship and hold the primary key of the related record.

Often, you won’t need to touch these lookups, thanks to navigation properties—which are the preferred way to work with relationships. However, the annotations on lookup properties can come in handy even in cases where the lookup values themselves aren’t interesting—particularly when a relationship can point to more than one table (i.e. can point to more than one type of entity, like column types customer and owner).

Suppose your orders table has a SoldTo column of type customer. This column type can hold a reference to either an account entity or a contact entity, allowing an order to be sold to either a company (account) or an individual (contact). Logically, SoldTo is one relationship; but technically, it has two navigation properties, one for the connection to accounts and the other to contacts. For a given order, how do you know what type of entity this relationship points to and so which of the two navigation properties to use?

Continue reading

Dataverse Web API Tip #11: Extra Error Details

,

By default, a Dataverse Web API error message contains two fields, code and message. However, some errors have extra details available just for the asking.

Below, an OData annotations preference request is used to ask for additional error details. The request’s syntax is simple: include odata.include-annotations="{comma-separated list of requested annotations}" in the Prefer header.

GET {{webApiUrl}}SomethingThatCausesAnError
Prefer: odata.include-annotations="*" 
(include the "always include" headers)

(Above, a wildcard is used to request all annotations. It may be best to only use a wildcard like this only temporarily, during development. Once the specific annotations of interest have been identified, change the request to be more specific. This avoids the cost of unnecessary extra data being assembled and sent across the wire.)

Response:

{
  "error": {
    "code": "0x80048d08",
    "message": "Example Error Message.",
    "@Microsoft.PowerApps.CDS.ErrorDetails.OperationStatus": "0", 
    "@Microsoft.PowerApps.CDS.ErrorDetails.SubErrorCode": "10001",
    "@Microsoft.PowerApps.CDS.HelpLink": "http://go.microsoft.com/fwlink/?LinkID=398563&error=Microsoft.Crm.CrmException%3a80048d08&client=platform",
    "@Microsoft.PowerApps.CDS.TraceText": "\r\n[Plugin: Plugin.Plugin]\r\n[03ded7cb-60a7-eb11-c2ac-010d4d3c26cd: My Plugin Step Name]\r\n\r\n\t\r\n\tGetting ready to do something.\r\n\t\r\n",,
    "@Microsoft.PowerApps.CDS.InnerError.Message": "Example Error Message."
  }
}

Be aware that error annotations are not necessarily intended for end-user or public consumption. The component raising the error may expect that these values will only be used for development and technical troubleshooting purposes and so may include content that is not end-user friendly or even that contains sensitive data. It is a good practice not to expose these extra error details publicly except when you are sure that the component raising the error will only return “safe to display” values.

Bonus Tip: If you’re writing a custom plugin, InvalidPluginExecutionException‘s first three arguments correspond to the OperationStatus, SubErrorCode and InnerError.Message annotations. Additionally, annotation TraceText will include any trace text written by the plugin, a fact that can be leveraged to include a full stack trace, if desired. Unfortunately, HelpLink is auto-set by Microsoft; I’m not aware of a way to set or override it.

Note

Don’t forget to include the “always include” headers with every request sent to the Web API.

Reference/Further Reading

Parse errors from the response (in Compose HTTP requests and handle errors)

Dataverse Web API Tip #10: Formatted Values

,

Ever notice that the values a Dataverse Web API call returns aren’t always identical to what a Microsoft Power Apps/Dynamics user would see when viewing the same data in the UI?

For example:

  • In the UI, a Boolean is displayed using configurable “friendly” text like “Allow” or “Disallow”, “Yes” or “No”, “OK” or “Cancel”, etc.—but Web API responses always return Bool values as true or false.
  • Similarly, choices are returned as numeric codes by the Web API while the UI shows their corresponding textual value.
  • For lookup columns, the Web API returns the GUID of the related record while the UI shows its display name.
  • Dates and numbers are displayed nicely formatted in the UI but returned by the API in raw technical form.

Did you know that, thanks to an OData concept known as annotations, you can request that these display values (a.k.a. formatted values) accompany Web API results?

Continue reading

Dataverse Web API Tip #9: Deltas (Tracking Changes)

,

Let’s say you want to find out about changes made to a table in Dataverse (Microsoft Dynamics, Microsoft Power Apps, etc.), perhaps because you’d like to synchronize those changes to an external system.

A primitive way of pulling this off is to query the table of interest, selecting only those records whose modifiedon value is greater than or equal to the last time you checked for changes:

GET {{webApiUrl}}contacts?$filter=modifiedon ge 2021-04-28T15:19:30Z
(include the "always include" headers)

This approach has several potential downsides, including:

  • Deletes aren’t included—If a record was removed from the specified table, the results to a “GET filtered on modifiedon” query won’t return anything indicating the deletion.
  • Missed modifications—Normally, modifiedon is automatically set to the moment the record was last changed in Dataverse; however, this behavior can be overridden. Since modifiedon isn’t an absolute indicator of whether or when changes occurred, filtering by it isn’t guaranteed to find all changes actually made in Dataverse after a point in time. (Think of a data import or an inbound data sync scenario: inserts/updates might be backdated so modifiedon matches when the record was last changed in the external system, which could be hours or days prior to when the data is brought into Dataverse.)

Dataverse’s Web API provides a better option for finding changes—an option built specifically for this purpose. When fetching entities from a table, if the table has change tracking enabled, you can ask Dataverse to provide you with a delta link. Later, you can use that link to fetch the delta of changes that occurred since your initial GET.

Continue reading