Author: Luc Debois © August 2026 – Version 1.0
When most people think about Power BI, they immediately picture beautiful dashboards, advanced DAX calculations, and interactive visualisations. While these elements certainly play an important role, the true foundation of every successful Power BI solution is often less visible: Power Query.
Power Query is the data preparation and transformation engine of Power BI Desktop. Microsoft describes it as the environment used to connect to data sources, shape and transform data, and prepare it for loading into a Power BI model.
For many Power BI professionals, Power Query is where the real work begins. It transforms messy, inconsistent, real-world data into a clean and structured dataset that can be used for reporting and analysis. Data preparation through Power Query is a crucial step before modelling and reporting can begin.
But Power Query itself is only part of the story.
Behind every transformation step lies a powerful programming language called M.
Understanding both Power Query and the M(ashup) Language can elevate a Power BI developer from report builder to data engineer.
What is Power Query?
Power Query is Power BI’s built-in ETL engine.
ETL stands for:
- Extract data from source systems
- Transform the data into a usable format
- Load the prepared data into Power BI
Typical Power Query tasks include:
- Removing duplicates
- Renaming columns
- Splitting columns
- Merging datasets
- Appending tables
- Replacing values
- Handling missing data
- Creating custom calculations
- …
Every transformation is documented as an individual step in the Applied Steps pane.
This means that when new data arrives, Power BI simply replays the same sequence of transformations automatically during refresh.
Microsoft explicitly highlights this step-based transformation process as one of the key features of Power Query Editor.
Connecting to Virtually Any Data Source
One of Power Query’s greatest strengths is its impressive collection of connectors.
Common data sources include:
- Excel
- CSV
- SQL Server
- Oracle
- SharePoint
- OneDrive
- Teams
- Azure
- APIs
- Web sites
- JSON files
Modern cloud applications increasingly expose data through APIs and JSON structures.
Without Power Query, much of today’s cloud-based data would be difficult to consume in Power BI.
What Is M Language?
Most users interact with Power Query through the graphical interface.
However, every click generates code written in M.
M is a functional programming language specifically designed for:
- Data acquisition
- Data transformation
- Data shaping
- Data automation
For example, filtering a table to show only positive sales values might generate:
Table.SelectRows(
Source,
each [Amount] > 0
)
The moment you start using Power Query, you are already using M—even if you never see the code.
Understanding the Structure of an M Query
A typical query follows a simple structure:
let
Source = Excel.Workbook(
File.Contents(“C:\Sales.xlsx”)
),
Sales = Source{[Item=”Sales”]}[Data],
#”Changed Type” =
Table.TransformColumnTypes(
Sales,
{
{“Date”, type date},
{“Amount”, Currency.Type}
}
)
in
#”Changed Type”
Every M query contains two sections:
let
transformations
in
final_result
The let section defines a series of transformation steps.
The in statement determines which step should be returned as the final result.
Understanding this structure allows developers to move beyond simple point-and-click transformations and build sophisticated solutions.
Why Learn M?
The Power Query user interface handles most day-to-day scenarios.
However, M unlocks possibilities that are impossible or cumbersome through the graphical interface alone.
Learning M enables you to:
✅ Create reusable functions
✅ Build dynamic solutions
✅ Automate complex workflows
✅ Process APIs
✅ Generate tables programmatically
✅ Improve maintainability
✅ Reduce manual intervention
A Practical Example: Combining Hundreds of Files
Imagine a business generates one Excel file every month in the same folder ‘Sales”:
Sales_Jan.xlsx – Sales_Feb.xlsx – Sales_Mar.xlsx – …
Many users import each file manually.
A Power Query developer takes a different approach using:
Folder.Files(
“C:\Sales”
)
Power Query automatically discovers every file in the folder.
After defining the transformation once:
- New files can simply be added
- No modifications are required
- Refresh updates everything automatically
This same folder-based approach is widely used for SharePoint folders, OneDrive folders, and network locations.
Building Reusable Business Logic with Functions
One of M’s most powerful features is the ability to create custom functions.
Suppose customer names need consistent formatting.
Instead of repeating:
Text.Proper(
Text.Trim(CustomerName)
)
across multiple queries, create a function:
(CustomerName as text) =>
Text.Proper(
Text.Trim(CustomerName)
)
and call it whenever needed:
fxCleanCustomer([Customer])
This dramatically improves consistency and maintainability.
Creating Calendar Tables Using M
Most Power BI developers generate date tables using DAX:
CALENDAR(
DATE(2020,1,1),
DATE(2030,12,31)
)
However, M can create a calendar before data even reaches the model.
let
StartDate = #date(2020,1,1),
EndDate = #date(2030,12,31),
Dates =
List.Dates(
StartDate,
Duration.Days(
EndDate – StartDate
) + 1,
#duration(1,0,0,0)
),
Calendar =
Table.FromList(Dates)
in
Calendar
Advantages include:
- Smaller semantic models
- Refresh-time generation
- Reuse across Dataflows and Fabric environments
Essential M Functions Every Developer Should Know
Text Functions
Text.Upper() – Text.Lower() – Text.Trim() – Text.Replace() – Text.Split()
Date Functions
Date.Year() – Date.Month() – Date.Day() – Date.AddDays() – Date.StartOfMonth()
Table Functions
Table.AddColumn() – Table.SelectRows() – Table.Group() – Table.Sort() – Table.Join()
List Functions
List.Sum() – List.Max() – List.Min() – List.Generate()
These categories cover the majority of advanced business transformation scenarios.
The Hidden Superpower: List.Generate()
One of the most underappreciated M functions is:
List.Generate()
A simple example:
List.Generate(
()=>1,
each _ <= 12,
each _ + 1
)
Produces:
1
2
…
12
This concept can be extended to:
- Generate complex fiscal calendars
- Iterate through paginated APIs
- Create simulation data
- Build recurring date structures
For advanced Power Query developers List.Generate() often becomes a game changer.
Power Query Best Practices
Experience shows that a few principles consistently improve Power BI performance and maintainability.
- Load Only What You Need
- Avoid importing unnecessary data.
- Smaller models refresh faster and perform bette
- Use Correct Data Types
- Always verify dates, numbers, and text values.
- Incorrect data types are a major cause of refresh and performance issues.
- Use Parameters
Parameters make solutions reusable across:- Development
- Test
- Production
environments.
- Use Functions to Avoid Repetition
Avoid copying the same transformation logic across multiple queries, consider creating a custom function instead. - Group Queries
Organise queries into logical folders to improve maintainability. - Create Staging Queries
Staging layers reduce duplication and simplify troubleshooting. - Push Work Upstream
Perform filtering, aggregation, and cleanup as close to the source system as possible.
The less Power BI has to process, the better.
M versus DAX: Understanding the Difference
A common question is:
Should I use M or DAX?
The answer is simple.
| Task | Preferred Tool |
|---|---|
| Data cleaning | M |
| Importing files | M |
| API connectivity | M |
| Data transformation | M |
| Measures | DAX |
| Time intelligence | DAX |
| Rankings | DAX |
| Business calculations | DAX |
A rule I often share is:
“Transform with M – Analyse with DAX.”
The more data preparation work that is completed in Power Query, the cleaner and more performant your semantic model becomes.
Conclusion
Power Query is often described as the heart of Power BI, while DAX is its brain.
Power Query retrieves, shapes, cleans, combines, and prepares data from virtually any source before it enters the model. M language provides the flexibility and power needed to automate complex transformations, connect to APIs, create reusable functions, build custom calendars, and engineer enterprise-ready data solutions.
Many Power BI developers spend years focusing exclusively on DAX.
The most effective developers, however, learn both.
Because while DAX answers business questions, M solves the data challenges that stand in the way of answering them.
Master Power Query. Learn M language. And you’ll discover that some of the most powerful capabilities in Power BI exist long before the first visual is ever created.
From Messy M Code to Readable Solutions – Use the Power Query Formatter
As your Power Query projects grow, the generated M code can quickly become difficult to read. Nested functions, long parameter lists, and multiple transformation steps often result in code that is technically correct but challenging to maintain.
This is where Power Query Formatter becomes an invaluable tool => https://www.powerqueryformatter.com/formatter
From
To
For more info see
