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

Privacy Levels: Context Is Key

, , ,

What is the context, or scope, of this dialog’s settings?

Microsoft Excel's Privacy Levels dialog

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

Shedding Light on the Mysterious Embedded.Value

, , ,

“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

Power Query M Primer (Part 18): Type System III – Custom Types

, , , ,

After gaining a solid grasp on type system basics and exploring type facets, it’s now time to learn about complex types (also known as custom types or derived types). Thankfully, constructing these types isn’t very complex. Don’t worry, though, we’ll make up for that simplicity when we talk about how M handles them!

Let’s dive right in. To keep things simple, the focus for this post is syntax and conformance rules. We’ll save most of the discussion about how M works with these types for the next post in this series.

“Conformance—that sounds complicated!” you might be thinking. Actually, no. Just the name makes it sound that way. Saying that a value conforms to a type means that the value can be described by the given type, or to put it in other words, the value is compatible with the type. So, the numeric value 1 conforms to types number, nullable number, anynonnull and any because each of those types can be used to describe that value. Conformance rules, simply put, are the rules used to determine whether a value conforms to—is described by—a type.

Now, on to the custom types!

Continue reading

Searching Power Query in Multiple Files

, , , ,

You’re looking for all mashups that touch column “LegacyAmount”…or maybe the task is to find all files whose Power Query uses variable “CalculatedDiscount”…or perhaps you’re trying to locate any M code referencing server names that match the regular expression pattern “SomeServer(0[135]|[2-6]\d)“. Ultimately, you need to search the Power Query mashups in an entire directory (or directory tree) of Microsoft Excel and Microsoft Power BI files. You could open files one at a time and check each by hand…ouch! Is there an easier way to quickly search Power Query across multiple files?

Try Search-DataMashup!

Output of Search-DataMashup '.\' '*Source*'
Continue reading

Comparing M Code Between Files

, , ,

You’d like to compare the Power Query mashups contained in two Microsoft Excel and/or Microsoft Power BI files. Opening Query Editor for each and hand-comparing the queries one at a time quickly becomes tediously painful with all but the smallest of query sets. An easy way to find the differences between mashups in the files would be most helpful.

How about something as simple as:

Compare-DataMashup SomeFile.xlsx OtherFile.pbix
Screenshot showing the comparison of a query between two files:
======
Query: Employee
======
--- File1
+++ File2
@@ -1,4 +1,6 @@
 let
-    Source = #table(null, {{"Tom", 15, true}})
+    Source = #table(null, {{"Tom", 15, true}}),
+    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Currency.Type}, {"Column3", type logical}}),
+    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Name"}, {"Column2", "Rate"}, {"Column3", "Active"}})
 in
-    Source
+    #"Renamed Columns"
Continue reading

The Data Mashup Binary Stream: How Power Queries Are Stored

, , ,

You create a Power Query and save it. Its written somewhere inside your Microsoft Excel or Microsoft Power BI file—but where and in what format?

First, though, why would understanding the file format used to store Power Queries be of interest? Maybe you’re just curious and like to know how things work. 🙂 Maybe comparing query text between files or hooking up some kind of M version control is important. Maybe you’re looking for automation options, so you can do something like mass update the M code in a folder full of files quickly. While it’s unnecessary to understand the underlying file format to simply use Power Query, there are times and places where knowing about that format can come in handy.

In this post, we’ll lay a foundation on how to get to those saved mashups. We’ll overview the file format used to persist mashups, describing the various pieces of supporting information saved along with them, and point you to where you can learn about the fine, byte-level details of the format (in case you want to write your own parsing logic that reads and even modifies saved M queries!).

Continue reading