- 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 awholeData 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 functionGivendatatype, but the datatype for your column isText, 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 selecttype of dataI amto transformband group
In Data View or Report View, select the column, and then select the dropdown arrow next totype of dataNocolumn toolsribbon 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 Zonemitime duration. When you load a column with these data types into the Power BI model, aDate/Time/Time ZoneThe column becomes aappointment timedata type and atime durationThe column becomes adecimal numbertype of data.
OTracksThe data type is not supported outside of the Power Query Editor. In the Power Query Editor, you can use theTracksData type when uploading binary files if you convert them to other data types before uploading them to the Power BI model. EITHERTracksThe selection exists in the Data View and Report View menus for legacy reasons, but when trying to loadTracksInsert 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, miwhole.
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 numberis the most common type of number and can handle whole numbers and fractions.decimal numberrepresents 64-bit (eight byte) floating point numbers with negative values-1,79E +308through-2.23E -308, positive values out2.23E-308through1,79E +308, mi0. numbers like34,34.01, mi34.000367063are valid decimal numbers.
The highest precision thatdecimal numberThe 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
Ofixed decimalThe 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 offixed decimalcan represent is positive or negative922.337.203.685.477.5807.
Ofixed decimalThe 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. EITHERfixed decimaltype 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 ServerDecimals (19.4), or theCurrencyData type in Analysis Services and Power Pivot in Excel. TOM specifies this type as
Type of data. DecimalSay.
wholerepresents a 64-bit (eight byte) integer value. Since it is an integer,wholedoes 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) mi9.223.372.036.854.775.806(2^63-2), so that it can represent as many types of numeric data as possible.
As with himfixed decimalwrite thewholeThe type can be useful when you need to control rounding. TOM represents thewholedata type as
Precision of numeric type calculations
column values ofdecimal numberThe data types are saved asaboutData 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 ofdecimal numberThe 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 fromdecimal numberanyfixed decimalowhole, or carry out a forced round withREDONDO. Ofixed decimalThe 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 ofdecimal numberThe 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 fromdecimal numberForfixed decimalowhole.
Types of date/time
Power BI Desktop supports cincoappointment timeData types in Power Query Editor. BothDate/Time/Time Zonemitime durationConvert to the Power BI Desktop data model when loaded. The model supportsappointment time, or you can format the values asGivenoTempoindependent.
appointment timerepresents a datetime value. the underlying valueappointment timeThe value is saved asdecimal numberWrite 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.
Givenrepresents just a date without a time slice.Givenconverted to the model as aappointment timeValue with zero for the fractional value.
Temporepresents just a time with no date part.Tempoconverted to the model as aappointment timeValue with no digits to the left of the decimal point.
Date/Time/Time Zonerepresents a UTC datetime with a time zone offset and is converted toappointment timewhen 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 durationrepresents a period of time and becomes adecimal numberinsert when loading them into the model. Asdecimal numbertype, you can add or subtract the valuesappointment timeValues with correct results and just use the values in visualizations that show magnitude.
kind of text
OTextThe 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.TextData 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 iscase does not matterand treats different uppercase and lowercase letters as the same value. "A" is equal to "a". However, Power Query isBe 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: Onrequest noUnique column for each order and acontainerColumn showing the name of the recipient entered manually when placing the order. Power Query Editor displays multiple requests with the samecontainernames entered into the system with different capitalizations.
After Power BI loads the data, duplicate the names in theDataThe 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 likecontainerthe engine stores a dictionary of unique values to improve performance through data compression. The engine sees the first three values in thecontainercolumn 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 thecontainerdictionary and addsTaina Hasubesides. The engine also adds a reference to this value in thecontainerColumn 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.
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: aNameColumn containing the name of the customer and aIndexUnique 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|
In the Power Query Editor, the resulting data is displayed as follows.
if you go toDataAfter 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 of60for himIndexField for the first row of the visual to represent the last two rows of data loaded. The second line with totalIndexvalue of11represents 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, odirectssobto transformto remove spaces in Power Query Editor.
Otruth or falseThe data type is a boolean value.TRUEoINCORRECT. For the best and most consistent results, when loading a column with boolean true/false information in Power BI, set the column type totruth 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 ofTRUEindicates that the customer has subscribed to the newsletter and a value ofINCORRECTindicates that the client is not logged in.
However, when you publish the report to the Power BI service, the newsletter subscription status column appears.0mi-1instead of the expected values ofTRUEoINCORRECT. 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 ofsubscribed to the newsletterthe column is definedAnyand as a result, Power BI loads the data into the model asText.
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, thesubscribed to the newsletterThe column in the visual displays values as-1mi0, instead of displaying them asTRUEoINCORRECT:
If you republish the report from Power BI Desktop, thesubscribed to the newslettercolumn shows againTRUEoINCORRECTas expected, but as soon as there is an update in the Power BI service, the values are displayed again-1mi0.
The solution to avoid this situation is to set all boolean columns to writetruth or falsein Power BI Desktop and republish your report.
When you make the change, the preview shows the values in thesubscribed to the newsletterSlightly 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 asTRUEoINCORRECT, as expected.
In short, when working with Boolean data in Power BI, make sure your columns are set totruth or falseData type in Power BI Desktop.
Archiveis 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.
You can use the...TracksA 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. EITHERTracksFor 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.
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.
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.
- 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 addTRUE + 1and get the result2, since DAX implicitly convertsTRUEby the number1, and perform the operation1+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" + 22Returns44.
- If you try to concatenate two numbers, DAX will represent them as strings and concatenate them. The expression= 12 y 34Returns"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.
These tables do not containTexttype 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.
|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.
In the following table, the row heading is the minuend (left side) and the column heading is the subtrahend (right side).
|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.
Data models support the unary operator - (negative), but this operator does not change the data type of the operand.
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.
In the following table, the row heading is the numerator and the column heading is the denominator.
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.
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 becomes0,"", oINCORRECT, 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:
|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.
|LEARN + LEARN||ARCHIVE||0 (null)|
|LEARN + 5||5||5|
|LEARN * 5||ARCHIVE||0 (null)|
|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|
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
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|
|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.|
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. ...
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.