Expose Power Query as a Standalone Runtime and Command-Line Interface (CLI)
One practical example from my own work: I use PowerShell to orchestrate a master data backup process where a list of SQL files is executed sequentially through hdbsql.exe, and the results are automatically exported to CSV files for backup and downstream consumption.
Oversimplified example of an hdbsql.exe execution using server information stored in $HanaServer, a single hardcoded .sql file, and a single output file. In practice, this process can execute multiple SQL files and produce multiple .csv files recursively and sequentially when looped:
PowerShell
↓
& hdbsql.exe -n $HanaServer -I query1.sql -o example1.csv
The key benefit is that SQL scripts are treated as first-class, reusable assets. PowerShell does not need to know the query logic itself. It simply invokes hdbsql.exe, passes the .sql file, and handles orchestration, logging, scheduling, and output management.
I would like to see a similar capability for Power Query. Today, .pq files contain transformation logic but cannot be executed directly as standalone assets in the same way that .sql files can be executed through hdbsql.exe. An official powerquery.exe (or equivalent runtime) would allow organizations to maintain reusable .pq files and execute them from PowerShell, Task Scheduler, Azure Automation, or CI/CD pipelines.
For example:
PowerShell
↓
& powerquery.exe -I etl1.pq -o example1.csv
This runtime could leverage the same authentication mechanisms already supported by Power Query connectors, allowing secure access to sources such as SharePoint, SQL databases, SAP systems, APIs, and other enterprise data platforms.
The particular use case that made me think about this is: I wanted to use a .pq file to connect directly to a read-only .xlsx file from SharePoint, retrieve a finance reporting schedule from a spreadsheet that currently has a non-table structure, perform all required transformations, and export a normalized dataset (without requiring Excel, Power BI Desktop, or any other host application) to be used as an input downstream in a similar orchestration/automation process as the one I designed for the Backup solution mentioned earlier. This would create the same clean separation of responsibilities that already exists with PowerShell and hdbsql.exe:
- Power Query for data transformation
- PowerShell for orchestration and automation
- SQL for data consumption and reporting
This approach would make Power Query a true first-class component of enterprise automation and ETL workflows, while preserving the existing user experience within Excel, Power BI, Fabric, and other Microsoft products.
