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….
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.
“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?
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.
A notification pops up on your system: you have a new, red exclamation point, high-priority email. Before you can even read it, your phone rings. It’s your boss. “I just emailed you. We just discovered that column X in our database contains restricted PII that we can’t be storing. The DBAs are deleting the column as we speak. Please be sure it is gone from reports and your system immediately!” You open Microsoft Power BI Desktop, hit refresh, and the column disappears from the dataset because has been removed from the server; then you save the file. But is column really gone, or could there be remnants of its data left on your system?
A prospective client asks you to look at a PBIX file. When you’re done, to minimize liability you don’t want someone else’s data or credentials left sitting on your system, so you delete the file. Are its contents and associated credentials really all gone?
A coworker needs advice on a report, handing it to you on a thumb drive, with the file marked read-only. You open the PBIX file directly from the external drive, give your thoughts then close the file without saving it. Since the drive was external and the file read only, the file was contained exclusively on the flash drive—or was it?
Whether for security purposes, regulatory compliance’s sake, good business practices or liability management, having a sense for whether and, if so, how widely, Power BI spreads report data on your system is a good thing.
In query editor, ever notice that the column data type menu includes four options for numbers: Decimal Number, Currency, Whole Number and Percentage? In this series, we’ve only talked about one numeric type: type number. Are there types we’ve missed?
Behind the scenes, menu item Decimal Number maps to type number, Currency to Currency.Type, Whole Number to Int64.Type and Percentage to Percentage.Type. If you look at the names defined in your Power Query environment, you’ll likely see a host of other “Type” names, including Int8.Type, Int16.Type, Int32.Type, Single.Type and Double.Type. What are all these “Type” names—even more types we have yet to cover?!
Power Query’s type system helps us understand the values we works with, allows us to describe the kinds of data expected by functions we write, offers a means to share documentation (which IntelliSense can display) and provides a mechanism to inform consumers about the structure of the data we’re handing them (which the host environment can use—for example, guiding Power BI to correctly set column types).
To sum up M’s approach to types at a high level:
Every value has a type. A type is itself a value. Types are used to classify values.
A simple statement, but with a lot packed into it and behind it—so much, in fact, that we’ll explore Power Query’s type system in over several posts. Today, we’ll start with the basics, centered around what are known as nullable primitive types. Later, as we get deeper in, hold on to your hat—you might find a major puzzling surprise, where the type system doesn’t work the way you’d expect.
Let’s start delving into the type system by examining the summary statement we read a moment ago.
Your Power Query is skipping merrily along its mashup way. Then, bam! Something bad happens! Uh oh! What do you do when an error raises its ugly head? Or, for that matter, what if code you write detects an anomaly and you want to announce this fact in an informative manner?
Thankfully, M has error handling capabilities, allowing you to both raise and handle runtime errors. We’ll learn how to do both.
Important: If you’re familiar with the idea of an exception from other programming languages, Power Query’s error handling is different in at least one significant respect from what you may be familiar with.
Nope. That’s not a typo in the title. In the Power Query world, there aren’t control structures (plural); there’s just one control structure (singular). We’re about to examine its simplicity. As to the “missing” control structures (which you may be used to from other programming languages), we’ll explore ways of implementing similar functionality the M way.
Last time, we began exploring how Power Query “thinks” about tables, delving more deeply into streaming and query folding. This time, we’ll continue building our understanding of how tables are processed by learning about keys, native query result caching and the data protection layer (firewall). We’ll also explore why native queries may be executed more times than you might expect.
The goal between these two posts is to equip you with a better understanding of the context in which your mashups are executed—knowledge you can use to author more efficient M queries, avoid unexpected data changes during processing and keep the data protection layer (firewall) happy.