Scenario practice questions

You have a dataset with a column 'FullName' containing values like 'John Doe'. You need to split this column into 'FirstName' and 'LastName' using the space delimiter. Which Power Query transformation should you use?

Trap 1: Merge Columns.

Merge combines columns, not splits.

Trap 2: Extract Text.

Extract is for extracting characters from the beginning or end.

Trap 3: Replace Values.

Replace Values substitutes text, not splits.

A
Split Column by Delimiter.
Splits a column into multiple columns based on a delimiter.
B
Merge Columns.
Why wrong: Merge combines columns, not splits.
C
Extract Text.
Why wrong: Extract is for extracting characters from the beginning or end.
D
Replace Values.
Why wrong: Replace Values substitutes text, not splits.

Question 2easymultiple choice

You are creating a Power BI report for a marketing team. The data is stored in a folder of CSV files on a network drive that is accessible from your computer. You need to combine all CSV files into a single table in Power BI. The files have the same structure. What should you do in Power Query?

Trap 1: Connect to each CSV file individually and use Append Queries.

This is possible but less efficient.

Trap 2: Use Merge Queries to join the files based on a common column.

Merge is for joining, not combining rows.

Trap 3: Change the data source settings to treat the folder as a single…

No such setting exists.

A
Connect to each CSV file individually and use Append Queries.
Why wrong: This is possible but less efficient.
B
Use the 'Combine Files' transform after connecting to the folder.
Correct. This automatically merges files.
C
Use Merge Queries to join the files based on a common column.
Why wrong: Merge is for joining, not combining rows.
D
Change the data source settings to treat the folder as a single data source.
Why wrong: No such setting exists.

Question 3mediummultiple choice

You need to create a calculated column that categorizes products based on price: 'Low' (<$10), 'Medium' ($10-$50), 'High' (>$50). Which DAX expression should you use?

Trap 1: SWITCH(Product[Price], <10, "Low", <=50, "Medium", "High")

Invalid syntax; SWITCH requires a value to compare.

Trap 2: LOOKUPVALUE(Category, Product[Price], Product[Price])

Not used for conditional categorization.

Trap 3: IF(Product[Price] < 10, "Low", Product[Price] <= 50, "Medium",…

IF only handles two outcomes; nesting is required for more.

A
SWITCH(Product[Price], <10, "Low", <=50, "Medium", "High")
Why wrong: Invalid syntax; SWITCH requires a value to compare.
B
LOOKUPVALUE(Category, Product[Price], Product[Price])
Why wrong: Not used for conditional categorization.
C
SWITCH(TRUE(), Product[Price] < 10, "Low", Product[Price] <= 50, "Medium", "High")
Correct use of SWITCH with TRUE() for multiple conditions.
D
IF(Product[Price] < 10, "Low", Product[Price] <= 50, "Medium", "High")
Why wrong: IF only handles two outcomes; nesting is required for more.

Question 4hardmulti select

You are modeling data with a many-to-many relationship between 'Student' and 'Class' tables. Which THREE configurations can handle this scenario?

Trap 1: Remove duplicate rows from both tables

Removing duplicates loses data.

Trap 2: Change relationship to one-to-many by creating a unique key

Not possible if data truly has many-to-many.

A
Use CROSSFILTER function to enable many-to-many filtering
Allows filtering across both tables.
B
Remove duplicate rows from both tables
Why wrong: Removing duplicates loses data.
C
Create a bridge table with StudentID and ClassID
Standard relational modeling technique.
D
Change relationship to one-to-many by creating a unique key
Why wrong: Not possible if data truly has many-to-many.
E
Use a calculated column to concatenate Class names in the Student table
Works for simple scenarios like listing classes.

Question 5hardmultiple choice

You have a Power BI report that uses DirectQuery to a SQL Server database. Users report that the report is slow when filtering by a slicer on a large dimension table. You need to improve performance without changing the data source. Which approach should you take?

Trap 1: Create an aggregation table on the dimension

Aggregations are not supported for DirectQuery on dimension tables.

Trap 2: Change the storage mode of the dimension table to Dual

Dual mode is not applicable for DirectQuery sources.

Trap 3: Reduce the cardinality of the slicer field by grouping values

This may reduce accuracy and not address the root cause.

A
Create an aggregation table on the dimension
Why wrong: Aggregations are not supported for DirectQuery on dimension tables.
B
Change the storage mode of the dimension table to Dual
Why wrong: Dual mode is not applicable for DirectQuery sources.
C
Set 'Assume Referential Integrity' on the relationship between tables
This enables optimized query execution in DirectQuery.
D
Reduce the cardinality of the slicer field by grouping values
Why wrong: This may reduce accuracy and not address the root cause.

Read the full NAT/PAT explanation →

Question 6hardmultiple choice

You are optimizing a DAX query that returns product-country combinations with total sales over 10,000. The query runs slowly. What is the primary performance issue with this query?

Exhibit

Refer to the exhibit.

```dax
EVALUATE
FILTER(
    ADDCOLUMNS(
        SUMMARIZE(
            Sales,
            Products[ProductName],
            Customers[Country]
        ),
        "SalesAmount", [Total Sales]
    ),
    [SalesAmount] > 10000
)
```

Trap 1: The measure [Total Sales] is defined using SUMX which is slow.

The measure may be fine; the grouping method is the bottleneck.

Trap 2: The FILTER function should be replaced with CALCULATETABLE for…

FILTER is acceptable; the grouping method is the issue.

Trap 3: The query lacks proper relationships between tables, causing cross…

Assuming relationships exist, this is not the primary issue.

A
The measure [Total Sales] is defined using SUMX which is slow.
Why wrong: The measure may be fine; the grouping method is the bottleneck.
B
Using SUMMARIZE instead of SUMMARIZECOLUMNS causes inefficient grouping and measure evaluation.
SUMMARIZECOLUMNS is more efficient for grouping with measures.
C
The FILTER function should be replaced with CALCULATETABLE for better performance.
Why wrong: FILTER is acceptable; the grouping method is the issue.
D
The query lacks proper relationships between tables, causing cross joins.
Why wrong: Assuming relationships exist, this is not the primary issue.

Question 7easymultiple choice

You need to ensure that only members of the 'Sales' security group can edit reports in the 'Sales Reports' workspace. The workspace currently has four members: three from Sales and one from Marketing. What should you do?

Trap 1: Configure the workspace to allow only specific users to edit by…

There is no 'Restrict editing' option; editing is controlled by workspace roles.

Trap 2: Publish the reports to a Power BI app and grant the Sales group…

Build permissions allow creating content but not editing reports in the workspace.

Trap 3: Assign the Sales security group the 'Contributor' role and keep the…

Contributor role allows editing, but the Marketing user as Member also gets edit access, which is not desired.

A
Add the Sales security group as a workspace Admin and remove the Marketing user.
Adding the Sales group with Admin role grants edit access, and removing the Marketing user prevents their access.
B
Configure the workspace to allow only specific users to edit by using the 'Restrict editing' option.
Why wrong: There is no 'Restrict editing' option; editing is controlled by workspace roles.
C
Publish the reports to a Power BI app and grant the Sales group 'Build' permissions.
Why wrong: Build permissions allow creating content but not editing reports in the workspace.
D
Assign the Sales security group the 'Contributor' role and keep the Marketing user as 'Member'.
Why wrong: Contributor role allows editing, but the Marketing user as Member also gets edit access, which is not desired.