The PL-300 exam objective 'Profile data to understand its structure and quality' is the foundation of every data analysis project you will ever do. Data profiling and cleaning in Power Query is the step where you look at your data with a critical eye, find the problems, and fix them before you build a single chart or measure. This chapter will teach you how to inspect data like a detective, spot issues beginners miss, and clean it efficiently.
Jump to a section
A simple way to picture Data Profiling and Cleaning in Power Query
A house organiser who specialises in decluttering and preparing homes for sale. They arrive at a client's house that has been lived in for twenty years. The organiser's first task is not to clean anything. They open every cupboard, every drawer, every box in the attic. They run their fingers along shelves to feel for dust. They flip through papers in filing cabinets and note which labels are faded and which folders are empty. They look at the pile of mail on the kitchen counter and separate bills from junk flyers. They check the fridge: is that yoghurt past its expiry date? Is that open jar of pasta sauce starting to grow mould? The organiser makes a detailed inventory of everything they find — the number of items, their condition, their location, and any obvious problems. This step is called profiling. Only after they have a full picture of what is in the house do they start the actual cleaning. They throw away expired food, shred old bank statements, donate clothes that no longer fit, and wipe down sticky surfaces. They repair small cracks in the wall and replace missing lightbulbs. Every action is guided by the inventory they created.
This is exactly what a data analyst does when they profile and clean data in Power Query. The house is your dataset. The organiser is Power Query's profiling tools. The inventory is the column quality, column distribution, and column profile statistics. The cleaning steps are the transformations you apply — removing rows, replacing values, changing data types. You never start cleaning before you understand what is dirty. You never assume a column contains dates until you check that every row actually contains a valid date. You never trust that a column labelled 'Phone Number' actually contains phone numbers. You inspect first, then act.
Data profiling is the process of examining your data to understand its structure, content, and quality before you start analysing it. In Power Query, this happens inside Power Query Editor, a separate tool that opens when you connect to a data source in Power BI Desktop. Think of Power Query Editor as your preparation kitchen: you do not serve raw ingredients directly to guests — you wash them, chop them, and cook them first. Similarly, you do not load raw data directly into Power BI’s data model — you clean and shape it in Power Query first.
The profiling tools in Power Query are grouped into three options in the View ribbon tab: Column Quality, Column Distribution, and Column Profile. Each gives you a different layer of understanding about your data.
Column Quality shows you three percentages at the top of each column: Valid, Error, and Empty. Valid means the value looks correct for that column's data type. Error means Power Query tried to read the value but could not interpret it as the expected type — for example, the text 'thirty' in a column set to Whole Number. Empty means the cell has no value at all; it is either null or an empty string. This is often the first thing a beginner checks, and it is a fast way to spot columns with serious problems.
Column Distribution shows a small bar chart under each column header. The bars represent how many times each distinct value appears. For a column like 'Country', you might see bars for 'USA', 'Canada', 'Mexico', with the tallest bar being the most frequent value. This helps you spot values that look wrong — for example, 'U.S.A.' next to 'USA' when they should be the same. It also helps you find outliers like a single row that says 'Atlantis'.
Column Profile is the most powerful tool. It adds a full pane at the bottom of Power Query Editor that shows detailed statistics for the selected column: minimum value, maximum value, count of distinct values, count of nulls, and a complete value distribution table. You can see exactly which values appear, how many times, and as what percentage of total rows. This is what you use when you need to understand a column deeply, such as checking whether a 'Date' column actually contains dates from the expected range or whether a 'Price' column has negative numbers that should not exist.
Cleaning is the next stage after profiling. Once you know what is wrong, you use Power Query’s transform tools to fix it. Common cleaning operations include:
Removing empty rows or replacing nulls with a default value
Changing data types — forcing 'Text' to 'Whole Number' or 'Date'
Replacing specific values — for example, replacing 'N/A' with null, or 'U.S.A' with 'USA'
Splitting columns that contain multiple pieces of information, like a full name that should be first name and last name separately
Merging columns that are split across multiple fields
Removing duplicate rows based on a key column
Filtering out rows that fail validation, such as negative ages or future birth dates
Each transform you apply appears as a step in the Applied Steps pane on the right side of Power Query Editor. These steps run in order every time your data refreshes. You can rename them, reorder them, delete them, or edit them without breaking the rest of your logic. This is what makes Power Query deterministic: give it the same inputs, it always produces the same outputs.
Data profiling and cleaning are not optional. If you skip profiling, you will build reports on dirty data, and every chart will be unreliable. The PL-300 exam tests whether you know how to use these tools effectively, and that is why Microsoft made profiling a dedicated objective in the exam syllabus.
Load data into Power Query Editor
In Power BI Desktop, click Get Data and select your source. After choosing your tables or files, Power Query Editor opens. This is where all profiling and cleaning happens. You never skip this step — even if the data looks clean, you must inspect it.
Enable profiling tools from the View ribbon
Go to the View tab in Power Query Editor. Check the boxes for Column Quality, Column Distribution, and Column Profile. These three tools appear above and below your columns. This is your inspection toolkit. Without them you are working blind.
Select a column and examine Column Quality and Column Distribution
Click on a column header. Look at the Column Quality bar at the top — note the percentages of Valid, Error, and Empty. Look at Column Distribution bars just below the header — note which values appear most frequently. This gives you a quick health check of the column.
Open Column Profile for a deep dive
In the View ribbon, toggle on Column Profile. A pane appears at the bottom of Power Query Editor showing detailed statistics for the selected column: min and max values, distinct count, null count, and a full value distribution table. Use this when you need to see every unique value and its frequency.
Apply transforms to fix identified issues
Based on what profiling revealed, use the Transform or Add Column ribbons to apply cleaning operations. For example, use Replace Values to fix typos, Change Type to set the correct data type, Remove Rows to delete blanks, or Split Column to break a combined field into separate columns. Each action becomes a new step in Applied Steps.
Rename and reorder Applied Steps for clarity
In the right-side Applied Steps pane, right-click each step to rename it descriptively (for example, 'Remove blank postal codes'). Drag steps up or down to reorder them if needed. This step is critical for maintainability — the next analyst (or you in six months) must understand your logic.
Close and Apply — check results in the data model
Once all profiling and cleaning is done, click Close and Apply on the Home ribbon. Power Query runs all steps in order and loads the cleaned data into the Power BI data model. You can now build measures and visuals on trustworthy data. If the data refreshes later, the same steps run automatically.
A junior data analyst at a retail company is tasked with building a sales dashboard for the CFO. The data comes from three sources: an Excel spreadsheet exported from the point-of-sale system, a CSV file from the warehouse management system, and a SQL database of customer profiles. The analyst opens Power BI Desktop, clicks 'Get Data', and loads all three sources into Power Query Editor for profiling.
The analyst starts with the Excel sales file. Column Quality immediately shows that the 'Sales Amount' column has 12% errors. The analyst clicks on the column and opens Column Profile. The bottom pane reveals that the errors come from rows where the sales amount contains a currency symbol like '$45.99' instead of just '45.99'. The analyst uses the Replace Values transform to remove the dollar sign, then changes the column data type to Decimal Number. The error percentage drops to zero.
Next, the analyst looks at the CSV warehouse file. Column Distribution shows a column called 'Product ID' with 5,000 distinct values, but the analyst expected only 2,000 products. Column Profile reveals that the same product IDs appear with slight variations — 'PROD-1234' and 'PROD- 1234' (with a trailing space). The analyst uses the Transform > Trim function to remove leading and trailing spaces, then the number of distinct values drops to 2,100. Still too high. The analyst uses Column Profile again and sees that some product IDs have leading zeros missing — '1234' instead of '01234'. The analyst applies Transform > Format > Add Prefix to add a leading zero where the length is fewer than 5 characters. Now the distinct count is exactly 2,000.
The SQL customer data looks clean at first. Column Quality shows 100% valid across all columns. But Column Distribution on the 'Postal Code' column shows a bar for value '99999' with 47 rows. The analyst knows that '99999' is often used as a placeholder for missing or unknown postal codes in the company's database. The analyst replaces '99999' with null, then adds a note in the Applied Steps pane renaming that step 'Fix placeholder postal codes'.
After all profiling and cleaning steps are complete, the analyst checks the Applied Steps pane to confirm the order is correct: removal of duplicates happens before type changes, type changes happen before value replacements, and value replacements happen before filtering. The analyst names each step descriptively so that the next person to touch this query can understand the logic.
When the data refreshes next week with new sales data, all these steps will run automatically. The analyst never has to repeat the manual inspection. That is the power of profiling and cleaning in Power Query: you do the detective work once, and the computer does the cleaning forever.
The PL-300 exam tests data profiling and cleaning in Power Query primarily through case-study questions and scenario-based multiple-choice questions. You will not be asked to perform clicks in a live Power Query Editor during the exam, but you will be shown screenshots of Column Quality, Column Distribution, and Column Profile statistics and asked to interpret them. Expect at least 3-5 questions that directly reference profiling outputs, and another 3-5 that test your understanding of cleaning transforms.
The concept they love to test most is the difference between Column Quality, Column Distribution, and Column Profile. They will show you a screenshot showing Valid, Error, and Empty percentages for a column and ask: 'What is the most appropriate first action?' The correct pattern is always to open Column Profile to see the specific error values before applying a transform. The trap is picking an answer that jumps straight to a cleaning action like 'Replace values' without verifying what the errors actually are.
Another exam favourite is the order of operations in Applied Steps. They will present a sequence of steps and ask whether the order will produce the expected result. The key rule to memorise is: filter before grouping, type change before aggregation, and always profile before cleaning. If a step reorders values that a later step assumes are in a certain order, that is a trap.
Exam topics you must know:
How to enable Column Profile for all columns from the View ribbon (not individually)
The difference between null and empty string (null means no value exists; empty string is a zero-length text value)
The 'Remove Duplicates' transform only keeps the first occurrence of each distinct combination of the selected columns
The 'Keep Top Rows' and 'Keep Bottom Rows' transforms operate on the current sort order of the table
The 'Group By' dialog gives you options for aggregation like Sum, Average, Count Rows, Distinct Count
The 'Replace Errors' transform can only replace errors with a single fixed value — it cannot conditionally replace different errors with different values
The 'Split Column' transform has multiple modes: by delimiter, by number of characters, by positions, and by uppercase/lowercase/letter case transitions
Traps they set:
They will show a column with 5% errors and a correct answer that says 'Open Column Profile to investigate the errors.' The wrong answers will suggest using Merge Queries or adding an index column first.
They will show a column labelled 'Customer Name' with 100% valid quality and ask: 'Does this mean the names are correct?' The correct answer is no — Column Quality only checks data type validity, not whether the name actually belongs to that customer. Validity is not correctness.
They will present a scenario where you need to combine first name and last name from two columns. The correct pattern is Merge Columns, not adding them together with arithmetic.
They will include a question about 'Fuzzy Merge' that involves profiling: you must first profile both columns to see the pattern of typos before you can configure the fuzzy matching threshold.
Key definitions to memorise:
Column Quality: the percentage of Valid, Error, and Empty values in a column
Column Distribution: a bar chart showing the frequency of each distinct value in a column
Column Profile: a full statistics pane showing min, max, count, distinct count, null count, and value distribution
Applied Steps: the sequential list of transform actions applied to a query, visible in the right pane
Data type: the classification of data in a column — text, number, date, boolean, etc.
Transform: any operation that changes the shape, structure, or content of your data
Data profiling always comes before data cleaning: you cannot fix what you have not inspected.
Column Quality checks data type validity, not data correctness — a column can be 100% valid and still full of wrong values.
Column Profile is your detective tool for understanding the exact values, their frequencies, and statistics in any column.
Every transform you apply in Power Query becomes a step in Applied Steps, and these steps run in sequence every time data refreshes.
The order of your Applied Steps matters: later steps depend on the output of earlier steps, and changing the order can break your logic.
Cleaning in Power Query is automated and repeatable: you do the detective work once, and the computer applies the same cleaning forever.
Do not clean data in the source system before importing; import raw data and let Power Query handle all transforms.
Null and empty string are different: null means the absence of a value, an empty string is a zero-length text value that still occupies space in the column.
These come up on the exam all the time. Here's how to tell them apart.
Column Quality
Shows only percentages: Valid, Error, Empty
Appears as a coloured bar above each column header
Quick overview — does not show specific values
Column Profile
Shows full statistics: min, max, distinct count, null count, value distribution
Appears as a pane at the bottom of Power Query Editor for the selected column
Deep dive — shows every unique value and its frequency
Remove Duplicates
Removes duplicate rows from the table based on selected columns
Reduces the number of rows in the table
Keeps the first occurrence of each distinct combination
Group By with Distinct Count
Counts how many distinct values exist in a column per group
Does not remove rows — it creates a summary table
Useful for profiling, not for cleaning duplicates
Replace Values
Replaces any value you specify with another value
Works on valid data — cannot target errors
Useful for standardising text like 'U.S.A' to 'USA'
Replace Errors
Replaces all error values in a column with a fixed value
Only targets error values — cannot replace valid data
Useful for turning type conversion errors into nulls or zeroes
Null
Represents the absence of any value
Counts as Empty in Column Quality statistics
Cannot be used in string concatenation without special handling
Empty String
Represents a text value with zero characters
Counts as Valid in Column Quality if the column is text type
Can be concatenated with other strings — it just adds nothing
Split Column by Delimiter
Divides a column into multiple columns based on a chosen delimiter
Creates as many new columns as there are delimited parts
Preserves all parts of the original value across separate columns
Extract (Text Before Delimiter)
Creates a single new column containing only the text before a delimiter
Discards the text after the delimiter
Useful when you only need the first part of a string
Mistake
Column Quality showing 100% Valid means the data is completely correct and ready for analysis.
Correct
Column Quality only checks whether every value matches the expected data type. A column of text can be 100% valid yet contain typos, duplicates, or wrong information.
The word 'Valid' sounds like it means 'correct', but in Power Query it means 'of the correct type'. Beginners hear the word and assume it implies accuracy.
Mistake
You should clean data in the source system (like Excel) before importing it into Power BI.
Correct
You should import raw data into Power Query and clean it there, because Power Query preserves the cleaning steps. Cleaning in the source is manual, brittle, and you lose the audit trail.
People come from spreadsheet habits where they manually fix data before importing. Power Query is designed to automate cleaning, so doing it beforehand defeats the purpose.
Mistake
The 'Remove Duplicates' transform removes all duplicate rows from the table.
Correct
Remove Duplicates keeps the first occurrence of each distinct combination of values in the selected columns and deletes all subsequent matches. It does not remove rows where some columns are duplicates if you selected only a subset of columns.
Beginners think of duplicates in the whole table sense, but Power Query allows you to select specific columns for deduplication. The behavior feels surprising until you understand it.
Mistake
Errors in a column mean the source data file is corrupt and you need to restart.
Correct
Errors usually mean a data type mismatch or an unexpected value. You can fix them with Replace Errors, or by changing the column data type after cleaning the values.
The word 'Error' sounds catastrophic. Beginners panic and think the data is broken beyond repair, when in fact errors are common and easily fixable.
Mistake
You should always filter out null rows during profiling to get cleaner statistics.
Correct
You should keep null rows visible during profiling because they are part of the data quality assessment. Filtering them out hides the problem and gives a false sense of cleanliness.
It feels logical to remove the 'bad' data before inspecting it, but profiling is supposed to show you the problems. Removing nulls removes the evidence.
Mistake
Column Profile shows the same information as Column Quality, just in a bigger view.
Correct
Column Quality only shows percentages of valid, error, and empty. Column Profile shows full descriptive statistics including min, max, distinct count, null count, and a complete value distribution table. They are entirely different tools.
Both tools are found in the same View ribbon section, so beginners lump them together. They do not realise each tool serves a distinct inspection purpose.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
No. Always load raw data into Power Query and clean it there. Power Query records every transform as a step that runs automatically on refresh. Cleaning in Excel is manual and error-prone.
Null means no value exists in that cell. An empty string is a text value that contains zero characters — it still occupies space. You can see the difference in Column Profile: nulls count as empty, empty strings count as valid text.
Errors usually mean a data type mismatch. For example, a column set to Whole Number that contains text like '123abc' will show errors. Open Column Profile to see the specific error values, then fix them with Replace Values or Change Type.
Yes. Reopen Power Query Editor by clicking Transform Data on the Home ribbon. In the Applied Steps pane, you can delete or reorder any step. The data model reloads automatically when you close and apply.
Remove Duplicates keeps the first occurrence of each distinct combination of values in the selected columns and removes all subsequent matches. It does not delete both — it keeps exactly one copy.
Start with columns that will be used in relationships, measures, or slicers — typically keys, dates, numbers, and categories. Use Column Quality to spot obvious problems, then Column Profile for deeper investigation on suspicious columns.
You've finished Data Profiling and Cleaning in Power Query. Continue through the PL-300 study guide to build a complete picture of the exam.
Done with this chapter?