The task seemed simple enough: Determine the earliest date between two columns, so that this value could be used when generating a date dimension.
A little M code that took the minimum of each column, then the minimum of those minimums seemed to meet the need until—ouch—I discovered it wasn’t finding the earliest date. Why?
let
OrderCreatedMin = List.Min(SomeTable[OrderCreated]),
TransactionReceivedMin = List.Min(SomeTable[TransactionReceived]),
OverallMin = Date.From(List.Min({ OrderCreatedMin, TransactionReceivedMin }))
in
OverallMin
What do you think? Do you see any problems with the above?
During 2022, the Power QueryM Language Specification received seven substantiative revisions (beyond typo fixes, formatting tweaks, and the such). Each brought clarification to ambiguous points or corrected cases where the specification did not align with actual mashup engine behavior. None of the revisions added new language functionality or otherwise resulted in the mashup engine changing.
Interestingly, while the M language gained two new features last year (try‘s catch and structured error messages), neither of these has yet to make it into the language spec.
To the average Power Query user, how the standard library and data connectors end up in the global environment may be irrelevant. What matters is that, however they get there, they’re there and they work! But in the world of advanced M development, how identifiers come to be injected directly into the global environment becomes interesting. Of particular pertinence is the extension/module system that plays a pivotal role in part of this process.
Welcome to a new world: extending the global environment, here we come!
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!
A challenge with the current Power Query PostgreSQL data connector is that it does not understand how to work with PostgreSQL’s complex typed columns.
Background
Some examples of PostgreSQL’s complex typed columns:
Scenario: PostgreSQL allows a single column to be defined as containing a specific complex type. Example: Column phone_number could be defined to as being of type telephone_number (which has fields “areacode”, “number” and “extension”). M Equivalent: Column contains a record.
Scenario: A single column can also be defined to contain an array of values of a specific scalar type. Example: Column visit_dates could be defined as an array of date values. M Equivalent: Column contains a list.
Scenario: A column can be configured to contain an array of a given complex type. Example: Column order_lines could be defined as an array of order_line values. M Equivalent: Column contains a nested table.
Challenge
Power Query does not understand any of these more advanced column set ups. Depending on the exact scenario, when PQ encounters one of the above, it either renders out the raw textual equivalent of the entire column (like “{}” in column project_contingency_items, below) or an error (such as the below complaint that “We don’t support CLR type ‘System.Dynamic.ExpandoObject’.”).
The net effect is that, for a table/view where these more complex structures are used, hand-written SQL (e.g. Value.NativeQuery) may be necessary as Power Query may be unable to make “sense” out of the relevant columns on its own. This makes the level of effort involved with ingesting this data into PQ much higher.
You’re authoring in Power Query. You decide that rows with 0001-01-01 in a certain column should be removed, so you filter on the column, excluding 0001-01-01 values. After you apply your filter, nothing changes: the 0001-01-01 rows are still present. What is going on?
This issue bit me recently. Turns out, it’s due to a translation loss when bridging between the worlds of MySQL and Power Query.
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:
The official public preview announcement shares more details on the project and Microsoft’s plans for it.
Visual Studio Marketplace is the place to go to install the SDK.
Uh oh! You’re using Microsoft Power BI or Excel and you’ve discovered that Power Query does not have a built-in connector for the data source you’re interested in consuming.
Question: What does this mean for you?
Answer: (pick which of the below you think is most likely correct)
No go. You cannot access this data source from Power Query.
Custom connector. “Built-in connector” and “custom connector” sound like opposites. Either you may need to fork out a bit of $$ to get a custom connector created, or give up.
Something else. Hmm…what are the other options?
I’d suggest starting with “something else.” When Power Query doesn’t ship with a connector for your source of interest, there are a number of other options to consider.
Let’s take a journey through the main other options for directly connecting to a source from Power Query. We’ll order these into “stages” based on an approximation of the level of effort involved. Then, we’ll expand our scope and touch on several possibilities that involve using external tools or languages to provide the needed data to Power Query.
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.
Your Power Query contains more columns than you need. How do you get rid of the ones you don’t want? The mechanics of making extraneous columns go away is easy: in Query Editor, right click on a column and choose either Remove Columns or Remove Other Columns—but what differentiates these two options?
