How to Transform Data Types Using Power Query

16Jul

How to Transform Data Types Using Power Query

by Best Excel Tutorial Excel for Business and Finance

Power Query makes it easy to change column data types in Excel. You can convert text to dates, numbers to text or whole numbers to decimals. This helps your data work correctly in formulas, charts and reports.

Table of Contents

Why data types matter

Each column in Power Query should have the correct type. If a date is stored as text, Excel may not sort or filter it correctly. If a number is stored as text, calculations may fail. Setting the right type is an important part of data cleaning.

Open Power Query Editor

Select your data in Excel.
Go to the Data tab.
Click From Table/Range.
Power Query Editor will open.

Change a data type

You can change a column type in a few simple steps.

Select the column you want to change.
Go to the Transform tab.
Click the Data Type drop-down.
Choose the correct type.

Common data types

Text for names, labels or IDs.
Whole Number for counts.
Decimal Number for values with fractions.
Date for calendar dates.
Date/Time for dates with time.
True/False for logical values.

Use Detect Data Type

If Power Query is not sure about a column, you can let it detect the type automatically. Select the column and choose Transform > Detect Data Type. This is useful when the column is set to Any.

Change data type with locale

Sometimes numbers and dates look different in different regions. In that case, use Change Type with Locale. This helps Power Query read formats such as day/month/year or decimal commas correctly.

Use locale settings when your source data uses a regional format that does not match your Excel settings.

Example

If a column contains the text 2026-07-01, you can change it to a Date type. If another column contains 123.45, you can change it to Decimal Number. Once the types are correct, your data becomes easier to analyze.

Apply the changes

After changing the data types, click Home > Close & Load. Excel will return the cleaned table to your worksheet.

Tips for better results

Check every important column before loading the data back to Excel.
Use the right type for each column before building formulas.
Be careful with dates and numbers when your source file uses a different locale.
Review the Applied Steps pane to see the type changes Power Query made.

Best Excel Tutorial