What is Power Query?
Power Query is the data preparation and transformation component in Power BI and Excel, used to connect, cleanse and reshape data from various sources. The steps are recorded and repeatable, so data preparation can be automated rather than repeated manually.
Also known as: Power Query Editor · M (Power Query M) · Get & Transform · data transformation
Where Power Query is used
Power Query handles the extraction and transformation of data, that is part of a classic ETL process. Users connect to sources such as Excel, SQL databases, REST APIs or CRM systems, cleanse the data (remove columns, set types, replace values), join tables and reshape them into a structure suited to analysis.
All steps are captured in the M language and re-run on every refresh. This creates a repeatable, documented preparation that replaces manual Excel handwork.
Typical use cases
Power Query is used wherever data needs to be prepared regularly before analysis.
- Automatically combine multiple Excel files or worksheets
- Connect to and cleanse data from databases or APIs
- Rename columns, set types and clean up erroneous values
- Automate recurring preparation instead of repeating it manually
How it relates & how smiit uses it
Power Query is the transformation component before the data model, not the calculation language; for metrics in the model, DAX is used. With larger data volumes or many sources, preparation is sensibly moved into a central data platform such as a data warehouse or lakehouse, for example with ETL/ELT pipelines. In the dy Project AG data platform, the heavy preparation was done on Azure Databricks, while Power Query in Power BI is used for lighter, report-facing adjustments. smiit uses Power Query specifically where it stays lean and maintainable.
Common mistakes & misconceptions
- Power Query is not just an Excel feature; the same engine powers data preparation and transformation in Power BI, dataflows and Fabric.
- Many think transformations must be written in M code, but most steps are built through the interface and the code is generated automatically.
- A common error is to assume step order does not matter. Without query folding and a sensible step sequence, performance suffers heavily on large data.
Frequently asked questions
What is the difference between Power Query and DAX?
Power Query prepares the data before it is loaded into the model (connect, cleanse, reshape). DAX calculates metrics and aggregations within the finished data model.
Is Power Query also available in Excel?
Yes. Power Query is available in both Power BI and Excel (there as Get & Transform) and uses the same M language, so knowledge transfers between them.
Do you have to know the M language to use Power Query?
For most tasks, no. The graphical editor generates the M steps automatically as you clean columns, merge tables or set types. Knowledge of M only becomes helpful for advanced or reusable transformations.
When does Power Query reach its limits?
With very large data volumes, many sources or complex processing, preparation in Power Query can become slow and hard to maintain. It then makes sense to move the heavy transformation into a central data platform such as a data warehouse or lakehouse and use Power Query only for light, report-facing adjustments.
Related terms
Sources & further reading
Want to put this topic to work in your company?
Updated · Back to the glossary