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:
[sourcecode language=”text” padlinenumbers=”true”]
[DataSource.Kind="ExtensionContentsDemo",
Publish="ExtensionContentsDemo.Publish"]
shared ExtensionContentsDemo.Contents = () =>
let
GetFileContents = Extension.Contents("MyTextFile.txt"),
ConvertToText = Text.FromBinary(GetFileContents)
in
ConvertToText;
[/sourcecode]
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…
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.
How can we hide or encrypt client credentials inside .txt file. Once we change .mez extension to .zip it could read all files inside and even the credentials. Can you please suggest ?