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 位元 (8 位元組) 浮點數。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. 小數分隔符號右邊一律為 4 位數,並允許 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. 由於在 [固定十進位數字] 類型中,超過小數分隔符號右邊 4 位數的值會遭到截斷,因此可協助您避免這類錯誤。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 的 Decimal(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 位元 (8 位元組) 整數值。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.33 毫秒)。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 字元 (2.56 億個字元) 或 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,因此傳回的結果為 REALFor 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/BLANKBLANK/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