Why Power Query transforms messy data in Excel - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to load data into Power Query.

Excel

let Source = Excel.CurrentWorkbook(){[Name=[1]]}[Content] in Source

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

A"Range1"

B"Sheet1"

C"Data"

D"Table1"

Attempts:

3 left

💡 Hint

Common Mistakes

Using sheet names instead of table names

Forgetting quotes around the name

Power Query loads data from named tables like "Table1" in Excel.

2fill in blank

medium

Complete the code to remove empty rows in Power Query.

Excel

CleanedData = Table.SelectRows(Source, each [1] <> null)

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

AText.Length([Column1])

BList.Count([Column1])

C[Column1]

DRecord.FieldCount([Column1])

Attempts:

3 left

💡 Hint

Common Mistakes

Using functions that don't apply to the column type

Checking for empty string instead of null

Checking if the value in [Column1] is not null removes empty rows.

3fill in blank

hard

Fix the error in the code to change data types.

Excel

ChangedType = Table.TransformColumnTypes(Source, {{"Date", [1])

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

Atype date

Btype text

Ctype number

Dtype datetime

Attempts:

3 left

💡 Hint

Common Mistakes

Using text or number type instead of date

Missing the type keyword

To convert a column to date type, use type date.

4fill in blank

hard

Fill both blanks to filter rows where sales are greater than 1000.

Excel

FilteredRows = Table.SelectRows(Source, each [[1]] [2] 1000)

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

ASales

B>

C<

DQuantity

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong column name

Using less than operator instead of greater than

We filter rows where the Sales column is greater than 1000.

5fill in blank

hard

Fill all three blanks to create a new column with doubled sales values for sales above 500.

Excel

AddedColumn = Table.AddColumn(Source, "DoubleSales", each if [[1]] [2] [3] then [Sales] * 2 else [Sales])

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

ASales

B>

C500

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong operator

Using wrong threshold value

Checking wrong column

This adds a new column doubling sales only if sales are greater than 500.