Power BI Data Types and Column Formatting

Every column in your data contains a specific kind of information — a name, a number, a date, or a true/false value. Power BI needs to know what kind of data each column holds so it can handle calculations, filters, and displays correctly. This is what data types are for. Column formatting then controls how that data appears to the reader.

Why Data Types Matter

Imagine you ask a calculator to add "apple" and "banana." The calculator cannot do it because those are words, not numbers. Power BI works the same way. If a column containing sales amounts is accidentally set to the Text data type, Power BI cannot sum or average those amounts. It will either show an error or produce wrong results.

Setting the correct data type is not optional — it is a fundamental requirement before building any calculations or visuals.

The Main Data Types in Power BI

Whole Number

Use this for numbers that are always complete — no decimals. Examples include item quantities (5 units, 12 boxes), age (28 years), employee count (150 staff), and year (2024). A whole number cannot hold a value like 5.7.

Decimal Number

Use this for numbers that can have decimal points. Examples include prices (₹299.50), weight (2.75 kg), and percentages stored as decimals (0.15 for 15%). Calculations with decimal numbers are more precise but use slightly more memory.

Fixed Decimal Number (Currency)

A special version of decimal numbers designed for financial data. It stores values with exactly four decimal places for maximum accuracy in monetary calculations. Use this for amounts in rupees, dollars, or any other currency when precision matters.

Text

Use this for words, names, labels, codes, and any value you read rather than calculate. Examples include customer names, city names, product categories, order status ("Completed," "Pending"), and phone numbers (even though phone numbers contain digits, you never add or subtract them, so Text is correct).

Date

Use this for calendar dates — day, month, and year — where the time of day is not important. Power BI's date intelligence functions (calculating this month's sales vs last month's) require proper Date type columns to work.

Date/Time

Use this when you need both the date and the exact time — such as a transaction timestamp showing 14 March 2024 at 3:47 PM. This is useful for tracking when events happened at a specific hour or minute.

True/False

Also called Boolean. The value is either True or False, 1 or 0, Yes or No. Examples include "Is the customer active?" (True/False), "Did the item pass quality check?" (True/False). This type is useful for quick filters and conditional logic.

Where to Change Data Types

You change data types in two places:

In Power Query Editor

Click the data type icon on the left side of the column header. A menu appears with all available types. Changing types here is preferred because it applies before the data loads into the model. This is the best practice location for data type changes.

In the Data View or Model View

Select a column in the Fields pane. In the Column Tools tab that appears in the ribbon, you can change the data type. Changes here apply after the data is already loaded. Use this for minor adjustments, but prefer Power Query for type changes when possible.

Column Formatting — Making Numbers Readable

Setting a data type tells Power BI what kind of data a column holds. Column formatting tells Power BI how to display that data to the reader. A number like 2500000 is technically correct, but a formatted display like ₹25,00,000 is far easier to read.

Currency Formatting

In the Data View, select a numeric column. In the Column Tools tab, click the Format dropdown and choose Currency. Then select the appropriate currency symbol. Values like 2500 now display as ₹2,500.00 in your visuals.

Percentage Formatting

For columns that store percentages as decimals (0.25 meaning 25%), apply Percentage formatting. Power BI multiplies the value by 100 and adds a % symbol — so 0.25 becomes 25%.

Thousands Separator

For large numbers, enable the thousands separator (comma). A value like 1500000 becomes 1,500,000 — much easier for a reader to parse quickly.

Decimal Places

Control how many decimal places Power BI shows. A price column might show two decimal places (₹299.50). A percentage might show one decimal place (15.3%). A quantity column might show zero decimal places (12 units).

Data Categories for Special Columns

Beyond data types, Power BI has a concept called Data Category for columns that hold specific kinds of information. Setting the correct category unlocks special features.

Web URL Category

If a text column contains website links, setting its Data Category to Web URL allows Power BI to display clickable hyperlinks in table and matrix visuals instead of plain text.

Image URL Category

If a text column contains links to images, set Data Category to Image URL. Power BI can then display the actual images inside table visuals instead of showing the URL text.

Geographic Categories

Setting Data Category to Country, State or Province, City, or Postal Code tells Power BI's map visual how to interpret location data. Without these categories, map visuals may misplace locations or fail to recognize them at all.

To set a Data Category, select the column in Data View or Model View. In the Column Tools tab, find the Data Category dropdown and select the appropriate option.

Summarization Settings

Power BI automatically decides how to summarize a numeric column when you use it in a visual — usually by summing all values. Sometimes this is not what you want.

For example, a column containing "Year" holds values like 2022, 2023, 2024. If you drag this column into a visual, Power BI might try to sum the years (2022 + 2023 + 2024 = 6069), which makes no sense. You want Power BI to group by year, not sum them.

Select the Year column in the Fields pane. In the Column Tools tab, find Default Summarization and change it from Sum to Don't Summarize. Power BI will now treat Year as a category label rather than a number to calculate.

Key Points

• Data types tell Power BI what kind of data a column contains — wrong types cause calculation errors.
• Change data types in Power Query Editor for best results, before data loads into the model.
• Column formatting controls how numbers and dates display — currency symbols, decimal places, and separators.
• Data Categories like Country, City, and Web URL unlock special features in map and table visuals.
• Set Default Summarization to "Don't Summarize" for columns like Year or ID that should not be added up.