Is there any way to invoke PowerQuery/M outside of Excel or PowerBI?

18
votes

Our BI team is really growing to like the Power Query ETL tool used within Excel and Power BI. The functional language M/PowerQuery has great utility and it would be nice to be able to utilize outside of the context of PowerBI.

Is there or are there plans for exposing "M" as a stand-alone module, callable form the likes of c# or PowerShell?

3
c#powershellpowerbipowerquerym
You can post this feature to power-bi-ideas site: ideas.powerbi.com/forums/265200-power-bi-ideas – M.Hassan
good idea. ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/… – dkackman
SSIS integration was briefly touted (e.g mentioned in article linked below) for SQL Server 2016. But sadly that didn't make the cut. I would've used it for 95%+ of my SSIS data flow sources. sqlmag.com/blog/… – Mike Honey
Doesn't it run inside of Visual Studio when creating a Power BI custom connector ? – Daisha Lynn

3 Answers

5
votes

It appears that this is now possible via the Power Query SDK.

This has enabled projects such as PowerQueryNet, which can already execute the M PowerQuery expressions programmatically.

Although you should keep in mind that the magic of PowerQueryNet is achieved through the use of undocumented APIs and in a commercial setup, may violate the Power BI / Power Query EULA. So tread with caution.

I'm currently working to put this into a docker container which receives M via REST and returns data in the format requested (JSON/CSV/XML).

If anything, all of this shows how easy it would be for Microsoft to satisfy this request. It's also an interesting insight into how under funded Microsoft software development might be, or how little Microsoft they realize the value of Power BI / Power Query: it enables savvy business users who know their data best to do hefty data transformations with Office apps already installed on their computers - a massive step forward.

7
votes

https://ideas.powerbi.com is probably the right place to post this. I've passed the link around internally, it will probably get some more upvotes at the minimum :)

I don't know of any official plan to have a public standalone library to invoke "M" from a different application, but that's what other Power BI products like the Power BI Gateway use to run "M".

Edit: OP created the idea here: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/16969474-make-powerquery-etl-a-scriptable-language-targetin

0
votes

One way to get the capabilities of PowerQuery/M from your own .Net applications, is by using the Powery Query Source for SSIS https://docs.microsoft.com/en-us/sql/integration-services/data-flow/power-query-source?view=sql-server-ver15 with a stand alone DTS packages. You can create a DTSX (SSIS Pkg) and use the Power Query data source for your ETL in a data flow task, then call the DTS Package programatically via .Net and the Managed DTS Runtime. This works very well. Keep in mind the SSIS Power Query source requires SQL Server on the machine you with to install it on (installation dependecy check) and also the PowerQuery functionality is not as full features as that in PowerBI, SSAS Tabular; for example you cannot use PDF as a data source. I have found this pattern useful for various Excel Doc ETL projects.