- Article
- 19 minutes to read

This article describes the data types supported by Power BI Desktop and Data Analysis Expressions (DAX).

When Power BI loads data, it attempts to convert the data types of the source columns to data types that support more efficient data storage, calculations, and display. For example, if a column of values that you import from Excel does not contain fractional values, Power BI Desktop converts the column of data to a**whole**Data type best suited for storing integers.

This concept is important because some DAX functions have special data type requirements. In many cases, DAXconverts data types implicitly, but in some cases not. For example, if a DAX function**Given**datatype, but the datatype for your column is**Text**, the DAX function will not work correctly. Therefore, it is important and helpful to use the correct data types for your columns.

## Determine and specify the data type of a column

In Power BI Desktop, you can determine and specify the data type of a column in Power Query Editor, Data View, or Report View:

In the Power Query Editor, select the column, and then select

**type of data**I am**to transform**band groupIn Data View or Report View, select the column, and then select the dropdown arrow next to

**type of data**No**column tools**ribbon tab.

The Data Type dropdown selector in the Power Query Editor contains two types of data that are not present in Data View or Report View:**Date/Time/Time Zone**mi**time duration**. When you load a column with these data types into the Power BI model, a**Date/Time/Time Zone**The column becomes a**appointment time**data type and a**time duration**The column becomes a**decimal number**type of data.

O**Tracks**The data type is not supported outside of the Power Query Editor. In the Power Query Editor, you can use the**Tracks**Data type when uploading binary files if you convert them to other data types before uploading them to the Power BI model. EITHER**Tracks**The selection exists in the Data View and Report View menus for legacy reasons, but when trying to load**Tracks**Insert columns into the Power BI model, you might encounter errors.

## types of numbers

Power BI Desktop supports three types of numbers:**decimal number**,**fixed decimal**, mi**whole**.

You can use the Tabular Column Object Model (TOM)type of dataproperty to specify thetype of dataEnumerations for number types. To learn more about how to change objects programmatically in Power BI, seeEncode Power BI data sets using the tabular object model.

### decimal number

**decimal number**is the most common type of number and can handle whole numbers and fractions.**decimal number**represents 64-bit (eight byte) floating point numbers with negative values*-1,79E +308*through*-2.23E -308*, positive values out*2.23E-308*through*1,79E +308*, mi*0*. numbers like*34*,*34.01*, mi*34.000367063*are valid decimal numbers.

The highest precision that**decimal number**The type can represent 15 digits. The decimal separator can appear anywhere in the number. This type is how Excel stores its numbers and TOM specifies this type as`Data Type.Double`

Say.

### fixed decimal

O**fixed decimal**The data type has a fixed place for the decimal point. The decimal separator always has four digits to the right and allows 19 significant digits. The highest value of**fixed decimal**can represent is positive or negative*922.337.203.685.477.5807*.

O**fixed decimal**The type is useful in cases where rounding can lead to errors. Numbers with small fractional values can sometimes accumulate, forcing a number to be slightly inaccurate. EITHER**fixed decimal**type can help you avoid these kinds of errors by truncating values after the four digits to the right of the decimal point.

This data type corresponds to that of SQL Server**Decimals (19.4)**, or the**Currency**Data type in Analysis Services and Power Pivot in Excel. TOM specifies this type as`Type of data. Decimal`

Say.

### whole

**whole**represents a 64-bit (eight byte) integer value. Since it is an integer,**whole**does not have final decimals. This type allows 19 digits of positive or negative integers between*-9.223.372.036.854.775.807*(*-2^63+1*) mi*9.223.372.036.854.775.806*(*2^63-2*), so that it can represent as many types of numeric data as possible.

As with him**fixed decimal**write the**whole**The type can be useful when you need to control rounding. TOM represents the**whole**data type as`Data type.Int64`

Say.

observation

The Power BI Desktop data model supports 64-bit integer values, but due to JavaScript limitations, this is the largest number that Power BI visuals can safely express.*9.007.199.254.740.991*(*2^53-1*). If your data model contains larger numbers, you can use calculations to reduce their size before adding them to the images.

### Precision of numeric type calculations

column values of**decimal number**The data types are saved as*about*Data types, according to the IEEE 754 standard for floating point numbers. Approximate data types have inherent precision limitations because, instead of storing exact numeric values, they can store extremely close or rounded approximations.

loss of precision,*vagueness*, can occur when the floating point value cannot reliably quantify the number of floating point digits. Inaccuracies can show up as unexpected or inaccurate calculation results in some reporting scenarios.

Comparison calculations related to equality between values of**decimal number**The data type may return unexpected results. Equality comparisons include equals`=`

, Larger than`>`

, smaller than`<`

, better than or equal to`>=`

, and less than or equal to`<=`

.

This problem is more noticeable when you use theRANGE functionin a DAX expression that calculates the result twice, resulting in slightly different numbers. Report users may not notice the difference between the two numbers, but the ranking result may be grossly inaccurate. To avoid unexpected results, you can change the data type of the column from**decimal number**any**fixed decimal**o**whole**, or carry out a forced round withREDONDO. O**fixed decimal**The data type has higher precision because the decimal separator always has four digits to the right.

Calculations are seldom performed that add the values in a column of**decimal number**The data type may return unexpected results. This result is more likely to occur with columns that contain large numbers of positive and negative numbers. The total result is affected by the distribution of values in the rows of the column.

When a required calculation adds most positive numbers before adding most negative numbers, the large positive partial sum at the beginning can potentially skew the results. When the calculation adds balanced positive and negative numbers, the query retains more precision and therefore returns more accurate results. To avoid unexpected results, you can change the data type of the column from**decimal number**For**fixed decimal**o**whole**.

## Types of date/time

Power BI Desktop supports cinco**appointment time**Data types in Power Query Editor. Both**Date/Time/Time Zone**mi**time duration**Convert to the Power BI Desktop data model when loaded. The model supports**appointment time**, or you can format the values as**Given**o**Tempo**independent.

**appointment time**represents a datetime value. the underlying value**appointment time**The value is saved as**decimal number**Write so you can convert between the two types. The time interval is stored as an integer fraction of 1/300 seconds (3.33 ms). The data type supports dates between the years 1900 and 9999.**Given**represents just a date without a time slice.**Given**converted to the model as a**appointment time**Value with zero for the fractional value.**Tempo**represents just a time with no date part.**Tempo**converted to the model as a**appointment time**Value with no digits to the left of the decimal point.**Date/Time/Time Zone**represents a UTC datetime with a time zone offset and is converted to**appointment time**when loading into the model. The Power BI model doesn't adjust the time zone based on a user's location or locale. A value of 09:00 loaded in the US model displays as 09:00 each time the report is opened or viewed.**time duration**represents a period of time and becomes a**decimal number**insert when loading them into the model. As**decimal number**type, you can add or subtract the values**appointment time**Values with correct results and just use the values in visualizations that show magnitude.

## kind of text

O**Text**The data type is a Unicode string, which can be letters, numbers, or dates represented in a text format. The maximum length of the string is 268,435,456 Unicode characters (256 megacharacters), or 536,870,912 bytes.

The way Power BI stores text data can cause the data to appear differently in certain situations. The following sections describe common situations that can lead to this.**Text**Data to easily switch appearance between querying data in Power Query Editor and loading it into Power BI.

### upper case and lower case

The engine that stores and queries data in Power BI is*case does not matter*and treats different uppercase and lowercase letters as the same value. "A" is equal to "a". However, Power Query is*Be aware of upper and lower case letters*, where "A" is not the same as "a". The difference in capitalization can lead to situations where text data appears to inexplicably change capitalization after it's loaded into Power BI.

The following example displays the order data: On**request no**Unique column for each order and a**container**Column showing the name of the recipient entered manually when placing the order. Power Query Editor displays multiple requests with the same**container**names entered into the system with different capitalizations.

After Power BI loads the data, duplicate the names in the**Data**The tab changes from the original entry to one of the case variants.

This change is because the Power Query Editor is case sensitive, so the data is displayed exactly as it is stored in the source system. The engine that stores data in Power BI is not case sensitive, so it treats the uppercase and lowercase versions of a character as the same. Power Query data loaded into the Power BI engine may change accordingly.

The Power BI engine evaluates each row individually as it loads the data, starting at the top. For each column of text like**container**the engine stores a dictionary of unique values to improve performance through data compression. The engine sees the first three values in the**container**column as unique and stores it in the dictionary. Since the engine is not case sensitive, it evaluates the names as identical.

The engine sees the name "Taina Hasu" as the same as "TAINA HASU" and "Taina HASU", so it does not save those variations, but refers to the first saved variation. The name "MURALI DAS" is capitalized because that is how the name appeared when the engine first evaluated it from top to bottom when loading data.

This image illustrates the scoring process:

In the example above, the Power BI engine loads the first row of data, creates the**container**dictionary and adds*Taina Hasu*besides. The engine also adds a reference to this value in the**container**Column of the table being loaded. The engine does the same for the second and third lines, since these names will not match each other if case is ignored.

For the fourth line, the engine compares the value against the names in the dictionary and finds the name. Since the engine is not case sensitive, "TAINA HASU" and "Taina Hasu" are the same. The engine does not add a new name to the dictionary, but references the existing name. The same process occurs for the remaining lines.

observation

Because the engine that stores and queries data in Power BI is not case-sensitive, you must be very careful when working with a case-sensitive source in DirectQuery mode. Power BI assumes that the source has removed duplicate rows. Because Power BI is case-insensitive, it treats two case-sensitive values as duplicates, whereas the font might not treat them as such. In these cases, the end result is undefined.

To avoid this situation, normalize the case in the source query or in Power Query Editor when using DirectQuery mode with a case-sensitive data source.

### Leading and trailing spaces

The Power BI engine automatically trims all trailing spaces that follow text data, but doesn't remove leading spaces that precede data. To avoid confusion, use it when working with data that contains leading or trailing spaces.Text.TrimFunction to remove spaces at the beginning or end of the text. If you do not remove leading spaces, a relationship may not be built due to duplicate values or images may return unexpected results.

The following example shows data about customers: a**Name**Column containing the name of the customer and a**Index**Unique column for each entry. The names are in quotes for clarity. The customer's name is repeated four times, but each time with a different combination of leading and trailing spaces. These fluctuations can occur over time with manual data entry.

Line | leadership space | trailing spaces | Name | Index | text length |
---|---|---|---|---|---|

1 | NO | NO | "Dylan Williams" | 1 | 14 |

2 | NO | mi | "Dylan Williams" | 10 | 15 |

3 | mi | NO | "Dylan Willians" | 20 | 15 |

4 | mi | mi | "Dylan Willians" | 40 | sixteen |

In the Power Query Editor, the resulting data is displayed as follows.

if you go to**Data**After loading the data into the Power BI tab, the same table looks like the image below, with the same number of rows as before.

However, a visual based on this data only returns two rows.

In the above image, the first row has a total value of*60*for him**Index**Field for the first row of the visual to represent the last two rows of data loaded. The second line with total**Index**value of*11*represents the first two lines. The difference in the number of rows between the visual and data tables is because the mechanism automatically removes or trims trailing spaces, but not leading spaces. Therefore, the engine evaluates the first and second rows and the third and fourth rows as identical, and the visual returns those results.

This behavior can also cause relationship-related error messages when duplicate values are detected. For example, depending on how your relationships are set up, you might see an error similar to the following image:

In other situations, you might not be able to create a many-to-one or one-to-one relationship because duplicate values are detected.

You can trace these errors back to leading or trailing spaces and correct them withText.Trim, o**directs**sob**to transform**to remove spaces in Power Query Editor.

## true/false type

O**truth or false**The data type is a boolean value.*TRUE*o*INCORRECT*. For the best and most consistent results, when loading a column with boolean true/false information in Power BI, set the column type to**truth or false**.

Power BI converts and displays data differently in certain situations. This section describes common cases of Boolean conversions and how to handle conversions that produce unexpected results in Power BI.

In this example, you load data about whether your customers subscribed to your newsletter. a value of*TRUE*indicates that the customer has subscribed to the newsletter and a value of*INCORRECT*indicates that the client is not logged in.

However, when you publish the report to the Power BI service, the newsletter subscription status column appears.*0*mi*-1*instead of the expected values of*TRUE*o*INCORRECT*. The following steps describe how this conversion takes place and how to avoid it.

The simplified query for this table is shown in the following image:

The data type of**subscribed to the newsletter**the column is defined**Any**and as a result, Power BI loads the data into the model as**Text**.

If you add a simple visualization that shows detailed information by customer, the data appears in the visualization as expected, both in Power BI Desktop and when published to the Power BI service.

However, when you refresh the dataset in the Power BI service, the**subscribed to the newsletter**The column in the visual displays values as*-1*mi*0*, instead of displaying them as*TRUE*o*INCORRECT*:

If you republish the report from Power BI Desktop, the**subscribed to the newsletter**column shows again*TRUE*o*INCORRECT*as expected, but as soon as there is an update in the Power BI service, the values are displayed again*-1*mi*0*.

The solution to avoid this situation is to set all boolean columns to write**truth or false**in Power BI Desktop and republish your report.

When you make the change, the preview shows the values in the**subscribed to the newsletter**Slightly different column. Instead of all text being capitalized as entered into the table, only the first letter is capitalized. This change is the result of changing the data type of the column.

After changing the data type, republishing to the Power BI service, and refreshing, the report will show the values as*TRUE*o*INCORRECT*, as expected.

In short, when working with Boolean data in Power BI, make sure your columns are set to**truth or false**Data type in Power BI Desktop.

## empty face

**Archive**is a DAX data type that represents and replaces SQL null values. You can create a space using theARCHIVEfunction and check for spaces using theIT IS EMPTYlogical function.

## binary type

You can use the...**Tracks**A data type to represent any data in binary format. In the Power Query Editor, you can use this data type when uploading binary files by converting them to other data types before uploading them to the Power BI model.

Binary columns are not supported in the Power BI data model. EITHER**Tracks**For legacy reasons, there is a selector in the Data View and Report View menus, but you may encounter errors when trying to load binary columns into the Power BI model.

observation

If there is a binary column in the result of a query steps, attempting to update the data through a gateway may result in errors. We recommend that you explicitly remove all binary columns as the last step in your queries.

## table type

DAX uses a table data type in many functions, such as B. Aggregation and time intelligence calculations. Some functions require a reference to a table. Other functions return a table, which you can use as input to other functions.

In some functions that take a table as input, you can specify an expression that evaluates to a table. Some functions require a reference to a base table. For specific feature requirements, seeDAX function reference.

## Implicit and Explicit Data Type Conversion

Each DAX function has specific requirements for the data types to be used as inputs and outputs. For example, some functions require integers for some arguments and dates for others. Other functions require text or tables.

If the data in the column supplied as an argument does not match the data type required by the function, DAX may return an error. However, whenever possible, DAX attempts to implicitly convert data to the required data type.

For example:

- If you enter a date as a string, DAX will parse the string and try to convert it to one of the Windows date and time formats.
- you can add
*TRUE + 1*and get the result*2*, since DAX implicitly converts*TRUE*by the number*1*, and perform the operation*1+1*. - When you add values in two columns, with one value represented as text ("12") and the other as a number (12), DAX implicitly converts the string to a number and then performs addition to get a numeric result. The expression
*= "22" + 22*Returns*44*. - If you try to concatenate two numbers, DAX will represent them as strings and concatenate them. The expression
*= 12 y 34*Returns*"1234"*.

### Implicit data conversion tables

The operator determines the type of conversion that DAX performs by converting the required values before performing the requested operation. The following tables list the operators and the conversion that DAX performs on each data type when it is matched to the data type in the intersection cell.

observation

These tables do not contain**Text**type of data. In some cases, when displaying a number in text format, Power BI tries to determine the type of number and display the data as a number.

#### sum (+)

WHOLE | CURRENCY | REAL | appointment time | |
---|---|---|---|---|

WHOLE | WHOLE | CURRENCY | REAL | appointment time |

CURRENCY | CURRENCY | CURRENCY | REAL | appointment time |

REAL | REAL | REAL | REAL | appointment time |

appointment time | appointment time | appointment time | appointment time | appointment time |

For example, if a sum operation uses a real number in combination with currency data, DAX will convert both values to REAL and return the result as REAL.

#### Subtraction (-)

In the following table, the row heading is the minuend (left side) and the column heading is the subtrahend (right side).

WHOLE | CURRENCY | REAL | appointment time | |
---|---|---|---|---|

WHOLE | WHOLE | CURRENCY | REAL | REAL |

CURRENCY | CURRENCY | CURRENCY | REAL | REAL |

REAL | REAL | REAL | REAL | REAL |

appointment time | appointment time | appointment time | appointment time | appointment time |

For example, if a subtraction operation uses a date of a different data type, DAX will convert both values to dates and the return value will also be a date.

observation

Data models support the unary operator - (negative), but this operator does not change the data type of the operand.

#### multiplication (*)

WHOLE | CURRENCY | REAL | appointment time | |
---|---|---|---|---|

WHOLE | WHOLE | CURRENCY | REAL | WHOLE |

CURRENCY | CURRENCY | REAL | CURRENCY | CURRENCY |

REAL | REAL | CURRENCY | REAL | REAL |

For example, if a multiplication operation combines an integer with a real number, DAX will convert both numbers to real numbers and the return value will also be REAL.

#### Division (/)

In the following table, the row heading is the numerator and the column heading is the denominator.

WHOLE | CURRENCY | REAL | appointment time | |
---|---|---|---|---|

WHOLE | REAL | CURRENCY | REAL | REAL |

CURRENCY | CURRENCY | REAL | CURRENCY | REAL |

REAL | REAL | REAL | REAL | REAL |

appointment time | REAL | REAL | REAL | REAL |

For example, if a division operation combines an integer with a money value, DAX will convert both values to real numbers, and the result will also be a real number.

### comparison operators

In comparison expressions, DAX considers boolean values greater than string values and string values greater than numeric or date/time values. Numbers and date/time values have the same precedence.

DAX does not perform implicit conversions on boolean or string values. BLANK or a blank value becomes*0*,*""*, o*INCORRECT*, based on the data type of the other value being compared.

The following DAX expressions demonstrate this behavior:

`=IF(FALSE()>"true","expression is true","expression is false")`

returns "expression is true".`=IF("12">12,"expression is true", "expression is false")`

returns "expression is true".`=IF("12"=12,"expression is true", "expression is false")`

returns "expression is false".

DAX performs implicit conversions to numeric or date/time types, as described in the following table:

Comparison Operator | WHOLE | CURRENCY | REAL | appointment time |
---|---|---|---|---|

WHOLE | WHOLE | CURRENCY | REAL | REAL |

CURRENCY | CURRENCY | CURRENCY | REAL | REAL |

REAL | REAL | REAL | REAL | REAL |

appointment time | REAL | REAL | REAL | appointment time |

### Spaces, empty strings, and null values

DAX represents a null value, a blank value, an empty cell, or a missing value with the same new value type, a BLANK. You can also generate blanks using the BLANK function or test for blanks using the ISBLANK usage function.

The way that operations such as addition or concatenation handle spaces depends on the function in question. The following table summarizes the differences between how spaces are handled in DAX formulas and Microsoft Excel.

Expression | DAX | standing out |
---|---|---|

LEARN + LEARN | ARCHIVE | 0 (null) |

LEARN + 5 | 5 | 5 |

LEARN * 5 | ARCHIVE | 0 (null) |

5/ LEARN | plenty | Error |

0/LEER | Yaya | Error |

LEARN/LEARN | ARCHIVE | Error |

FALSE OR EMPTY | INCORRECT | INCORRECT |

FALSE AND EMPTY | INCORRECT | INCORRECT |

TRUE OR EMPTY | TRUE | TRUE |

TRUE AND EMPTY | INCORRECT | TRUE |

EMPTY OR EMPTY | ARCHIVE | Error |

EMPTY AND EMPTY | ARCHIVE | Error |

## Next steps

You can do all sorts of things with Power BI Desktop and the data. To learn more about Power BI features, see the following resources:

- What is Power BI Desktop?
- General information about queries with Power BI Desktop
- Data sources in Power BI Desktop
- Model and combine data with Power BI Desktop
- Common query tasks in Power BI Desktop

## FAQs

### Which type of data can be uploaded in Power BI? ›

In Power BI, you can connect to or import data and reports from three types of files: **Microsoft Excel .** **xlsx and .** **xlsm files**.

**What are the data types of DAX in Power BI? ›**

Data type in model | Data type in DAX |
---|---|

Whole Number | A 64 bit (eight-bytes) integer value ^{1}^{,} ^{2} |

Decimal Number | A 64 bit (eight-bytes) real number ^{1}^{,} ^{2} |

Boolean | Boolean |

Text | String |

**What are the different data types in power query? ›**

Data type | Description |
---|---|

True/False | A Boolean value of either True or False. |

Decimal number | A 64-bit (eight-byte) floating point number. |

Percentage | A fixed decimal number with a mask to format as a percentage. |

Text | Strings, numbers, or dates represented in a text format. |

**How many datatypes of DAX are there? ›**

DAX offers **three** different numeric data types, which have an internal name that does not always correspond to the name provided in the user interface. DAX is currently used by three different products: Power Pivot, Analysis Services, and Power BI.

**What are the different data types? ›**

- Integer (int) It is the most common numeric data type used to store numbers without a fractional component (-707, 0, 707).
- Floating Point (float) ...
- Character (char) ...
- String (str or text) ...
- Boolean (bool) ...
- Enumerated type (enum) ...
- Array. ...
- Date.

**What is data type list the types? ›**

A data type is a classification of data which tells the compiler or interpreter how the programmer intends to use the data. Most programming languages support various types of data, including **integer, real, character or string, and Boolean**.

**What is query data type? ›**

Query is **a variable of the Query data type that specifies the query object**. Open() Runs a query object and generates a data set that can be read. The following code shows the syntax of the OPEN method. Query is a variable of the Query data type that specifies the query object.

**What are the types of context in DAX? ›**

There are different types of context: **row context, query context, and filter context**. Row context can be thought of as "the current row.” If you have created a calculated column, the row context consists of the values in each individual row and values in columns that are related to the current row.

**What are DAX variables? ›**

A DAX variable is indeed a constant, meaning that it is **a name for the value computed by the definition of the variable**. Each set of VAR / RETURN can include multiple variables, you can have any number of VAR for one RETURN.