Module 1: Excel ETL & Power Query Quiz

You are starting Module 2 but do not see the “Power Pivot” tab in your Excel ribbon. What is the first practical step you must take to activate the add-in?

Go to the Data tab and click Refresh All.

Correct Incorrect Correct answer

Go to File > Options > Add-Ins > COM Add-Ins and check the Microsoft Power Pivot for Excel box.

Correct Incorrect Correct answer

Right-click the ribbon and select Show Power Pivot Tab.

Correct Incorrect Correct answer

Download and install it from the Microsoft Store.

Correct Incorrect Correct answer

You need to bring the Sales_Cleaned query, which you built in Module 1, into your new Data Model. What is the most direct and correct way to do this?

Re-import the original Sales.SalesOrderHeader.xlsx CSV file.

Correct Incorrect Correct answer

From the Power Pivot window, click From Other Sources and find the Sales_Cleaned table.

Correct Incorrect Correct answer

Go to the Power Query pane, right-click your Sales_Cleaned query, and select Load to… -> Add to Data Model.

Correct Incorrect Correct answer

Manually copy and paste the data from the ETL_Output sheet into a new sheet.

Correct Incorrect Correct answer

You have imported all your tables. To build a star schema, you must first identify the central “Fact” table. Which table is it, and why?

DimCustomer, because it has the most columns.

Correct Incorrect Correct answer

DimDate, because it is used in all time calculations.

Correct Incorrect Correct answer

FactSales (or Sales_Cleaned), because it contains the numeric, transactional measurements.

Correct Incorrect Correct answer

DimProduct, because it is the most important lookup.

Correct Incorrect Correct answer

Your manager wants to analyze sales by Year, Quarter, and Month. What is the best practice solution for enabling this in a scalable Power Pivot model?

Just use the OrderDate column from the FactSales table.

Correct Incorrect Correct answer

Create a separate DimDate table using a DAX calendar function or Power Query M code.

Correct Incorrect Correct answer

Manually create an Excel sheet with all dates for the next 10 years.

Correct Incorrect Correct answer

Add calculated columns for Year, Month, and Quarter directly to the FactSales table.

Correct Incorrect Correct answer

You have imported the Sales.Customer.xlsx table. To comply with the governance standards in Lesson 2.2, what is the first action you should take on this table within the Data Model?

Rename the table Customer to DimCustomer in the Power Pivot window.

Correct Incorrect Correct answer

Change the source filename to DimCustomer.xlsx.

Correct Incorrect Correct answer

Create a copy of the table and name the copy DimCustomer.

Correct Incorrect Correct answer

Add a column to the table named DimCustomer.

Correct Incorrect Correct answer

You have loaded FactSales and DimCustomer into the model. What is the first step to visually create the relationship between them?

Go to the Diagram View in the Power Pivot window.

Correct Incorrect Correct answer

Go to the Data tab in Excel and click Relationships.

Correct Incorrect Correct answer

Use the Merge Queries feature in Power Query.

Correct Incorrect Correct answer

Write a DAX formula using the RELATED function.

Correct Incorrect Correct answer

You are in the Diagram View. To create the correct “One-to-Many” (1:*) relationship, what specific action must you take?

Drag CustomerID from FactSales to CustomerID in DimCustomer.

Correct Incorrect Correct answer

Drag CustomerID from DimCustomer to CustomerID in FactSales.

Correct Incorrect Correct answer

Drag SalesOrderID from FactSales to CustomerID in DimCustomer.

Correct Incorrect Correct answer

Merge the two tables in Power Query.

Correct Incorrect Correct answer

You have created four relationships. Your project lead asks for the governance document that formally lists and verifies each relationship, its direction, and its type. Which document from Lesson 2.3 do you provide?

The Model Metadata Summary

Correct Incorrect Correct answer

The Calculation Dictionary

Correct Incorrect Correct answer

The Relationship Register

Correct Incorrect Correct answer

The ETL_Architecture diagram

Correct Incorrect Correct answer

You need to create a new CustomerFullName field by combining FirstName and LastName. You want this new field to behave just like any other column, allowing you to use it as a row label or filter in a PivotTable. What should you create?

Create a DAX Measure.

Correct Incorrect Correct answer

Create a Calculated Column in the DimCustomer table.

Correct Incorrect Correct answer

Create a new column in the source Excel file.

Correct Incorrect Correct answer

Create a Custom Column in Power Query.

Correct Incorrect Correct answer

You are creating the CustomerFullName calculated column in the DimCustomer table. What is the correct DAX syntax to join the text fields?

-=1:23[FirstName] + " " + [LastName]

Correct Incorrect Correct answer

-=[FirstName] & " " & [LastName]

Correct Incorrect Correct answer

-=[FirstName].Value & " " & [LastName].Value

Correct Incorrect Correct answer

-=CONCATENATE([FirstName], " ", [LastName])

Correct Incorrect Correct answer

You are creating the ProfitMargin calculated column. You are concerned about “divide by zero” errors for sales with zero revenue. What is the best practice DAX function to use, as shown in Lesson 2.4?

ProfitMargin –= ([TotalSales] – [TotalCost]) / [TotalSales]

Correct Incorrect Correct answer

ProfitMargin –= DIVIDE([TotalSales] – [TotalCost], [TotalSales])

Correct Incorrect Correct answer

ProfitMargin –= IF([TotalSales] –= 0, 0, ([TotalSales] – [TotalCost]) / [TotalSales])

Correct Incorrect Correct answer

ProfitMargin –= SUM([TotalSales] – [TotalCost]) / SUM([TotalSales])

Correct Incorrect Correct answer

You need to create the primary KPI for your model: Total Sales. What is the correct “explicit DAX measure” as specified in Lesson 2.5?

In a PivotTable, drag the TotalSales column into the "Values" area.

Correct Incorrect Correct answer

In the Power Pivot window, in the calculation area, write the formula Total Sales :–= SUM(FactSales[TotalSales]).

Correct Incorrect Correct answer

In the FactSales table, add a calculated column: SUM(FactSales[TotalSales]).

Correct Incorrect Correct answer

In Power Query, use the Group By feature to sum the TotalSales column.

Correct Incorrect Correct answer

You need to create a measure that counts the number of unique sales orders. (Note: The FactSales table has multiple line items for the same SalesOrderID). What is the correct DAX formula?

Total Orders :–= COUNT(FactSales[SalesOrderID])

Correct Incorrect Correct answer

Total Orders :–= SUM(FactSales[SalesOrderID])

Correct Incorrect Correct answer

Total Orders :–= DISTINCTCOUNT(FactSales[SalesOrderID])

Correct Incorrect Correct answer

Total Orders :–= CALCULATE(COUNT(FactSales[SalesOrderID]))

Correct Incorrect Correct answer

You are tasked with creating the Sales Growth YoY measure. You already have a [Total Sales] measure and a DimDate table. What is the DAX formula provided in Lesson 2.5 for this?

Sales Growth YoY :–= [Total Sales] – CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DimDate[Date]))

Correct Incorrect Correct answer

Sales Growth YoY :–= CALCULATE([Total Sales], DATEADD(DimDate[Date], -1, YEAR))

Correct Incorrect Correct answer

Sales Growth YoY :–= [Total Sales] / [Total Sales] – 1

Correct Incorrect Correct answer

Sales Growth YoY :–= [Total Sales] – [Total Sales Previous Year]

Correct Incorrect Correct answer

Your manager wants to be able to “drill down” in a PivotTable from Category to Subcategory to Product Name. What is the correct procedure to enable this?

In the DimProduct table, right-click Category and select Create Hierarchy. Then drag Subcategory and Product Name into it.

Correct Incorrect Correct answer

In a PivotTable, drag Category, Subcategory, and Product Name into the "Rows" field one by one.

Correct Incorrect Correct answer

In Power Query, merge the Category, Subcategory, and Product tables.

Correct Incorrect Correct answer

In the FactSales table, add calculated columns for each of these fields.

Correct Incorrect Correct answer

You have created your [Total Sales] (Base) and [Total Sales YoY] (Target) measures. Per Lesson 2.6, what is the next step to formally define these as a KPI with status indicators?

Right-click the [Total Sales] measure and select Create KPI….

Correct Incorrect Correct answer

In Excel, use Conditional Formatting with icon sets.

Correct Incorrect Correct answer

Write a complex DAX formula using the SWITCH function.

Correct Incorrect Correct answer

Add a new Calculated Column to the FactSales table.

Correct Incorrect Correct answer

An auditor is reviewing your model and needs to know the exact DAX formula and business purpose for the [Average Order Value] measure. Which governance document must you provide?

The Model Metadata Summary

Correct Incorrect Correct answer

The Relationship Register

Correct Incorrect Correct answer

The Calculation Dictionary

Correct Incorrect Correct answer

The ETL_Architecture diagram

Correct Incorrect Correct answer

A new analyst needs to get a high-level overview of the model, including a list of all tables, their current row counts, and their last refresh time. Which governance document provides this?

The Model Metadata Summary

Correct Incorrect Correct answer

The Calculation Dictionary

Correct Incorrect Correct answer

A PivotTable on the FactSales table.

Correct Incorrect Correct answer

The Relationship Register

Correct Incorrect Correct answer

According to the “Validation Checklist,” what is a critical, practical step to validate that your DAX measures are returning accurate values?

By checking that all table names start with Dim or Fact.

Correct Incorrect Correct answer

By creating a PivotTable, adding a measure (e.g., [Total Sales]), and checking the grand total against a known, verified value.

Correct Incorrect Correct answer

By ensuring all relationships are Inner Joins.

Correct Incorrect Correct answer

By confirming that all DAX formulas are formatted correctly.

Correct Incorrect Correct answer

You are ready to submit the Module 2 Project. Based on the “Deliverables” section, what is the single, primary file you will be evaluated on?

A PowerPoint presentation explaining the star schema.

Correct Incorrect Correct answer

A Word document containing only the Calculation Dictionary.

Correct Incorrect Correct answer

A single Excel workbook (.xlsx) that contains the full Data Model, DAX measures, and all governance worksheets.

Correct Incorrect Correct answer

A collection of CSV files, one for each dimension table.

Correct Incorrect Correct answer

Module 1: Excel ETL & Power Query Quiz

DataPro 22 October 2025

Exam Summary

Information

Results

Results

Categories

1. Question

Hint

2. Question

Hint

3. Question

Hint

4. Question

Hint

5. Question

Hint

6. Question

Hint

7. Question

Hint

8. Question

Hint

9. Question

Hint

10. Question

Hint

11. Question

Hint

12. Question

Hint

13. Question

Hint

14. Question

Hint

15. Question

Hint

16. Question

Hint

17. Question

Hint

18. Question

Hint

19. Question

Hint

20. Question

Hint