How do you define custom data types?

Event Tracing for Windows (ETW) defines several simple and complex types for use in the tracing functions. These types are declared in the Defaultwpp.ini file. However, you can create your own custom data types.

You use a custom data type when you want to declare variables and use meaningful terms—instead of integers—to describe the value of the variables.

For example, the DiskState variable contains the state of the disk. The following are values of DiskState:

Instead of reading «DiskState=2» in a trace message, and then having to look up the meaning of 2, you can define a custom type called DiskState, to get a trace message that says «DiskState is Failed.»

Creating a Custom Data Type

To create a custom data type, complete the following steps:

Create a local configuration file that has the .ini file name extension, such as localwpp.ini. You cannot add a custom type to a header or source file.

Use the TYPEMACRO constant to define the custom data type.

Identify the configuration data in your sources or header file.

Add the -ini parameter to the RUN_WPP macro in your source file.

Use the custom data type in trace messages.

Defining a TYPEMACRO constant

Define a TYPEMACRO constant that has the following format. The values are defined as a list of strings.

ItemListShort
Signed 16-bit integer.

ItemListLong
Signed or unsigned 32-bit integer.

ItemSetByteShort
Signed 16-bit bit value.

ItemSetByteLong
Signed or unsigned 32-bit bit value.

Identifying the Configuration Data

If you added the custom data type to a file that has other code, such as a source file or a header file, then use the begin_wpp config and end_wpp statements to identify the configuration data in the file. For example:

If you added the custom data type to a local configuration file, then the begin_wpp config and end_wpp statements are not needed.

Add the -ini parameter

When you create a local configuration file for a custom type, you need to add the -ini parameter to the RUN_WPP statement that invokes the WPP preprocessor.

The -ini parameter directs ETW to search for configuration data in configuration files (.ini), in addition to using Defaultwpp.ini. For example:

You must not specify the -km switch in the RUN_WPP directive for user-mode applications or dynamic-link libraries (DLLs).

Using the Custom Data Type

After you have defined a custom data type, you can use it in trace messages. Precede the type name with a percent sign (%) and surround it with exclamation marks (!). For example:

The resulting trace message uses the constant that you defined to represent the value:

Data types in Power Query

Data types in Power Query are used to classify values to have a more structured dataset. Data types are defined at the field level—values inside a field are set to conform to the data type of the field.

The data type of a column is displayed on the left side of the column heading with an icon that symbolizes the data type.

Power Query provides a set of contextual transformations and options based on the data type of the column. For example, when you select a column with a data type of Date, you get transformations and options that apply to that specific data type. These transformations and options occur throughout the Power Query interface, such as on the Transform and Add column tabs and the smart filter options.

The most common data types used in Power Query are listed in the following table. Although beyond the scope of this article, you can find the complete list of data types in the Power Query M formula language Types article.

Data type	Icon	Description
Text		A Unicode character data string. Can be strings, numbers, or dates represented in a text format. Maximum string length is 268,435,456 Unicode characters (where each Unicode character is two bytes) or 536,870,912 bytes.
True/False		A Boolean value of either True or False.
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. 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. For example, numbers like 34, 34.01, and 34.000367063 are valid decimal numbers. The largest precision that can be represented in a Decimal Number type is 15 digits long. The decimal separator can occur anywhere in the number. The Decimal Number type corresponds to how Excel stores its numbers.
Fixed decimal number		Also known as the Currency type, this data type has a fixed location for the decimal separator. The decimal separator always has four digits to its right and allows for 19 digits of significance. 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 slightly off. Because the values past the four digits to the right of the decimal separator are truncated, the Fixed Decimal Number type can help you avoid these kinds of errors.
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. It allows for 19 digits; positive or negative whole numbers between –9,223,372,036,854,775,807 (–2^63+1) and 9,223,372,036,854,775,806 (2^63–2). It can represent the largest possible precision of the various numeric data types. As with the Fixed Decimal Number type, the Whole Number type can be useful in cases where you need to control rounding.
Percentage		Fundamentally the same as a Decimal Number type, but it has a mask to format the values in the column as a percentage in the Power Query Editor window.
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. The time portion of a date is stored as a fraction to whole multiples of 1/300 seconds (3.33 ms). Dates between the 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.
Date/Time/Timezone		Represents a UTC Date/Time with a time-zone offset. It’s converted into Date/Time when loaded into the model.
Duration		Represents a length of time, which is 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. Because it’s a Decimal Number type, you can easily use it in visualizations that show magnitude.
Binary		The Binary data type can be used to represent any other data with a binary format.
Any		The Any data type is the status given to a column that doesn’t have an explicit data type definition. Any is the data type that classifies all values. We recommend that you always explicitly define the column data types for your queries from unstructured sources, and avoid having any columns with the Any data type as the output of your query.

Data type detection

Data type detection occurs automatically when connecting to:

Structured data sources such as databases, Power Query reads the table schema from the data source and automatically displays the data by using the correct data type for each column.

Unstructured sources such as Excel, CSV, and text files, Power Query automatically detects data types by inspecting the values in the table. By default, automatic data type detection is enabled in Power Query for unstructured sources.

You can also use the Detect data type command in the Any column group on the Transform tab to automatically detect the data types of the columns in your table.

How to define a column data type

You can define or change the data type of a column in any of four places:

On the Home tab, in the Transform group, on the Data type drop-down menu.

On the Transform tab, in the Any column group, on the Data type drop-down menu.

By selecting the icon on the left side of the column heading.

On the column shortcut menu, under Change Type.

Automatic detection of column data type and headers

This setting is specifically for unstructured sources. It helps you by automatically inspecting and detecting column types and headers based on the first 200 rows of your table. When this setting is enabled, Power Query automatically adds two steps to your query:

• Promote column headers: Promotes the first row of the table to be the column header.
• Changed type: Converts the values from the Any data type to a data type based on the inspection of the values from each column.

By default, this setting is enabled. To disable or enable this setting, follow the steps that apply to your Power Query experience.

To configure automatic data type detection in Power Query Online

On the Home tab, select Options, and then select Project options. In the Project options window, select the Automatically detect column types and headers for unstructured sources check box.

To configure automatic data type detection in Power Query for Desktop

You can define this behavior both at the global and per-file level in the Options window (in the Power Query Editor, on the File tab, select Options and settings > Options).

Global: On the left pane under Global, select Data load. On the right pane under Type detection, you can select any of three type detection configurations that will be applied to every new file created in your application:

• Always detect column types and headers for unstructured sources
• Detect column types and headers for unstructured sources according to each file’s setting
• Never detect column types and headers for unstructured sources

Current file: On the left pane under Current file, select Data load. On the right pane under Type detection, select whether you want to enable or disable type detection for the current file.

Document or project locale

Power Query handles two distinct components that manage the way that things look and are interpreted:

• Localization: the component that tells Power Query in what language it should be displayed.
• Globalization: the component that handles the formatting of the values, in addition to the interpretation of text values.

Locale is a single value that holds both the localization and globalization components. Locale is used to interpret text values and convert them into other data types. For example, the locale English (United States) means that the localization is in United States English and the globalization, or format of the value, is based on the standards used in the United States.

When Power Query defines a column data type or converts from one data type to another, it has to interpret the values to be converted before it can transform them to a different data type.

In Power Query Online, this interpretation is defined in Project options, under Locale.

In Power Query for Desktop, Power Query automatically recognizes your operating system regional format and uses that to interpret the values for data type conversion. To override this locale configuration, open the query Options window, and in the left pane under Current file, select Regional settings. From here, you can change the locale to the setting you want.

This locale setting is important for interpreting text values into a specific data type. For example, imagine that you have your locale set as English (United States), but a column in one of your CSV files has dates formatted in the United Kingdom format of day/month/year.

When you try setting the data type of the Date column to be Date, you get error values.

These errors occur because the locale being used is trying to interpret the date in the English (United States) format, which is month/day/year. Because there’s no month 22 in the calendar, it causes an error.

Instead of trying to just select the Date data type, you can right-click the column heading, select Change type, and then select Using locale.

In the Change column type with locale dialog box, you select the data type that you want to set, but you also select which locale to use, which in this case needs to be English (United Kingdom).

Using this locale, Power Query will be able to interpret values correctly and convert those values to the right data type.

To verify final date values

The formatting of the values is driven by the globalization value. If you have any doubts about the value displayed by Power Query, you can verify the conversion of date values by adding new columns for the day, month, and year from the value. To do this, select the Date column and go to the Add column tab on the ribbon. In the Date and time column group, you’ll see the options for a date column.

From here, you can extract parts of the date value, such as the year number, the month number, the day number, or even more columns extracted from the Date column.

By using these columns, you can verify that your date value has been converted correctly.

Data type conversion matrix

The following matrix is designed to give you a quick look at the feasibility of data type conversion of a value from one data type to another.