Power Query Fundamentals & Data Profiling
Power Query is the most valuable thing in Power BI and the part beginners are most tempted to rush. It is a recorder: every click becomes a step, and the sequence of steps becomes a recipe that runs identically on every refresh, forever. This lesson covers what it is, how the editor works, and how to look at data properly before you touch it.
Learning Objective
The single most important habit in Power Query is looking before fixing. Data profiling shows you what is actually in a column, and it takes one click to turn on.
- Explain what Power Query is and why the Applied Steps list matters
- Navigate the Query Editor's four regions confidently
- Turn on and read column quality, distribution and profile
- Set profiling to the entire dataset rather than a 1,000-row sample
- Produce a written data-quality assessment before writing a single transformation
Why This Matters
Two ideas make Power Query worth taking seriously.
It is repeatable. Cleaning data in Excel is a one-off act — do it again next month, by hand, and hope you remember the steps. Power Query records what you did. Next month's file goes through the identical sequence with no human involvement and no opportunity to make a different mistake.
It is inspectable. The Applied Steps list is a readable audit trail of every decision made about the data. When someone asks "why is this number different from the source?", the answer is in that list. Spreadsheet cleaning leaves no such record.
There is also a career point. In interviews for analyst roles, Power Query competence is tested more often than DAX, because it is where most real project time goes and because it is harder to fake.
Concept
What Power Query is
Power Query is a data connection and transformation engine. It appears inside Power BI Desktop, Excel, and dataflows in the service. Its language is called M, though you can go a long way without writing any.
The mental model that matters: Power Query does not change your source. It reads the source, applies your recorded steps in order, and hands the result to the model. The original file is untouched. This means you can experiment freely — the worst case is deleting a step.
The Query Editor
Home › Transform data opens it. Four regions:
| Region | Where | What it holds |
|---|---|---|
| Queries pane | Left | Every query in the file, plus parameters, functions and groups |
| Data preview | Centre | A preview of the current query's output at the selected step. A preview, not the whole table — this matters |
| Query Settings | Right | Properties (the query name) and Applied Steps — the recipe |
| Ribbon | Top | Home, Transform, Add Column, View, Tools. Most operations also appear on right-click menus |
Applied Steps
The heart of Power Query. Every action adds a step. Steps run top to bottom, each taking the previous step's output as input.
- Click any step to see the data as it was at that point. This is how you debug — find the step where the data first looks wrong.
- Delete a step with the × icon. Later steps that referenced it will error, which is informative.
- Reorder steps by dragging, where the logic allows. This is how you fix folding problems (Module 2 Lesson 3).
- Rename a step by double-clicking. Do this always — it turns the list into documentation.
- Insert a step by selecting the one before it and performing an action.
Changed Type, Filtered Rows, Filtered Rows1. Renamed to Set types with UK locale, Remove cancelled orders, Remove test accounts, the same list becomes a plain-English explanation of everything done to the data.It costs seconds per step and it is the single clearest signal of a professional query. Reviewers notice immediately.
The preview is a preview
This causes real confusion. The data preview shows a subset — typically the first 1,000 rows. Consequences:
- A column can look clean in the preview and contain errors further down.
- Row counts shown while editing are not necessarily the full count.
- Type detection, if left to Power BI, is inferred from the preview.
This is why the profiling settings below matter so much.
Data profiling
Three tools on the View ribbon tab, off by default, that should be on always.
| Tool | Shows | What you learn |
|---|---|---|
| Column quality | Valid / Error / Empty percentages under each column header | Whether a column has type errors or nulls, at a glance. The highest-value one |
| Column distribution | A small bar chart plus distinct and unique counts | Cardinality, and whether a supposed key is actually unique |
| Column profile | A detailed panel: min, max, average, standard deviation, value distribution | Outliers, unexpected ranges, and the real spread of values |
Without this, you are profiling a sample. A column can show 100% valid across the first thousand rows and be 8% errors overall. On very large tables this setting makes profiling slow — but a slow honest answer beats a fast misleading one.
Distinct versus unique
Column distribution reports both, and the difference is not obvious:
- Distinct — how many different values exist.
- Unique — how many values appear exactly once.
For a genuine primary key, distinct = unique = row count. If distinct equals the row count but unique is lower, you have duplicates. This single check is the fastest way to validate a key, and it is why Module 4 depends on this lesson.
Visual Explanation
Below is the actual first page of bwb-messy-customers.csv as it loads. Every problem in it is real — this is the file you will clean across this module.
| CustomerID | FullName | Country | SignupDate | LifetimeRevenue | Status | |
|---|---|---|---|---|---|---|
| CUST0043 | Tariq Weber | us | 23-Sep-2024 | 9,692.90 | active | |
| CUST0056 | Zoya Khan | zoya.khan@mail.co | AUS | 31.07.2025 | 8277.17 | Active |
| CUST0198 | Beatriz Costa | beatriz.costa@mail.co | CA | 25/01/2024 | 34210.64 | ACTIVE |
| CUST0151 | Vera Olsen | Canada | 08/07/2024 | 17240.96 | Churned | |
| CUST0020 | Gabriel Malik | gabriel.malik@example.com | australia | 2024-02-09 | 31349.40 | inactive |
| CUST0088 | Tariq Rossi | tariq.rossi@mail.co | DE | 25.05.2023 | 7,754.90 | inactive |
| CUST0005 | Chen Costa | chen.costa@mail.co | PAK | 2023-12-05 | 35830.04 | Churned |
| CUST0005 | Chen Costa | chen.costa@mail.co | PAK | 2023-12-05 | 35830.04 | Churned |
Read across the columns and count the distinct problems:
- Email — two blanks in eight rows.
- Country —
us,AUS,CA,Canada,australia,DE,PAK. Seven spellings, five countries, in eight rows. - SignupDate —
23-Sep-2024,31.07.2025,25/01/2024,2024-02-09. Four formats. - LifetimeRevenue —
9,692.90has a thousands separator,8277.17does not. It will load as text. - FullName —
Gabriel Malikhas a double space and a trailing space. - Status —
active,Active,ACTIVE,inactive,Churned. Case chaos. - Row 8 is an exact duplicate of row 7.
None of this is exaggerated for teaching. This is what an export that combines two systems actually looks like.
What profiling tells you before you touch anything
| Column | Valid | Error | Empty | Distinct | What it tells you |
|---|---|---|---|---|---|
| CustomerID | 100% | 0% | 0% | 260 | 260 distinct in 280 rows — 20 duplicates |
| FullName | 100% | 0% | 0% | ~250 | Whitespace and case variants inflate the count |
| 90% | 0% | 10% | — | 28 blanks — a real gap, not an error | |
| Country | 100% | 0% | 0% | 25 | 25 spellings for 6 countries |
| SignupDate | 100% | 0% | 0% | — | Loads as text — mixed formats defeat detection |
| LifetimeRevenue | — | — | 6% | — | Loads as text — currency symbols and separators |
| Status | 100% | 0% | 0% | 7 | 7 spellings for 3 states |
The problems are semantic: 25 spellings that should be 6, dates that should be dates, statuses that should be 3. Profiling finds structural problems; you find meaning problems. Column distribution's distinct count is what exposes them, which is why you look at all three tools rather than just quality.
Power BI Demonstration
bwb-messy-customers.csv → Transform Data.Profile completely, write down what you found, decide the approach, then transform. It is slower for ten minutes and faster for the rest of the project.
Example Dataset
This is the file every lesson in Module 3 uses. It is deliberately dirty — the faults in it are the ones that actually arrive in real exports. Download it now.
bwb-messy-customers.csv26 KB · 280 rows · 8 columns · CSV
Columns & data types
| Column | Data type | Meaning |
|---|---|---|
CustomerID | Text | Intended to be unique — it is not. Duplicates are your first clue. |
FullName | Text | Some entries have double spaces, trailing spaces, or ALL CAPS. |
Email | Text | Blank in some rows, malformed in others, whitespace-padded in others. |
Country | Text | 25 distinct spellings for 6 real countries. |
SignupDate | Text | Loads as text because five date formats are mixed together. |
LifetimeRevenue | Text | Loads as text: currency symbols and thousands separators present. |
Phone | Text | Four different formatting conventions. |
Status | Text | 7 spellings for 3 real states (Active, Inactive, Churned). |
Questions this dataset can answer
- How many genuinely distinct customers are in this file?
- What is total lifetime revenue once the column is numeric?
- How many customers are Active once status is standardised?
- Which rows should be rejected rather than repaired, and why?
Step-by-Step Exercise
Produce a written data-quality assessment. No transformations — just looking, and recording.
Expected Result
A completed assessment
| Column | Loaded as | Empty | Distinct | Problem |
|---|---|---|---|---|
| CustomerID | Text | 0% | 260 | Not unique — 20 duplicate values in 280 rows |
| FullName | Text | 0% | ~250 | Whitespace and case variants create false distinctness |
| Text | 10% | — | 28 blanks; some malformed; some whitespace-padded | |
| Country | Text | 0% | 25 | 25 spellings for 6 countries |
| SignupDate | Text | 0% | — | 5 date formats mixed — cannot be typed as-is |
| LifetimeRevenue | Text | 6% | — | Currency symbols and thousands separators |
| Phone | Text | 0% | — | 4 formatting conventions — cosmetic only |
| Status | Text | 0% | 7 | 7 spellings for 3 states |
Ranked by severity
| Rank | Problem | Consequence if unfixed |
|---|---|---|
| 1 | Duplicate CustomerIDs | Breaks the data model. A dimension key must be unique — the relationship cannot be created at all, or it double-counts |
| 2 | LifetimeRevenue as text | Cannot be aggregated. Any revenue analysis is impossible |
| 3 | SignupDate as text | No time analysis. No cohorts, no trends, no time intelligence |
| 4 | 25 country spellings | Wrong numbers. Revenue splits across 4 versions of 'United Kingdom' and every country total is understated |
| 5 | 7 status spellings | Same problem — 'Active' and 'active' count as different states |
| 6 | Blank emails | A real gap. May be acceptable — depends on what the report needs |
| 7 | Name whitespace, phone formats | Cosmetic. Fix for tidiness, not correctness |
Knowing the difference tells you where to spend your time, and it tells you what to say when someone asks whether the data is "clean enough" — a question that has no answer without knowing what it is for.
Common Mistakes
Fix: Change it in the status bar to entire data set, every time. Make it the first thing you do after opening the editor.
Fix: Use column distribution and profile as well. The distinct count is what exposes semantic problems.
Fix: Profile completely, write the assessment down, decide the approach, then transform. Ten minutes slower once; faster for the rest of the project.
CustomerID is unique because of its name. Here it is not, and the relationship will fail in Module 4 with a confusing message.Fix: Distinct versus unique versus row count, on every intended key, every time. Five seconds.
Filtered Rows2 tells the next person nothing about why rows were removed — including you in six months.Fix: Rename every step to describe intent. It is the cheapest documentation available.
Professional Tip
Three reasons this is worth ten minutes. It stops you fixing things in a haphazard order. It gives you the cleaning log that Module 14 says every portfolio project needs — written at the moment you actually know the answers, rather than reconstructed later. And when someone questions a number six months on, you can show them exactly what the source contained and what you did about it.
Analysts who do this look organised. They are just writing things down.
One more habit:
- Keep an untouched reference query. Duplicate the source query, name it
_Raw, disable its load, and never transform it. When a number looks wrong you can compare against the untransformed data inside the same file, without going back to the source.
Knowledge Check
Knowledge Check
5 questions. Answer each one to see why the right answer is right.
Column quality shows 100% valid for a Country column containing 25 different spellings of 6 countries. Why?
A column has 260 distinct values, 240 unique values, and the table has 280 rows. What does that tell you?
Why do the SignupDate and LifetimeRevenue columns load as text?
What should you change in the status bar immediately after opening Power Query?
In the messy customer file, which problem would you rank as most severe?
Practical Challenge
Produce a data-quality report someone else could act on
Profile bwb-messy-customers.csv completely and write a data-quality report you could send to whoever owns the source system. It should tell them what is wrong, why it matters, and what you want them to change — without you doing any cleaning.
- A summary line: row count, column count, and a one-sentence verdict
- A column table with loaded type, empty %, distinct count and the problem
- Problems ranked by severity, with the consequence of each stated
- A clear separation between problems you can fix downstream and problems only the source owner can fix
- Three specific requests to the source owner, phrased so a non-technical person could act on them
- One sentence on what you will do in the meantime
The distinction in requirement four is the substance of this challenge. Ask of each problem: can I reliably repair this from the data I have, or am I guessing?
- Case inconsistency in Status — you can fix that reliably. Three states, obvious mapping.
- 25 country spellings — you can fix that, though it needs a mapping you must maintain.
- Blank emails — you cannot fix that. The data is not there.
- Duplicate CustomerIDs — you can remove them, but why they exist is a source question, and the answer changes what you should do.
Solution
Try the challenge properly before opening this. Reading a solution you have not attempted feels like learning, but it is not.
A model data-quality report
Verdict: the file is usable for reporting after cleaning, but three defects should be fixed at source because downstream repair either loses information or requires assumptions I cannot verify.
Column detail
| Column | Type | Empty | Distinct | Problem |
|---|---|---|---|---|
| CustomerID | Text | 0% | 260 | 20 duplicate values |
| FullName | Text | 0% | ~250 | Double spaces, trailing spaces, inconsistent case |
| Text | 10% | — | 28 blank; some malformed; some padded with whitespace | |
| Country | Text | 0% | 25 | 25 spellings for 6 countries |
| SignupDate | Text | 0% | — | 5 mixed date formats |
| LifetimeRevenue | Text | 6% | — | Currency symbols and thousands separators |
| Phone | Text | 0% | — | 4 formatting conventions |
| Status | Text | 0% | 7 | 7 spellings for 3 states |
What I can fix, and what I cannot
| Problem | Fixable downstream? | Reasoning |
|---|---|---|
| Status case inconsistency | Yes, reliably | Three states, unambiguous mapping. Lowercase and map |
| Name whitespace and case | Yes, reliably | Trim, clean, proper-case. No information lost |
| Phone formats | Yes | Cosmetic. Standardise or leave as-is depending on need |
| Country spellings | Yes, but with a maintained mapping | I can map all 25 current spellings. A new spelling next month will pass through unmapped unless I add a check |
| Date formats | Yes, with an assumption | Most are unambiguous. 03/04/2025-style values are genuinely ambiguous — I must assume a convention, and if I assume wrong the error is silent |
| Revenue as text | Yes | Strip symbols and separators, then type with an explicit locale |
| Blank emails | No | The data does not exist. I can flag or exclude, but I cannot create it |
| Duplicate CustomerIDs | Not properly | I can deduplicate, but I do not know why they exist. If they are a double-export, removing them is correct. If two real customers were assigned the same ID, removing them deletes a customer. These need opposite treatments and I cannot tell them apart |
Three requests to the source owner
2. Please export dates in a single consistent format — ideally YYYY-MM-DD. The file currently mixes five formats. Some values, such as 03/04/2025, could mean either 3 April or 4 March, and I have no way to tell which. A consistent format removes the guesswork entirely.
3. Please export the revenue figures as plain numbers, without currency symbols or thousands separators. They currently arrive as text such as "$9,692.90", which has to be converted before it can be added up. Plain numbers would remove a step and a risk.
What I will do in the meantime
"I will build the cleaning into the report so it runs automatically on every refresh, and treat the duplicate IDs as an export error for now — but I will add a check that flags any new country spellings or duplicate IDs, so we find out when something changes rather than discovering it in a wrong number."
Why this report is good
It separates what I can fix from what I am guessing about, and it is honest that the duplicate IDs are the latter. A less careful analyst would just deduplicate and move on — and would be right most of the time, and catastrophically wrong occasionally, with no way to know which.
The three requests are also phrased so that someone in an operations role can act on them without understanding Power Query. Requests that require the reader to understand your tooling do not get actioned.
And the final sentence commits to a monitoring check rather than a one-off fix — which is what makes this a pipeline rather than a clean-up.
Lesson Summary
- Power Query is a recorder. Every action becomes a step; the sequence runs identically on every refresh. It never modifies your source.
- Applied Steps is the recipe and the audit trail. Click any step to see the data at that point — this is how you debug.
- Rename every step to describe intent. It is the cheapest documentation there is.
- Turn on column quality, distribution and profile, and change profiling to the entire dataset.
- Quality checks type validity, not meaning. 25 spellings of 6 countries all read as 100% valid.
- For a genuine key, distinct = unique = row count. This is the fastest key validation there is.
- Profile completely before transforming anything. Write the assessment, then clean.
- Separate what you can fix reliably from what you are assuming — and escalate the second category.
Syllabus topics delivered
Progress
Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.