Combining Query String Parameters—a.ka. Inclusive Record Field Combining

• , ,

Your expression is building a query string for use in a Web.Contents call. Different parts of your code each separately compute parameters that should be included in the final query string. These are provided as “query fragment” records, which all need to be combined into a single, consolidated record that’s then passed to Web.Contents:

ProductCodeFragment = GetProductCodeQueryFragment(), // might return [productId = 123]
LimitFragment = GetLimitQueryFragment(), // might return [limit = 100]
FinalQueryParams = (single, consolidated record containing query parameters from all fragment records, such as ProductCodeFragment and LimitFragment),
Result = Web.Contents("some-url", [Query = FinalQueryParams]

Using Power Query’s built-in functionality, combining the fragment records into a consolidated record is easy, so long as the fragment records each have different fields. If they do, a record merge can be performed using the combination operator (&) or the records can be combined using Record.Combine, both of which produce the same output.

[productId = 123] & [limit = 100] // outputs [productId = 123, limit = 100]]
Record.Combine({[productId = 123], [limit = 100]}) // outputs [productId = 123, limit = 100]

The challenge comes if multiple records contain the same field—say, one fragment record contains [productId = 123] while another contains [productId = 456]. Record.Combine and & are exclusive in how they compute the field values they output. When the same field name is present in multiple input records (e.g. both input records contain field productId), the value for that field that’s output will be the value from the last/right-most input record (in this case, productId = 456). The other input record value(s) for that field will be ignored (so in this case, productId = 123 is ignored).

// notice that productId 123 is *not* included in the outputs
[productId = 123] & [productId = 456] // outputs [productId = 456]
Record.Combine({[productId = 123], [productId = 456]}) // outputs [productId = 456]
Continue reading →

Power Query M Primer (Part 24): Query Folding II

• , , , , ,

Last time, we began a deep dive into the inner workings of query folding. We examined how you can implement foldability using Table.View, ending with a firm grasp on why answering the question “what functions fold?” isn’t simple, but rather depends on the Power Query version, the data connector and possibly even some combination of the particular operation’s parameters and the data set being accessed.

But this isn’t the only “moral of the lesson” to be gleaned from our query folding deep dive….

As part of processing an expression, do you think Power Query communicates just once with each external source? For that matter, does Power Query process your expression verbatim and exactly one time? On query folding: Is it guaranteed to be transparent, producing identical results regardless of whether an expression is processed locally by Power Query, partly folded to source or fully folded to source?

These questions, and their answers, will lead us to more morals to be learned from this continuation of our lesson on query folding!

Continue reading →

Custom Data Connectors and You

• , ,

Curious about what a Power Query custom data connector can do for you—or for your customers?

Maybe you offer a product or service that exposes data. Would a custom connector help users more easily access your app’s data? Perhaps it’s not your own external offering, but rather a tool from someone else that your teams uses internally. You’re wondering if a custom connector could reduce the pain currently involved when interfacing with it. Or, maybe you’re just curious: You’d like to understand what a custom connector can offer, so that, down the road, you’ll be better positioned to evaluate whether one of these connectors would be an appropriate component to bring into the picture.

So what exactly does a custom connector offer you?

The ability to make interacting with your data source more efficient, convenient and faster to set up from Power Query in Microsoft Power BI.

Continue reading →

Recursive Polymorphic Deserialization with System.Text.Json

• ,

Goal: Deserialize a nested JSON structure, where the objects instantiated are not instances of the given abstract base type but rather are of the appropriate derived types.

In other words, if the specified destination type is AbstractBaseTpe, which should be returned is not an instance of that class but rather an instance of ChildTypeA or ChildTypeB or GrandchildType1, etc., as appropriate. This polymorphic deserialization behavior should hold true at the root level (that is, for the type directly returned by JsonSerializer.Deserialize<AbstractBaseType>(…)), as well as recursively for nested properties (that is, anytime the destination property is of type AbstractBaseType, the object instantiated for the property should be of the appropriate derived type, like ChildTypeA, etc.).

Hopefully, one day support for something along these lines will be built into System.Text.Json. However, for now, when we need it, we have craft our own solution (or borrow one from someone else).

In my case, the other day I needed a simple solution for polymorphic deserialization. Performance wasn’t critical. Just something simple that worked.

Continue reading →

Power Query M Primer (Part 23): Query Folding I

• , , , , ,

Query folding, by now, is a concept you’re likely already familiar with. In short, Power Query’s query folding takes an M expression and translates part or all of it into the data source’s native query/request language (for example, translating M into T-SQL or into an OData request), then asks the source to execute that native request. You wrote M, but query folding offloaded some portion of your expression’s processing to the external data source by rewriting M logic into the source’s native language.

Query folding is a key concept in the Power Query ecosystem. It brings the potential for extremely significant performance benefits. Thanks to mechanisms like indexes and in-memory paging of data, the data source often can find the data of interest much more efficiently than when raw rows are streamed from the source to Power Query for local filtering. The source may also be able to perform other processing, such as aggregation and joins, again with much better performance than Power Query can locally. In addition to these benefits, offloading execution to the source usually reduces the quantity of data that needs to be sent across the wire to Power Query. For reasons such as these, query folding as much as possible is usually more efficient (and so quite desirable) in contrast to having Power Query internally handle all the processing itself.

Perhaps surprisingly, considering its importance, query folding is not part of the M language. You could write your own M mashup engine which is 100% compliant with the language specification without even knowing that query folding exists. How could such a key Power Query concept not be part—a prominent part!—of the language specification?

It doesn’t need to be.

Let’s take another look at query folding. This time, instead of focusing on what it is or why it is advantageous (topics we’ve touched on in the past—see parts 5 & 12), let’s explore how it works. We’ll do this by looking at the general concepts involved with how folding is implemented using Table.View.

The knowledge we gain should help make folding much less mysterious—which should help us write better queries and debug query folding problems. As a bonus, if we ever decide to try custom connector development or feel the need to override (or augment) an existing connector’s folding, what we learn here should serve as a useful starting place.

Let’s get going!

Continue reading →

Custom Folding Joins: Tunneling State Between Table.Views

• , , ,

“Handling joins doesn’t look too bad,” you think as you examine the handler’s signature. You’re building out a Table.View (perhaps as part of a Power Query custom data connector), expanding its query folding capabilities by implementing additional handlers. OnJoin is up next.

OnJoin = (joinSide, leftTable, rightTable, joinKeys, joinKind) => ...

Its arguments look straightforward, mostly a mirror of what’s passed to Table.Join. Only one parameter, joinSide, is unfamiliar, but is easy to figure out.

All is well, that is, until you start writing code. You’re stopped by a brick wall.

Continue reading →

Privacy Level Descriptions Revised

• , , ,

Last week, Microsoft Power BI’s privacy level descriptions were revised.

Previously, Microsoft documentation implied that the data protection firewall imposed unilateral, bidirectional isolation between data sources having incompatible privacy levels. In actuality, the direction of data flow is factored in when determining whether data folding can take place between sources. Privacy level documentation now reflects this.

Continue reading →

Custom Folding Arbitrary Functions: OnInvoke & Table.ViewFunction

• , , , ,

You are working happily away on a Power Query custom data connector (or maybe on a standalone Table.View). Implement OnTake and OnSkip handlers? Check. Implement OnSelectColumns? Check. And on your journey goes, adding functionality by coding up new handlers—that is, until you realize you want to handle folding for a function where there doesn’t seem to be a corresponding handler.

Maybe it’s a standard library function like Table.ApproximateRowCount. Your data source provides a shortcut way to fetch a close-to accurate count and you’d like users of your custom connector to be able to retrieve that count using the standard function that already exists for this purpose. The catch? There’s no publicly documented GetApproximateRowCount handler which you can handle.

Instead, maybe it’s a custom function. Perhaps your data source maintains snapshots of historic data. Your connector users would like to be able to fetch data as it existed at a user-selected point in time by doing something like MySource.AsOf(someTableFromMySource, someDateTime). For this to work efficiently, that method needs to be foldable, but there’s no OnMySourceAsOf handler provided by Microsoft. Are you stuck or is there a way to fold custom methods?

In either case, the challenge is that you want to fold something that doesn’t have a specific handler.

The solution?

Continue reading →