Data type mismatches and unexamined data quality issues are the number one source of silent errors in Power BI reports. This lesson teaches you how to use Power Query's type system and column profiling tools to catch every problem before it reaches your model.
Picture this: you've spent two hours building a clean, polished sales dashboard. The bar charts look great, the filters are working, and you're about to share it with your manager. Then you try to calculate total revenue — and you get a blank. Or worse, an error. You dig in and discover the problem: your OrderAmount column was loaded as text instead of a decimal number. Every calculation failed silently, and your report has been showing nothing useful the whole time.
This is one of the most common and frustrating experiences for new Power BI users. The good news is that it's almost entirely preventable. Power Query — the data transformation engine built into Power BI Desktop — gives you the tools to catch these problems before your data ever reaches your report canvas. By understanding data types and using column profiling features, you can build a habit of verifying your data at the source, not discovering problems after you've built everything.
By the end of this lesson, you'll be able to confidently inspect incoming data, correct type assignments, use column profiling tools to spot anomalies, and develop a clean, error-resistant data preparation workflow.
What you'll learn:
This lesson assumes you have Power BI Desktop installed and know how to open a file and navigate to Power Query Editor. If you're brand new to the tool, take a few minutes with Importing and Transforming Your First Dataset in Power Query: A Step-by-Step Beginner Walkthrough before continuing here. You don't need to know M code (Power Query's formula language) to follow this lesson — everything we cover uses the built-in graphical interface.
A data type is a label Power Query assigns to each column that tells it — and Power BI — what kind of data lives there. Is this column a number you can add up? A date you can filter by month? A piece of text to display as a label?
Think of it like the slots in a filing cabinet. If you try to file a photograph in a slot designed for index cards, nothing works as expected. Data types work the same way. Power BI's calculation engine, its DAX formula language, and its relationship system all depend on columns having the correct type assigned. When a type is wrong, operations fail — sometimes noisily, sometimes silently.
Power Query supports several core data types. Here are the ones you'll encounter most often:
Whole Number — integers with no decimal component (e.g., 42, 1000, -5). Use this for counts, IDs, and quantities.
Decimal Number — numbers with fractional parts (e.g., 19.99, 3.14). Use this for prices, rates, and measurements.
Fixed Decimal Number — a variant of decimal optimized for currency values. It stores exactly four decimal places.
Text — any string of characters. Power BI treats this as a label, not a number. You cannot sum a text column.
Date — a calendar date without a time component (e.g., 2024-03-15). Essential for time intelligence calculations.
Date/Time — a combined date and time value (e.g., 2024-03-15 14:30:00). Useful for transaction logs and event data.
True/False — a boolean column containing only true or false values.
Any — the wildcard type. Power Query uses this when it can't determine the type. It's almost always a problem you need to fix.
Warning: The "Any" data type looks harmless, but it disables type-specific optimizations and can cause unpredictable behavior in DAX calculations. Whenever you see a column set to "Any," treat it as an unresolved issue that needs your attention.
To see data types in action, open Power BI Desktop and connect to a data source — even a simple CSV or Excel file works well for practice. Then click the Transform Data button in the Home ribbon. This opens Power Query Editor.
In Power Query Editor, look at the column headers in the data preview. Each column header has a small icon on its left side:
These icons are your first, fastest signal about what Power Query thinks is in each column. Scan them every time you open a query — before you do anything else.
You can change a column's data type by clicking that icon and selecting a new type from the dropdown, or by right-clicking a column header and choosing "Change Type." You can also use the Data Type dropdown in the Home or Transform ribbon tabs.
Tip: When you change a type, Power Query adds a step called "Changed Type" to your Applied Steps panel on the right. If you ever need to undo or revisit a type change, you can click that step to see what was changed, or delete it to roll back.
When Power Query first loads a dataset, it often adds a step called "Changed Type" automatically based on what it detects in the first few rows of your data. This sounds helpful — and sometimes it is. But it's also a significant source of silent errors.
Here's why: Power Query only samples a limited number of rows for type detection. If your SaleDate column has actual dates in the first 200 rows but a handful of free-text entries like "N/A" or "Pending" further down, Power Query will assign it a Date type — and then generate errors for every row it can't parse.
Additionally, auto-detection is sensitive to locale. A column with values like 1,500.00 might be detected as a whole number, a decimal number, or even text depending on your system's regional settings.
The professional habit here is simple: never trust auto-detected types without verifying them yourself. Treat the auto-detected step as a starting point, not a finished product.
Key insight: It's worth deleting the auto-detected "Changed Type" step entirely on complex or messy datasets, then re-applying type changes manually after you've explored the data. This forces you to make deliberate decisions rather than inheriting assumptions.
Power Query has three profiling views that give you statistical snapshots of your columns. These are invaluable for catching data quality problems before they become report errors. By default, they're hidden — here's how to turn them on.
In Power Query Editor, click the View tab in the ribbon. In the "Data Preview" group, you'll see three checkboxes:
Check all three to start. Here's what each one shows you.
When Column Quality is enabled, a thin bar appears beneath each column header. This bar is divided into three color segments:
Hover over any segment for the exact percentage. A column that's 98% valid, 2% error might seem fine — but in a table with 500,000 rows, that's 10,000 broken records.
Below the Column Quality bar, Column Distribution renders a small histogram showing how values are spread across the column. Beneath the chart, Power Query displays two numbers:
For a CustomerID column that's supposed to be a primary key, you'd want Distinct to equal Unique. If Distinct is 8,500 but Unique is 8,200, that tells you 300 IDs appear more than once — worth investigating before you build relationships on that column. Understanding this connects directly to how Power BI relationships behave when joining tables.
Column Quality and Distribution show bars above every column simultaneously. Column Profile is different — it applies to one column at a time. Click any column, then check Column Profile, and the bottom panel of Power Query Editor shows a detailed statistics pane for that column.
For numeric columns, you'll see:
For text columns, you'll see a frequency list — which values appear most often and how many times.
This is extremely useful for sanity checks. If your UnitPrice column shows a minimum of -15.00, that's a business logic problem worth flagging. If your CountryCode column has 47 distinct values when you know you only operate in 12 countries, something is wrong with the upstream data.
Note: By default, Column Profile analyzes only the first 1,000 rows of your data (because previewing the full dataset would be slow). To profile the entire dataset, look at the status bar at the bottom of Power Query Editor. You'll see text that says "Column profiling based on top 1000 rows." Click that text to toggle it to "Column profiling based on entire data set." Do this before drawing conclusions from profiling stats on large files.
Let's walk through a realistic scenario. Imagine you've been handed a CSV export from an older order management system. The file has these columns:
OrderID — supposedly a unique identifierOrderDate — date the order was placedCustomerID — identifier for the customerProductCategory — text category labelQuantity — number of units orderedUnitPrice — price per unitTotalAmount — quantity × unit priceHere's what you might discover using data types and profiling:
Step 1 — Scan the type icons. You open the query and immediately notice that OrderDate has an "ABC" text icon, not a calendar icon. Power Query loaded it as text. If you try to build a time intelligence DAX measure off this column, you'll get errors because DAX requires date columns to have the Date type.
Step 2 — Check Column Quality. After enabling Column Quality, you see that UnitPrice is 96% valid and 4% error. Hovering reveals 4% errors. Those rows probably have values like "$12.00" with a dollar sign, which won't parse as a decimal number. You'll need to clean that column before changing its type.
Step 3 — Check Column Distribution. The OrderID column shows 10,000 distinct values but only 9,800 unique. That means 200 order IDs are duplicated. Duplicates in what should be a key column will cause problems with star schema design and many-to-many relationship issues.
Step 4 — Column Profile on TotalAmount. Clicking the column and viewing its profile reveals a minimum value of -250.00. Negative totals might represent refunds — or they might be data entry errors. Either way, you need to know before the data reaches your model.
This four-step inspection takes five minutes and saves hours of debugging later.
This is extremely common in exports from legacy systems. If your dates look like "2024-03-15" (note the quotes indicate text), you need to change the type to Date. But first, confirm the format. If some dates are 03/15/2024 and others are 15-03-2024, a single type change will fail for one format or the other.
In Power Query Editor, right-click the column header and choose "Change Type → Using Locale." This lets you specify both the target type (Date) and the locale that matches the date format in your data, so Power Query parses it correctly.
Values like "$1,500.00" or "1.500,00" (European format) are stored as text because of the non-numeric characters. The fix is a two-step process: first, use the Replace Values or Transform → Extract to strip the formatting characters, then change the type to Decimal Number.
In Power Query, select the column, go to Transform → Replace Values, enter $ in the "Value to Find" box and leave the "Replace With" box empty, then click OK. Repeat for commas if needed. After cleaning, change the type.
Sometimes a column has mostly numbers but a few rows contain text like "N/A" or "Unknown." If you change the type to Decimal, those rows generate errors. You have two options:
Warning: Be careful when replacing errors with 0. In some contexts, 0 is a meaningful number (zero quantity sold) while null means "unknown." Replacing unknown values with 0 can skew averages and totals. Null is usually the safer choice.
After working through several datasets, the following checklist becomes second nature. Run through it every time you open or refresh a query in Power Query Editor.
1. Scan all type icons. Anything set to "Any" or an unexpected type needs attention.
2. Enable Column Quality across all columns. Any column showing red (errors) or high grey (nulls) needs investigation. Acceptable thresholds depend on your data, but errors above 1% in key columns should be treated as critical.
3. Check Distribution for key identifier columns. If a column is used as a join key in a relationship, Distinct should equal Unique (or you need to understand why it doesn't).
4. Run Column Profile on numeric columns. Verify that min, max, and average values make business sense. A revenue column with a max of $5,000,000 in a small retail business is probably an error.
5. Check Date columns. Confirm the range is reasonable — dates in the year 1900 or 9999 are common placeholders in legacy systems that will break time intelligence.
6. Close and Apply only after all checks pass. Don't load data into your model if you still have unresolved errors in the Applied Steps. Fix them in Power Query, not with workarounds in DAX.
This discipline pairs well with the broader transformation skills covered in Mastering Power Query in Power BI: Transforming, Cleaning, and Shaping Data Before It Hits Your Model, which goes deeper into the full range of transformation steps available.
For this exercise, you'll need a CSV file with intentionally messy data. You can create one yourself in any text editor. Save the following as sales_exercise.csv:
OrderID,OrderDate,CustomerID,Quantity,UnitPrice,TotalAmount
1001,2024-01-05,C001,3,$15.99,47.97
1002,2024-01-06,C002,1,12.50,12.50
1003,2024-01-07,C001,N/A,8.00,N/A
1001,2024-01-08,C003,2,22.00,44.00
1004,2024-01-09,C004,5,3.99,19.95
1005,15/01/2024,C005,1,100.00,100.00
Notice the intentional problems: OrderID 1001 is duplicated, Quantity and TotalAmount have "N/A" text values, UnitPrice has a dollar sign on one row, and OrderDate has a mixed format (ISO and DD/MM/YYYY).
Your tasks:
$ from UnitPrice and change it to Decimal NumberQuantity with null, then change to Whole NumberTotalAmount with null, then change to Decimal NumberOrderDate to Date type using "Using Locale" for the ISO format rows (you may need to investigate how to handle mixed formats — that's intentional)Tip: The mixed date format in step 5 is genuinely tricky and mirrors real-world data quality issues. One approach is to add a conditional column that parses each format separately, then combine them. If you get stuck, that's a realistic stopping point — it tells you exactly what additional transformation skills to build next.
Changing the type before cleaning the data. Always clean first. If a column has dollar signs, commas, or text placeholders, change the type last. Changing the type first generates errors that cascade through later steps.
Forgetting about locale for dates. A date change that works on your laptop in the United States will fail when a European colleague opens the same PBIX file with different regional settings. Always use "Change Type → Using Locale" for date columns and specify the source locale explicitly.
Profiling only 1,000 rows. The default row limit for profiling hides problems that occur deeper in large datasets. Always toggle to "entire data set" before treating profiling results as final.
Applying types after an append or merge. If you merge two queries and then change types, the type step only applies to the merged result. But if the merge changes later (because source data changes), the types may need re-validation. Add type steps after every significant structural transformation.
Ignoring nulls. A column that's 40% null might be acceptable (optional fields) or catastrophic (required fields). Don't dismiss nulls as harmless without understanding the business context. High null rates in a CustomerID column, for example, will break relationship filters throughout your report — this matters especially when you start building filters and slicers that depend on those columns.
Relying on DAX to fix type problems. You can sometimes work around type issues in DAX using functions like VALUE() or DATEVALUE(), but this is a bad habit. It adds complexity to your measures, makes them harder to debug, and means the problem recurs every time someone opens the report. Fix types in Power Query — that's what it's built for.
Data type errors and unexamined data quality problems are the hidden foundations of most Power BI headaches. The good news is that Power Query gives you everything you need to catch them early: clear visual type indicators on every column, Column Quality bars that surface errors and nulls at a glance, Column Distribution charts that reveal unexpected duplicates or concentrations, and Column Profile statistics that tell you whether your numbers make business sense.
The workflow is simple but powerful:
These habits pay dividends on every project. A clean, well-typed dataset loads faster, calculates correctly, and makes the work of building reports far less frustrating. When you move on to building measures in DAX, you'll find that correct data types make formulas simpler and results reliable. And when your data model is well-structured, the star schema relationships that connect your tables will work exactly as intended.
If you're ready to build on this foundation, a natural next step is Your First Power BI Report in 30 Minutes, which walks you through taking clean, properly typed data all the way to a finished, interactive report canvas.