Data type changes in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to convert the 'Sales' column to a whole number in Power Query.

Power BI

Table.TransformColumnTypes(Source, {{"Sales", [1])

Drag options to blanks, or click blank then click option'

ADate.Type

BText.Type

CInt64.Type

DLogical.Type

Attempts:

3 left

💡 Hint

Common Mistakes

Using Text.Type instead of Int64.Type causes the column to become text.

Using Date.Type or Logical.Type will cause errors if the data is numeric.

In Power Query, Int64.Type converts a column to whole numbers (integers).

2fill in blank

medium

Complete the DAX formula to convert the 'Amount' column to a decimal number.

Power BI

ConvertedAmount = CONVERT(Table[Amount], [1])

Drag options to blanks, or click blank then click option'

AINTEGER

BDOUBLE

CSTRING

DBOOLEAN

Attempts:

3 left

💡 Hint

Common Mistakes

Using INTEGER converts to whole numbers, not decimals.

STRING or BOOLEAN are not numeric types.

The CONVERT function in DAX uses DOUBLE to convert to decimal numbers.

3fill in blank

hard

Fix the error in this Power Query step to convert 'DateText' to date type.

Power BI

Table.TransformColumnTypes(Source, {{"DateText", [1])

Drag options to blanks, or click blank then click option'

ADate.Type

BDateTime.Type

CText.Type

DNumber.Type

Attempts:

3 left

💡 Hint

Common Mistakes

Using Text.Type does not convert the data, it keeps it as text.

Number.Type is invalid for date data.

To convert text to date, use Date.Type in Power Query.

4fill in blank

hard

Fill both blanks to convert 'Price' to decimal and 'IsAvailable' to logical in Power Query.

Power BI

Table.TransformColumnTypes(Source, {{"Price", [1], {"IsAvailable", [2])

Drag options to blanks, or click blank then click option'

ACurrency.Type

BLogical.Type

CDecimal.Type

DText.Type

Attempts:

3 left

💡 Hint

Common Mistakes

Using Currency.Type instead of Decimal.Type changes formatting but not data type.

Using Text.Type for IsAvailable keeps it as text, not logical.

Decimal.Type converts to decimal numbers, and Logical.Type converts to true/false values.

5fill in blank

hard

Fill all three blanks to create a DAX measure that converts 'SalesAmount' to integer, filters for values greater than 1000, and sums them.

Power BI

TotalSales = SUMX(FILTER(Table, CONVERT(Table[SalesAmount], [1]) [2] 1000), Table[SalesAmount])

Drag options to blanks, or click blank then click option'

ADOUBLE

B>

CINTEGER

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using DOUBLE converts to decimal, not integer.

Using < instead of > filters the wrong values.

We convert 'SalesAmount' to INTEGER and filter for values greater than 1000 using >.

Practice

1. Why is it important to change data types correctly in Power BI?

easy

A. Because it speeds up the internet connection

B. Because it changes the color of the visuals automatically

C. Because it deletes duplicate rows in the dataset

D. Because it helps Power BI understand and process data accurately

Solution

1. Step 1: Understand the role of data types in Power BI

   Data types tell Power BI how to treat each column, like numbers or text.

2. Step 2: Recognize the impact of correct data types

   Correct types ensure calculations and visuals work properly without errors.

3. Final Answer:

   Because it helps Power BI understand and process data accurately -> Option D

4. Quick Check:

   Correct data types = Accurate processing [OK]

Hint: Data types guide Power BI how to handle data [OK]

Common Mistakes:

• Thinking data types change visual colors
• Believing data types remove duplicates
• Assuming data types affect internet speed

2. Which of the following is the correct way to change a column's data type to Whole Number in Power Query Editor?

easy

A. Right-click the column header, select 'Change Type' > 'Whole Number'

B. Double-click the column header and type 'Whole Number'

C. Click 'Home' tab and press 'Delete' key

D. Drag the column to the 'Values' area

Solution

1. Step 1: Recall how to change data types in Power Query Editor

   Right-clicking the column header opens a menu with 'Change Type' options.

2. Step 2: Identify the correct option for Whole Number

   Selecting 'Whole Number' under 'Change Type' sets the column data type correctly.

3. Final Answer:

   Right-click the column header, select 'Change Type' > 'Whole Number' -> Option A

4. Quick Check:

   Right-click > Change Type > Whole Number [OK]

Hint: Use right-click menu to change data types quickly [OK]

Common Mistakes:

• Trying to type data type directly in header
• Pressing Delete key removes data, not changes type
• Dragging columns changes layout, not data type

3. Given a column 'Sales' with text data type containing values like '100', '200', '300', what will be the result of this DAX measure after changing 'Sales' to Whole Number type?

Total Sales = SUM('Table'[Sales])

medium

A. It will return an error because SUM cannot be used on numbers

B. It will concatenate the text values: '100200300'

C. It will sum the numbers correctly: 600

D. It will return zero because the column is text

Solution

1. Step 1: Understand the effect of changing 'Sales' to Whole Number

   Changing 'Sales' from text to whole number converts values like '100' to 100 as numbers.

2. Step 2: Evaluate the SUM function on numeric data

   SUM adds numeric values, so 100 + 200 + 300 = 600.

3. Final Answer:

   It will sum the numbers correctly: 600 -> Option C

4. Quick Check:

   SUM of numbers = 600 [OK]

Hint: SUM works only on numeric data types [OK]

Common Mistakes:

• Expecting SUM to concatenate text
• Thinking SUM causes error on numbers
• Assuming text columns sum to zero

4. You changed a date column's data type to Text in Power Query, but your visuals show incorrect dates. What is the likely problem?

medium

A. Text data type improves date calculations automatically

B. Changing date to text breaks date calculations and sorting

C. Power BI ignores data types in visuals

D. You must refresh the data source to fix visuals

Solution

1. Step 1: Analyze the impact of changing date to text

   Changing a date column to text removes date properties, so calculations and sorting fail.

2. Step 2: Understand why visuals show incorrect dates

   Without date type, Power BI cannot interpret or order dates properly in visuals.

3. Final Answer:

   Changing date to text breaks date calculations and sorting -> Option B

4. Quick Check:

   Date as text breaks date logic [OK]

Hint: Keep date columns as Date type for correct visuals [OK]

Common Mistakes:

• Assuming text type improves date calculations
• Believing Power BI ignores data types in visuals
• Thinking refresh fixes data type issues

5. You have a column 'Price' imported as Text with values like '12.50', '15.00', and '9.99'. You want to calculate the total price in a measure. Which steps correctly fix the data type and calculate the total?

hard

A. Change 'Price' to Decimal Number in Power Query, then use Total Price = SUM('Table'[Price])

B. Keep 'Price' as Text and use Total Price = CONCATENATE('Table'[Price])

C. Change 'Price' to Whole Number and use Total Price = SUM('Table'[Price])

D. Change 'Price' to Date and use Total Price = SUM('Table'[Price])

Solution

1. Step 1: Identify correct data type for decimal prices

   Prices with decimals require Decimal Number type, not Whole Number or Text.

2. Step 2: Use SUM on Decimal Number column

   SUM works on numeric types, so after changing type, Total Price = SUM('Table'[Price]) calculates total correctly.

3. Final Answer:

   Change 'Price' to Decimal Number in Power Query, then use Total Price = SUM('Table'[Price]) -> Option A

4. Quick Check:

   Decimal type + SUM = correct total [OK]

Hint: Use Decimal Number for prices with decimals [OK]

Common Mistakes:

• Using Whole Number loses decimals
• Concatenating text instead of summing
• Changing price to Date type causes errors