What is Power Query?
Power Query is a data transformation and connection tool built directly into Microsoft Excel (and Power BI). It allows users to connect to a wide range of data sources — databases, CSV files, websites, SharePoint, APIs, and more — and apply a sequence of transformation steps that run automatically every time the data refreshes. Rather than manually cleaning and reshaping data each time a report is prepared, Power Query does the work in seconds.
For many New Zealand businesses, Power Query is the single most impactful Excel feature they are not using. Finance teams that spend Monday mornings consolidating last week's data, operations managers who manually update reports from multiple system exports, and analysts who rebuild the same pivot tables from scratch each month — all of these workflows can be automated with Power Query.
What Power Query Can Connect To
- Excel files and CSV exports
- SQL Server, MySQL, PostgreSQL, Oracle databases
- SharePoint lists and document libraries
- REST APIs and web data
- Xero, MYOB, and accounting system exports
- ERP system data exports
- JSON and XML files
- OneDrive and SharePoint Online folders
Common Transformations
Power Query can merge data from multiple sources, unpivot and reshape tables, remove duplicates and filter outliers, split and combine columns, apply conditional logic, create calculated fields, standardise inconsistent data formats, and append multiple files from a folder automatically. Once the transformation steps are defined, the entire sequence runs every time the query refreshes — no manual intervention required.
Power Query vs VBA for Data Automation
Power Query is generally better than VBA for data import and transformation tasks. It is easier to maintain, does not require programming knowledge to modify, and runs more reliably across different Excel environments. VBA remains essential for tasks involving custom business logic, user interfaces, document generation, and system integration. For most data pipeline work, Power Query should be the first choice.
Frequently Asked Questions
- Is Power Query available in all Excel versions?
- Power Query is available in Excel 2016 and later, and in all Microsoft 365 Excel versions. It is also built into Power BI Desktop.
- Can Power Query replace manual report preparation?
- In many cases, yes. If a report is prepared by importing, cleaning, and consolidating data from known sources, Power Query can automate the entire preparation process.
- What is the difference between Power Query and pivot tables?
- Power Query handles data connection and transformation — bringing raw data into a clean, structured form. Pivot tables handle analysis and summarisation of that clean data. They work best together.
Conclusion
Power Query is one of the most impactful features in Excel for businesses dealing with recurring data from multiple sources. For New Zealand organisations spending significant time on manual data preparation each week, implementing Power Query automation delivers an immediate and measurable productivity improvement. XLS Experts designs and builds Power Query solutions that fit your specific data sources, reporting needs, and team capabilities.
