Select appropriate column data types

Exam PL-300 Microsoft Power BI Data Analyst tests your knowledge of various aspects related to data modeling, including selecting appropriate column data types. This is a crucial step in data modeling and ensure reliable and efficient analysis.

Power BI supports a range of data types for columns and it is essential to understand the differences between them before deciding which one suits your data best.

Power BI Data Types:

• Whole Number: Used for numerical data without any decimal places.
• Decimal Number: Used for numerical data with decimal places.
• Text: This data type contains alphanumeric characters and is used when dealing with text-based data.
• Boolean: Suitable for data containing only the information ‘true/false’.
• Date/Time: Specifically designed for any data that deals with date and/or time information.

Choosing the appropriate data type is crucial because it directly influences how the data can be manipulated and analyzed in Power BI.

Consider an Example:

Suppose we have a dataset about a shop’s sales with attributes like Item Id, Item Price, Sale Date, and Sold or Not.

If we categorize ‘Item Id’ as whole number, ‘Item Price’ as decimal number, ‘Sale Date’ as date/time, and ‘Sold or Not’ as Boolean, we can perform expected operations, like arithmetic computations on price, apply date functions on sale date, and perform logical operations on ‘Sold or Not’.

Fail to use appropriate data types may lead to data inconsistencies and affect the efficiency and effectiveness of your data analysis.

For example, if you mistakenly use the data type of item id as ‘text’ instead of ‘whole number’, you may encounter difficulties while summarizing or filtering the data based on item id.

To change the data type of a column in Power BI, navigate to the ‘Data View’, select the column, and then select the data type from the column tools tab on the ribbon.

Summary:

Understanding and selecting appropriate column data types is a vital skill set to have for passing the PL-300 Microsoft Power BI Data Analyst exam. The right choice ensures the efficiency of your data model, allows for comprehensive and accurate data analysis, and maintains data integrity.

Practice Test

True or False: The INT data type in SQL Server is used for storing numeric data.

• True

Answer: True

Explanation: The INT data type in SQL Server is used for storing numeric data. It can store 4 bytes of data, with values ranging from -2^31 to 2^31 –

Which of the following data types should be chosen to store monetary data in SQL Server?

• A. Decimal
• B. Money
• C. Float
• D. Text

Answer: B. Money

Explanation: The Money data type in SQL Server is specifically designed for storing monetary or currency values. It can store values from -2^63 to 2^63 –

True or False: The nvarchar data type in SQL Server is used to store non-Unicode strings.

• False

Answer: False

Explanation: The nvarchar data type in SQL Server is used to store Unicode strings, which include special characters and symbols from various languages.

Which of the following data types would you use to store a large amount of text data in SQL Server?

• A. Text
• B. Varchar
• C. Ntext
• D. Char

Answer: A. Text

Explanation: The Text data type in SQL Server is used for storing large amounts of text data up to a size of 2^31 – 1 characters.

True or False: The DateTime data type in SQL Server should be used to store date and time values including milliseconds.

• True

Answer: True

Explanation: The DateTime data type in SQL Server is used for storing both date and time, including milliseconds.

What data type would you use in SQL Server to store a fixed-length non-Unicode character data of length 10?

• A. Char(10)
• B. Varchar(10)
• C. Nchar(10)
• D. Nvarchar(10)

Answer: A. Char(10)

Explanation: The Char data type in SQL Server is used to store fixed-length, non-Unicode string data.

Which data type would you use to store Boolean values in SQL Server?

• A. Bit
• B. Char
• C. Tinyint
• D. Binary

Answer: A. Bit

Explanation: The Bit data type in SQL Server is used to store Boolean values, 1, 0 or NULL.

Which data type should be used to store small whole numbers ranging from -128 to 127 in SQL Server?

• A. Smallint
• B. Int
• C. Tinyint
• D. Bigint

Answer: A. Smallint

Explanation: The Smallint data type in SQL Server is used to store small whole numbers, and its range is from -32768 to For range -128 to 127, the correct data type is Tinyint.

True or False: The Varbinary data type in SQL Server should not be used to store binary data.

• False

Answer: False

Explanation: The Varbinary data type in SQL Server is used to store variable length binary data.

Which data type would you select to store large amounts of binary data in SQL Server?

• A. Varbinary
• B. Image
• C. Binary
• D. BLOB

Answer: B. Image

Explanation: The Image data type in SQL Server is used to store large binary objects such as images, audio, or any other types of files.

Can the VARCHAR data type in SQL Server store non-English alphabets?

• A. Yes
• B. No

Answer: B. No

Explanation: The VARCHAR datatype in SQL Server is used to store variable length non-Unicode character string data. For storing non-English alphabets, the NVARCHAR datatype should be used.

True or False: In SQL Server, the FLOAT data type is used for storing real numbers.

• True

Answer: True

Explanation: The FLOAT data type in SQL Server is used for storing floating point numbers.

Which data type would you use to store a URL in SQL Server?

• A. Varbinary
• B. Varchar
• C. Text
• D. Nvarchar

Answer: B. Varchar

Explanation: The Varchar data type in SQL Server can be used to store URLs. URLs are typically variable length, non-Unicode strings which make them a perfect use case for this data type.

What data type would you use in SQL Server to store the age of a person?

• A. Tinyint
• B. Smallint
• C. Int
• D. Bigint

Answer: A. Tinyint

Explanation: The Tinyint data type in SQL Server, which has a range from 0 to 255, is sufficient to store the age of a person.

True or False: In SQL Server, the TIME data type should be used to store date and time data.

• False

Answer: False

Explanation: The TIME data type in SQL Server is used to store time data. To store date and time data, the DATETIME or DATETIME2 type should be used.

Interview Questions

What is the importance of selecting appropriate column data types when working with Power BI?

Selecting the appropriate column data types is crucial because it determines what kind of data can be stored in the data model and ultimately affects the report’s effectiveness. Importantly, it also aids in improving the accuracy of calculations and visual effects in Power BI.

What happens if a column data type is not correctly specified in Power BI?

If a column data type is not correctly specified, it may cause incorrect results in calculations or by providing the wrong context for the data being analyzed. This could potentially lead to inaccurate data representations and misinterpretations.

How do you change a column data type in Power BI?

To change a column data type in Power BI, click on the desired dataset, and then on “Data view.” Locate the column, right-click its header, and select “change type.” A menu will appear with different data type options. Choose the appropriate one and click “OK.”

Which data type would be ideal to store a list of customer ages in Power BI?

The whole number data type would be ideal to store customer ages in Power BI, as ages are integer values.

What Power BI data type would you use to store a mix of letters and numbers?

You would use the text data type in Power BI to store a mix of letters and numbers.

What examples of column data types are utilized in Power BI?

Examples of column data types used in Power BI include text (string), decimal number, whole number, date/time, duration, percentage, and Boolean.

How does Power BI treat columns defined as date/time data type?

Power BI automatically generates a hierarchical navigation for columns defined as a date/time data type, providing year, quarter, month, and day.

Which data type should be used in Power BI to store currency information?

To store currency information, the decimal number data type should be used in Power BI.

What is the Boolean data type in Power BI?

The Boolean data type in Power BI can store two values: true or false. It is often used for columns that contain only two categories or states.

What is the role of the duration data type in Power BI?

The duration data type can be used to determine the difference between two date/time columns. The values can be represented in days, hours, minutes, or seconds.

What is the benefit of using whole number data type over decimal number data type in Power BI?

The benefit of using whole number data type over decimal number data type is that it consumes less memory, resulting in potentially faster query execution.

What does the percentage data type in Power BI represent?

The percentage data type in Power BI represents a number as a fraction of 100. It is generally used for columns that store data like interest rates, revenue growth, and other percent-based metrics.

What happens when text data is inputted into a column defined as a whole number data type in Power BI?

If text data is inputted into a column defined as a whole number, Power BI will return an error because the datatype of the input doesn’t match with the defined column data type.

What happens if you assign a column with a date/time data type but some cells in the column contain text?

If a column with a date/time datatype has cells with text, Power BI will not be able to parse those cells and will return a ‘null’ value for them. This can lead to data loss for records with non-date time values.

How can you determine the data type of a column in Power BI?

In Power BI, you can determine a column’s data type by navigating to the Data view, selecting the desired column, and checking the data type shown in the column tools on the ribbon.