What do you think of when you see the following C# code:
ExecuteSql($"SELECT * FROM SomeTable WHERE SomeColumn = {someValue}");
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 readingAll 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.)
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 readingErrors 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.
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 readingData 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!
What is the context, or scope, of this dialog’s settings?
Since there’s more than one category of context in play here, there’s more than one answer. The most obvious contextual category is probably data sets. Looking at settings from the data set scope perspective, “.\sql2017express” is scoped to that entire database server while data set “c:\” covers the entire C: drive.
Data set isn’t the only classification of context that applies to this dialog. Another is at the environment level. How broadly do each of the settings in the above dialog apply? If you open another report, will the settings you set here apply there? What if another user opens this report—how many (if any) of these settings will carry over to them?
Data set and environment are both groupings of context at the technical level. However, technical scopes aren’t the only kind of context present. There’s also the context the user assumes when they encounter the dialog, which can very easily be incorrect (not necessarily because of a fault with the user).
The ramifications of getting privacy levels wrong are significant. It’s very important to correctly configure these levels. Understanding the various contexts applicable to each setting one key to getting these settings right. Let’s take a few minutes to articulate these contextual groupings so we have a better understanding of how these settings apply.
While we’re at it, we’ll bump into a data source that never shows up in this list—but which we should be cognizant of when thinking about privacy levels.
Continue reading“Accesses a value by name in an embedded mashup”—documentation’s terse description of this function raises almost as many questions as it answers. Let’s try to clear up at least some of the puzzle around this function. While we’re at it, we’ll uncover some surprises—did you know this function breaks an M language rule and that it exposes something never otherwise seen (as far as I’m aware) by end-user mashups?
Continue reading