Running a native query within an M project

A few weeks ago, Microsoft published the Power Query SDK, an extension of Visual Studio, on the Visual Studio Marketplace. It is available for free for VS2015 and VS2017. This extension is a really welcome in the world of “Power Query M language” aficionados. By using it, you can save a lot of time when trying to debug a query or when you want to manually test your implementation. Naturally, it’s just a version 1.0.1 and there are rooms for improvements (having more than one file in a .mproj, support for debugging, …) but it’s much better than relying on the “advanced editor” of Power BI (the name “advanced editor” is somewhere fun for just a textbox).

I was just using it since a few days when I found an interesting case. My query had a native query

Sql.Database(
   "server",
   "db",
   [Query = "select * from myTable where field=" & value]
)

When I tried to execute it, I received a message from the Power Query SDK that

The evaluation requires a permission that has not been provided. Data source kind: ‘SQL’. Permission kind: ‘NativeQuery’.

No luck with my google-fu, so I checked the github repository linked to data connectors and documenting this SDK. Nothing was available in the documentation but an attribute in the project definition caught my attention: AllowNativeQuery. This attribute is by default at false, I changed it to true

<AllowNativeQuery>True</AllowNativeQuery>

I reloaded the project and no surprise: it was where the magic happened. I can now run my native queries within Visual Studio for this project!