Scenario practice questions

You are connecting to a SharePoint folder containing 100 Excel files. Each file has a similar structure but different column names. What is the best practice to combine these files into a single table while preserving the data?

Trap 1: Load each file as a separate table and create relationships in the…

This creates multiple tables, not a single combined table.

Trap 2: Use Power Query's 'Merge Queries' to join all files into one table.

Merge is for combining columns from different tables, not rows from multiple files.

Trap 3: Use 'Append Queries' to stack all files, then rename columns…

Append requires identical column names; renaming manually is error-prone.

A
Use Power Query's 'Combine Files' feature, selecting a sample file and promoting headers, then transforming column names to a standard set.
This automates combining files with different structures.
B
Load each file as a separate table and create relationships in the model.
Why wrong: This creates multiple tables, not a single combined table.
C
Use Power Query's 'Merge Queries' to join all files into one table.
Why wrong: Merge is for combining columns from different tables, not rows from multiple files.
D
Use 'Append Queries' to stack all files, then rename columns manually.
Why wrong: Append requires identical column names; renaming manually is error-prone.

Question 2easymultiple choice

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 3mediummultiple choice

You have a Power BI model where the Sales table is filtered by a Customer dimension. Users report that when they filter a measure from the Customer table (e.g., Customer Count), it does not affect the Sales visual. What is the most likely cause?

Exhibit

Refer to the exhibit. The following is a JSON snippet of a Power BI model relationship:

{
  "name": "Sales to Customer",
  "fromTable": "Sales",
  "fromColumn": "CustomerKey",
  "toTable": "Customer",
  "toColumn": "CustomerKey",
  "crossFilteringBehavior": "oneDirection",
  "securityFilteringBehavior": "oneDirection"
}

Trap 1: The relationship cardinality is many-to-many

Many-to-many cardinality can still allow filtering with proper cross-filtering settings; it doesn't prevent filtering entirely.

Trap 2: The cross-filtering direction is set to one direction (Customer ->…

One-directional filtering from Customer to Sales would actually allow Customer filters to affect Sales, so this would not cause the issue.

Trap 3: The security filtering behavior is set to oneDirection

Security filtering behavior is not typically set to 'oneDirection' and would not prevent filtering from Customer to Sales if the relationship is active.

A
The relationship is inactive
The relationship is inactive, so no filtering occurs. This is the most likely cause.
B
The relationship cardinality is many-to-many
Why wrong: Many-to-many cardinality can still allow filtering with proper cross-filtering settings; it doesn't prevent filtering entirely.
C
The cross-filtering direction is set to one direction (Customer -> Sales), but the filter is applied on Customer
Why wrong: One-directional filtering from Customer to Sales would actually allow Customer filters to affect Sales, so this would not cause the issue.
D
The security filtering behavior is set to oneDirection
Why wrong: Security filtering behavior is not typically set to 'oneDirection' and would not prevent filtering from Customer to Sales if the relationship is active.

Question 4hardmultiple choice

Refer to the exhibit. You have a DAX measure that calculates customer lifetime value (CLV) as total revenue divided by distinct customer count. When you use this measure in a visual with Product category, you notice that the CLV values are higher than expected. What is the most likely reason?

Exhibit

Refer to the exhibit.

```dax
Customer Lifetime Value = 
VAR TotalRevenue = SUM(Sales[Amount])
VAR CustomerCount = DISTINCTCOUNT(Sales[CustomerID])
RETURN
DIVIDE(TotalRevenue, CustomerCount, 0)
```

Trap 1: The measure does not filter out returns

Not relevant to the high values.

Trap 2: The measure should use COUNTROWS instead of DISTINCTCOUNT

COUNTROWS would count each transaction, not distinct customers.

Trap 3: The measure is dividing by zero for categories with no customers

DIVIDE returns 0 for division by zero.

A
The measure does not filter out returns
Why wrong: Not relevant to the high values.
B
The measure counts customers per category, but customers who buy multiple categories are counted in each category, reducing the denominator
This inflates CLV per category.
C
The measure should use COUNTROWS instead of DISTINCTCOUNT
Why wrong: COUNTROWS would count each transaction, not distinct customers.
D
The measure is dividing by zero for categories with no customers
Why wrong: DIVIDE returns 0 for division by zero.

Question 5hardmultiple choice

Refer to the exhibit. The Power Query M code connects to a SQL Server database and performs data transformation. However, the query is failing with a privacy level error. What is the most likely cause?

Exhibit

Refer to the exhibit.

let
    Source = Sql.Database("server01", "AdventureWorks"),
    dbo_SalesOrderHeader = Source{[Schema="dbo",Item="SalesOrderHeader"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_SalesOrderHeader, each Date.Year([OrderDate]) = 2024),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"CustomerID"}, {{"TotalSales", each List.Sum([SubTotal]), type number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows", {{["TotalSales"], Order.Descending}})
in
    #"Sorted Rows"

Trap 1: The SQL Server credentials are not correctly configured in the data…

Misconfigured SQL Server credentials would produce an authentication failure, such as 'Login failed for user' or an ODBC login timeout, not a privacy-level error. Privacy-level errors are unrelated to credential validation; they are raised during the query-folding and data-combination phase when Power Query checks whether sources can be merged under the current privacy settings. Therefore, this option points to a completely different class of data-source configuration issue.

Trap 2: The privacy levels for the SQL Server data source are set…

Inconsistent privacy levels for the same SQL Server data source across different environments do not cause a privacy-level error within a single Power Query execution. Privacy level is a per-source, per-user property in Power BI Desktop, and the engine evaluates the combination of sources in the current session using those stored levels. The error requires at least two distinct sources being combined with incompatible settings, not a mismatch of settings for one source across machines or users.

Trap 3: The query uses a CSV file from a local folder that has a privacy…

The CSV file's 'Private' privacy level is a necessary contributing factor, but it is not sufficient on its own to trigger a privacy-level error. Power Query raises this error only at the point where data from the CSV is combined with another source, such as SQL Server, and the two sources' privacy levels are incompatible. If the CSV were used in isolation, no privacy fault would occur, so this option incorrectly identifies the CSV alone as the root cause.

A
The SQL Server credentials are not correctly configured in the data source settings.
Why wrong: Misconfigured SQL Server credentials would produce an authentication failure, such as 'Login failed for user' or an ODBC login timeout, not a privacy-level error. Privacy-level errors are unrelated to credential validation; they are raised during the query-folding and data-combination phase when Power Query checks whether sources can be merged under the current privacy settings. Therefore, this option points to a completely different class of data-source configuration issue.
B
The privacy levels for the SQL Server data source are set inconsistently across the environment.
Why wrong: Inconsistent privacy levels for the same SQL Server data source across different environments do not cause a privacy-level error within a single Power Query execution. Privacy level is a per-source, per-user property in Power BI Desktop, and the engine evaluates the combination of sources in the current session using those stored levels. The error requires at least two distinct sources being combined with incompatible settings, not a mismatch of settings for one source across machines or users.
C
The query uses a CSV file from a local folder that has a privacy level set to 'Private'.
Why wrong: The CSV file's 'Private' privacy level is a necessary contributing factor, but it is not sufficient on its own to trigger a privacy-level error. Power Query raises this error only at the point where data from the CSV is combined with another source, such as SQL Server, and the two sources' privacy levels are incompatible. If the CSV were used in isolation, no privacy fault would occur, so this option incorrectly identifies the CSV alone as the root cause.
D
The query combines data from SQL Server and another data source with incompatible privacy levels.
This is correct because Power Query's privacy-level engine prevents data from being combined across sources that have incompatible privacy levels, such as a 'Private' CSV file from a local folder and a SQL Server source with a different level. When the query attempts to fold or merge data from these two sources, the engine raises a privacy-level error unless levels are adjusted to allow the combination or a suitable firewall level is configured. The error is specifically triggered by the act of combining the sources, not by the nature of any single source.

Question 6easymultiple choice

You need to create a calculated column in Power BI that categorizes sales amounts as 'Low', 'Medium', or 'High' based on the value. The column should be evaluated row by row. Which DAX function should you use?

Trap 1: IF

While `IF` can evaluate a single condition and return one of two results, it cannot directly produce three distinct categories ('Low', 'Medium', 'High') from a single expression without nesting multiple `IF` statements, which becomes unwieldy and error-prone. The requirement for a row-by-row categorisation into three tiers is better met by `SWITCH`, which evaluates an expression against a list of possible values cleanly. `IF` is tempting because it is the most intuitive function for simple binary logic, such as flagging sales as 'High' or 'Not High' based on a threshold.

Trap 2: CALCULATE

CALCULATE is not appropriate for row-level categorization because it is an aggregation function that modifies the filter context of a measure, typically to evaluate expressions under changed filters. It does not perform scalar per-row condition testing on a table row, and using it in a calculated column for categorizing values would be conceptually incorrect and likely produce errors or unintended results since it expects an aggregation as its first argument.

Trap 3: FORMAT

FORMAT is used to convert a value to text using a specified format string, such as formatting a number as currency or a date as a custom string. It does not perform logical comparisons or return category labels based on thresholds; applying FORMAT to a numeric measure would simply change its display representation, not assign it to a tier like 'Low', 'Medium', or 'High'.

A
SWITCH
SWITCH is the correct choice because it evaluates a single expression against a list of possible values and returns the corresponding result, making it ideal for creating multi-category calculated columns such as 'Low', 'Medium', or 'High'. Unlike nested IF functions, SWITCH keeps the logic linear and readable, and it can evaluate true/false conditions when used with a leading TRUE(), providing a clean row-level classification without context transition complexity.
B
IF
Why wrong: While `IF` can evaluate a single condition and return one of two results, it cannot directly produce three distinct categories ('Low', 'Medium', 'High') from a single expression without nesting multiple `IF` statements, which becomes unwieldy and error-prone. The requirement for a row-by-row categorisation into three tiers is better met by `SWITCH`, which evaluates an expression against a list of possible values cleanly. `IF` is tempting because it is the most intuitive function for simple binary logic, such as flagging sales as 'High' or 'Not High' based on a threshold.
C
CALCULATE
Why wrong: CALCULATE is not appropriate for row-level categorization because it is an aggregation function that modifies the filter context of a measure, typically to evaluate expressions under changed filters. It does not perform scalar per-row condition testing on a table row, and using it in a calculated column for categorizing values would be conceptually incorrect and likely produce errors or unintended results since it expects an aggregation as its first argument.
D
FORMAT
Why wrong: FORMAT is used to convert a value to text using a specified format string, such as formatting a number as currency or a date as a custom string. It does not perform logical comparisons or return category labels based on thresholds; applying FORMAT to a numeric measure would simply change its display representation, not assign it to a tier like 'Low', 'Medium', or 'High'.

Question 7mediummulti select

Which TWO of the following are best practices for modeling many-to-many relationships in Power BI?

Trap 1: Set cross-filter direction to both for all relationships

Can cause ambiguous filter propagation.

Trap 2: Use a composite model with DirectQuery for one of the tables

Not directly related to many-to-many.

Trap 3: Create a calculated table that combines the two tables

Not necessary and may cause duplication.

A
Use a bridge table to resolve the many-to-many relationship
Using a bridge table alone does not configure the many-to-many relationship; you typically need to adjust cross-filter direction or use many-to-many cardinality.
B
Set cross-filter direction to both for all relationships
Why wrong: Can cause ambiguous filter propagation.
C
Use a composite model with DirectQuery for one of the tables
Why wrong: Not directly related to many-to-many.
D
Use a measure that iterates over the bridge table with SUMX
Using a measure with SUMX that iterates over the bridge table is a common pattern to aggregate correctly and is a best practice.
E
Create a calculated table that combines the two tables
Why wrong: Not necessary and may cause duplication.

Question 8hardmultiple choice

You are a Power BI developer at a retail company. You have a data model with a 'Sales' fact table (10 million rows) and dimension tables: 'Date', 'Customer', 'Product', 'Store'. The 'Sales' table includes columns: SalesID, DateKey, CustomerID, ProductID, StoreID, Quantity, UnitPrice, Discount, TotalAmount. The 'Product' dimension has 5,000 rows and includes columns: ProductID, ProductName, Category, SubCategory, Brand, Price. The 'Store' dimension has 200 rows and includes columns: StoreID, StoreName, Region, City, Manager. The 'Customer' dimension has 100,000 rows. The report currently has a measure 'Total Sales' = SUM(Sales[TotalAmount]) and a measure 'Total Quantity' = SUM(Sales[Quantity]). Users complain that the report is slow when filtering by multiple categories and regions simultaneously. You need to improve performance without changing the data source. Which action should you take first?

Trap 1: Create an aggregation table for the Sales table

Aggregations are useful but require careful design and are not the simplest first step.

Trap 2: Disable the auto date/time feature in Power BI

This may reduce model size slightly but is not the most impactful first step.

Trap 3: Change the storage mode of the Sales table to Dual

Dual mode may not improve performance if the model is already in Import mode.

A
Create an aggregation table for the Sales table
Why wrong: Aggregations are useful but require careful design and are not the simplest first step.
B
Disable the auto date/time feature in Power BI
Why wrong: This may reduce model size slightly but is not the most impactful first step.
C
Change the storage mode of the Sales table to Dual
Why wrong: Dual mode may not improve performance if the model is already in Import mode.
D
Reduce the number of columns in the Customer dimension by removing unused columns
Removing unnecessary columns reduces model size and improves performance.