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:
[DataSource.Kind="ExtensionContentsDemo", Publish="ExtensionContentsDemo.Publish"] shared ExtensionContentsDemo.Contents = () => let GetFileContents = Extension.Contents("MyTextFile.txt"), ConvertToText = Text.FromBinary(GetFileContents) in ConvertToText;
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:
Woah, it’s interesting. It could be, for example, csv or xlsx or other file with data, or… any file PQ could read and execute
Interesting…
Pingback: More bookmarks, Azure Analysis Services, and subscriptions with Power BI | Guy in a Cube
Pingback: The Extension.Contents() M Function - SSWUG.ORG
Pingback: Using OpenApi.Document() To Create A Power BI Custom Connector For The Power BI REST API « Chris Webb's BI Blog
Hello, thank you so much for this explenation, in particular I found usefull the part where you specify how to include and compile “external” files in a Visual Studio project.