What are the most used Power BI Data Types in Power Query

What are the most used Power BI Data Types:

In the computer programming context, the Power BI data types are an attribute of data. This attribute informs the analyst about the programmer’s intention of using the data. These data types provide a set of values that gives value to an expression.

The Power BI data types can be classified into various categories. These are as follows:-

• Text.
• Number
• Date and time
• Boolean or logical.

The Text data type is generally referred to as a string. This is very simple in the sense that it is just simple text. The Boolean data types are also known as true/false types. These boolean values are as simple as their name suggests. They are either true or false. The essential Power BI data types are number day types and the date and time data types. 

They are discussed in the following manner- 

1. Number Data Types – These data types can be used for aggregating and assimilating relevant organization information. Several functions like adding, subtracting, calculating an average and finding fractional values can be done with this data type. The numeric data type can be further categorised as follows- 

• Whole number data type.
• Decimal data type.
• Currency data type.
• Percentage data type.

2. Date and time data types – This data type provides a huge source of information. The right data, time, month, year and other information can be easily extracted from these data types. The date and time data type formats can be categorised further into the following-

•  Date/ Time
• Date
• Time
• Date/ Time/ Timezone
• Duration.

Recently, a new data type called the Binary data type has also been noticed.

Meaning of Data Types: A Power Query Overview

In Power Query, data types are utilized to classify values. This is done to give a structure to the data. The data types are defined clearly at the field level. The values within these fields are provided to comfort the data type of the field. 

Some of the most common data types within the Power Query can be illustrated as follows:-

• Text data type – This is a Unicode character data string. It may consist of dates, numbers and other information in the form of texts.
• Boolean data type – It is also known as the true/ false data type. In this, the value provided is either true or false.
• Decimal data type – This data type represents a 64-bit pointing point in the number. It is considered the most common type of number day. The decimal data type is believed to be similar to numbers as they generally are.
• Fixed Decimal Number data type – This data type is also known as the currency data type. In this, there is a specific location fixed for providing the decimal. The decimal separator usually has 4 digits to the right. About 19 digits get significance beyond the decimal separator.
• Whole number data type – This data type recognises 64-bit integer values. Moreover, as the value presented here is an integer, there is no digit to the right of the decimal.
• Percentage Data type – This data type is used to represent the column values in the percentage format. The representation is made on the Power Query Editor Window.

Few More,

• Date/Time data type – The Date and time data type is useful in representing both date and time value. But in hiding, this data type is also stored as the decimal data type.
• Date data type –  This data type represents the date as its value.
• Time data type – It represents time as its value.
• Date/ Time/ Timezone – The data type represents the time and timezone value. This same value can be converted into the date and time model when formatted.
• Duration data type – This represents the period as its value.
• Binary data type – The binary data type can be used to represent any other kind of data. Also, this data needs to be in a binary format.
• Any data type – Any data type is a kind of status. This is provided to any column that does not consist of any specific data type.

What are the new Power Query types in Excel:

The Power Query in Excel consists of a separate type system. This is generally known as the M-type system. In simple words, the type is a value that is used to classify other values. If a value is classified by a type, the value is considered to conform to that type. 

The data types within this system can be classified as follows:-

• Primitive types – These are used to classify several primitive values, like the binary, date, time, duration and others. It also represents certain abstract values like the function, any and others.
• Record types – This type is used to classify record values. These depend on the field names and type of values.
• List types – The list type is used for the classification of lists. It is done using a single item base type.
• Function types These are used to classify function values. Moreover, it is done based on the types of their parameters and their values for return.
• Table types –  The table type classifies the values within tables. It is done based on column names, types and keys.
• Nullable types – The nullable type classifies the value null. It is done in addition to the values classified by a null type.
• Type types – These classify certain values that are types.

Numeric Data Types in DAX: Meaning

The concept of numeric data types consists of data types that represent numeric values. Data models for DAX functions consist of numeric data types that can be categorized into three types. These are as follows:-

1. Integer
2. Floating Point
3. Fixed Decimal Number

The data models created under Power Pivot, Power BI or Analysis Services Tabular will contain columns. 

These columns may consist of the following three data types – 

• Integer – This data type consists of values that have no decimal places. These integers can be either positive, negative or even whole numbers. The data here is stored in 8 bytes.
• Floating Point – It is also known as the Decimal number. The data type consists of numbers that can have decimal places covering a wide range of values. The significant digits after the decimal are limited to 15. Here also, the data is stored in 8 bytes.
• Fixed Decimal Number – This data type is also known as Currency.  This store the values that contain four digits after decimal with precision. The data in this type too is stored in 8 bytes.

The process of creating a data model must include the type detection configuration that must be kept in mind.

The difference in Calculation: Numeric Data Conversion approach

There comes a difference in the calculation process due to numeric data type conversion. Firstly, a situation consisting of multiplication and division in operands of different data types is taken. In this, the presence of a currency data type must be considered. The multiplication calculation is bound to show different results than the usual. This is because of the point in the calculation where the conversion is performed. 

The scenario implies that the currency data type is necessary to avoid rounding errors in aggregation. But even if it is present, it should be managed to avoid other rounding errors.

Dataflow Query Editor Types: A detailed Understanding

1. Implicit and Explicit Data Type Conversion in DAX Formulas

The DAX functions in Power BI have certain specifications. Also, these include the specific requirement of certain data types for input and output purposes. For instance, on one hand some functions require integers or dates for arguments. On the other hand, some functions need texts or tables.

In certain scenarios, DAX returns functions as errors. This occurs when the data in the column considered as argument is incompatible with the day type required for the function. 

But, in several places, DAX attempts to convert or implicit the data to the required data type. As an instance, the following can be considered – 

• In an attempt to concentrate two numbers, EXCEL will present them as strings. Then the concentration will be performed.
• The user can attempt to concentrate TRUE+1. Here, TRUE is converted in the implicit method into 1. Then the calculation 1+1 is performed.

2. Data Type Conversion Matrix

Data type conversion matrix performs the conversion functions in the following scenarios – 

1. If data from one object is moved to, compared, or combined with data from a different object.
2. Secondly. the data from Transact-SQL result column, code or a variable is moved into a variable program.

In the first scenario, the data from one type is converted to the data of another type in the object. The second scenario consists of the conversion of SQL Server system data type into the data type of the variable.

Explanation on Data Conversion while changing data types in Power Pivot:

In the Power Pivot, if an error occurs in choosing the data type in a column, then the data type is not compatible. All the data types are not configured to support the conversion. After a user chooses a conversion, the Power Pivot warns him about possible changes to the data. In such a scenario, the user needs to select OK in order to accept. This changes the data into the desired data type.

Fill In The Form For – FREE 30 Mins Power BI Consulting