Selecting Column Data Types

Setting the data types of columns in Power BI Desktop is usually the final step of data retrieval queries, and has great implications for all layers of the solution, including data refresh, data modeling, and visualization. Choosing appropriate data types reduces the risk of refresh failures, ensures consistent report results, and provides analytical flexibility to the data model and visualization layers.

This recipe demonstrates how to choose and define data types to load to Power BI Desktop. Additional details on data types and the implications of data types for Power BI development are contained in the sections following these examples.

Getting ready

In preparation for this recipe, import the FactCallCenter table from the AdventureWorksDW2019 database by doing the following:

1. Open Power BI Desktop and choose Transform data from the ribbon of the Home tab to open the Power Query Editor.
2. Create an Import mode data source query called AdWorksDW. This query should be similar to the following:

   let
       Source = Sql.Database("localhost\MSSQLSERVERDEV", "AdventureWorksDW2019")
   in
       Source
   

3. Isolate this query in a query group called Data Sources.
4. Right-click AdWorksDW and choose Reference, select the FactFinance table in the data preview area, and rename this query FactFinance.
5. Move the FactFinance query to the Other Queries group.

For additional details on performing these steps, see the Managing Queries and Data Sources recipe in this chapter.

How to Select Column Data Types

To implement this recipe, perform the following steps:

1. Use the Table.TransformColumnTypes function to revise the type of the numeric integer FinanceKey column to text. Note that the column header for the FinanceKey column is initially prefaced with a 123 icon and changes to an ABC icon. Also, note that the row values are initially left justified and italicized and after transformation to text are aligned to the right and not italicized.

   let
       Source = AdWorksDW,
       dbo_FactFinance = Source{[Schema="dbo",Item="FactFinance"]}[Data],
       FinanceKeyText = 
           Table.TransformColumnTypes(
               dbo_FactFinance, {{"FinanceKey", Text.Type}}
           )
   in
       FinanceKeyText
   

2. Add a numeric column from a source column stored as text using the Number.FromText and Table.AddColumn functions.

       FinanceKeyNum = 
           Table.AddColumn(
               FinanceKeyText, "FinanceKeyNum",
               each Number.FromText([FinanceKey]),Int64.Type
           )
   

3. Change the fixed decimal Amount column to currency using the Table.TransformColumnTypes function. Note that the column header icon changes from 1.2 initially to a dollar sign, $.

       Currency = 
           Table.TransformColumnTypes(
               FinanceKeyNum,{{"Amount", Currency.Type}}
           )
   

How it works

For the FinanceKeyText expression, the Table.TransformColumnTypes function takes two parameters. The first parameter is the table to operate upon; the second is a list of list pairs that include the column name to transform and then the transformation data type. Multiple pairs can be included in the form:

{ { "Column1", type }, { "Column2", type } }

Additional valid values for the type parameter include the following:

• Currency.Type sets the column as a Fixed Decimal Number to two decimal places.
• Decimal.Type or type number sets the new column as a Decimal Number.
• Percentage.Type sets the column as a Percentage data type. Unlike Whole Number, Fixed Decimal Number, and Decimal Number, this type does not have a corresponding type in the data model. When loaded to the data model, the Percentage data type is represented as a Decimal Number type.
• Text.Type or type text sets the column to a Text data type.
• Date.Type or type date sets the column to a Date data type.
• DateTime.Type or type datetime sets the column to a Date Time data type.
• DateTimeZone.Type or type datetimezone sets the column to a Date/Time/Timezone data type.
• Time.Type or type time sets the column to a Time data type.
• Duration.Type or type duration sets the column to a Duration data type.
• Logical.Type or type logical sets the column to a True/False data type.
• Binary.Type or type binary sets the column to a Binary data type.

Like all M expressions, data type declarations are case-sensitive.

For the FinanceKeyNum expression, the type parameter for the Table.AddColumn function is optional. Leaving this parameter blank results in an Any data type, which would be loaded into the data model as a Text data type. By specifying Int64.Type as the optional type parameter to Table.AddColumn, the new column stores whole numbers instead of text.

Great care should be taken when choosing data types. For example, convert Decimal Number to Fixed Decimal Number data types if consistent rounding results are required. Also, converting from Decimal Number to Fixed Decimal Number can marginally improve data compression and query performance. A Decimal data type is approximate and can produce inconsistent reporting results due to rounding. Decimal Number data types are floating-point (approximate) data types with 15 digits of precision. Fixed Decimal Number data types store 19 digits of precision and four significant digits after the decimal. Whole Number data types store up to 19 digits of precision.

Revising Text data types to Numeric data types impacts the DAX metrics that can be written. For example, if a Calendar Year column is stored as a Text data type, the following DAX metric will fail due to the type incompatibility of the number 2016:

Figure 2.38: DAX measure expression

Revising Calendar Year to a whole number type avoids the need to use VALUE or FORMAT functions in each DAX measure. Power BI Desktop provides rich analysis capabilities for columns of the Date data type, including drill-down hierarchies, visual calendar pickers for filters, custom date filtering logic in slicers, and calculations such as for the first and last date. Given these capabilities, as well as DAX functionality with Date data types, converting Text and Numeric types to Date data types can provide significantly more options to develop Power BI content.

There's more...

While there are many topics that can be discussed regarding data types in Power Query, two are worth exploring in greater detail: automatic data type detection and date with locale. When enabled, automatic data type detection occurs when using unstructured data sources, such as flat files and Excel workbooks. When importing such data, a Changed Type step is added that uses a sampling or preview of each source column to determine the appropriate type.

Figure 2.39: Automatic Data Type Selection Step

If the data sample does not reflect the full or future set of values for the column, the data type selected may be incorrect. Automatic type detection is not used with structured relational database systems such as SQL Server.

You can avoid automatic type detection via the Data Load options in the File | Options and settings | Options dialog.

As automatic detection is a CURRENT FILE option only, and since the setting is enabled by default, you currently need to disable this automatic type detection for each new file. It is recommended that you disable this for each file or manually remove the Changed Type step and make your own decisions regarding data type transformations.

Power Query also supports different locales or cultures. If there's any potential for Date data types to be sourced from a region with a different date standard than your local system, you should apply the Locale option to the Table.TransformColumnTypes function; for example, when a date column stores date values in the format dd/mm/yyyy, whereas the local system uses mm/dd/yyyy. Trying to convert this date directly on a desktop configured for US English causes an error as the first two digits are greater than 12. However, specifying the source locale in the transform expression allows for successful conversion. The Query Editor provides a simple interface for the source locale. Simply right-click on the column, expand Change Type, and then select Using Locale….

Figure 2.40: Change Type with Locale interface

Alternatively, you can add the locale to the expression itself directly:

DateLocale = 
    Table.TransformColumnTypes(
        PreviousStep, {{ "Date", type date }}, "en-GB"
    )

See also

• Table.AddColumn: http://bit.ly/3vGJZ6b
• Table.TransformColumnTypes: http://bit.ly/3s5AfjC
• Data types in Power Query: http://bit.ly/392iWIP
• Language ID and tag reference: http://bit.ly/3cXpPfw
• 10 Common Mistakes You Do In #PowerBI #PowerQuery – Pitfall #2: http://bit.ly/2otDbcU
• Choosing Numeric Data Types in DAX: http://bit.ly/2nOWYAm