dkackman
Advocate I
Advocate IStatus:
Completed
Make PowerQuery ETL a scriptable language targeting more than Excel and PowerBI
The PowerQuery/M language component of PowerBI has grown into a powerful data preparation tool that would have immense utility in data pipelines outside of just PowerBI. If custom M functions could be invoked from things like PowerShell, bash, SSIS, data factory etc I could see using it throughout an IT operation, not just on a BI team. Some examples: - The myriad scheduled jobs that an ops team uses to synchronize systems - Ad-Hoc operational data pipelines that apps teams create in things like python, powershell, C# or whatever other imperative language they are familiar with - As an extension to SSIS for a warehouse team working with targets other than PowerBI
9 Comments
- This is a potential game-changer for DBAs and sysadmins, too! Imagine if we could use M to output .CSVs or even write to databases. Combine this is an IDE similar to the Power BI/Excel "get data" environment and you have the ETL tool of choice for companies big and small.
- jrecasensThis is a great idea!
- Is there any news on this? We'd love to be able to script or schedule Power BI M query.
- Once this is done, a standalone PowerQuery/M will fast displace most other ETL tools on the market.
- The idea is similar to this one : https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/12856434-make-powerquery-a-standalone-server-tool We are spreading the votes.
- I would like to have a way to utilize PowerQuery/M to transform data from within MS Access. This would expand the use of Access beyond the existing connection options. I wouldn't mind if I had to use Excel or PowerBI to build the scripts using the Power Query Editor. Once the M script is created I'd like to be able to invoke it from Access and return the result as a dataset.
- It is possible to do this, but it is using undocumented Microsoft APIs and may violate the EULA. Microsoft need to realize the value of M and the Power Query/BI Editor and start developing it out to supplement or supersede SSIS. The editor enables people who are most familiar with the data (business users) to tame and curate data, there is HUGE value in that. We would use it extensively to prepare data for our Data Lake and Data Mart - pursuing this would really begin to make Power BI a 'killer app'. See the following link on how it can be done currently: https://stackoverflow.com/questions/40432718/is-there-any-way-to-invoke-powerquery-m-outside-of-excel-or-powerbi/54898353#54898353
To learn more about all products that Power Query is currently supported in please visit: https://powerquery.microsoft.com
- Status added:Completed
