Tag Archives: Custom Connector

Custom Connectors: Native Query Support

, , ,

You’ve built a custom connector—and it works great! But regardless of how good your query folding implementation is, likely there are times that a hand-crafted native query can beat it in performance and capability. So, in addition to supporting query folding, you’d like to give users the option of using native requests that they write with your connector.

In a nutshell, you’re hoping to enable something like:

let
  Source = MyConnector.DataSourceFunction("some-host"),
  Results = Value.NativeQuery(Source, "some native request goes here")
in
  Results

Is this possible? From Power Query’s perspective, at the technical level: Yes—and it is easy to pull off.

Continue reading

Custom Connectors: Introducing Table.ViewError & Terminal Errors

,

If a table view handler raises an error, Power Query’s default behavior is to suppress the error and instead perform the requested operation internally. In a nutshell, Power Query internally does whatever it was that the user requested, since the view was unable to do so.

But what if this isn’t the desired behavior—what if a view handler should stop an operation from taking place, so the user sees it dying with an error?

Continue reading

Custom Connectors: Populating Table.Schema

,

Table.Schema offers to expose a variety of details about a table’s columns. Your custom connector can leverage this functionality to give users easy access to column-specific details from the external data source about the tables they are fetching.

Table.Schema output

Say that external source allows users to create new columns and give them descriptions. Why not make these descriptions available from within Power Query by having them appear in Table.Schema‘s Description column? Perhaps the type system on the source is a bit different from Power Query’s. Table.Schema columns like NativeTypeName, NumericScale and NumericPrecision can be used to communicate relevant details to your users for reference purposes, enhancing their understanding of the data they’re pulling.

How?

The idea of exposing informative details about a source’s columns is great—but how do you provide this information in a way that Table.Schema will read it? The answer depends on which Table.Schema column you’re trying to populate.

While these techniques may primarily be of interest to custom connector developers, they’re valid to use from any M code—no connector required.

Continue reading

Zero Rows Can Bite (part 2): The Mysterious All-Null Row

, , ,

The table you fetch from a web API mysteriously contains an unexpected row with a null value in each column. You manually try the API using a tool like Postman or Insomnia and don’t find any all-null objects in the raw response. Where is this null table row coming from?

Zero rows (again!).

Previously, we dug into refreshes mysteriously dying with the complaint that “column ‘Column1’ of the table wasn’t found” even though no M code or data source schema changes had occurred. Upon investigation, we learned that an insufficiently in some fetch data M code results in it outputting zero columns when the web API returns no rows, which breaks later code that expects the presence of specific columns.

This time, zero rows is again the trigger condition, though it’s not zero rows altogether. Instead, it’s when a web API that returns paged responses returns a page containing no rows. Receiving back an empty page is a real-world possibility. For example, the last page of a response might contain zero rows because the rows that were to have been in it were deleted just moments ago, after the preceding page was fetched.

A common pattern for processing paged responses is to read the various pages into a list, then turn that list into a table, which is then expanded out into the appropriate rows and columns. However, the implementation of this flow sometimes leaves a corner case unaccounted for which leads to the all-null row being present. Unfortunately, such an oversight is present in Table.GenerateByPage (a function commonly used by custom connectors).

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

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