Home Course home Curriculum Learner dashboard Skill tree Business projects Datasets Final project
🧹 Module 3 · Power Query & Data Transformation Beginner LESSON 16 / 72

Power Query Fundamentals & Data Profiling

30 min 5 quiz questions 1 practical challenge 5 syllabus topics

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.

Lesson 16 of 72 · Power Query & Data Transformation 22%
01

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.

By the end of this lesson you will be able to
  • 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
02

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.

The consequence for how you work
Because it is repeatable and inspectable, Power Query rewards care taken once. Ten extra minutes spent profiling and naming steps properly is paid back every month for years, and it is the difference between a model someone can inherit and one that has to be reverse-engineered.

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.

03

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:

The editor's regions
RegionWhereWhat it holds
Queries paneLeftEvery query in the file, plus parameters, functions and groups
Data previewCentreA preview of the current query's output at the selected step. A preview, not the whole table — this matters
Query SettingsRightProperties (the query name) and Applied Steps — the recipe
RibbonTopHome, 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.
Rename every step you create
Power Query names steps 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.

The three profiling tools
ToolShowsWhat you learn
Column qualityValid / Error / Empty percentages under each column headerWhether a column has type errors or nulls, at a glance. The highest-value one
Column distributionA small bar chart plus distinct and unique countsCardinality, and whether a supposed key is actually unique
Column profileA detailed panel: min, max, average, standard deviation, value distributionOutliers, unexpected ranges, and the real spread of values
Change profiling to the entire dataset
At the bottom of the editor window, the status bar says "Column profiling based on top 1000 rows". Click it and change to "based on entire data set".

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.

04

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.

Raw load — before any cleaning
CustomerIDFullNameEmailCountrySignupDateLifetimeRevenueStatus
CUST0043Tariq Weberus23-Sep-20249,692.90active
CUST0056Zoya Khanzoya.khan@mail.coAUS31.07.20258277.17Active
CUST0198Beatriz Costabeatriz.costa@mail.coCA25/01/202434210.64ACTIVE
CUST0151Vera OlsenCanada08/07/202417240.96Churned
CUST0020Gabriel Malik gabriel.malik@example.comaustralia 2024-02-0931349.40inactive
CUST0088Tariq Rossitariq.rossi@mail.coDE25.05.20237,754.90inactive
CUST0005Chen Costachen.costa@mail.coPAK2023-12-0535830.04Churned
CUST0005Chen Costachen.costa@mail.coPAK2023-12-0535830.04Churned
Missing / duplicate rowInconsistent formatting

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.90 has a thousands separator, 8277.17 does not. It will load as text.
  • FullName — Gabriel  Malik  has a double space and a trailing space.
  • Status — active, Active, ACTIVE, inactive, Churned. Case chaos.
  • Row 8 is an exact duplicate of row 7.
Across the whole file
That is one screen. In the complete file there are 280 rows containing 25 different spellings of 6 countries, 20 duplicate CustomerID values, 28 blank emails, 17 blank revenue values and 7 spellings of 3 statuses.

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 quality on the full file
ColumnValidErrorEmptyDistinctWhat it tells you
CustomerID100%0%0%260260 distinct in 280 rows — 20 duplicates
FullName100%0%0%~250Whitespace and case variants inflate the count
Email90%0%10%—28 blanks — a real gap, not an error
Country100%0%0%2525 spellings for 6 countries
SignupDate100%0%0%—Loads as text — mixed formats defeat detection
LifetimeRevenue——6%—Loads as text — currency symbols and separators
Status100%0%0%77 spellings for 3 states
Notice what profiling did NOT flag
Column quality shows 100% valid for Country, SignupDate and Status. Nothing is technically wrong — they are all valid text.

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.
05

Power BI Demonstration

Load the messy file
Home › Get data › Text/CSV → bwb-messy-customers.csv → Transform Data.
Turn on all three profiling tools
View tab → tick Column quality, Column distribution and Column profile.
Change profiling to the entire dataset
Click the text in the status bar at the bottom that says "Column profiling based on top 1000 rows" and switch it to entire data set. On this file it is instant.
Read Column quality across every column
Note the Empty percentages on Email and LifetimeRevenue. Note that everything else reads 100% valid — which, as section 04 explained, does not mean clean.
Check CustomerID for uniqueness
Click the CustomerID column. In the distribution panel, compare Distinct against Unique and against the row count. Distinct 260, unique lower, rows 280 — that gap is your duplicates.
Inspect Country's distinct values
Select the column and open Column profile. The value distribution lists every distinct value with its count. Scroll it. This is the clearest possible view of the 25-spellings problem.
Do the same for Status
7 distinct values that should be 3. Note which spellings are common and which are rare — that shapes how you fix them.
Look at the Changed Type step
Click it in Applied Steps and read the formula bar. Note which columns Power BI typed and which it left as text. SignupDate and LifetimeRevenue stayed text — mixed formats defeated detection, which is Power Query being honest rather than failing.
Click through the steps
Select Source, then Promoted Headers, then Changed Type. Watch the preview change at each. This is the debugging technique for every problem you will meet.
Rename the existing steps
Double-click Changed Type and rename it to something meaningful. Practise the habit now, before there are twenty steps.
Write down what you found — do not fix anything yet
Resist the urge. The next lesson does the fixing. This lesson is about looking.
ViewColumn qualityColumn distributionColumn profile
Why 'do not fix anything yet' is a real instruction
The instinct is to fix each problem as you spot it. That produces a query where the step order reflects the order you noticed things, not the order that makes sense — and it means you make decisions about individual columns before you understand the file as a whole.

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.
06

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.csv
26 KB · 280 rows · 8 columns · CSV
Business context
A deliberately dirty extract — the kind of file that actually lands in your inbox. It combines two exports, so it contains duplicate rows and duplicate customer IDs. Country is spelled 25 different ways for 6 real countries. Dates use five different formats. Revenue mixes currency symbols, thousands separators and blanks. Status has 7 spellings for 3 real states. Emails are missing, malformed or padded with whitespace. Every Power Query lesson uses this file.

Columns & data types

ColumnData typeMeaning
CustomerIDTextIntended to be unique — it is not. Duplicates are your first clue.
FullNameTextSome entries have double spaces, trailing spaces, or ALL CAPS.
EmailTextBlank in some rows, malformed in others, whitespace-padded in others.
CountryText25 distinct spellings for 6 real countries.
SignupDateTextLoads as text because five date formats are mixed together.
LifetimeRevenueTextLoads as text: currency symbols and thousands separators present.
PhoneTextFour different formatting conventions.
StatusText7 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?
07

Step-by-Step Exercise

Produce a written data-quality assessment. No transformations — just looking, and recording.

Load and profile
Load the messy file, turn on all three profiling tools, set profiling to the entire dataset.
Record row and column counts
Note both, and compare the row count against the source file's line count minus one.
For every column, record four things
Data type as loaded · Empty percentage · Distinct count · Unique count.
Identify the intended key
Which column is meant to be unique? Verify it with distinct versus unique. Record the gap.
List every column loaded as text that should not be
And note why Power BI could not type it.
Count the semantic problems profiling did not flag
For Country and Status, list every distinct value and group them by what they should be.
Rank the problems by severity
Which would produce a wrong number if left unfixed? Which are merely untidy? These are different and should be treated differently.
08

Expected Result

A completed assessment

Column-by-column
ColumnLoaded asEmptyDistinctProblem
CustomerIDText0%260Not unique — 20 duplicate values in 280 rows
FullNameText0%~250Whitespace and case variants create false distinctness
EmailText10%—28 blanks; some malformed; some whitespace-padded
CountryText0%2525 spellings for 6 countries
SignupDateText0%—5 date formats mixed — cannot be typed as-is
LifetimeRevenueText6%—Currency symbols and thousands separators
PhoneText0%—4 formatting conventions — cosmetic only
StatusText0%77 spellings for 3 states

Ranked by severity

RankProblemConsequence if unfixed
1Duplicate CustomerIDsBreaks the data model. A dimension key must be unique — the relationship cannot be created at all, or it double-counts
2LifetimeRevenue as textCannot be aggregated. Any revenue analysis is impossible
3SignupDate as textNo time analysis. No cohorts, no trends, no time intelligence
425 country spellingsWrong numbers. Revenue splits across 4 versions of 'United Kingdom' and every country total is understated
57 status spellingsSame problem — 'Active' and 'active' count as different states
6Blank emailsA real gap. May be acceptable — depends on what the report needs
7Name whitespace, phone formatsCosmetic. Fix for tidiness, not correctness
The ranking is the valuable part
Problems 1–3 make analysis impossible. Problems 4–5 make it quietly wrong, which is worse in practice. Problems 6–7 are untidy but harmless.

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.
09

Common Mistakes

Leaving profiling on the top 1,000 rows
The default. A column shows 100% valid on the sample and is 8% errors overall. You discover this after building the report.

Fix: Change it in the status bar to entire data set, every time. Make it the first thing you do after opening the editor.
Trusting 'Valid 100%' to mean clean
Column quality checks type validity, not meaning. 25 spellings of 6 countries are all perfectly valid text.

Fix: Use column distribution and profile as well. The distinct count is what exposes semantic problems.
Fixing problems as you spot them
Produces a query whose step order reflects the order you noticed things. Harder to read, harder to reorder for folding, and you decide about individual columns before understanding the file as a whole.

Fix: Profile completely, write the assessment down, decide the approach, then transform. Ten minutes slower once; faster for the rest of the project.
Not checking whether the key is actually unique
Assuming a column called 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.
Leaving steps with default names
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.
10

Professional Tip

Write the assessment before you write the query
Before any transformation, produce the table from section 08: every column, its loaded type, its empty percentage, its distinct count, and the problem.

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.
11

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?

12

Practical Challenge

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.

Requirements
  • 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.
13

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

Summary
File: bwb-messy-customers.csv · 280 rows · 8 columns

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

ColumnTypeEmptyDistinctProblem
CustomerIDText0%26020 duplicate values
FullNameText0%~250Double spaces, trailing spaces, inconsistent case
EmailText10%—28 blank; some malformed; some padded with whitespace
CountryText0%2525 spellings for 6 countries
SignupDateText0%—5 mixed date formats
LifetimeRevenueText6%—Currency symbols and thousands separators
PhoneText0%—4 formatting conventions
StatusText0%77 spellings for 3 states

What I can fix, and what I cannot

ProblemFixable downstream?Reasoning
Status case inconsistencyYes, reliablyThree states, unambiguous mapping. Lowercase and map
Name whitespace and caseYes, reliablyTrim, clean, proper-case. No information lost
Phone formatsYesCosmetic. Standardise or leave as-is depending on need
Country spellingsYes, but with a maintained mappingI can map all 25 current spellings. A new spelling next month will pass through unmapped unless I add a check
Date formatsYes, with an assumptionMost 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 textYesStrip symbols and separators, then type with an explicit locale
Blank emailsNoThe data does not exist. I can flag or exclude, but I cannot create it
Duplicate CustomerIDsNot properlyI 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

Phrased for a non-technical reader
1. Please confirm whether the duplicate customer IDs are an export error or two real customers sharing an ID. I have found 20 IDs appearing more than once. If this is the export running twice, I will remove the repeats. If two different customers genuinely have the same ID, removing them would delete a customer from the reporting — so I need to know before I do anything.

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.

14

Lesson Summary

Key takeaways
  • 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

What is Power Query?Query EditorLoading dataData profilingApplied Steps
15

Progress

Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.

Lesson complete — 22% of Power BI Mastery done
Progress saved. Keep the momentum: the next lesson builds directly on this one.