Tag Archives: Custom Connector

RowExpression.From/ItemExpression.From

• , , , ,

Power Query’s RowExpression.From/ItemExpression.From function (both names reference the same underlying function) provides a way to learn about what a single-parameter function does by outputting an abstract syntax tree (AST) describing it.

Why might you want to use a programmatic structure like an AST to analyze the logic of a function’s body instead of simply invoking the function?

Well, one reason may be that you are implementing query folding in a custom connector. You might want to translate the filter predicate function passed to Table.SelectRows, or the generator function passed to Table.AddColumn, into the upstream data source’s native query/language. In either case, you don’t want to invoke the passed-in function; instead, you want to understand its behavior so that you can factor it in as you build an equivalent native request/query. RowExpression.From/ItemExpression.From is tailored for this purpose.

Unfortunately, this function is little documented—but it is time for that to change!

(Note: For simplicity, the below will refer to this function by the name RowExpression.From. However, ItemExpression.From is an equally valid way to reference the function.)

Continue reading →

Extension.LoadFunction, Now With Enhanced Error Reporting!

• , ,

Ever had a syntax error in a .pqm file? Likely, the error’s message described the problem but did not tell you which .pqm file contained the error. Instead, you may have had to peruse the various recently-edited .pqm files in your connector to figure out which one the problem originated from.

Thankfully, there is a way to improve this experience, so that extension module load errors identify the problematic source file!

Continue reading →

Highlights from the New Power Query SDK!

• , ,

Earlier this month, Microsoft announced a public preview of the new Power Query SDK! This is an exciting advancement for those engaged in the world of custom connector development! The initial goal is for the new SDK to achieve feature parity with the long since abandoned legacy SDK, then to keep going by adding additional features to the new SDK that enhance connector development.

Microsoft has provided several online resources related to the new SDK:

I thought it would be interesting to highlight a few of the feature additions and enhancements present in the current preview version (version 0.1.7).

Continue reading →

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 →