Data Types in Power BI Desktop

What is Power BI?

Power BI is a Business Intelligence (BI) tool. The main purpose of BI tools is to track Key Performance Indicators (KPIs) and uncover insights in business data to inform decision-making across the organization better.

Power BI is used in many different ways depending on the role of the individual, from developers, analysts, managers, and directors, to everyone in between.

Power BI comprises three main applications:

1.    Power BI Desktop - a free desktop application for building and designing reports.

2.    Power BI Service - the online publishing service for viewing and sharing reports and dashboards.

3.    Power BI mobile apps - for viewing reports and dashboards on the go.

What is DAX in Power BI?

Data Analysis Expressions (DAX) is a formula language in Power BI that allows users to create custom calculations and aggregations for data analysis. It is a library of functions and operators that can be combined to build formulas and expressions in Power BI, Analysis Services, and Power Pivot in Excel data models.

DAX formulas include functions, operators, and values to perform advanced calculations and queries on data in related tables and columns in tabular data models.

DATA TYPES in Power BI Desktop 

When Power BI imports data, it automatically optimizes the data types of the source columns to improve data compression, calculation performance, and visualization capabilities, ensuring a more efficient and effective data analysis experience.

For example, if a column of values you import from Excel has no fractional values, Power BI Desktop converts the data column to a Whole number data type, which is better suited for storing integers.

Binary, Boolean, Currency, Date/Time, Decimal, Integer, String, Variant are the most significant data types in Power BI.

Binary data

What is binary data used for?

The binary number system is the base of all computing systems and operations. It enables devices to store, access and manipulate all types of information directed to and from the CPU or memory.

The Binary data in Power BI is any data that is not in text format and is stored as a series of 1s and 0s

The Binary data type is used in the data model to store images or other non-structured types of information. It is not available in DAX. It was mainly used by Power View, but it might not be available in other tools such as Power BI

Boolean

The True/false data type is a Boolean value of either True or False. For the best and most consistent results, when you load a column that contains Boolean true/false information into Power BI, set the column type to True/False. Power BI converts and displays data differently in certain situations.

How do I create a Boolean column in Power BI?

Creating a New Column in Power BI to Count Boolean Values

1. Click on the “Modeling” tab on the ribbon.

2. Select “New Column” from the “Calculation” group.

3. Enter a name for your new column.

4. Use the “IF” function to assign a numerical value to each true/false value in your dataset.

Click “OK” to create the new column

Decimal Number

The Currency data type, also known as Fixed Decimal Number in Power BI, stores a fixed decimal number.

 It can represent four decimal points and it is internally stored as a 64-bit integer value divided by 10,000.

Currency in Power BI

Formatting data as currency in Power BI is relatively straightforward.

First, select the data point or field you want to format.

Next, click on 'Format' option located in the Visualizations pane.

In the formatting options, select the 'Currency' category, which is represented by a dollar sign ($) icon.

Date/Time

Power BI Desktop supports five Date/Time data types in Power Query Editor.

• Both Date/Time/Timezone and Duration convert during load into the Power BI Desktop data model.

• The model supports Date/Time, or you can format the values as Date or Time independently.

• Date/Time represents both a date and time value.

• Date represents just a date with no time portion. A Date converts into the model as a Date/Time value with zero for the fractional value.

• Time represents just a time with no date portion. A Time converts into the model as a Date/Time value with no digits to the left of the decimal point.

Requirements for Creating a Data Table in Power BI

Some of the requirements for a date table are as follows:

• The date table should have a date column with datatype date/time.

• A date column should not have blanks.

• A date column must have unique values.

• There should be no dates missing from a date column.

• The date table must span whole years, i.e. it must encompass all date values stored in the date table, which could be calendar years (January-December) or fiscal years, for example.

• The date column must be marked as a Date Table (this will be discussed in detail later on in this article).

Integer

The DAX Integer data type stores a 64-bit value.DAX does not have other integer data types with a smaller number of bits.All the internal calculations between integer values in DAX also use a 64-bit value.

String

Every string in DAX is stored as a Unicode (UTF-16) string, where each character is stored in at least 16 bits (2 bytes). By default, the comparison between strings is case-insensitive and accent-sensitive, so the two strings “Report” and “REPORT” are considered equal, whereas “e” and “è” are considered different.

Variant

The Variant data type is used for expressions that might return different data types, depending on the conditions. 

The Variant data type cannot be used as a data type for a column in a regular table. A DAX measure and in general a DAX expression can be Variant.

Conclusion:

Data types are an essential part of Power BI. Understanding data types and how to check them is an essential skill for any Python programmer. With this knowledge, you'll be better equipped to work with different types of data and create more versatile and robust applications.