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!
Nice post. Curious if there is (or will be) any way to save and execute M queries as some sort of deploy-able object? I have seen the discussions on adding this technology to SSIS (and challenges). I’d be interested in being able to deploy something like this even in a non-SSIS context since it is so powerful. Thx D
Can’t believe I missed the release of this. Chris, can you think of how one could “kinda” incorporate this into a larger, non-Power-BI data stack, so either within SQL Server, or within SSIS/AzureDataFactory? For example, I would like to have the ability to (just as one example) scrape a table from a web page, as you can currently do in PowerBI, but instead of the contents going into a specific Power BI model, I would like to put them into either a traditional SQL DW table, or into an SSAS Tabular model, ideally somehow supporting with configurable caching (see: Lambda architecture).
Does what I’m saying make sense?
If not, I’m thinking an alternative way to achieve this today would be a cachable pattern in SQL 2017 sitting on top of the new Python capabilities (as a substitute for M in this case). I think that would work.
A followup question is: do you know if there is a Slack/Reddit/Other community somewhere that focuses on the “data stack” of Microsoft/Azure? Discussions would be *tangentially* related to SQL Server, Power BI, SSAS/SSIS, Azure, but would generally be too specific for either of those communities individually.
Chris, do you have any idea why the Query Result is editable (even check boxes) ?
Pure oversight on our part, I imagine. Can you file an issue on GitHub? 🙂
When trying to create a new project using the File > Project > Online, I get the following error after I choose the type of the project and its location and press OK:
Microsoft Visual Studio
Could not load file or assembly ‘Microsoft.VisualStudio.TemplateWizardInterface, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ or one of its dependencies. The system cannot find the file specified.
This made me chuckle: “… I only write about 50% of it manually.”.
Is there anyone who is getting this error when trying to install it?
“The installation was unable to install the extension to all the selected product.
For more information, click on the install log link at the bottom of the dialog”.
This extension is not installable on any currently installed products.
And here is the log:
13/11/2018 11:33:26 – Microsoft VSIX Installer
13/11/2018 11:33:26 – ——————————————-
13/11/2018 11:33:26 – Initializing Install…
13/11/2018 11:33:26 – Extension Details…
13/11/2018 11:33:26 – Identifier : PowerQuerySDK.Microsoft.30831070-f420-4649-a031-6f679996b182
13/11/2018 11:33:26 – Name : Power Query SDK
13/11/2018 11:33:26 – Author : Microsoft
13/11/2018 11:33:26 – Version : 1.0.0.20
13/11/2018 11:33:26 – Description : A Power Query language service for Visual Studio
13/11/2018 11:33:26 – Locale : en-US
13/11/2018 11:33:26 – MoreInfoURL :
13/11/2018 11:33:26 – InstalledByMSI : False
13/11/2018 11:33:26 – SupportedFrameworkVersionRange : [4.5,)
13/11/2018 11:33:26 –
13/11/2018 11:33:29 – SignedBy : Microsoft Corporation
13/11/2018 11:33:29 – Certificate Info : [Subject]
CN=Microsoft Corporation, OU=OPC, O=Microsoft Corporation, L=Redmond, S=Washington, C=US
[Issuer]
CN=Microsoft Code Signing PCA 2010, O=Microsoft Corporation, L=Redmond, S=Washington, C=US
[Serial Number]
33000001FC5DB8191027A666C10000000001FC
[Not Before]
05/10/2017 19:26:08
[Not After]
05/10/2018 19:26:08
[Thumbprint]
5E940B33DEAE5945F506ADAB946A997867A40FEF
13/11/2018 11:33:29 – Supported Products :
13/11/2018 11:33:29 – Microsoft.VisualStudio.Community
13/11/2018 11:33:29 – Version : [14.0,15.0]
13/11/2018 11:33:29 – Microsoft.VisualStudio.Pro
13/11/2018 11:33:29 – Version : [14.0,15.0]
13/11/2018 11:33:29 – Microsoft.VisualStudio.Enterprise
13/11/2018 11:33:29 – Version : [14.0,15.0]
13/11/2018 11:33:29 –
13/11/2018 11:33:29 – References :
13/11/2018 11:33:29 –
13/11/2018 11:33:29 – Searching for applicable products…
13/11/2018 11:33:29 – Found installed product – Microsoft Visual Studio 2015 Shell (Integrated)
13/11/2018 11:33:29 – Found installed product – Global Location
13/11/2018 11:33:29 – Found installed product – ssms
13/11/2018 11:33:29 – VSIXInstaller.NoApplicableSKUsException: This extension is not installable on any currently installed products.
at VSIXInstaller.App.InitializeInstall(Boolean isRepairSupported)
at VSIXInstaller.App.InitializeInstall()
at System.Threading.Tasks.Task.InnerInvoke()
at System.Threading.Tasks.Task.Execute()
It looks like not all the VS versions are supported?
Hmm, I think the problem is you only have “Microsoft Visual Studio 2015 Shell (Integrated)” installed (possibly because you have SSDT installed?) and you might need a standalone version instead.
Oh, yeah!
Thank you Chris, that’s exactly my current configuration.
Many Thanks for that!
Hi Chris, very useful post! I have a question for you: I wrote 2 custom connectors, actually two custom function libraries. Is it possible to call from the second library a function defined in the first library? I tried directly, with #shared[] or with the use of section-access-expression (TheSectionOfTheSecondLibrary!TheFunctionToCall) without succes. Thx, MM
I am creating a custom data connector, but I have to specify the connection method for each call. How do I resolve this? This is not the case with templates that use templates.
Can you explain what you mean by “specify the connection method for each call” please?
Yes, I have solved this problem because the parameters of the custom connector are not optional.
Hi Chris
Do you know if “Visual Studio Community” supports “Power Query SDK” or it needs a fully licensed version of Visual Studio?
It works with Visual Studio Community
As you said, this is really very basic functionality. It still does not support parameters and joins with-in Power Query, as far as I have used it. If your Power Query comprises parameters and joins, it would not run in this SDK. To fully replicate PowerBI functionality, this SDK needs to support parameters; and table objects so that they can be later joined in a subsequent Power Query. Until then, I don’t know how useful it is for PowerBI development.
The debugging experience in Power Query is, for want of a better term – terminally wanting.
Hi, i have made the file and see in output window an output, but how to work with the output in visual studio? all documents seem to work the other way arround. but i want to use an M query in viausla studio applicantion.
second question, how to chnage the query formula dynammically (e.g dates etc)
Is there any way to get sendgris data in a power BI without any aggregation
HI, Chris
I am trying to extract data from an API of type OAuth2. But in the middle of the way the browser opens a window asking for login and password.
This is normal? Shouldn’t the Power Query SDK replace this login and password window with a window in Power BI?
HI, Chris
I am trying to extract data from an API of type OAuth2. But in the middle of the way the browser opens a window asking for login and password.
This is normal? Shouldn’t the Power Query SDK replace this login and password window with a window in Power BI?
No, this is what I would expect to happen
Hi Cris,
When I connect to a dataflow I get the following error:
Query:
>
let
Source = PowerBI.Dataflows(null),
#”44bc0622-40f4-4767-b170-0eb5eadc8fc8″ = Source{[workspaceId=”44bc0622-40f4-4767-b170-0eb5eadc8fc8″]}[Data],
#”b00e94a8-6c62-48b2-9322-8cebee070f49″ = #”44bc0622-40f4-4767-b170-0eb5eadc8fc8″{[dataflowId=”b00e94a8-6c62-48b2-9322-8cebee070f49″]}[Data],
#”Dataflow Grouped1″ = #”b00e94a8-6c62-48b2-9322-8cebee070f49″{[entity=”Dataflow Grouped”]}[Data]
in
#”Dataflow Grouped1″
The import PowerBI.Dataflows matches no exports. Did you miss a module reference?<
Is it possible to connect to PBI Dataflows this way?
Sadly this doesn’t work in VS2022 yet (2022-02-03)
Is it possible to set breakpoints when running Power Query in Visual Studio?
No – and that’s because Power Query doesn’t run each step one by one, so break points wouldn’t make sense.
Thanks for quick reply – so how can you then debug in an efficient way?
Obviously you can click on each step in the Query Editor and see what the data looks like at that point. But I guess you’re asking about something more than that?
Well, I get the following error when doing one step:
The field ‘ ‘ already exists in the record.
I’m prettys sure it happens here:
InformationBrowserReport = Table.RenameColumns(
TransformedColumns, Table.ToRows(RemovedColumns), MissingField.Ignore
)
But this in in a separate function, invoked from another function so that’s why I woluld like to break (in Visual Studio) in the second function and analyze the data at that step…
Hmm, there’s no way of debugging this easily unfortunately. It sounds like you’re trying to add a field to a record where a field with that name already exists, but I’m sure you guessed that already.