Why data transformation ensures quality in Power BI - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to remove duplicate rows in Power Query.

Power BI

Table.Distinct([1])

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

ARemoveDuplicates

BSource

CFilteredRows

DChangedType

Attempts:

3 left

💡 Hint

Common Mistakes

Using a step name that does not exist yet.

Confusing function names with variable names.

In Power Query, Table.Distinct(Source) removes duplicate rows from the original data table named Source.

2fill in blank

medium

Complete the DAX formula to calculate the total sales after filtering for the current year.

Power BI

TotalSalesCY = CALCULATE(SUM(Sales[Amount]), YEAR(Sales[Date]) = [1])

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

AYEAR(TODAY())

B2022

CSales[Year]

DNOW()

Attempts:

3 left

💡 Hint

Common Mistakes

Using a fixed year instead of dynamic current year.

Using NOW() without extracting the year.

The expression YEAR(TODAY()) dynamically gets the current year to filter sales data for this year.

3fill in blank

hard

Fix the error in this Power Query step to change the data type of the 'Sales' column to number.

Power BI

Table.TransformColumnTypes(Source, [1])

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

A{{"Sales", type number}}

B["Sales", Number.Type]

C{"Sales", Int64.Type}

D["Sales", type number]

Attempts:

3 left

💡 Hint

Common Mistakes

Using square brackets instead of curly braces.

Using incorrect type names like Number.Type.

The correct syntax requires a list of lists: {{"Sales", type number}} to specify the column and its new type.

4fill in blank

hard

Fill both blanks to create a calculated column that flags sales above 1000 as 'High' and others as 'Low'.

Power BI

SalesFlag = IF(Sales[Amount] [1] 1000, [2], "Low")

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

A>

B"High"

C<

D"Medium"

Attempts:

3 left

💡 Hint

Common Mistakes

Using less than operator instead of greater than.

Returning wrong string values.

The condition checks if Sales[Amount] is greater than 1000, then returns "High", else "Low".

5fill in blank

hard

Fill all three blanks to create a measure that calculates average sales for the selected product category.

Power BI

AvgSalesCategory = CALCULATE(AVERAGE(Sales[Amount]), Sales[Category] [1] [2], ALL(Sales[[3]]))

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

A=

B"Electronics"

CCategory

DIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'IN' instead of '=' for comparison.

Not quoting the category name as a string.

Using wrong column name inside ALL.

The measure filters sales where category equals "Electronics" and removes filters on the Category column for correct calculation.