Power BI Desktop 中的数据类型Data types in Power BI Desktop

本文介绍 Power BI Desktop 和数据分析表达式 (DAX) 中支持的数据类型。This article describes data types supported in Power BI Desktop and Data Analysis Expressions (DAX).

当你将数据加载到 Power BI Desktop 中时,它将尝试将源列的数据类型转换为能更好地支持更高效的存储、计算和数据可视化的数据类型。When you load data into Power BI Desktop, it will attempt to convert the data type of the source column into a data type that better supports more efficient storage, calculations, and data visualization. 例如,如果从 Excel 导入的值的列没有小数值,Power BI Desktop 会将整个数据列转换为整数数据类型,这能更好地存储整数。For example, if a column of values you import from Excel has no fractional values, Power BI Desktop will convert the entire column of data to a Whole Number data type, which is better suited for storing integers.

这一点很重要,因为某些 DAX 函数具有特殊的数据类型要求。This is important because some DAX functions have special data type requirements. 虽然在许多情况下 DAX 会为你隐式转换数据类型,但在某些情况下,它不会进行转换。While in many cases DAX will implicitly convert a data type for you, there are some cases where it will not. 例如,如果 DAX 函数需要日期数据类型,而你的列的数据类型为文本,DAX 函数将不能正常工作。For instance, if a DAX function requires a Date data type and the data type for your column is Text, the DAX function will not work correctly. 因此,获取适用于列的正确数据类型是重要并且有用的。So, it’s both important and useful to get the correct data type for a column. 隐式转换将会在本文的后一部分进行介绍。Implicit conversions are described later in this article.

确定并指定列的数据类型Determine and specify a column’s data type

在 Power BI Desktop 中,你可以在查询编辑器(或数据视图和报表视图中)确定并指定列的数据类型:In Power BI Desktop, you can determine and specify a column’s data type in the Query Editor, or in Data View or Report View:

查询编辑器中的数据类型Data types in Query Editor

数据视图或报表视图中的数据类型Data types in Data View or Report View

查询编辑器中的数据类型下拉列表具有两种当前在数据或报表视图中不提供的数据类型:日期/时间/时区持续时间The Data Type drop down in Query Editor has two data types not currently present in Data or Report View: Date/Time/Timezone and Duration. 将使用这些数据类型的列加载到模型中,并在数据或报表视图中对其进行查看时,使用日期/时间/时区数据类型的列将会被转换为日期/时间,而使用持续时间数据类型的列将会被转换为十进制数字。When a column with these data types are loaded into the model and viewed in Data or Report view, a column with a Date/Time/Timezone data type will be converted into a Date/Time, and a column with a Duration data type is converted into a Decimal Number.

数字类型Number types

Power BI Desktop 支持三种数字类型:Power BI Desktop supports three number types:

十进制数 – 表示 64 位(八字节)浮点数。Decimal Number – Represents a 64 bit (eight-byte) floating point number. 它是最常见的数字类型,与你通常想象的数字相对应。It’s the most common number type and corresponds to numbers as you usually think of them. 虽然十进制数类型被设计为处理带小数值的数字,但还可以处理整数。Although designed to handle numbers with fractional values, it also handles whole numbers. 十进制数类型可以处理从 -1.79E +308 到 -2.23E -308 的负值、0,以及从 2.23E -308 到 1.79E + 308 的正值。The Decimal Number type can handle negative values from -1.79E +308 through -2.23E -308, 0, and positive values from 2.23E -308 through 1.79E + 308. 例如,34、34.01 和 34.000367063 等数字都是有效的十进制数。For example, numbers like 34, 34.01, and 34.000367063 are valid decimal numbers. 可以用十进制数类型表示的最大值为 15 位数。The largest value that can be represented in a Decimal Number type is 15 digits long. 小数分隔符可出现在数字的任意位置。The decimal separator can occur anywhere in the number. 十进制数类型与 Excel 存储其数字的方式相对应。The Decimal Number type corresponds to how Excel stores its numbers.

定点十进制数 – 小数分隔符的位置是固定的。Fixed Decimal Number – Has a fixed location for the decimal separator. 小数分隔符右侧始终有四位数,并可以表示有意义的 19 位数。The decimal separator always has four digits to its right and allows for 19 digits of significance. 它可以表示的最大值为 922,337,203,685,477.5807(正或负)。The largest value it can represent is 922,337,203,685,477.5807 (positive or negative). 定点十进制数类型在舍入可能会引发错误的情况下非常有用。The Fixed Decimal Number type is useful in cases where rounding might introduce errors. 在处理许多带小数值的数字时,有时它们会累积并强制使得数据稍有偏离。When you work with many numbers that have small fractional values they can sometimes accumulate and force a number to be just slightly off. 由于小数分隔符右侧四位数其后的数字会被截断,定点十进制数可以帮助你避免这些类型的错误。Since the values past the four digits to the right of decimal separator are truncated, the Fixed Decimal type can help you avoid these kinds of errors. 如果你熟悉 SQL Server,此数据类型可对应于 SQL Server 的十进制 (19,4),或 Power Pivot 中的货币数据类型。If you’re familiar with SQL Server, this data type corresponds to SQL Server’s Decimal (19,4), or the Currency Data type in Power Pivot.

整数 – 表示 64 位(八字节)整数值。Whole Number – Represents a 64 bit (eight-byte) integer value. 由于它是一个整数,其小数位数右侧没有数字。Because it’s an integer, it has no digits to the right of the decimal place. 它支持 19 位数;从 -9,223,372,036,854,775,808 (-2^63) 到 9,223,372,036,854,775,807 (2^63-1) 的正数或负数。It allows for 19 digits; positive or negative whole numbers between -9,223,372,036,854,775,808 (-2^63) and 9,223,372,036,854,775,807 (2^63-1). 它可以表示各种数值数据类型可能的最大数字。It can represent the largest possible number of the various numeric data types. 与定点十进制数类型相同,在需要控制舍入的情况下,正数类型非常有用。As with the Fixed Decimal type, the Whole Number type can be useful in cases where you need to control rounding.

日期/时间类型Date/time types

Power BI Desktop 支持查询视图中的五种日期/时间数据类型,以及报表视图和模型中的三种日期/时间数据类型。Power BI Desktop supports five Date/Time data types in Query View and three in the Report View and model. 在加载到模型的过程中,日期/时间/时区和持续时间都将被转换。Both Date/Time/Timezone and Duration are converted during load into the model.

日期/时间 – 表示日期和时间值。Date/Time – Represents both a date and time value. 实际上,日期/时间值是以十进制数类型进行存储的。Underneath the covers, the Date/Time value is stored as a Decimal Number Type. 因此你实际上可以在这两种类型之间进行转换。So you can actually convert between the two. 日期的时间部分是以 1/300 秒 (3.33ms) 的整数倍的分数进行存储的。The time portion of a date is stored as a fraction to whole multiples of 1/300 seconds (3.33ms). 支持 1900 年和 9999 年之间的日期。Dates between years 1900 and 9999 are supported.

日期 – 仅表示日期(没有时间部分)。Date – Represents just a Date (no time portion). 转换为模型时,日期与表示分数值的带零日期/时间值相同。When converted into the model, a Date is the same as a Date/Time value with zero for the fractional value.

时间 – 仅表示时间(没有日期部分)。Time – Represents just Time (no Date portion). 转换为模型时,时间值与小数位数左侧没有数字的日期/时间值相同。When converted into the model, a Time value is the same as a Date/Time value with no digits to the left of the decimal place.

日期/时间/时区 – 表示 UTC 日期/时间。Date/Time/Timezone – Represents a UTC Date/Time. 目前,加载到模型中时,它将被转换为日期/时间类型。Currently, it’s converted into Date/Time when loaded into the model.

持续时间 – 表示时间的长度。Duration – Represents a length of time. 加载到模型中时,它将被转换为十进制数类型。It’s converted into a Decimal Number Type when loaded into the model. 与十进制数类型相同,可将其添加到日期/时间字段,或从日期/时间字段中减去,并获取正确的结果。As a Decimal Number type it can be added or subtracted from a Date/Time field with correct results. 与十进制数类型相同,你可以在显示度量值的可视化效果中轻松地使用它。As a Decimal Number type, you can easily use it in visualizations that show magnitude.

文本类型Text type

文本 - Unicode 字符数据字符串。Text - A Unicode character data string. 可以是字符串、数字或文本格式表示的日期。Can be strings, numbers or dates represented in a text format. 其最大字符串长度为 268,435,456 Unicode 字符(256 Mega 字符)或 536,870,912 字节。Maximum string length is 268,435,456 Unicode characters (256 mega characters) or 536,870,912 bytes.

True/False 类型True/false type

True/False – 为 True 或 False 的布尔值。True/False – A Boolean value of either a True or False.

空白/Null 类型Blanks/nulls type

空白 - DAX 中表示和替代 SQL Null 的数据类型。Blank - Is a data type in DAX that represents and replaces SQL nulls. 你可以使用 BLANK 函数创建空白,并使用 ISBLANK 逻辑函数对其进行测试。You can create a blank by using the BLANK function, and test for blanks by using the ISBLANK logical function.

表数据类型Table data type

DAX 在许多函数中使用表数据类型,例如聚合和时间智能计算。DAX uses a table data type in many functions, such as aggregations and time intelligence calculations. 某些函数需要引用表;其他函数返回随后可用于输入到其他函数的表。Some functions require a reference to a table; other functions return a table that can then be used as input to other functions. 在某些需要表作为输入的函数中,你可以指定计算结果为表格的表达式;对于一些函数,则需要引用基础表。In some functions that require a table as input, you can specify an expression that evaluates to a table; for some functions, a reference to a base table is required. 有关特定函数的要求的详细信息,请参阅 DAX 函数引用For information about the requirements of specific functions, see DAX Function Reference.

DAX 公式中的隐式和显式数据类型转换Implicit and explicit data type conversion in DAX formulas

关于用作输入和输出的数据类型,每个 DAX 函数都有特定的要求。Each DAX function has specific requirements as to the types of data that are used as inputs and outputs. 例如,某些函数要求对某些参数使用整数,而将日期用于其他;其他函数则要求文本或表。For example, some functions require integers for some arguments and dates for others; other functions require text or tables.

如果你指定的在列中作为参数的数据与该函数所需的数据类型不兼容,在很多情况下,DAX 将返回错误信息。If the data in the column you specify as an argument is incompatible with the data type required by the function, DAX in many cases will return an error. 但是,只要可能,DAX 会尝试将数据隐式转换为所需的数据类型。However, wherever possible DAX will attempt to implicitly convert the data to the required data type. 例如:For example:

  • 你可以以字符串的形式键入日期,DAX 会分析该字符串并尝试将其转换为 Windows 日期和时间格式之一。You can type a date as a string, and DAX will parse the string and attempt to cast it as one of the Windows date and time formats.
  • 你可以添加 TRUE+1 并获得结果 2,因为 TRUE 被隐式转换为数字 1,并执行 1+1 的操作。You can add TRUE + 1 and get the result 2, because TRUE is implicitly converted to the number 1 and the operation 1+1 is performed.
  • 如果在两个列中添加值,其中一个值恰好以文本方式表示 ("12"),而另一个值以数字方式表示 (12),DAX 会将字符串隐式转换为数字,然后执行加法并获得数值结果。If you add values in two columns, and one value happens to be represented as text ("12") and the other as a number (12), DAX implicitly converts the string to a number and then does the addition for a numeric result. 下面的表达式将返回 44: = "22" + 22。The following expression returns 44: = "22" + 22.
  • 如果尝试连接两个数字,Excel 会将其以字符串表示并进行连接。If you attempt to concatenate two numbers, Excel will present them as strings and then concatenate. 下面的表达式将返回 "1234": = 12 & 34。The following expression returns "1234": = 12 & 34.

隐式数据转换表Table of implicit data conversions

执行的转换的类型由运算符确定,它在执行所请求的操作之前会将值转换为所需的值。The type of conversion that is performed is determined by the operator, which casts the values it requires before performing the requested operation. 这些表列出了运算符,并指示当其与交叉行内的数据类型配对时,对列中每种数据类型执行的转换。These tables list the operators, and indicate the conversion that is performed on each data type in the column when it is paired with the data type in the intersecting row.

备注

这些表中不包含文本数据类型。Text data types are not included in these tables. 当数字以文本格式表示时,在某些情况下,Power BI 将尝试确定数字类型并将其表示为数字。When a number is represented as in a text format, in some cases Power BI will attempt to determine the number type and represent it as a number.

加法 (+)Addition (+)

运算符 (+)Operator(+) INTEGERINTEGER CURRENCYCURRENCY REALREAL 日期/时间Date/time
INTEGERINTEGER INTEGERINTEGER CURRENCYCURRENCY REALREAL 日期/时间Date/time
CURRENCYCURRENCY CURRENCYCURRENCY CURRENCYCURRENCY REALREAL 日期/时间Date/time
REALREAL REALREAL REALREAL REALREAL 日期/时间Date/time
日期/时间Date/time 日期/时间Date/time 日期/时间Date/time 日期/时间Date/time 日期/时间Date/time

例如,如果在加法运算中将实数与货币数据结合使用,两个值都会转换为 REAL,并且返回的结果为 REAL。For example, if a real number is used in an addition operation in combination with currency data, both values are converted to REAL, and the result is returned as REAL.

减法 (-)Subtraction (-)

下表中,行标题是被减数(左侧),列标题是减数(右侧)。In the following table the row header is the minuend (left side) and the column header is the subtrahend (right side).

运算符Operator(-) INTEGERINTEGER CURRENCYCURRENCY REALREAL 日期/时间Date/time
INTEGERINTEGER INTEGERINTEGER CURRENCYCURRENCY REALREAL REALREAL
CURRENCYCURRENCY CURRENCYCURRENCY CURRENCYCURRENCY REALREAL REALREAL
REALREAL REALREAL REALREAL REALREAL REALREAL
日期/时间Date/time 日期/时间Date/time 日期/时间Date/time 日期/时间Date/time 日期/时间Date/time

例如,如果在减法运算中将日期与其他任何数据类型结合使用,两个值都会转换为日期,返回的值也会是日期。For example, if a date is used in a subtraction operation with any other data type, both values are converted to dates, and the return value is also a date.

备注

数据模型还支持一元运算符 - (负号),但此运算符不会更改操作数的数据类型。Data models also support the unary operator, - (negative), but this operator does not change the data type of the operand.

乘法 (*)Multiplication(*)

运算符 (*)Operator(*) INTEGERINTEGER CURRENCYCURRENCY REALREAL 日期/时间Date/time
INTEGERINTEGER INTEGERINTEGER CURRENCYCURRENCY REALREAL INTEGERINTEGER
CURRENCYCURRENCY CURRENCYCURRENCY REALREAL CURRENCYCURRENCY CURRENCYCURRENCY
REALREAL REALREAL CURRENCYCURRENCY REALREAL REALREAL

例如,如果在乘法运算中将整数与实数结合使用,两个数字都会转换成为实数,并且返回的值也是 REAL。For example, if an integer is combined with a real number in a multiplication operation, both numbers are converted to real numbers, and the return value is also REAL.

除法 (/)Division (/)

下表中,行标题是分子(左侧),列标题是分母(右侧)。In the following table, the row header is the numerator and the column header is the denominator.

运算符 (/)(行/列)Operator(/) (Row/Column) INTEGERINTEGER CURRENCYCURRENCY REALREAL 日期/时间Date/time
INTEGERINTEGER REALREAL CURRENCYCURRENCY REALREAL REALREAL
CURRENCYCURRENCY CURRENCYCURRENCY REALREAL CURRENCYCURRENCY REALREAL
REALREAL REALREAL REALREAL REALREAL REALREAL
日期/时间Date/time REALREAL REALREAL REALREAL REALREAL

例如,如果在除法运算将整数与货币值结合使用,两个值都会转换为实数,并且结果也是实数。For example, if an integer is combined with a currency value in a division operation, both values are converted to real numbers, and the result is also a real number.

比较运算符Comparison operators

在比较表达式中,布尔值被视为大于字符串值,字符串值被视为大于数字或日期/时间值;数字和时间值被视为同级。In comparison expressions, Boolean values are considered greater than string values and string values are considered greater than numeric or date/time values; numbers and date/time values are considered to have the same rank. 布尔值或字符串值不执行任何隐式转换;BLANK 或空白值会被转换为 0/""/false,具体取决于其他进行比较的值的数据类型。No implicit conversions are performed for Boolean or string values; BLANK or a blank value is converted to 0/""/false depending on the data type of the other compared value.

下面的 DAX 表达式对此行为进行了说明:The following DAX expressions illustrate this behavior:

=IF(FALSE()>"true","Expression is true", "Expression is false"),返回 "Expression is true"=IF(FALSE()>"true","Expression is true", "Expression is false"), returns "Expression is true"

=IF("12">12,"Expression is true", "Expression is false"),返回 "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"),返回 "Expression is false"=IF("12"=12,"Expression is true", "Expression is false"), returns "Expression is false"

针对数字或日期/时间类型所执行的隐式转换如下表所述:Conversions are performed implicitly for numeric or date/time types as described in the following table:

比较运算符Comparison Operator INTEGERINTEGER CURRENCYCURRENCY REALREAL 日期/时间Date/time
INTEGERINTEGER INTEGERINTEGER CURRENCYCURRENCY REALREAL REALREAL
CURRENCYCURRENCY CURRENCYCURRENCY CURRENCYCURRENCY REALREAL REALREAL
REALREAL REALREAL REALREAL REALREAL REALREAL
日期/时间Date/time REALREAL REALREAL REALREAL 日期/时间Date/Time

空白、空字符串和零值的处理Handling blanks, empty strings, and zero values

在 DAX 中,Null、空值、空单元格或缺失值全都以相同的新值类型 BLANK 表示。In DAX, a null, blank value, empty cell, or a missing value are all represented by the same new value type, a BLANK. 你可以使用 BLANK 函数创建空白,并使用 ISBLANK 函数对其进行测试。You can also generate blanks by using the BLANK function, or test for blanks by using the ISBLANK function.

空白在运算中的处理方式(例如加法或连接)取决于单个函数。How blanks are handled in operations such as addition or concatenation depends on the individual function. 下表总结了 DAX 和 Microsoft Excel 公式对空白的处理方式的区别。The following table summarizes the differences between DAX and Microsoft Excel formulas, in the way that blanks are handled.

表达式Expression DAXDAX ExcelExcel
BLANK + BLANKBLANK + BLANK BLANKBLANK 0(零)0(zero)
BLANK + 5BLANK + 5 55 55
BLANK * 5BLANK * 5 BLANKBLANK 0(零)0(zero)
5/BLANK5/BLANK 无穷大Infinity 错误Error
0/BLANK0/BLANK NaNNaN 错误Error
空白/空白BLANK/BLANK BLANKBLANK 错误Error
FALSE OR BLANKFALSE OR BLANK FALSEFALSE FALSEFALSE
FALSE AND BLANKFALSE AND BLANK FALSEFALSE FALSEFALSE
TRUE OR BLANKTRUE OR BLANK TRUETRUE TRUETRUE
TRUE AND BLANKTRUE AND BLANK FALSEFALSE TRUETRUE
BLANK OR BLANKBLANK OR BLANK BLANKBLANK 错误Error
BLANK AND BLANKBLANK AND BLANK BLANKBLANK 错误Error