Menu
×
   ❮   
HTML CSS JAVASCRIPT SQL PYTHON JAVA PHP C C++ C# AWS W3.CSS HOW TO BOOTSTRAP REACT MYSQL JQUERY EXCEL XML DJANGO NUMPY PANDAS NODEJS DSA TYPESCRIPT ANGULAR ANGULARJS GIT POSTGRESQL MONGODB ASP AI R GO KOTLIN SWIFT SASS VUE GEN AI SCIPY CYBERSECURITY DATA SCIENCE INTRO TO PROGRAMMING HTML & CSS BASH RUST TOOLS MATH

Power BI Power Query Data Types


Share

Data Types

Every column in Power Query has one data type, such as text, whole number or date.

The type decides what Power BI can do with the column.

Power BI can add up a number column, build a Year, Quarter and Month hierarchy from a date column, and sort each type the right way.

A number stored as text cannot be added up, and a date stored as text sorts like a word.

For files such as CSV, Microsoft recommends that you always make sure each column has a type, and that no column of your finished query is left with the type Any.

In this chapter you check the types of the Poke Mart query and fix the one that is missing.

This page continues the cleanup from Power Query Columns.

Open pq_cleanup.pbix, the report you saved there, select Home > Transform data, and select the last step of the poke_mart_raw_2024 query, Added Custom.


The Type Icons

The type of a column is shown as an icon on the left side of its header.

These are the types you meet most often:

IconData typeHoldsPoke Mart example
ABCTextLetters, words, codesLocation, Product
123Whole NumberNumbers without decimalsOrderID, Quantity, UnitPrice
1.2Decimal NumberNumbers with or without decimalsNone yet
$Fixed decimal numberMoney amounts, with up to four decimals. Also called CurrencyNone yet
DATEDateA date without a time. The icon is a small calendarOrderDate
Calendar and clockDate/TimeA date and a timeNone
ABC123AnyNo type has been set yetSales, for now

There are a few more, such as Time, Duration, Percentage and True/False.


The Changed Type Step

You did not set any types when you loaded the file, but most columns already have one.

The automatic Changed Type step did that.

For files such as CSV and Excel, Power Query looks at the values in the first 200 rows of each column and picks a type that fits. Check what it chose:

ColumnDetected typeCorrect?
OrderID123Whole NumberYes
OrderDateDATEDateYes
LocationABCTextYes
ProductABCTextYes
Quantity123Whole NumberYes
UnitPrice123Whole NumberYes
SalesABC123AnyNo, it was added after the Changed Type step

Check the Date Column

Dates deserve a second look.

OrderDate should have the calendar icon of the Date type:

  • If it shows ABC, the dates are text. Power BI cannot build a date hierarchy from them.
  • If it shows a calendar with a clock, the type is Date/Time, and every date gets a time of midnight that you do not need.

In the Poke Mart file, the dates are written as year-month-day, for example 2024-01-01. Power Query reads this format the same way on every computer, so OrderDate is detected as Date.

Note: Power Query only looks at the first 200 rows.

If row 900 held a value that does not fit the detected type, that cell would become an error.

If you chose Do not detect data types in the preview window, every column starts as text, and you set the types yourself.


How to Change a Data Type

Select a column, then use any of these:

  • Select the type icon on the left side of the column header.
  • Right-click the header and select Change Type.
  • On the Home tab, use the Data Type dropdown.
  • On the Transform tab, use the Data Type dropdown.

The data type menu opened from the Any type icon in the Sales column header in Power Query, with Whole Number highlighted

Transform > Detect Data Type asks Power Query to guess the type of the selected columns again.

Every type change is recorded as a step.

Before you fix Sales, see what happens when a type change goes wrong.



When a Type Change Fails

Try to turn Product into a number:

  1. Select the Product column.
  2. Select the ABC icon in its header.
  3. Select Whole Number.

Power Query adds a step called Changed Type1, and every cell in Product now says Error:

123OrderIDDATEOrderDateABCLocation123Product123Quantity123UnitPriceABC123Sales
10011/1/2024Lilycove City - HoennError112001200
10021/1/2024Cerulean City - KantoError192003800
10031/2/2024Lilycove City - HoennError136007800
10041/2/2024Cerulean City - KantoError9100900
10051/3/2024Violet City - JohtoError193005700
.....................

"Hyper Potion" is not a number, so Power Query cannot convert it.

Instead of stopping, it puts an Error value in the cell and goes on with the next row.

To see why a cell failed, click the white space next to the word Error, in the first cell of Product.

A pane opens below the preview with a message similar to this:

DataFormat.Error: We couldn't convert to Number.
Details:
Hyper Potion

The Product column in Power Query after changing it to Whole Number: every cell shows Error, and the error pane below the preview shows DataFormat.Error: We couldn't convert to Number, with the details Hyper Potion

If Column quality is turned on (on the View tab), it shows 100% Error for Product.

Replace or Remove Errors

In real data, often only a few cells fail, for example one "N/A" in a column of numbers.

Power Query has three commands for that. Select the column first:

CommandWhereWhat it does
Replace ErrorsTransform > the arrow next to Replace ValuesPuts a value that you type, such as 0, in every error cell
Remove ErrorsHome > Remove RowsRemoves every row that has an error in the selected columns
Keep ErrorsHome > Keep RowsKeeps only the rows with an error, so you can look at them

Note: Think before you use Remove Errors.

Here every Product cell is an error, so Remove Errors would delete all 1,285 rows.

The problem is not the data but the step, so the right fix is to delete the step.

Delete the Step

In Applied Steps, point to Changed Type1 and select the X next to it.

The step is gone, and Product is text again.

The last step is Added Custom again.

You can always undo a change this way.


Set Sales to Whole Number

Now the real fix.

Sales has the type Any, because in Power BI Desktop a custom column gets no type.

Quantity and UnitPrice are both whole numbers, so their product is always a whole number too.

  1. Select the Sales column.
  2. Select the ABC123 icon in its header.
  3. Select Whole Number.

Power Query adds a new step, Changed Type1, and the icon of Sales changes to 123:

123OrderIDDATEOrderDateABCLocationABCProduct123Quantity123UnitPrice123Sales
10011/1/2024Lilycove City - HoennHyper Potion112001200
10021/1/2024Cerulean City - Kantopoke ball192003800
10031/2/2024Lilycove City - Hoenn  Great Ball 136007800
10041/2/2024Cerulean City - KantoAntidote9100900
10051/3/2024Violet City - JohtoPotion193005700
10061/3/2024Slateport City - HoennParalyze Heal1200200
.....................

The formula bar shows something similar to this:

= Table.TransformColumnTypes(#"Added Custom",{{"Sales", Int64.Type}})

It is the same function as in the automatic Changed Type step.

Int64.Type is the M name of Whole Number.

If you change the type of a column that the last step has just converted, Power Query asks whether to replace the current conversion step or add a new step.

Replacing the current step keeps the list of steps short.


Using Locale

The same text can mean two different dates.

04/05/2024 is April 5 in the United States, but 4 May in the United Kingdom.

Power Query reads dates and numbers with the regional format of your computer.

When a file comes from another country, that can give wrong dates or errors.

The fix is to tell Power Query which locale the file uses:

  1. Right-click the column header.
  2. Select Change Type > Using Locale....
  3. Choose the data type, for example Date.
  4. Choose the locale of the file, for example English (United Kingdom).
  5. Select OK.

Locale matters for numbers too: in many countries, 1.200 means one thousand two hundred, and a comma is the decimal separator.

To change the locale for the whole file, select File > Options and settings > Options, and under CURRENT FILE select Regional Settings.

The Poke Mart dates are written year-month-day, so you do not need a locale for them.


Where You Are Now

All seven columns now have the right type:

ColumnType
OrderIDWhole Number
OrderDateDate
LocationText
ProductText
QuantityWhole Number
UnitPriceWhole Number
SalesWhole Number

The query still has 1,285 rows.

Its Applied Steps are: Source, Promoted Headers, Changed Type, Removed Columns, Renamed Columns, Added Custom and Changed Type1.

Select Close & Apply, then File > Save to save pq_cleanup.pbix.

In the next chapter you remove the rows that should not be there.



Free account

Track your progress

XP earned 0 Day streak 0 Spaces 0 League —

New

Learn in Adventure Mode W3Schools Adventure App

Coding fundamentals as bite-sized lessons and challenges.

×

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail:
sales@w3schools.com

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail:
help@w3schools.com

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookies and privacy policy.

Copyright 1999-2026 by Refsnes Data. All Rights Reserved. W3Schools is Powered by W3.CSS.