Data Transformation & ETL with Power Query

You have loaded FactSales, DimCustomer, DimProduct, DimDate, DimTerritory, and DimEmployee. Which table serves as a Dimension table in your star schema, and why?

DimCustomer, because it contains descriptive attributes about customers.

Correct Incorrect Correct answer

FactSales, because it is the largest table in the model.

Correct Incorrect Correct answer

DimDate, because it is required for all time calculations.

Correct Incorrect Correct answer

DimEmployee, because it links to the sales team structure.

Correct Incorrect Correct answer

Your project lead insists on using a star schema instead of combining everything into one large “flat” table. What is the primary advantage of the star schema in Power BI?

It makes Power Query transformations run faster.

Correct Incorrect Correct answer

It increases data redundancy to ensure data safety.

Correct Incorrect Correct answer

It complicates DAX calculations by requiring more filter context.

Correct Incorrect Correct answer

It simplifies DAX, improves performance, and enables intuitive drill-down.

Correct Incorrect Correct answer

You have loaded all your cleaned tables from Module 1 into Power BI. Where do you go to visually create and manage the relationships between these tables?

The Power Query Editor

Correct Incorrect Correct answer

The Data view in Power BI Desktop

Correct Incorrect Correct answer

The Report view in Power BI Desktop

Correct Incorrect Correct answer

The Model view in Power BI Desktop

Correct Incorrect Correct answer

You need to link FactSales (many side) to DimProduct (one side). What is the correct drag-and-drop action in the Model view to establish the standard one-to-many relationship?

Drag ProductKey from FactSales to ProductKey in DimProduct.

Correct Incorrect Correct answer

Drag ProductName from DimProduct to ProductName in FactSales.

Correct Incorrect Correct answer

Drag ProductKey from DimProduct to ProductKey in FactSales.

Correct Incorrect Correct answer

Merge the tables in Power Query instead.

Correct Incorrect Correct answer

For the relationship between DimCustomer and FactSales on CustomerKey, what is the standard, recommended Cardinality and Cross filter direction according to best practices?

Many-to-Many (:) ; Both Directions

Correct Incorrect Correct answer

One-to-One (1:1) ; Single Direction

Correct Incorrect Correct answer

One-to-Many (1:*) ; Single Direction

Correct Incorrect Correct answer

Many-to-One (*:1) ; Both Directions

Correct Incorrect Correct answer

When should you choose to create a Calculated Column instead of a Measure?

Calculating Total Sales for the entire company.

Correct Incorrect Correct answer

Calculating a Profit value (SalesAmount – TotalCost) for each individual row in the FactSales table.

Correct Incorrect Correct answer

Calculating Year-to-Date Sales.

Correct Incorrect Correct answer

Counting the Total Number of Customers.

Correct Incorrect Correct answer

When should you choose to create a Measure instead of a Calculated Column?

Creating a SalesYear column derived from the OrderDate.

Correct Incorrect Correct answer

Creating a FullName column by combining FirstName and LastName.

Correct Incorrect Correct answer

Creating a ProfitMarginCategory ("High", "Low") based on the profit for each row.

Correct Incorrect Correct answer

Creating Total Sales, calculated as the sum of the SalesAmount column, to be used in charts and KPIs.

Correct Incorrect Correct answer

You are in Power BI Desktop and need to add a Margin calculated column to your FactSales table. What is the correct procedure and syntax?

In the Power Query Editor, using Add Column > Custom Column.

Correct Incorrect Correct answer

In the Power BI Data view, select New column and enter Margin = DIVIDE([Profit], [SalesAmount]).

Correct Incorrect Correct answer

In the Power BI Model view, right-click the table and select New column.

Correct Incorrect Correct answer

In the Power BI Report view, using the New measure button.

Correct Incorrect Correct answer

You need to create an explicit DAX measure for Total Profit. You already have SalesAmount and TotalCost columns in FactSales. What is the correct DAX measure formula?

Total Profit = [SalesAmount] – [TotalCost]

Correct Incorrect Correct answer

Total Profit := SUM(FactSales[ProfitColumn])

Correct Incorrect Correct answer

Total Profit = SUMX(FactSales, [SalesAmount] – [TotalCost])

Correct Incorrect Correct answer

Total Profit = SUM(FactSales[SalesAmount]) – SUM(FactSales[TotalCost])

Correct Incorrect Correct answer

What is the primary and most powerful capability of the CALCULATE function in DAX?

To perform row-by-row calculations within a table.

Correct Incorrect Correct answer

To create new tables based on DAX expressions.

Correct Incorrect Correct answer

To modify the filter context under which another DAX expression is evaluated.

Correct Incorrect Correct answer

To calculate standard aggregations like SUM or AVERAGE.

Correct Incorrect Correct answer

You are creating the Profit Margin % measure. To prevent potential “division by zero” errors if [Total Sales] is ever zero, what is the safest DAX function to use?

Profit Margin % = [Total Profit] / [Total Sales]

Correct Incorrect Correct answer

Profit Margin % = IF([Total Sales] = 0, 0, [Total Profit] / [Total Sales])

Correct Incorrect Correct answer

Profit Margin % = DIVIDE([Total Profit], [Total Sales])

Correct Incorrect Correct answer

Profit Margin % = AVERAGEX(FactSales, DIVIDE([Profit], [SalesAmount]))

Correct Incorrect Correct answer

You need a measure to count the number of unique customers who actually made a purchase (i.e., appear in the FactSales table). Which DAX measure is correct?

Total Customers = COUNT(DimCustomer[CustomerKey])

Correct Incorrect Correct answer

Total Customers = DISTINCTCOUNT(FactSales[CustomerKey])

Correct Incorrect Correct answer

Total Customers = COUNTROWS(DimCustomer)

Correct Incorrect Correct answer

Total Customers = SUM(DimCustomer[CustomerKey])

Correct Incorrect Correct answer

You want to create standard time intelligence measures like YTD Sales and Previous Year Sales. What is the essential prerequisite in your data model?

A properly configured relationship between FactSales and DimProduct.

Correct Incorrect Correct answer

A dedicated Date table (DimDate) marked as a date table, with a relationship to FactSales.

Correct Incorrect Correct answer

Calculated columns for Year, Month, and Quarter in the FactSales table.

Correct Incorrect Correct answer

A measure calculating Total Sales.

Correct Incorrect Correct answer

What are the standard DAX functions used to calculate Year-to-Date Sales (YTD Sales) and Previous Year Sales (PY Sales), assuming you have a [Total Sales] measure and a DimDate table?

YTD = SUMMARIZE(…) ; PY = FILTER(…)

Correct Incorrect Correct answer

YTD = CALCULATE(…) ; PY = DATEADD(…)

Correct Incorrect Correct answer

YTD = TOTALYTD(…) ; PY = CALCULATE(…, SAMEPERIODLASTYEAR(…))

Correct Incorrect Correct answer

YTD = DATESYTD(…) ; PY = PREVIOUSYEAR(…)

Correct Incorrect Correct answer

You want users to be able to easily drill down from Category -> SubCategory -> ProductName in visuals. How do you create this hierarchy in your data model?

In Power Query Editor, using Group By.

Correct Incorrect Correct answer

In the Power BI Model view, right-click the top-level field (e.g., Category) and select Create hierarchy, then drag other fields into it.

Correct Incorrect Correct answer

In the Power BI Report view, using the Visualizations pane.

Correct Incorrect Correct answer

Write a DAX formula using PATH.

Correct Incorrect Correct answer

The study guide recommends hiding technical key columns (like ProductKey, EmployeeKey) from the report view. What is the main benefit of doing this?

To permanently delete the column from the dataset.

Correct Incorrect Correct answer

To improve model performance and reduce clutter in the Report view Fields pane.

Correct Incorrect Correct answer

To prevent the column from being used in DAX calculations.

Correct Incorrect Correct answer

To encrypt the data in the column.

Correct Incorrect Correct answer

Your imported DimCustomer table has a column named FName. For better readability in reports, you want to rename it to First Name. Where can you easily make this change?

Renaming is not recommended as it breaks relationships.

Correct Incorrect Correct answer

In Power Query Editor during the ETL stage.

Correct Incorrect Correct answer

In the Power BI Model view or Data view by double-clicking the field name.

Correct Incorrect Correct answer

Using a DAX formula like RENAMECOLUMN.

Correct Incorrect Correct answer

What is the primary purpose of creating and maintaining a Data Dictionary for your Power BI model?

To store the DAX formulas for all measures.

Correct Incorrect Correct answer

To provide definitions, data types, and descriptions for tables and fields in the model.

Correct Incorrect Correct answer

To create the visual relationship diagram.

Correct Incorrect Correct answer

To track the refresh history of the dataset.

Correct Incorrect Correct answer

According to the “Validation Checklist,” what is a key step to verify the structural integrity of your data model?

Check that all DAX measures calculate without errors.

Correct Incorrect Correct answer

In the Model view, ensure all relationship lines are active (solid) and have the correct cardinality (e.g., 1:*) and filter direction.

Correct Incorrect Correct answer

Ensure all unused columns have been hidden from the report view.

Correct Incorrect Correct answer

Confirm that a DimDate table exists and is marked as a date table.

Correct Incorrect Correct answer

The study guide provides several optimization tips. Which of the following is a key recommendation for maintaining good model performance?

Use calculated columns instead of measures whenever possible.

Correct Incorrect Correct answer

Create as many calculated tables as possible.

Correct Incorrect Correct answer

Avoid bi-directional relationships, especially on large tables.

Correct Incorrect Correct answer

Keep all technical key columns visible in the report view.

Correct Incorrect Correct answer

Data Transformation & ETL with Power Query

Data Transformation & ETL with Power Query

DataPro 23 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