Dataverse Web API Tip #5: Direct Single-Property Access

If all you’re interested in is working with a single column value from a single table row, you can reference the property of interest directly by name (no need to append a query string with a $select in it, like ?$select=firstname):

GET {{webapiurl}}contacts(b8d3f910-1896-eb11-b1ac-000d3a3ac80d)/firstname
(include the "always include" headers)

If the property of interest is not null, you’ll receive a JSON object in response along the lines of the following, with the requested value in value:

  "@odata.context": "{{webApiUrl}}$metadata#contacts(b8d3f910-1896-eb11-b1ac-000d3a3ac80d)/firstname",
  "value": "Yvonne"

This response can be streamlined. If all you want is a rendering of the specified property’s value instead of a formal JSON object, you can instruct the API to return just that appending /$value to the URL:

GET {{webapiUrl}}contacts(b8d3f910-1896-eb11-b1ac-000d3a3ac80d)/firstname/$value
(include the "always include" headers)

Continue reading

Dataverse Web API Tip #4: Alternate Identities (Alternate Keys)

, ,

Out of the box with Microsoft Dataverse, a single table row may be referenced using pluralized-table-name(primary-key) syntax, like:

GET {{webApiUrl}}stores(78c91f30-0e86-4716-8718-176c14174cab)
(include the "always include" headers)

However, there are times where you know that rows in a table may also be uniquely identified using another column’s value (or maybe even a unique set of values across several columns). For example, imagine a table describing your company’s stores. In the technical domain, Dataverse identifies each row using an auto-assigned GUID primary key—but to the business, each store is identified by a numerical store number stored in a StoreId column, like StoreId = 1 or StoreId = 2.

You can formally recognize the presence of an alternate unique identifier by declaring the relevant column (or columns) as an alternate key.

Defining an alternate key brings with it several advantages, including:

  • It’s uniqueness will be enforced. The system will not allow multiple rows to have the same key value (so, in our example, if StoreId is declared as an alternate key, two records with StoreId = 1 won’t be allowed).
  • The ability to reference rows using the alternate key (more on this below).
  • Optimized lookup performance.
Continue reading

Dataverse Web API Tip #3: Upsert


In database nomenclature, an upsert (update-or-insert) is an operation that results in the specified record being updated if it exists; and, if not, a new record being inserted.

Microsoft Dataverse’s Web API PATCH operation preforms upserts by default. It’s important to be aware of what this means and, when desired, how to change it, so you’re not inadvertently upset by an unexpected upsert.

What does the below request do?

PATCH {{webApiUrl}}contacts(00000000-0000-0000-0000-000000000001)
(include the "always include" headers)

  "firstname": "Bob",
  "lastname": "Brown"

If you said that it updates the contact with the primary key of 00000000-0000-0000-0000-000000000001, setting its first and last names, you are correct if a contact with that ID exists. However, if it doesn’t—thanks to Dataverse’s identity insert behavior—the request will result in a new record being created with the given primary key, first and last name values. What you expected to be an update turned into an insert.

Is upserting a good behavior? It depends.

Continue reading

Dataverse Web API Tip #2: Identity Insert


By default, Microsoft Dataverse auto-generates primary key values when data is inserted. Often, this is fine: these GUID key values are not necessarily of interest to users—in fact, users may not even be aware they exist!

However, when external integrations are involved, there are times where it may be advantageous to explicitly set the primary key. That is, instead of Dataverse generating the key’s value for a particular record, you specify what that value should be when you insert the record. In the database world, bypassing an identity column’s auto-generation behavior like this is sometimes known as “identity insert,” as you are inserting the record’s identity value along with the other data that defines it.

For example, suppose you are inserting a record into Dataverse that corresponds with an entity in another system. To link between the two, you could add an “external ID” column to the table in Dataverse and populate it with the corresponding ID from the external system—or if that external ID is a GUID (or can be transformed into a GUID), you could skip creating an extra column and instead simply set the Dataverse record’s primary key value to match the external system’s GUID key!

Continue reading

Dataverse Web API Tip #1: The “Always Include” Headers


All HTTP requests to Microsoft’s Dataverse Web API should include, at minimum, the following four headers:

Accept: application/json 
OData-MaxVersion: 4.0 
OData-Version: 4.0
If-None-Match: null

The last one’s value can be confusing. By default, it should be set to a literal string with the text value of “null”, not a null value (e.g. headers.Add("If-None-Match", "null"), not headers.Add("If-None-Match", null)). (There are some special cases where you’ll deviate from this default value, but in all cases the header itself should always be sent.)

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.

  Func = (input as number) as number => input,
  NewType = type function (input as text) as text,
  Ascribed = Value.ReplaceType(Func, NewType)
  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