Inner workings and documentation

[sio]

Hello,

Thank you for this project! It proves that an idea of mine is feasible and inspires to pursue it.

Could you please explain how does it work internally? Especially the Power Query execution part. I've been trying to figure that out by following the code but lack of C# and .NET familiarity limits my research to guesswork.

From what I've gathered, the magic happens here: Commands.cs, namely in the instance of QueryExecutor class. Where does that class come from? Is there any documentation for it?

It seems that QueryExecutor is imported from one of the Microsoft.Mashup.* libraries in the beginning of the file, but I was unable to find any documentation about those.

Please expand/correct my findings and point me to the relevant documentation.

Thank you!

[gsimardnet]

Hi Vitaly,

You have found the right place. The Command class wraps the QueryExecutor. That's where the formula is sent to Power Query and the result is returned in a DataTable.

Unfortunately, you won't find any documentation because Microsoft doesn't have any official support for this at the moment.

[sio]

Thank you! How did you discover that library and its API?

[gsimardnet]

I started testing Power BI then I found the Power Query SDK and also SSAS. They all share common objects and began exploring them. By trial and error I was able to use them and created this API. Power Query is a wonderful language and my goal is to open it to any application. This project is a starting point to any idea you may have around the subject and how to leverage this language. These last couple of weeks I didn't have much time but I have several feaures in mind. For instance, I notice that you have interest in adding PowerShell support. That's a very good point. Probably the next thing I would add is a command line interface to call a Power Query formula and export the result to a file (e.g. CSV, XML, JSON, ...)

[otykier]

Just a heads up, in case you're planning to use PowerQueryNet or a similar technique in a production scenario, where there is a risk of a Microsoft license/EULA audit on your company:

In addition to being not supported by Microsoft, interacting with the mashup engine in any other way than through the Power Query UI in Excel / PBI / SSDT is explicitly breaking the EULA :-(

[sio]

@gsimardnet, yeah, a command line interface to Power Query is what I need :)

@otykier, could you elaborate? I was not going to use that for anything other than a hobby project, but my understanding was that it did not violate the license terms. There is a clause about no right to: a) work around any technical limitations in the software that only allow you to use it in certain ways; but we are talking about an SDK here that's being used for software development only through exposed API, not some reverse engineering or disassembly.

[otykier]

@sio The Power Query SDK is only intended for developing custom connectors - not for direct access to the mashup engine itself. Just because a certain .dll ships with a product or an SDK, doesn't mean you're allowed to use the code in that .dll for other purposes. For example, you'll also find the mashup engine .dll shipping with PBI Desktop and Excel. What's more, PBI Desktop even includes all the .dll's necessary to spin up a local instance of Analysis Services. That doesn't mean you're allowed to do it.

That being said, I would be very surprised if Microsoft would ever pursue legal actions against individuals who break their EULAs for hobby projects or out of pure interest. Don't get me wrong - I think it's a damn shame that they're not opening up the mashup engine API, as it's extremely powerful and can do amazing stuff in terms of data movement and transformation. But intentionally breaking the EULA will probably not help convincing them to open up the API. Instead, I suggest voting for this idea and generally voicing your opinion on this on public channels.

[gsimardnet]

@otykier, the question remains : Where in the License do they mention something that could be violated by this project ?

[Seddryck]

From the EULA available at https://powerbi.microsoft.com/en-us/desktop-eula/

This agreement only gives you some rights to use the software. Microsoft reserves all other rights. Unless applicable law gives you more rights despite this limitation, you may use the software only as expressly permitted in this agreement. In doing so, you must comply with any technical limitations in the software that only allow you to use it in certain ways. You may not

...

·     reverse engineer, decompile or disassemble the software, except and only to the extent that applicable law expressly permits, despite this limitation;

...

·     work around any technical limitations in the software;

...

IMO, you are violating these two sentences. You are also violating the fact that you cannot redistribute the dll.

Now, you could argue that DAX Studio is violating a rule about no third party connection to Power BI, NBi is violating the same rule. The EULA is really difficult to not violate, if you want to improve Power BI Desktop experience for developers.

[Seddryck]

The same sentence are in the licence that you linked. That’s just my personal opinion and I’m not a lawyer.

[gsimardnet]

Since the last version I do not redistribute any dll from the SDK. You must download them by yourself. From my point of view, that was the only violation and I made the proper corrections to become compliant.

About your extract from the EULA, these are standard clauses that you find in most EULA even in applications made for developers.

You will find these same sentences in:
Visual Studio EULA
Visual Studio Code EULA

They certainly don't mean that we can't develop new applications that uses them.

[Seddryck]

Great that you fixed the distribution issue. As I stated before I'm not a lawyer, everything is my personal opinion

you wrote: "I started testing Power BI then I found the Power Query SDK and also SSAS. They all share common objects and began exploring them. By trial and error I was able to use them and created this API." … and the licence is stating that "You may not reverse engineer, decompile or disassemble the software" … I'm wondering how you could find undocumented methods without disassembling a dll.

You're providing an API on top of the Power BI Desktop (or the SDK) to let users execute Power Query commands outside of the Power BI environnement. That's something that is technically not possible with Power BI Desktop … but the EULA is stating that "you may not work around any technical limitations in the software" … I'm wondering how providing something that is not natively possible in a software is not working around technical limitations.

About Visual Code and Visual Studio, both products have extensions points. They are official and documented, so you don't need to work around technical limitations or reverse engineer to use them. You're using them for what they were intended. From what I read a few years ago, the developer of test-driven.net (an add-in of Visual Studio) had found a way to install his add-in on top the Community edition of Visual Studio (that doesn't have extension points and is not intended to support add-ins). He received a message to ask him to remove this hack. IMO, your work is in a similar case.

Now, it doesn't mean that your code is wrong or anything else, I appreciate it. From my personal opinion, you're border line with the EULA and I wouldn't use this in any professional software … again just a personal opinion. IMO, the only way to clarify this is to ask the product team.

and it's not because you're violating the EULA that you'll in troubles, DAX Studio is also violating the EULA by creating a connection to the SSAS instance running and I don't think they are in troubles with Microsoft for this :-)

[otykier]

Agree with everything @Seddryck wrote. Just one minor point: As of august 2018, Microsoft now officially supports connecting to the diagnostics port, so DAX Studio is no longer violating anything :-)

[Seddryck]

Waouw, great news! It means that NBi is also clean with this undocumented feature.

[gsimardnet]

Bottom line: the EULA has a lot of grey areas that make lawyers very happy.

Thank you @Seddryck for your feedback.

[janantos]

Now the fun about the licencing, for example in Czech Republic you are allowed by law to reverse engineer sw. So it ,mostly depends on local law.

[alazyworkaholic]

Any developments / thought refinement since this was last discussed? I'd like to use this at work. IANAL, but I don't consider trial & error testing of poorly documented functionality to be in the same league as reverse engineering, which I'd associate with figuring out how the code behind the API works, and re-implementing that, which could undercut Microsoft's product revenue.

[gsimardnet]

I never had any feedback so I assume that they tolerate this project. After all, we are only adding new features on top of their library and I don't make money out of it anyway.

[landall]

Now the fun about the licencing, for example in Czech Republic you are allowed by law to reverse engineer sw. So it ,mostly depends on local law.

reverse engineer is also allowed in some other countries for study purpose.
use an undocumented API from the result of other's study is also allowed.
so....

[mattmasson]

Hello – I'm on the Microsoft Power Query team. Consumption of Power Query DLLs in this way is not a permitted scenario. We recommend reviewing the license terms, which prohibit reverse engineering, decompiling, or disassembling the Power Query SDK, if you have questions about what is or isn't allowed.

[alazyworkaholic]

So this basically kills the project? Bummer. Does anyone know of similar functional ETL tools?

[otykier]

So this basically kills the project? Bummer.

Yep, this is what I was afraid of. I hope that, some day, we will get an API or other means of interacting with the Power Query mashup engine in a supported way for 3rd party tools. The engine can do amazing things, and it kind of feels like we’re missing out on a lot of its capabilities, simply because its only accessible through Excel/Power BI/PQ online.

Does anyone know of similar functional ETL tools?

There are lots of other non-Microsoft ETL tools out there, but to my knowledge, none that use Power Query / M to define transformations.

[landall]

Hello – I'm on the Microsoft Power Query team. Consumption of Power Query DLLs in this way is not a permitted scenario. We recommend reviewing the license terms, which prohibit reverse engineering, decompiling, or disassembling the Power Query SDK, if you have questions about what is or isn't allowed.

reverse engineering for study is allowed in some countries.
And i think this way can help many one who paid for Office Desktop version.

All in all, why Power Query is not a member of Excel Online and Microsoft Graph API?

[alazyworkaholic]

So this basically kills the project? Bummer. Does anyone know of similar functional ETL tools?

If anyone comes across this, my new favourite data wrangling tool (for files) is Visidata. It's vim-like, scriptable via Python, and very fast. It doesn't do everything PQNet did (upload to SQL Server, Power Query data connectors) but it's easy enough to add that via a script.