Changing Data Types in Power Query: When, Why, and How to Set Column Types Correctly

Introduction

You've connected to a dataset — maybe a CSV of sales transactions, a SharePoint list of employee records, or an Excel export from your ERP system. The data loads into Power Query and looks reasonable. But then you try to calculate total revenue and get an error. Or you attempt to filter by date and the date picker won't appear. Or you merge two tables on a customer ID column and end up with zero matching rows, even though you can see the same IDs in both tables.

Almost every one of these problems traces back to the same root cause: column data types are wrong, inconsistent, or simply weren't set at all. Power Query is powerful, but it needs to know what kind of data it's working with before it can sort, calculate, compare, or join correctly. Setting column types correctly isn't just housekeeping — it's one of the most consequential decisions you make in any data transformation pipeline.

By the end of this lesson, you'll understand exactly what data types exist in Power Query, why the automatic type detection often gets it wrong, and how to set types correctly yourself — both through the interface and directly in the M formula language. More importantly, you'll know when to set types and where in your transformation sequence to place that step.

What you'll learn:

• What data types Power Query supports and what each one means
• Why automatic type detection fails in predictable, fixable ways
• How to change column types using the Power Query interface
• How to write type conversion steps directly in M code
• Where in your query to place type-setting steps for maximum reliability
• How to handle conversion errors gracefully instead of letting them crash your pipeline

---

Prerequisites

This lesson assumes you know how to open Power Query and load data from at least one source. If you're brand new to Power Query, start with Getting Started with Power Query: Master Connect, Transform, Load for Real-World Data before continuing here. You should also be comfortable with the basic concept of transformation steps and the Applied Steps panel.

---

What Is a Data Type, and Why Does Power Query Care?

A data type tells Power Query what category of value lives in a column — and therefore what operations are legal on it. A number can be added to another number. A date can be subtracted from another date to find the difference in days. A piece of text can be split or trimmed. But you can't add text to a date, or find the average of a column of customer names.

Think of data types like the labels on bins in a warehouse. If the bin says "fragile glassware," the handling instructions are completely different from the bin labeled "bolts and fasteners." Same physical space, completely different rules. When Power Query doesn't know what type a column contains, it treats everything like an unmarked box — technically fine to store, but dangerous to work with.

Here are the core data types you'll encounter:

Data Type	What It Holds	Common Use
Text	Strings of characters	Names, codes, categories, IDs
Whole Number	Integers (no decimals)	Counts, quantities, integer IDs
Decimal Number	Numbers with decimal places	Prices, measurements, percentages
Fixed Decimal Number	Currency precision (4 decimal places)	Financial amounts
Date	A calendar date (no time)	Transaction dates, birth dates
Time	Time of day (no date)	Shift times, log timestamps
Date/Time	Both date and time together	Timestamps, event logs
Date/Time/Timezone	Timestamp with UTC offset	Cross-timezone event data
Duration	A span of time	Time elapsed, SLA windows
True/False	Boolean values	Flags, binary indicators
Binary	Raw binary data	File contents, images
Any	No type specified	Power Query doesn't know what this is

That last one — Any — is what you get when Power Query gives up trying to guess. Columns typed as Any will load into your model, but downstream tools like Power BI or Excel PivotTables won't know how to aggregate or filter them intelligently.

---

How Power Query Automatically Detects Types (and Where It Goes Wrong)

When you load data from most sources, Power Query runs an automatic type detection step called "Changed Type" that inspects the first 1,000 rows of each column and makes an educated guess. For clean, consistent data from well-structured sources like SQL Server databases, this works well — the database already knows the types and Power Query inherits them.

For everything else, the guessing engine runs into problems.

Scenario 1: Product codes that look like numbers

Imagine a column called ProductCode containing values like 00045, 00213, 01002. Power Query sees digits and decides this must be a Whole Number. It converts the column — and suddenly 00045 becomes 45. Those leading zeros are gone, and if your downstream system uses them as identifiers for lookups or joins, you now have a matching problem.

Scenario 2: European date formats

A CSV exported from a German ERP system contains dates formatted as 15.03.2024 (day.month.year). Power Query's automatic detection, running in a US locale, may interpret this as March 15 — or it may fail entirely and fall back to Text. Either way, you haven't gotten a reliable Date column.

Scenario 3: Mixed-type columns

A column called SalesAmount contains mostly numbers but has a few rows where someone typed "N/A" or "TBD." Power Query sees the mix and types the column as Text to avoid losing data. Now you can't do any arithmetic without fixing the problem first.

Scenario 4: The 1,000-row sample problem

Power Query checks only the first 1,000 rows for type detection. If your dataset has 500,000 rows and the problematic values — the text mixed into your number column — only appear in row 12,000, the auto-detection succeeds on the sample and fails when the full data loads.

---

Reading the Type Icons in the Column Header

In the Power Query editor, each column header shows a small icon to the left of the column name. This icon is your quickest visual indicator of the current data type:

• ABC — Text
• 123 — Whole Number
• 1.2 — Decimal Number
• $ — Fixed Decimal Number (Currency)
• A small calendar icon — Date
• A clock icon — Time
• A calendar-with-clock icon — Date/Time
• True/False — Boolean
• ABC / 123 stacked — Any (the warning sign)

When you're reviewing a loaded query, scan these icons before doing anything else. If a date column shows ABC, or a currency column shows 123, you need to intervene before proceeding with any transformations.

---

Changing Types Through the Interface

There are three main ways to change a column's data type in the Power Query editor without writing any code.

Method 1: Click the Type Icon

This is the fastest approach for single columns. Click the icon to the left of the column name in the header. A dropdown menu appears listing all available types. Click the one you want. Power Query adds a "Changed Type" step to your Applied Steps panel.

Method 2: Right-Click the Column Header

Right-click any column header and hover over Change Type in the context menu. A submenu appears with the full list of data types. This approach is identical in result to Method 1 — use whichever you find more comfortable.

Method 3: Use the Transform Tab

Select one or more columns (hold Ctrl to select multiple columns), then click the Transform tab in the ribbon. In the leftmost section labeled Any Column, you'll see a Data Type dropdown. This method is particularly useful when you want to apply the same type to several columns at once — for example, setting five numeric columns all to Decimal Number in a single step.

---

Understanding the M Code Behind Type Changes

Every action you take in the Power Query interface generates M code behind the scenes. Understanding what that code looks like gives you precision and control that the interface alone can't provide.

To see the M code for any step, click that step in the Applied Steps panel and then click the formula bar above the data grid. You can also open the Advanced Editor (Home tab → Advanced Editor) to see the full query.

A type change step typically looks like this:

= Table.TransformColumnTypes(
    PreviousStep,
    {
        {"OrderDate", type date},
        {"SaleAmount", type number},
        {"CustomerID", type text},
        {"IsActive", type logical}
    }
)

Table.TransformColumnTypes takes two arguments: the table from the previous step, and a list of {column name, type} pairs. The type values you'll use most often are:

type text           -- Text
type number         -- Decimal Number
type date           -- Date
type datetime       -- Date/Time
type datetimezone   -- Date/Time/Timezone
type duration       -- Duration
type logical        -- True/False
type any            -- Any (effectively removes the type)
Int64.Type          -- Whole Number (note: different syntax)
Currency.Type       -- Fixed Decimal Number (note: different syntax)

You can also write type conversions column-by-column using Table.TransformColumns with an explicit conversion function, which gives you more control over how the conversion happens:

= Table.TransformColumns(
    PreviousStep,
    {
        {"Revenue", each Number.FromText(_), type number},
        {"TransactionDate", each Date.FromText(_, "en-GB"), type date}
    }
)

This approach is especially useful when the automatic conversion isn't smart enough — for instance, when parsing dates in a specific locale or converting text representations of numbers that include currency symbols.

---

When to Set Types: Placement Matters

One of the most common mistakes beginners make is setting data types too late in the transformation sequence — or too early. Both cause real problems.

Set types early, but after source normalization

As a general rule, set your column types immediately after loading the source data and after any steps that restructure the columns (like renaming, removing, or promoting headers). Don't let untyped columns travel deep into your transformation pipeline.

Here's a typical well-structured sequence:

1. Source — Connect to the data
2. Promoted Headers — If the first row contains column names
3. Removed Other Columns — Drop columns you don't need
4. Renamed Columns — Standardize column names
5. Changed Type — ← Set all your types HERE
6. (All subsequent transformations)

If you set types before promoting headers or before removing columns, you'll end up converting columns that don't exist in later steps, or missing new columns you added.

Don't let Power Query's auto-step set types for important sources

When you load data from a CSV or Excel file, Power Query automatically inserts a "Changed Type" step using its best guess. For critical columns — especially IDs, dates, and financial figures — delete or replace that auto-generated step with one you've explicitly configured.

For more on structuring your transformation steps in a maintainable way, see Power Query Best Practices for Maintainable ETL.

---

Handling Type Conversion Errors

When Power Query tries to convert a column and encounters a value that doesn't fit — like the text "N/A" in a numeric column — it produces an Error value in that cell. Errors propagate. If you don't handle them, they'll surface in your reports as blank cells or broken aggregations.

You have several strategies:

Replace errors with a default value

After the type conversion step, right-click the column header and choose Replace Errors. Enter a sensible default — 0 for numeric columns, null for optional fields. This becomes its own Applied Step:

= Table.ReplaceErrorValues(
    PreviousStep,
    {{"SaleAmount", 0}, {"Quantity", 0}}
)

Use Try/Otherwise in a custom column

For fine-grained control, you can use M's error handling syntax to attempt a conversion and fall back gracefully:

= Table.AddColumn(
    PreviousStep,
    "SaleAmountClean",
    each try Number.From([SaleAmount]) otherwise null,
    type number
)

This is particularly useful when you want to keep the original column for auditing while creating a clean converted version alongside it. You'll find this pattern explored in depth in Cleaning Messy Data with Power Query: Nulls, Errors, and Type Conversions.

Filter out unconvertible rows

Sometimes the right answer is to remove rows that can't be properly typed. This is especially appropriate when bad data represents input errors rather than legitimate values:

= Table.SelectRows(
    PreviousStep,
    each [SaleAmount] <> null and [SaleAmount] <> ""
)

---

Date Type Gotchas: The Most Common Problem Area

Dates deserve special attention because they fail in uniquely frustrating ways. A few important situations to know:

Date vs. Date/Time: If your source has timestamps (like 2024-03-15 14:32:00) and you type the column as date, Power Query strips the time component. That's intentional and often desirable. But if you later need to compare two timestamps and they've been stripped to dates, your analysis will be wrong. Be deliberate about which one you need.

Locale-specific parsing: When converting text to dates, the locale matters enormously. 03/04/2024 means March 4 in the US and April 3 in the UK. In M code, you can specify the locale explicitly:

= Table.TransformColumns(
    PreviousStep,
    {{"OrderDate", each Date.FromText(_, [Culture="en-GB"]), type date}}
)

Duration columns: If you're working with data that records elapsed time (like call center handle times or delivery windows), resist the temptation to store these as numbers of minutes. Use type duration and let Power Query handle the arithmetic — you can always extract minutes or hours later using Duration.TotalMinutes().

For a deeper dive into working with dates, including building date dimensions and parsing complex formats, see Working with Dates and Text in Power Query: Advanced Transformation Techniques.

---

Text IDs: The Leading Zeros Trap

This problem appears constantly in real-world data and is worth calling out explicitly. Any identifier column — ZIP codes, product codes, employee numbers, account numbers — that contains leading zeros must be typed as Text, not Whole Number.

The moment Power Query (or Excel, or SQL Server) treats 00782 as an integer, it becomes 782. If your source system, lookup tables, or join keys use 00782, every merge and lookup will fail silently — you'll just get zero matches.

The fix is simple but must happen before any other transformation touches the column. In your type-setting step, explicitly type any ID or code column as Text:

{"ZipCode", type text},
{"ProductCode", type text},
{"EmployeeID", type text}

When you're combining data from multiple sources, mismatched ID types between two tables is the single most common reason a merge produces no results. Always verify that join key columns have the same type in both tables before merging.

---

Hands-On Exercise

Let's put this together with a realistic scenario. Imagine you have a CSV file of customer orders with these columns: OrderID, CustomerCode, OrderDate, Quantity, UnitPrice, Discounted.

The raw file contains:

• OrderID as integers (fine as Whole Number)
• CustomerCode as values like C-00421, C-00038 (must be Text — though no leading zero issue here, it's still a code)
• OrderDate as text in the format DD/MM/YYYY
• Quantity as whole numbers with occasional "TBD" entries
• UnitPrice as decimal numbers with a dollar sign prefix ($12.50)
• Discounted as text values "Yes" and "No"

Your task:

1. Load the CSV into Power Query (Data tab → Get Data → From Text/CSV)
2. Promote the first row as headers if needed
3. Delete the auto-generated "Changed Type" step from the Applied Steps panel
4. Open the Advanced Editor and write a single Table.TransformColumnTypes step that correctly types OrderID as Int64.Type, CustomerCode as type text, and OrderID as the appropriate type
5. Add a custom column called UnitPriceClean that strips the dollar sign from UnitPrice and converts it to type number using: each Number.FromText(Text.Replace([UnitPrice], "$", ""))
6. Add another custom column called IsDiscounted that converts "Yes" to true and "No" to false using a conditional expression
7. Handle errors in Quantity by replacing non-numeric values with null
8. Set OrderDate using Date.FromText with en-GB culture

After completing these steps, verify that every column header icon shows the correct type indicator. No column should show ABC/123 (Any).

---

Common Mistakes & Troubleshooting

"My type change step keeps getting re-added by Power Query." This happens when you refresh the query and Power Query re-runs its auto-detection. Check your Applied Steps — if there's an auto-generated "Changed Type" step before yours, delete it. Your explicit step should be the authoritative one.

"I changed a column to Date but the cells still show as text." Check whether the column values are actually parseable dates. Try sorting the column ascending — if it sorts alphabetically (1, 10, 11, 2) rather than chronologically, the underlying values are still text strings. You may need to use Date.FromText with an explicit culture rather than a direct type conversion.

"My merge query returns zero matches even though the IDs look the same." The most common cause is a type mismatch. Check the column type icon for the key column in both tables. If one shows 123 (number) and the other shows ABC (text), Power Query cannot match them even if the values look identical visually. Set both to the same type — usually type text for IDs.

"Power Query shows an error on only some rows after a type change." You have mixed-value rows — some cells contain values that can't be converted to the target type. Use Replace Errors or try/otherwise logic as described above. Also consider whether those problem rows represent data quality issues worth investigating at the source.

"I need to convert a column to a whole number but the values sometimes have decimals." You have two options: use Number.RoundDown() to explicitly truncate the decimal before applying Int64.Type, or keep the column as Decimal Number and accept that it stores whole values with zero decimal places. Don't use Int64.Type on a column that genuinely contains decimals — it will error on conversion.

---

Summary & Next Steps

Data types are the foundation that every transformation in Power Query builds on. When types are wrong, errors appear downstream in places that seem completely unrelated to the type issue — which is why understanding this topic makes you dramatically faster at debugging problems throughout your entire pipeline.

Here's what you now know:

• Power Query has specific types for numbers, dates, text, booleans, and more — and the Any type signals that something needs your attention
• Automatic type detection works well for structured sources like databases, but fails predictably on CSVs, Excel exports, and mixed-content files
• You can change types through the column icon, right-click menu, or Transform ribbon — all producing the same Table.TransformColumnTypes M code
• Type-setting steps belong early in your transformation sequence, after header promotion and column selection but before any downstream logic
• ID and code columns with leading zeros must always be typed as Text
• Date conversions require locale awareness when parsing from text
• Conversion errors should be handled explicitly with Replace Errors or try/otherwise logic

From here, there are several natural directions to continue building your Power Query skills. If you want to understand the M language that generates all these steps more deeply, Understanding the M Formula Language: Syntax, Data Types, and Expression Basics is the next logical lesson. If you're working with data that changes its structure between refreshes — columns appearing or disappearing — Handling Dynamic Schema Changes in Power Query: Strategies for Evolving Source Data Structures covers how to make your type-setting steps resilient to those surprises. And if you're ready to take on more complex transformations now that your types are solid, Power Query Transformations: Split, Merge, Pivot, and Unpivot builds directly on everything covered here.