Invoking M Functions In Parallel Using List.ParallelInvoke()

I was looking at the list of M functions supported in custom connectors and not in Power BI Desktop (using the technique I blogged about here) in the latest version of the Power Query SDK when I came across an intriguing new function: List.ParallelInvoke(). It doesn’t seem to be documented anywhere, but I think I’ve worked out what it does and it’s very exciting!

Consider the following M function, declared in a custom connector:

SlowFunction = () as number =>
    Function.InvokeAfter(()=>1, #duration(0,0,0,5));

When you call it, it waits 5 seconds and returns the value 1. If you call it three times and sum up the results, as follows:

List.Sum({SlowFunction(), SlowFunction(), SlowFunction()})

…then after 15 seconds you get the value 3 back.

Now, consider the following expression:

  {SlowFunction, SlowFunction, SlowFunction}

When this is evaluated in a custom connector, you get the value 3 back after 5 seconds – so it looks like List.ParallelInvoke() allows you to invoke a list of functions in parallel. There’s also an optional second parameter called concurrency, which seems to control the amount of parallelism. So, for example:

  {SlowFunction, SlowFunction, SlowFunction},

…returns after 10 seconds, suggesting that only two function calls at a time are invoked in parallel.

I can imagine all kinds of uses for this, for example making multiple parallel calls to data sources or doing expensive calculations in parallel. I wonder if it will ever be allowed to be used outside custom connectors?

UPDATE: see Curt Hagenlocher’s comment below for some important information about this function.

Running M Queries In Visual Studio With The Power Query SDK

Writing M in the Advanced Editor in Excel or Power BI can be a frustrating experience unless you’re the kind of masochist who loves writing code in Notepad. There are some options for writing M code outside Excel and Power BI, for example Lars Schreiber’s M extension for Notepad++ (see here for details) or the M extension for Visual Studio Code (available from the Visual Studio Marketplace here; more details on Brett Powell’s blog here), but the trouble with them is that you have to copy the code back into Excel or Power BI to run it. What many people don’t realise, however, is that it is possible to write M code and have IntelliSense, formatting, keyword highlighting and also the ability to execute your own M queries, using the Power Query SDK in Visual Studio.

The Power Query SDK (which you can download here) supports Visual Studio 2015 and 2017 and is intended for people who are writing custom Data Connectors for Power BI. To let you test your Data Connector you can create a .pq file containing M code, and this in fact allows you to run any M query you want whether you’re building a Data Connector or not.

Here’s how. First, install the Power Query SDK and then open Visual Studio and create a new project. Find the Power Query template, select the PQ file option and give your file a name:


Then, in the .pq file that is created, you can enter an M query and then either press the Start button on the toolbar or hit F5 to run the query. The output of the query is shown on the Output tab in the M Query Output window:


Right-clicking on your project in the Solution Explorer pane and selecting Properties brings up a Properties dialog with various properties that control how your queries behave:



Many of the properties are self-explanatory, at least for anyone used to writing M in Power BI or Excel. FastCombine turns off data privacy checks. Allow Native Query lets you use M queries that contain ‘native’ queries (for example your own SQL queries if you’re using a SQL Server data source), as Cédric Charlier shows here. A few of them, such as Legacy Redirects, I have no idea about yet (I should really ask someone…). Turning on Show Engine Traces displays engine trace information in the Log tab of the M Query Output pane; turning on Show User Traces displays trace information generated by the use of the Diagnostics.Trace() function in your code in the Log tab. You can save the contents of the Log tab to a text file.


Error messages are displayed on the Errors tab of the M Query Output pane:


When you have a query that connects to an external data source, the first time you try to run your query you will be prompted to set the credentials used to connect to that data source (as you would in Power BI Desktop), and the data privacy level for the data source, on the Errors pane:


The query won’t actually run this first time though; you’ll need to hit Start/F5 again to see the results. If you close the project and then reopen it you will need to enter credentials again; alternatively, on the Credentials tab you can save the credentials used for a data source to a .crd file which can then be reloaded when you reopen your project. You can also edit and delete credentials on the Credentials tab.


If I’m honest it’s all very basic but it does the job. The main thing that I miss from writing M code in Power BI is the Query Editor UI – when I write M code there I only write about 50% of it manually, the rest I generate by clicking buttons in the UI because it’s faster. Give me the Query Editor (or the ribbonless version of it that comes with SSDT, because Visual Studio doesn’t support ribbons apparently) and I’ll be happy. Even better, give me the improved code editing functionality in the Advanced Editor in Power BI Desktop and Excel that we’ve been promised!

The Extension.Contents() M Function

Following on from my post last week about M functions that are only available in custom data extensions, here’s a quick explanation of one of those functions: Extension.Contents().

Basically, it allows you to access the contents of any file you include in the .mez file of your custom data connector. Say you have a text file called MyTextFile.txt:


If you create a new Power BI Custom Data Connector project using the SDK, you can add this file to your project in Visual Studio like any other file:


Next, select the file and in the Visual Studio Properties pane set the Build Action property to Compile:


Setting this property means that when your custom data connector is built, this file is included inside it (the .mez file is just a zip file – if you unzip it you’ll now find this file inside).

Next, in the .pq file that contains the M code for your custom data connector, you can access the contents of this file as binary data using Extension.Contents(“MyTextFile.txt”). Here’s an example function for use in a custom data connector that does this:

shared ExtensionContentsDemo.Contents = () =>
        GetFileContents = Extension.Contents("MyTextFile.txt"),
        ConvertToText = Text.FromBinary(GetFileContents)


In the let expression here the GetFileContents step returns the contents of the text file as binary data and the ConvertToText step calls Text.FromBinary() to turn the binary data into a text value.

When this function is, in turn, called it returns the text from the text file. Here’s a screenshot of a query run from Power BI Desktop (after the custom data connector has been compiled and loaded into Power BI) that does this:


Which M Functions Are Only Available To Custom Data Connectors?

Here’s one for all you M geeks out there. If you look at the example code for custom data connectors on the Power BI custom data connectors GitHub repo it’s clear that there are several M functions that are available in custom data connectors that aren’t available in Power BI Desktop. But what are they? As I’m sure you know, you can get a list of all the functions, types and enums available in M using the #shared keyword. Well, I created a simple custom data connector that calls #shared and returned a table of all the functions, types and enums available to a custom data connector, and then compared that table with what #shared returns when you run it in Power BI Desktop. This made it easy to find a list of M functions, types and enums that are only available in custom data connectors, and here are all 34:

  • CryptoAlgorithm.Type
  • CryptoAlgorithm.SHA1
  • CryptoAlgorithm.SHA256
  • Crypto.CreateHmac
  • Crypto.CreateHash
  • Web.SignForOAuth1
  • OAuth1.Type
  • OAuth1.HMACSHA1
  • OAuth1.RSASHA1
  • Extension.Module
  • Extension.CurrentCredential
  • Extension.CurrentApplication
  • Extension.CredentialError
  • Extension.LoadString
  • Extension.Contents
  • Credential.AccessDenied
  • Credential.AccessForbidden
  • Credential.EncryptionNotSupported
  • Credential.NativeQueryPermission
  • Error.Unexpected
  • Uri.Type
  • Binary.End
  • Action.Type
  • Action.Sequence
  • Action.Return
  • Action.Try
  • Action.DoNothing
  • ValueAction.Replace
  • ValueAction.NativeStatement
  • TableAction.InsertRows
  • TableAction.UpdateRows
  • TableAction.DeleteRows
  • WebAction.Request
  • Delta.Since

Some of these, like the Action functions, are documented in the Power Query function reference, and as I said others are mentioned in the Power BI custom data connectors GitHub repo, but there’s definitely some detective work to do here…

Power BI Custom Data Connector For Language Detection, Key Phrase Extraction And Sentiment Analysis

I’m pleased to announce that I’ve published my first Power BI custom data connector on GitHub here:

Basically, it acts as a wrapper for the Microsoft Cognitive Services Text Analytics API and  makes it extremely easy to do language detection, sentiment analysis and to extract key phrases from text when you are loading data into Power BI.

Full documentation for the Text Analytics API can be found here and there is more detailed documentation available for the Detect Language, Key Phrases and Sentiment APIs. You can learn more about Power BI custom data connectors here and here.

Note: you will need to sign up for the Text Analytics API and obtain an access key before you use this custom data connector. You’ll be prompted to enter the access key in Power BI the first time you use the custom data connector. A number of pricing tiers are available, including a free tier that allows for 5000 calls per month. The custom data connector batches requests so that you can send up to 1000 individual pieces of text per call to the API.

Why build a custom data connector for this? Well, first of all, text analysis in Power BI and Power Query is something I’ve been interested in for a long time (see here for example), and I know a lot of other people want to do this too. However, calling any API – and the Microsoft Cognitive Services APIs in particular – involves a lot of tricky M code that is beyond most Power BI users. I certainly didn’t find it easy to write this custom data connector! I know Gil Raviv has blogged about how to use the Sentiment analysis API this data connector calls in two posts (here and here) but he doesn’t handle all the limitations of the API, including the 1MB limit per request, in his examples – which just goes to show what a complex task this is. Wrapping up the code for calling the Text Analytics API in a custom data connector hides this complexity from the developer, makes the code a lot more portable, and the fact that the code is open source means the community can work together to fix bugs and add new features. I welcome any contributions that anyone wants to make and I know there are a lot of improvements that can be made. Certainly the documentation is a bit sparse right now and I’ll be adding to it over the next week or so.

This is not quite a traditional custom data connector in the sense that it doesn’t act as a data source in its own right – you have to pass data to it in order to get data back. It exposes three M functions:

  • TextAnalytics.DetectLanguage(inputtext as list, optional numberoflanguages as number) as table
    This function takes a list of text values and returns a table containing the input text and the language detected in each piece of text
  • TextAnalytics.KeyPhrases(inputtext as list, optional languages as list) as table
    This function takes a list of text values (and an optional list of language identifiers for each piece of text) and returns a table containing the input text and key phrases detected in each piece of text. More than one key phrase may be returned for each piece of text.
  • TextAnalytics.Sentiment(inputtext as list, optional languages as list) as table
    This function takes a list of text values (and an optional list of language identifiers for each piece of text) and returns a table containing the input text and a score representing the sentiment detected for each piece of text.

Here are a few simple examples of how to use these functions:

First, the TextAnalytics.DetectLanguage() function. This query:

    input = {"hello all", "bonjour", "guten tag"},
    result = TextAnalytics.DetectLanguage(input)

Returns the following table:


For the TextAnalytics.KeyPhrases() function, the following query:

    input = 
        "blue is my favourite colour", 
        "what time it is please?", 
        "twinkle, twinkle little star, how I wonder what you are"
    result = TextAnalytics.KeyPhrases(input)

Returns this table:


And for the TextAnalytics.Sentiment() function, the following query:

     input = 
        "this is great", 
        "this is terrible", 
        "this is so-so"
     result = TextAnalytics.Sentiment(input)

Returns this table:


Because the first parameter of each of these functions is a list, it’s super-easy to pass in columns of data from existing tables. For example, here’s the output of a query that gets the last ten comments from the comments RSS feed of this blog:


If this query is called Comments, the following single line of code is all that’s needed to call the TextAnalytics.Sentiment() function for the Comment Text column on this table:

TextAnalytics.Sentiment(Comments[Comment Text])


You can download a .pbix file containing several examples of how to call these functions, including all the examples above and many more, here.

I hope you enjoy using these functions, and if you have any questions, find any bugs or want to make suggestions for how they can be improved please let me know via the Issues page on GitHub. Finally, this is my first time using GitHub and if I’ve done something really dumb while publishing the code please let me know what I need to do to fix it!

New M Functionality And Behaviour In Power BI Custom Data Connectors

Over the past few weeks I’ve spent some time playing around with Power BI custom data connectors and while I don’t have anything to share publicly yet (other people are way ahead of me in this respect – see the work of Igor Cotruta, Miguel Escobar and Kasper de Jonge among others) I have learned some interesting things that are worth blogging about.

First of all, the data privacy rules around combining data from different data sources do not apply in custom data connector code. As the docs say here:

Data combination checks do not occur when accessing multiple data sources from within an extension. Since all data source calls made from within the extension inherit the same authorization context, it is assumed they are “safe” to combine. Your extension will always be treated as a single data source when it comes to data combination rules. Users would still receive the regular privacy prompts when combining your source with other M sources.

Those of you who have followed my recent series on this topic, or who have struggled with the Formula.Firewall error, will appreciate how much easier this makes combining data from different sources.

Secondly, you have a lot more flexibility when it comes to different types of authentication for web services. As I showed in my session on web services and M at the Data Insights Summit, there are a lot of limitations when it comes to working with web services in Power BI or Excel. Within a custom data connector, however, you can connect to web services that use OAuth for authentication, you can make POST requests to web services that require authentication and you can pass a web API key from the credentials store through an HTTP custom header and not just through a query parameter – none of which are possible in Power BI or Excel.

I’m sure there are a lot of other useful bits of functionality or behaviour that are only available in custom data connectors – I know I’ve only just begun to learn what’s possible. Even with what I’ve listed here, though, I get the feeling that there will be a lot of cases where you will have no choice but to build a custom data connector just to be able to access certain data sources, even if you only need to create a single report. There may also be cases where it’s preferable to build a custom data connector rather than embed lots of complex M code in a Power BI report or Excel workbook, perhaps to make code portability easier. It’s a bit of a pain to have to have Visual Studio and the SDK installed in order to do this, but building a custom data connector is fairly easy if you already know M and the development experience in Visual Studio (with intellisense!) is much better than in the Advanced Query Editor window.

%d bloggers like this: