Types of data Power BI Desktop – Power BI (2023)

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 number

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 asData Type.DoubleSay.

fixed decimal

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 asType of data. DecimalSay.

whole

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 asData type.Int64Say.

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.

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.

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.

true/false type

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.

empty face

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.

binary type

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.

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 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.

sum (+)

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).

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.

multiplication (*)

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.

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 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:

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.

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