Intermediate Excel Training Quiz

What is Power Query also commonly referred to as?

• Power Pivot

• Data Model

• Get & Transform / Get Data

• Import

Answer explanation

In many versions of Excel (including Excel 2016), Power Query is known as "Get & Transform".

Where would you find tools to create relationships between tables?

• Power Pivot

• Data Model

• Power Query

• Any Excel Worksheet

Answer explanation

The Data Model is where you can create and edit table relationships.

What does DAX stand for, in the context of Excel?

• Data & Analysis Exchange

• Data and Expression

• Data Analysis Expression

• Data Analysis in Excel

Answer explanation

DAX = Data Analysis Expressions

Which tool would you use to prep and transform your data before loading it into Excel?

• Power Query

• Power Pivot

• DAX

• Dara Model

Answer explanation

Power Query allows you to shape and transform your raw data before loading it into Excel.

TRUE or FALSE: Power Query can load more than 1,048,576 rows of data into Excel's data model.

• TRUE

• FALSE

Answer explanation

Power Query can load and compress well over 1,000,000 rows of data into Excel's Data Model

When should you use Power Query and Power Pivot?

• When you want to make your data loading process more efficient

• When you are connecting to very large data sources

• When you need to blend data from multiple tables

• When you want to unlock the full potential of Excel for business intelligence

• All of the above

Answer explanation

All of these are excellent reasons to use Power Query and Power Pivot!

TRUE or FALSE:

Merging queries makes tables wider by adding columns, and appending queries makes tables taller by adding rows.

• TRUE

• FALSE

Answer explanation

Merging describes the process of adding columns from a related table, based on a common column.

In which data model view would you "draw" relationships between tables?

• Data View

• Diagram View

• Relationship View

• Power View

Answer explanation

Diagram view allows you to click and drag to create relationships between tables

What's the difference between a "Power" PivotTable and a regular PivotTable?

• "Power" PivotTables allow you to analyze data across multiple table and sources

• "Power" PivotTables can analyze much larger data sets (>1mn rows)

• "Power" PivotTables allow you to create dynamic calculated measure using DAX

• All of the above

Answer explanation

All of these answers are differences between "Power" PivotTables and regular PivotTables (access to more tables, measures with DAX,

What's one difference between DAX formulas and traditional Excel formulas?

• DAX formulas are not saved within workbook

• DAX formulas do not reference specific cells (example $A$1)

• DAX formulas can only be calculated within the data model window

• DAX formulas are not compatible with date/time values

Answer explanation

DAX formulas reference entire columns or rows (no A1-style "grid" formulas)