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 readingEarlier 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 readingYou’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 readingIf 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 readingTable.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.
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.
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 readingThe 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).
I’m excited to formally announce a project I’ve been working on over the past several months: a HubSpot connector for Microsoft Power BI!
Edit: Since the original posting of this article, this connector has been officially released. Details about it may now be found on its product page.
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 readingCurious 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 readingPower Query custom data connectors, including certified connectors, can consists of multiple components which are distributed using one of three file formats. This post attempts to (unofficially) document the basics of these formats.
Continue reading