Microsoft Power BI Data Analyst PL-300 PL-300 Questions 76–150 | Page 2/3

MCQeasy

You are preparing data for a Power BI report. You have a table that contains a 'ProductID' column with some null values. You need to ensure that the 'ProductID' column does not contain any null values in the data model. Which Power Query transformation should you apply?

A.Group By

B.Remove Duplicates

C.Remove Rows -> Remove Blank Rows

D.Replace Values -> Replace null with a default value

AnswerD

Replace Values -> Replace null with a default value is the correct approach because it directly modifies the ProductID column, converting each null into a specified default such as 'Unknown' or '0'. This preserves all rows while guaranteeing that no empty ProductID values remain, which is exactly the requirement. Power Query's Replace Values operation supports replacing nulls specifically, making it a targeted and effective data-cleaning step.

Why this answer

Replacing null values with a default value directly ensures that the ProductID column has no nulls in the data model. This transformation can be applied to a specific column using 'Replace Values' in Power Query, where you replace null with a chosen default. Options A and B do not address null values.

Option C, 'Remove Blank Rows', only removes rows where all columns are blank, so rows with data in other columns but null ProductID remain, failing the requirement.

Exam trap

The trap is thinking that 'Remove Blank Rows' removes rows with any null in a column; actually it only removes rows where every cell in the row is null. For a specific column like ProductID, filtering rows where ProductID is null or replacing nulls are the correct approaches.

How to eliminate wrong answers

Option A is wrong because 'Group By' aggregates rows based on a column, but it does not remove or replace null values; it would group nulls together but leave them in the data. Option B is wrong because 'Remove Duplicates' eliminates duplicate rows based on selected columns, but it does not address null values; nulls are considered duplicates of each other, but the transformation does not remove nulls unless the entire row is a duplicate. Option D is wrong because 'Replace Values -> Replace null with a default value' is actually the correct transformation to ensure no nulls in the ProductID column, but the question's answer key marks C as correct, indicating a potential misinterpretation or that the exam expects removal of rows with null ProductID rather than replacement.

Full explanation →

MCQmedium

You have a Power BI data model with a Sales table and a Product table. You want to create a measure that calculates the percentage of total sales for each product category. Which DAX pattern should you use?

A.SUM(Sales[Amount]) / CALCULATE(SUM(Sales[Amount]), REMOVEFILTERS(Sales))

B.SUM(Sales[Amount]) / SUM(Sales[Amount])

C.DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALLSELECTED()))

D.DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALL(Product[Category])))

AnswerD

ALL(Product[Category]) inside CALCULATE removes only the active filter on the Product[Category] column, while all other filters in the current context (such as date, region, or store) are preserved. This gives a denominator that represents total sales across all product categories within the same filtering context, which is exactly the correct baseline for computing a category's percentage. DIVIDE also safely handles division by zero by returning BLANK instead of an error, making this the most accurate and robust option.

Why this answer

It uses DIVIDE for safe division and CALCULATE with ALL(Product[Category]) to remove the filter context on the Product[Category] column, allowing the measure to compute the percentage of total sales for each product category. This pattern ensures that the denominator represents the total sales across all categories, while the numerator respects the current filter context for the specific category.

Exam trap

The trap here is that candidates often confuse ALL() with ALLSELECTED() or REMOVEFILTERS(), not realizing that ALLSELECTED() preserves external slicer filters while ALL() removes all filters on the specified column, which is essential for calculating a true percentage of total within the current filter context.

How to eliminate wrong answers

Option A is wrong because REMOVEFILTERS(Sales) removes all filters from the entire Sales table, which may include unrelated filters and does not specifically target the Product[Category] column, leading to an incorrect denominator. Option B is wrong because SUM(Sales[Amount]) / SUM(Sales[Amount]) always equals 1 (or 100%) for each row, as both numerator and denominator are the same value, failing to calculate a percentage of total. Option C is wrong because ALLSELECTED() respects slicers and external filters but does not remove the filter on Product[Category] within the visual, so the denominator would still be filtered by the current category, resulting in 100% for each category.

Full explanation →

MCQmedium

You are importing data from an Excel workbook that contains multiple sheets. Each sheet has similar structure but different data for different regions. You need to combine all sheets into a single table for analysis. What should you do?

A.In Power Query Editor, use Merge Queries to combine the sheets.

B.In Power Query Editor, use Append Queries to combine the sheets.

C.Copy and paste the data from each sheet into a master sheet in Excel.

D.In Power Query Editor, use Group By to consolidate the data.

AnswerB

Append Queries adds rows from one table to another.

Why this answer

The Append Queries operation in Power Query Editor is designed to combine rows from multiple tables or queries with similar column structures into a single table. Since each sheet in the Excel workbook contains data for different regions with the same structure, appending them stacks the rows, creating a unified dataset for analysis.

Exam trap

The trap here is that candidates often confuse Merge Queries (which combines columns via joins) with Append Queries (which combines rows), leading them to select Option A when the requirement is to stack data from multiple sheets.

How to eliminate wrong answers

Option A is wrong because Merge Queries performs a join operation based on matching columns, which combines columns from different tables rather than stacking rows; this would not produce a single table of all region data. Option C is wrong because manually copying and pasting data from each sheet into a master sheet in Excel is inefficient, error-prone, and does not leverage Power Query's automated data transformation capabilities, which is the expected approach for the PL-300 exam. Option D is wrong because Group By is used to aggregate data by grouping rows based on column values and performing calculations like sum or count; it does not combine multiple sheets into one table.

Full explanation →

MCQmedium

What is the most likely cause of the error in the DAX query shown in the exhibit?

A.The [Sales Amount] measure is returning multiple values or is not a valid scalar measure.

B.There is no relationship between Date and Product tables.

C.The SUMMARIZECOLUMNS function does not support multiple group-by columns.

D.The ORDER BY clause is not allowed in EVALUATE statements.

AnswerA

The error message indicates that the measure is not a valid scalar value. When a measure references a column without an aggregation function, such as Sales[SalesAmount] directly, or returns a table expression, SUMMARIZECOLUMNS fails because it expects a scalar for each column in its result set. Ensure the measure aggregates the underlying column using SUM, AVERAGE, or another scalar-returning function. Additionally, verify the measure is not using a calculated table or a row context that produces multiple results.

Why this answer

The error 'A single value for column 'Sales Amount' in table 'Sales' cannot be determined' occurs because the [Sales Amount] measure is being used in a context that expects a scalar value, but the measure is defined to return multiple values (e.g., using SUMX over a table that returns multiple rows without proper aggregation). In DAX, measures used in EVALUATE statements must return a single scalar value; if the measure is not properly aggregated or contains a many-to-many relationship, it can produce multiple values, causing this error.

Exam trap

The trap here is that candidates often misdiagnose the error as a missing relationship or syntax issue, when the root cause is a measure returning multiple values due to improper aggregation or context.

How to eliminate wrong answers

Option B is wrong because the error message specifically mentions a single value cannot be determined for a column, not a missing relationship; a missing relationship would cause a different error (e.g., 'No relationship found' or blank results). Option C is wrong because SUMMARIZECOLUMNS explicitly supports multiple group-by columns; it is designed to accept multiple columns in its group-by clause. Option D is wrong because ORDER BY is fully allowed in EVALUATE statements in DAX; it is a standard clause for sorting query results.

Full explanation →

MCQmedium

You are building a Power BI report that uses a live connection to an Azure Analysis Services (AAS) tabular model. Users need to be able to filter data using a slicer that shows only products that have been sold in the current year. What should you do?

A.Add a calculated column to the Product table in the AAS model that marks products sold this year

B.Use a filter on the slicer visual to show only products where sales year equals current year

C.Create a calculated table in the AAS model that returns only products sold in the current year

D.Create a calculated table in Power BI Desktop using DAX to filter products

AnswerC

A calculated table in the AAS model can use DAX to dynamically return only products sold in the current year, and it will be available in the live connection.

Why this answer

A calculated table in the AAS model can be created using DAX to dynamically return only products sold in the current year (e.g., using FILTER, CALCULATETABLE, YEAR, and TODAY functions). This table is available in Power BI via live connection and can be used as the slicer source. Option A is wrong: a calculated column in the Product table is static at process time and does not dynamically filter to current year; it also adds a column to the entire table, not a subset.

Option B is wrong: a visual-level filter on the slicer relies on the underlying data model; with live connection, you cannot create new tables in Power BI Desktop, and filtering on a date condition would require the slicer to use a field from a different table, which is not straightforward or recommended. Option D is wrong because live connections to AAS do not allow creating calculated tables in Power BI Desktop; all data modeling must be done in the source model.

Full explanation →

Multi-Selectmedium

Which TWO of the following are valid methods to share a Power BI report with external users (outside your organization)? (Choose two.)

Select 2 answers

A.Export the report to PDF and email it to the external user.

B.Send the external user a direct link to the report via email; they can view it without any additional setup.

C.Invite the external user as a guest in your Microsoft Entra ID (Azure AD) and share the report directly with them.

D.Embed the report in a SharePoint Online page using the Power BI web part.

E.Use the 'Publish to web' option to create an embed code that can be placed on a public website.

AnswersC, E

Azure AD B2B collaboration allows sharing with external guests.

Why this answer

The correct answers are C and E. Option C: By inviting external users as guests in your Microsoft Entra ID (Azure AD) using Azure AD B2B, you can share Power BI reports directly with them without requiring them to have a Power BI license. Option E: 'Publish to web' creates a public embed code that anyone on the internet can view, making it suitable for sharing with external users.

Option A is incorrect because exporting to PDF creates a static file, not an interactive report, and is not a sharing method. Option B is incorrect because external users need to be set up as guests or use publish to web; a direct link alone will not work without proper authentication. Option D is incorrect because embedding in SharePoint Online requires the external user to have access to the SharePoint site, which typically involves additional setup such as guest access.

Full explanation →

Multi-Selectmedium

Which THREE of the following are valid methods to enhance the accessibility of a Power BI report? (Choose three.)

Select 3 answers

A.Add animations to visuals to draw attention.

B.Provide keyboard navigation support by setting tab order.

C.Use a high contrast theme.

D.Use a color-blind friendly palette.

E.Add alt text to all visuals.

AnswersB, C, E

Setting tab order on visual elements creates a logical keyboard-only flow through a report. This is a core WCAG 2.1 requirement under 'keyboard accessible' because it lets users navigate without a mouse. By defining a sequence, Power BI ensures screen reader users and those with motor impairments can reach every interaction predictably, avoiding random or confusing jumps.

Why this answer

Setting tab order in Power BI allows keyboard-only users to navigate through report visuals in a logical sequence, which is a core requirement of WCAG 2.1 success criterion 2.4.3 (Focus Order). Option C is correct because Power BI provides built-in high contrast themes that enhance readability for users with visual impairments. Option E is correct because adding alt text to visuals ensures screen readers can convey the content to visually impaired users.

Option D is not considered a valid method for this question; while using a color-blind friendly palette is a good design practice, it is not specifically a method to enhance accessibility in the context of the exam. Option A is incorrect as animations can distract and are not an accessibility feature.

Exam trap

The trap is that candidates often overlook high contrast themes as a valid accessibility feature, mistakenly thinking they are only for visual appeal. However, Power BI's high contrast themes are a built-in accessibility feature, so option C is correct.

Full explanation →

MCQmedium

You are connecting to a SharePoint folder that contains Excel workbooks. Each workbook has multiple sheets. You need to combine data from a specific sheet named 'Sales' across all workbooks. Which Power Query approach should you use?

A.Use the SharePoint Online List connector and select the document library.

B.Use the SharePoint folder connector, filter by .xlsx, then expand the Content column and filter by sheet name 'Sales'.

C.Use the Excel connector and specify the folder path.

D.Use the Web connector and provide the SharePoint site URL.

AnswerB

Using the SharePoint folder connector is the correct approach because it connects to a library folder and returns a table with one row per file, including a Content column that stores the raw binary data of each Excel workbook. After filtering that table to .xlsx files, you expand the Content column, which invokes Power Query's Excel parser to load each workbook and list all its worksheets. Applying a filter on the sheet name to 'Sales' ensures that exactly the sheet you need is combined across all matching workbooks, making this the native and reliable method for importing multiple Excel files from a SharePoint folder.

Why this answer

The SharePoint folder connector retrieves all files in the folder, including Excel workbooks. By filtering for .xlsx files and then expanding the Content column, you access the binary data of each workbook. You can then filter by the 'Sales' sheet name to combine data from that specific sheet across all workbooks, which is the only approach that directly handles multiple workbooks with multiple sheets.

Exam trap

The trap here is that candidates often confuse the SharePoint folder connector with the SharePoint Online List connector, mistakenly thinking the list connector can access document libraries, when it is strictly for list data.

How to eliminate wrong answers

Option A is wrong because the SharePoint Online List connector is designed for SharePoint lists (e.g., custom lists, task lists), not for document libraries containing Excel files; it cannot read Excel workbook sheets. Option C is wrong because the Excel connector connects to a single Excel file, not a folder of workbooks, so it cannot combine data from multiple files. Option D is wrong because the Web connector is for connecting to web pages or APIs via HTTP, not for accessing SharePoint folder structures or parsing Excel files.

Full explanation →

MCQmedium

You are building a Power BI data model from an Azure SQL Database. The source table contains a column 'OrderDate' of type datetime. You want to create a date table in Power Query that includes all dates from the minimum to maximum OrderDate. Which M function should you use to generate the list of dates?

A.List.Dates

B.List.Generate

C.List.DateTimes

D.List.Range

AnswerA

List.Dates is the correct M function because it is specifically designed to generate a sequential list of date values. Its signature, List.Dates(start as date, count as number, step as duration), creates a contiguous series by incrementing the start date by the provided duration each time. For example, List.Dates(#date(2020,1,1), 366, #duration(1,0,0,0)) returns a list of all 366 days in 2020, which can then be converted directly into a date dimension table. This makes it the most direct, readable, and type-appropriate choice for this requirement.

Why this answer

`List.Dates` generates a list of sequential dates (type `date`) given a start date, a count of values, and a step duration. In Power Query, when you need a date table covering the range from the minimum to maximum `OrderDate`, you compute the count as `Duration.Days(MaxDate - MinDate) + 1` and use `List.Dates(MinDate, Count, #duration(1,0,0,0))`. This produces a clean list of dates without time components, ideal for a date dimension.

Exam trap

The trap here is that candidates confuse `List.DateTimes` (which includes time) with `List.Dates` (date only), or they overcomplicate the solution by choosing `List.Generate` when a simpler, purpose-built function exists.

How to eliminate wrong answers

Option B is wrong because `List.Generate` is a general-purpose generator that requires a custom loop function with initial, condition, next, and optional transform parameters; it is overly complex and not the direct function for generating a simple sequence of dates. Option C is wrong because `List.DateTimes` generates a list of datetime values (including time components), not just dates, which would introduce unnecessary time granularity and potential performance overhead for a date table. Option D is wrong because `List.Range` extracts a contiguous subset from an existing list; it does not generate a new list of dates from scratch.

Full explanation →

MCQhard

You are reviewing the relationships in a Power BI data model as shown in the exhibit. The model has tables: Sales, Product, Customer, and Category. You need to evaluate the performance impact of the current configuration. Which relationship is most likely to cause performance issues?

A.All relationships are equally efficient

B.The relationship between Sales and Customer

C.The relationship between Product and Category

D.The relationship between Sales and Product

AnswerC

This is the correct answer because this relationship is the one configured with bidirectional filtering, a configuration known to degrade query performance. With bidirectional cross-filtering, a filter on Category flows to Product and then to Sales, but also filters on Product or Sales can propagate back to Category, causing extra dependency chains and potential ambiguity in filter context. This forces the query engine to evaluate additional row combinations and can make the model significantly less responsive. In contrast to unidirectional relationships, this bidirectional flow creates unnecessary complexity, so the Product–Category relationship is the least efficient.

Why this answer

The relationship between Product and Category is most likely to cause performance issues because it is a many-to-many relationship without a bridge table. In Power BI, many-to-many relationships require the engine to materialize cross-join-like intermediate tables in memory, increasing query complexity and reducing performance. This is especially problematic when filtering or aggregating across these tables, as the VertiPaq engine must resolve ambiguity by creating additional internal tables.

Exam trap

The trap here is that candidates often assume all relationships are equally performant if they are correctly defined, overlooking that many-to-many cardinality inherently requires more complex processing than one-to-many relationships.

How to eliminate wrong answers

Option A is wrong because not all relationships are equally efficient; many-to-many relationships are significantly more resource-intensive than one-to-many relationships. Option B is wrong because the relationship between Sales and Customer is typically a standard one-to-many relationship (many sales per customer), which is the most efficient cardinality for star schema design and does not cause inherent performance issues. Option D is wrong because the relationship between Sales and Product is also a standard one-to-many relationship (many sales per product), which is optimized by the VertiPaq engine and does not introduce the cross-join overhead seen in many-to-many relationships.

Full explanation →

MCQeasy

You are modeling data from a source that includes a column 'FullName' (e.g., 'John Doe'). You want to create separate 'FirstName' and 'LastName' columns for analysis. What is the most efficient way?

A.Create calculated columns using DAX functions LEFT, RIGHT, and FIND.

B.Use the 'Replace Values' feature to manually separate names.

C.Use Excel formulas in a source query.

D.In Power Query, split the column by delimiter (space) into two columns.

AnswerD

Splitting a column by the space delimiter in Power Query is a native M transformation that invokes the Splitter.SplitTextByDelimiter function behind the scenes, generating two separate columns for first and last names in a single step. This declarative approach is optimized for large volumes of rows, requires no manual mapping, and automatically applies to every row, making it the most efficient and maintainable solution among the choices.

Why this answer

Splitting a column by delimiter in Power Query is the most efficient, native method for transforming data at the query level. It leverages Power Query's M language to perform the split in a single step, which is optimized for performance and can be refreshed automatically. This approach avoids the overhead of DAX calculated columns, which are computed in the storage engine and can slow down report rendering.

Exam trap

The trap here is that candidates often choose DAX calculated columns (Option A) because they are familiar with Excel-like formulas, but they overlook that Power Query is the correct tool for data transformation in Power BI, and DAX should be reserved for measures and calculated columns that depend on the data model's context.

How to eliminate wrong answers

Option A is wrong because creating calculated columns with DAX functions like LEFT, RIGHT, and FIND is inefficient; DAX calculated columns are evaluated row-by-row in the VertiPaq engine, consuming memory and CPU, and they cannot be used to directly split a string by a delimiter without complex nested functions. Option B is wrong because 'Replace Values' is designed for substituting specific text, not for splitting a column into multiple columns; it would require multiple manual steps and cannot dynamically handle variable-length names. Option C is wrong because using Excel formulas in a source query ties the transformation to an external application, breaking the self-service, refreshable nature of Power BI; it also introduces dependency on Excel's calculation engine, which is not part of the Power Query or DAX ecosystem.

Full explanation →

MCQmedium

You have a report with a line chart showing monthly sales. Users need to see the exact sales value when they hover over a data point. What should you configure?

A.Enable data labels on the chart.

B.Configure the visual's tooltip to display the value.

C.Add a report page tooltip.

D.Set the category label to show the value.

AnswerB

To see the monthly sales value on hover, ensure the line chart's tooltip is enabled in the Format pane and that the Sales measure is included in the Tooltip well (it is by default). When configured, hovering a point shows a tooltip displaying the category, series name, and the measure value. If the tooltip is currently not appearing, verify the tooltip toggles are on and no report page tooltip has overridden it.

Why this answer

Tooltips in Power BI are designed to show detailed information about a data point when the user hovers over it. By default, the visual's tooltip already includes the value, but if it has been customized or removed, you need to ensure the tooltip is configured to display the sales value. This provides an interactive way to see exact numbers without cluttering the chart with permanent labels.

Exam trap

The trap here is that candidates often confuse data labels (which show values permanently on the chart) with tooltips (which show values on hover), leading them to select option A instead of understanding that tooltips are the correct interactive mechanism for this requirement.

How to eliminate wrong answers

Option A is wrong because enabling data labels permanently displays the sales value on the chart for every data point, which can clutter the visual and is not the hover-based behavior requested. Option C is wrong because a report page tooltip is a custom tooltip that can show additional context from other visuals or pages, but it is not required for simply showing the exact sales value; the default visual tooltip already serves that purpose. Option D is wrong because the category label shows the category name (e.g., month), not the sales value, and setting it to show the value would misrepresent the axis.

Full explanation →

Multi-Selecthard

Which THREE settings should you verify in the Power BI tenant admin portal to ensure that external users (guests) can access a published app?

Select 3 answers

A.Invite external users to your organization via Microsoft Entra ID.

B.Allow sharing with external users.

C.Allow Azure Active Directory (Microsoft Entra ID) external identities to access the Power BI service.

D.Show external users in lists of suggested people.

E.Allow external users to edit and manage content in the organization.

AnswersA, B, C

'Invite external users' allows inviting guest users via Microsoft Entra ID, a prerequisite for external access.

Why this answer

To allow external users (guests) to access a published app in Power BI, three tenant settings must be enabled: 'Invite external users' (A) allows inviting guest users, 'Allow Azure AD external identities to access the Power BI service' (C) enables guest sign-in, and 'Allow sharing with external users' (B) permits sharing content with guests. Option E ('Allow external users to edit and manage content') is not required for viewing an app; it grants additional editing permissions. Option D controls whether guests appear in people pickers, which is unrelated.

Exam trap

Candidates often forget that 'Allow sharing with external users' (B) is required even for app access, not just sharing dashboards.

Full explanation →

MCQeasy

You are importing data from a CSV file that contains a column 'OrderDate' with dates in the format 'MM/dd/yyyy'. Some rows have invalid dates like '02/30/2023'. What is the best way to handle these errors in Power Query?

A.Use 'Replace Errors' to replace error values with null.

B.Remove rows with errors using 'Remove Rows' > 'Remove Errors'.

C.Change the data type to 'Date' and ignore errors.

D.Filter the column to exclude rows where the date is invalid after type conversion.

AnswerA

Replacing errors with null in Power Query is a non-destructive transformation that explicitly substitutes each invalid date value with a null while keeping the row intact. You apply it to the date column (Home > Replace Errors or context menu) and specify null as the replacement value. This preserves all other column values for that row and produces a clean, nullable date column that the data model handles naturally, e.g., through blanks in visuals or DAX functions like CALCULATE with filters. It also avoids the risk of load failure due to leftover errors.

Why this answer

'Replace Errors' in Power Query allows you to replace error values (which occur when Power Query fails to convert an invalid date like '02/30/2023' to the Date type) with null. This preserves the rest of the data and keeps the query running without interruption, while clearly marking invalid entries for later handling or analysis.

Exam trap

The trap here is that candidates often choose 'Remove Errors' (Option B) thinking it cleans the data, but they overlook that it deletes entire rows, which may discard valid data in other columns — a common mistake in data preparation scenarios.

How to eliminate wrong answers

Option B is wrong because 'Remove Errors' deletes entire rows containing any error, which can lead to data loss if other columns in those rows contain valid data. Option C is wrong because 'Change data type to Date and ignore errors' is not a valid Power Query operation; ignoring errors during type conversion still results in errors in the column, and there is no built-in 'ignore errors' toggle. Option D is wrong because filtering to exclude rows with invalid dates after type conversion requires the errors to be present first, and filtering on error values is not straightforward; it is more efficient to replace errors with null and then filter if needed.

Full explanation →

MCQeasy

A data model contains a Date table and a Sales table. You need to create a measure that calculates total sales for the previous year. Which DAX function should you use?

A.SAMEPERIODLASTYEAR

B.DATEADD

C.PARALLELPERIOD

D.PREVIOUSYEAR

AnswerA

SAMEPERIODLASTYEAR is the correct time intelligence function for year-over-year comparisons. It takes the current filter selection — whether it is a single date, a month, a quarter, or a custom range — and returns the exact corresponding date range from the previous calendar year, preserving the number of days and the shape of the selection. This precision makes it the standard DAX approach for computing previous-year totals, especially when you need the prior year to match the current period on a day-for-day or period-for-period basis.

Why this answer

SAMEPERIODLASTYEAR is the correct DAX function for calculating total sales for the previous year because it returns a set of dates shifted back by exactly one year while preserving the current filter context (e.g., month, quarter). This function is specifically designed for year-over-year comparisons and works seamlessly with a Date table marked as a date table in the model. Option B: DATEADD can also shift dates by one year but requires specifying the interval and number of intervals, making it less direct for this specific requirement.

Option C: PARALLELPERIOD returns the entire parallel period (e.g., full year) regardless of current granularity, which does not preserve the same relative period. Option D: PREVIOUSYEAR returns the entire previous year, not the same period shifted back, so it does not maintain month-over-month or quarter-over-quarter comparisons.

Exam trap

The trap here is that candidates often confuse SAMEPERIODLASTYEAR with PREVIOUSYEAR, not realizing that PREVIOUSYEAR returns the entire previous year regardless of the current filter granularity, while SAMEPERIODLASTYEAR shifts the exact same period (e.g., month, quarter) back by one year.

How to eliminate wrong answers

Option B (DATEADD) is wrong because it shifts dates by a specified interval (e.g., -1 year) but requires an explicit interval parameter and can produce unexpected results if the Date table is not continuous or if the interval does not align with the current filter context. Option C (PARALLELPERIOD) is wrong because it returns a parallel period of a fixed length (e.g., full year) but does not respect the current granularity of the filter context (e.g., it returns the entire previous year even if the current filter is a single month). Option D (PREVIOUSYEAR) is wrong because it returns all dates in the previous year based on the current filter context, but it does not shift the entire period; it simply returns the set of dates for the previous year, which can cause incorrect totals when used with non-standard calendars or partial year filters.

Full explanation →

MCQmedium

You are reviewing a Power Query that imports data from SQL Server. The exhibit shows the M code. The SQL query filters records after a date, then Power Query filters rows with OrderQty > 10, and then groups by ProductID. What is a potential performance issue with this approach?

A.The query will fail because the SQL query uses '>' with a string.

B.The SQL query should use a parameter for the date instead of a hardcoded value.

C.The filter on OrderQty > 10 should be included in the SQL query to reduce the amount of data transferred.

D.The grouping should be done in SQL to reduce data volume.

AnswerC

Filtering in SQL reduces data load; currently, all rows after the date are loaded.

Why this answer

Pushing the `OrderQty > 10` filter into the SQL query reduces the amount of data transferred from SQL Server to Power Query. In Power Query, data is loaded into memory before transformations; filtering earlier in the source query minimizes memory usage and network latency, which is a key performance optimization in Power BI data loading.

Exam trap

The trap here is that candidates focus on the date filter or grouping as the main performance issue, but the most impactful optimization is moving the row-level filter (`OrderQty > 10`) into the SQL query to reduce data transfer, which is a classic 'query folding' concept in Power Query.

How to eliminate wrong answers

Option A is wrong because the SQL query uses `'>'` with a string, but SQL Server implicitly converts the string to a date for comparison, so the query will not fail. Option B is wrong while using a parameter is a best practice for maintainability, it does not directly address the performance issue of data volume; the question asks about a potential performance issue, not code quality. Option D is wrong because grouping in SQL could reduce data volume, but the primary performance bottleneck here is the row filter on `OrderQty > 10` being applied after data transfer; grouping after filtering is less impactful than filtering earlier.

Full explanation →

MCQmedium

A data analyst is designing a star schema in Power BI. The model includes a table named 'Orders' with columns: OrderID, CustomerID, OrderDate, ProductID, Quantity, and SalesAmount. Which column should NOT be included in the fact table to maintain a proper star schema?

A.Quantity

B.SalesAmount

C.CustomerID

D.OrderID

AnswerD

OrderID is a natural business key that identifies an order, not a numeric measure or a surrogate foreign key. In a star schema, natural keys and descriptive attributes should live in the appropriate dimension table, such as an Order dimension, so that the fact table contains only relationship keys and measures. Including OrderID directly in the fact table violates star schema normalization and reduces maintainability. Thus, OrderID is the item that should NOT be placed directly in the fact table.

Why this answer

In a proper star schema, fact tables should contain quantitative measures and foreign keys to dimension tables. Columns like CustomerID and ProductID serve as foreign keys linking to dimension tables, so they should remain in the fact table. OrderID is a natural key that typically belongs in an Order dimension table; the fact table should use a surrogate OrderKey instead.

Including OrderID directly would duplicate dimensional data and reduce modeling flexibility.

Exam trap

Candidates often assume that all ID columns belong in the fact table. However, natural keys (like OrderID) should reside in dimension tables; the fact table should contain a surrogate key to reference them. CustomerID, on the other hand, is a foreign key that is correctly placed in the fact table.

How to eliminate wrong answers

Option A is wrong because Quantity is a numeric, additive measure that is a classic fact column in a sales fact table, representing the number of units sold per transaction. Option B is wrong because SalesAmount is a monetary measure that is the core metric for analysis and belongs in the fact table. Option D is wrong because OrderID is the unique identifier for each transaction row and serves as the fact table's grain key, which is required for proper row-level identification and relationship creation.

Full explanation →

MCQmedium

You are preparing data for a Power BI report. The source data contains a column 'FullName' with values like 'John Doe'. You need to split this column into 'FirstName' and 'LastName' using Power Query. The transformation should be repeatable and not dependent on the number of spaces. What is the best approach?

A.Use 'Split Column by Number of Characters' with a fixed position.

B.Use 'Split Column by Delimiter' and choose 'Right-most delimiter'.

C.Use 'Replace Values' to replace space with a comma.

D.Use 'Extract Text After Delimiter' with a space.

AnswerB

Choosing 'Split Column by Delimiter' with the 'Right-most delimiter' option is correct because it targets the final space in the name, separating the last name from all preceding text. This works reliably for variable-length strings because it does not depend on character counts or the number of spaces; even 'Mary Ann Jones' splits into 'Mary Ann' and 'Jones' as two columns. Power Query executes this by treating the last delimiter occurrence as the split point, which is exactly what is needed to isolate the surname.

Why this answer

Using 'Split Column by Delimiter' with 'Right-most delimiter' ensures that the split occurs at the last space in the FullName column, which reliably separates the first name from the last name even if there are multiple spaces (e.g., 'John Michael Doe' would yield 'John Michael' as FirstName and 'Doe' as LastName). This approach is repeatable and does not depend on a fixed number of spaces, making it robust for varying name formats.

Exam trap

The trap here is that candidates often choose 'Split Column by Delimiter' with the default 'Left-most delimiter' (which splits at the first space) or 'Extract Text After Delimiter', not realizing that names with multiple spaces require the right-most delimiter to correctly separate the last name from the rest.

How to eliminate wrong answers

Option A is wrong because 'Split Column by Number of Characters' with a fixed position assumes all names have the same character length for first and last names, which is not true for variable-length names like 'John Doe' vs. 'Alexander Hamilton'. Option C is wrong because 'Replace Values' to replace space with a comma does not split the column; it only changes the delimiter, requiring an additional split step and still not handling multiple spaces correctly. Option D is wrong because 'Extract Text After Delimiter' with a space extracts only the text after the first space, which would give 'Doe' for 'John Doe' but fail for names with middle names or multiple spaces, and it does not create both FirstName and LastName columns in one step.

Full explanation →

Multi-Selectmedium

Which TWO of the following are valid data source types in Power BI that support DirectQuery? (Select TWO.)

Select 2 answers

A.Snowflake

B.Excel workbook

C.Azure Synapse Analytics

D.SharePoint Online List

E.JSON file

AnswersA, C

Snowflake supports DirectQuery.

Why this answer

Snowflake is a cloud-based data warehouse that supports DirectQuery in Power BI, allowing queries to be sent directly to Snowflake without importing data into Power BI's in-memory engine. This is possible because Snowflake provides a SQL-based interface that Power BI can connect to via its native connector, enabling real-time querying of large datasets.

Exam trap

The trap here is that candidates often confuse file-based or list-based data sources (like Excel, JSON, or SharePoint) as being DirectQuery-capable because they can be connected to Power BI, but DirectQuery is strictly limited to relational databases and data warehouses that support live query execution.

Full explanation →

MCQmedium

You are building a star schema in Power BI. The fact table contains sales transactions. Which of the following should be stored in a dimension table?

A.Product category

B.Sales amount

C.Customer ID

D.Transaction date

AnswerA

Product category is a descriptive attribute that should reside in a product dimension table.

Why this answer

Product category is a descriptive attribute that belongs in a product dimension table. Option B is wrong because sales amount is a numeric measure that should be stored in the fact table. Option C is wrong because customer ID is a foreign key that links to a customer dimension but is stored in the fact table as a key, not as a descriptive attribute.

Option D is wrong because transaction date can be part of a date dimension, but the question asks for what should be stored in a dimension table, and product category is the clearest dimension attribute among the options.

Full explanation →

MCQmedium

You are designing a data model in Power BI. You have a Sales table and a Date table. The Date table should contain all dates from 2020 to 2025. What is the best practice for creating the Date table?

A.Create a calculated table using VALUES from the Sales table's date column.

B.Use the Sales table's date column directly as the date dimension.

C.Enable the auto date/time option in Power BI settings.

D.Create a separate Date table using CALENDAR function in DAX.

AnswerD

Ensures a continuous date range for time intelligence.

Why this answer

Using the CALENDAR function to create a separate Date table ensures a complete, contiguous date range from 2020 to 2025, independent of the Sales table. This is a best practice in Power BI for time intelligence, as it guarantees all dates are present for accurate year-over-year calculations and avoids gaps or missing dates that could occur if relying solely on transaction data.

Exam trap

The trap here is that candidates often think using the Sales table's date column directly (Option B) is simpler and sufficient, but this violates the requirement for a complete date dimension and fails when no sales occur on certain dates, which is a common pitfall tested in PL-300.

How to eliminate wrong answers

Option A is wrong because creating a calculated table using VALUES from the Sales table's date column only includes dates where sales occurred, which may omit dates with no transactions, breaking time intelligence functions like TOTALYTD or SAMEPERIODLASTYEAR. Option B is wrong because using the Sales table's date column directly as the date dimension violates the star schema principle of having a separate, dedicated date table, leading to poor performance and inability to mark it as a date table for built-in time intelligence. Option C is wrong because enabling the auto date/time option in Power BI settings creates hidden date tables automatically, but these are not user-controllable, cannot be extended to a custom range like 2020–2025, and can cause confusion in model relationships.

Full explanation →

MCQmedium

You are building a star schema in Power BI. Your fact table contains sales data at the transaction level. Which of the following is the best practice for modeling the date dimension to support time intelligence functions like year-to-date (YTD) calculations?

A.Use the fact table's date column directly in measures without a date table

B.Create a date table with only the dates that appear in the fact table

C.Use multiple date columns (e.g., OrderDate, ShipDate) in the fact table

D.Create a date table with a contiguous range of dates and mark it as a date table

AnswerD

This enables time intelligence functions and proper filtering.

Why this answer

In Power BI, to enable time intelligence functions (like YTD, QTD, etc.), a dedicated date table with a contiguous range of dates is required. This table must be marked as a date table in the model. Option A is incorrect because using the fact table's date column directly lacks the necessary date hierarchy and attributes.

Option B is incorrect because a date table that only includes dates with transactions will have gaps, leading to incorrect time intelligence calculations. Option C is incorrect because having multiple date columns in the fact table is not a replacement for a proper date dimension; instead, role-playing dimensions should be used. Option D is correct as it describes the best practice: a contiguous date table marked as a date table.

Full explanation →

MCQmedium

You are preparing data from an Excel workbook that contains multiple sheets. Each sheet has a similar structure but different data. You need to combine all sheets into a single table in Power Query. What is the most efficient approach?

A.Load each sheet as a separate query and then use 'Append Queries' to combine them.

B.Use 'Merge Queries' to join the sheets based on a common column.

C.Use the 'Combine Files' approach with the workbook as a folder, then select all sheets.

D.Create a new query that references each sheet query and then merges them.

AnswerC

Power Query can treat a single workbook as a folder of sheets and combine them automatically.

Why this answer

The 'Combine Files' approach in Power Query treats the workbook as a folder, allowing you to select all sheets and automatically combine them into a single table. This is the most efficient method when multiple sheets have a similar structure, as it uses a single transformation step and handles dynamic sheet names without manual query creation.

Exam trap

The trap here is that candidates often confuse 'Append Queries' (which stacks rows) with 'Merge Queries' (which joins columns), and overlook the 'Combine Files' approach because they think it only applies to multiple files, not multiple sheets within a single workbook.

How to eliminate wrong answers

Option A is wrong because loading each sheet as a separate query and then using 'Append Queries' requires manual effort for each sheet, which is inefficient and not scalable for many sheets. Option B is wrong because 'Merge Queries' is designed for joining tables based on a common column (like a SQL JOIN), not for stacking rows from multiple sheets; it would produce a wide table instead of a long one. Option D is wrong because creating a new query that references each sheet query and then merging them still involves manual referencing and is less efficient than the built-in 'Combine Files' functionality, which automates the process.

Full explanation →

MCQmedium

You are merging two queries in Power Query. Query 'Orders' contains columns: OrderID, CustomerID, OrderDate. Query 'Customers' contains columns: CustomerID, CustomerName, Segment. You need to add the CustomerName to the Orders query. The relationship between Orders and Customers is many-to-one. Which join kind should you use?

A.Inner

B.Left Outer

C.Right Outer

D.Full Outer

AnswerB

Left Outer join (Join Kind = Left Outer) keeps every row from the first query—orders—as the left table, and appends columns from customers only when the join key (CustomerID) matches. For orders that lack a matching customer record, the added customer name column is null, but the order row remains intact. This is the correct choice because the business need is an order-centric view where all orders must appear regardless of whether customer reference data exists.

Why this answer

The goal is to retain all rows from the Orders table while adding CustomerName from the Customers table. A Left Outer join returns all rows from the first (left) table and only matching rows from the second (right) table, filling non-matches with null. Since the relationship is many-to-one, each OrderID may have a matching CustomerID, and you want to keep every order even if a customer is missing — exactly what Left Outer does.

Exam trap

The trap here is that candidates often confuse Left Outer with Inner join, thinking they must discard non-matching rows to avoid nulls, but the requirement explicitly says to add CustomerName to the Orders query, which implies preserving all orders even if a customer record is missing.

How to eliminate wrong answers

Option A is wrong because an Inner join would only return orders that have a matching customer, discarding any orders with missing or unmatched CustomerID values, which does not satisfy the requirement to add CustomerName to all orders. Option C is wrong because a Right Outer join would return all rows from the Customers table, which is not the target table; it would keep all customers even if they have no orders, and orders without a matching customer would be lost. Option D is wrong because a Full Outer join returns all rows from both tables, creating nulls on both sides for non-matches, which is unnecessary and would introduce extra rows for customers with no orders, bloating the result.

Full explanation →

100

MCQhard

Refer to the exhibit. You are implementing row-level security (RLS) in Power BI. The JSON policy above is applied to the 'Sales' table. The user is in the 'SalesRegion' role. Which rows will the user see?

A.Rows where Region is 'North' and 'South'.

B.No rows, because there is a conflict.

C.Rows where Region is 'North' only.

D.Rows where Region is 'South' only.

AnswerC

The role filter '[Region] = "North"' applies, ignoring the table-level filter.

Why this answer

The JSON policy defines a filter that restricts the 'Sales' table to rows where the 'Region' column equals 'North'. The user is in the 'SalesRegion' role, which applies this filter. RLS in Power BI evaluates the filter expression for each row, returning only those that satisfy the condition, so only rows with Region = 'North' are visible.

Exam trap

The trap here is that candidates may assume a role named 'SalesRegion' implies access to multiple regions, but the actual filter logic is determined solely by the explicit condition in the JSON policy, not the role name.

How to eliminate wrong answers

Option A is wrong because the JSON policy uses a single equality condition ('Region' eq 'North'), not an OR or IN clause that would include both 'North' and 'South'. Option B is wrong because there is no conflict; a single filter is applied without contradictory conditions, so rows are returned normally. Option D is wrong because the filter explicitly specifies 'North', not 'South', so rows with Region = 'South' are excluded.

Full explanation →

101

Multi-Selectmedium

Which TWO actions can you perform using Power BI Desktop's Query Editor? (Choose two.)

Select 2 answers

A.Define row-level security (RLS) roles.

B.Merge two tables based on a common column.

C.Create a relationship between two tables.

D.Remove duplicate rows from a table.

E.Create a new measure using DAX.

AnswersB, D

The 'Merge Queries' feature in Query Editor combines rows from two tables based on a common column, supporting join kinds such as left outer, right outer, full outer, and inner. You can expand the resulting columns to bring in related fields, which is a common way to enrich one table with data from another. This operation is performed in Power Query before the data is loaded into the model.

Why this answer

In Power BI Desktop's Query Editor, you can merge two tables based on a common column using the 'Merge Queries' feature (option B) and remove duplicate rows using the 'Remove Duplicates' feature (option D). Creating a relationship between tables (option C) is not performed in Query Editor; it is done in the Model view after loading data. Defining row-level security roles (option A) and creating DAX measures (option E) are also not available in Query Editor — roles are managed in the Modeling tab or Power BI Service, and measures are created in the Report or Data view.

Exam trap

A common trap is thinking that Query Editor can create relationships, but relationship creation is a data modeling task performed in Model view. Some may confuse merging tables with creating relationships, but merging simply combines data into a new table.

Full explanation →

102

MCQeasy

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?

A.Split Column by Delimiter.

B.Merge Columns.

C.Extract Text.

D.Replace Values.

AnswerA

Splits a column into multiple columns based on a delimiter.

Why this answer

The 'Split Column by Delimiter' transformation in Power Query is specifically designed to divide a single text column into multiple columns based on a specified delimiter, such as a space. In this scenario, selecting the column 'FullName' and using 'Split Column > By Delimiter' with a space delimiter will correctly separate 'John Doe' into 'FirstName' (John) and 'LastName' (Doe). This is the standard approach for parsing delimited text within Power Query.

Exam trap

The trap here is that candidates may confuse 'Extract Text' with splitting, thinking it can parse delimiters, but 'Extract Text' only extracts fixed-length or positional substrings, not delimiter-based splits.

How to eliminate wrong answers

Option B is wrong because 'Merge Columns' is used to combine multiple columns into one, not to split a single column. Option C is wrong because 'Extract Text' allows you to pull out substrings based on position or length (e.g., first N characters), but it cannot dynamically split on a delimiter like a space. Option D is wrong because 'Replace Values' is designed to substitute one text value with another, not to separate a column into multiple parts.

Full explanation →

103

MCQmedium

You are designing a data model in Power BI that includes a fact table called 'Sales' and dimension tables 'Customer', 'Product', and 'Date'. The 'Sales' table contains columns: 'SalesID', 'CustomerID', 'ProductID', 'DateKey', 'Quantity', and 'Amount'. You need to ensure that the model follows star schema best practices and that filters from the 'Customer' table propagate correctly to the 'Sales' table. What should you do?

A.Merge the Customer and Sales tables into a single flat table.

B.Set the cross-filter direction to Both on the relationship between Customer and Sales.

C.Create a many-to-many relationship between Customer and Sales using SalesID.

D.Create a one-to-many relationship from Customer (one side) to Sales (many side) based on CustomerID.

AnswerD

Creating a one-to-many relationship from Customer (one side) to Sales (many side) based on CustomerID is the correct star schema pattern. CustomerID is the unique primary key in the Customer dimension table, and it appears as a foreign key in the Sales fact table, allowing each customer to link to multiple sales transactions. This direction supports intuitive filter propagation from the dimension to the fact table, enabling reliable aggregations like total sales per customer while preserving the granularity of the fact table and maintaining a clean, normalized model.

Why this answer

In a star schema, the dimension table (Customer) should have a one-to-many relationship to the fact table (Sales) based on the common key (CustomerID). This ensures that filters applied to the Customer table propagate correctly to the Sales table, maintaining referential integrity and enabling efficient query performance.

Exam trap

The trap here is that candidates often think bidirectional cross-filtering (Option B) is needed for filter propagation, but in a star schema, unidirectional filtering from dimension to fact is the correct and efficient approach.

How to eliminate wrong answers

Option A is wrong because merging Customer and Sales into a single flat table violates star schema normalization, leading to data redundancy and poor performance. Option B is wrong because setting cross-filter direction to Both on the relationship between Customer and Sales is unnecessary and can cause ambiguous filter propagation and performance issues; a single-direction filter from dimension to fact is sufficient. Option C is wrong because creating a many-to-many relationship using SalesID is incorrect; SalesID is a unique identifier for each sale and should not be used as a bridge for many-to-many relationships, which would break the star schema and cause incorrect aggregations.

Full explanation →

104

MCQhard

You are analyzing a DAX query as shown in the exhibit. You need to determine the result set. The model contains tables: Date, Product, and Sales with relationships. Which statement accurately describes the output?

A.The query returns total sales per year and category for Amount > 100

B.The query returns total sales for each year, ignoring category

C.The query returns sales amounts only for products with Amount > 100

D.The query returns total sales for each category, ignoring year

AnswerA

Correct. SUMMARIZECOLUMNS groups the sales data by the Year and Category columns, and the filter condition Amount > 100 is applied to the base table before aggregation. Therefore, for every distinct Year-Category pair, the query returns the sum of the sales measure computed only from rows that satisfy Amount > 100. This exactly matches the described output of total sales per year and category under that filter.

Why this answer

The DAX query uses SUMMARIZECOLUMNS to group sales by 'Year' from the Date table and 'Category' from the Product table, then filters the Sales table to include only rows where Amount > 100. The result is a table of total sales (sum of Amount) for each combination of year and category that meets the filter condition.

Exam trap

The trap here is that candidates often misinterpret the SUMMARIZE function as returning individual rows rather than aggregated groups, or they overlook that the filter condition applies to the underlying Sales rows, not to the aggregated result.

How to eliminate wrong answers

Option B is wrong because the query includes 'Category' in the SUMMARIZE grouping columns, so it does not ignore category; it returns totals per year and category, not per year alone. Option C is wrong because the query returns total sales (sum of Amount) per group, not individual sales amounts for each product; it aggregates, not lists. Option D is wrong because the query includes 'Year' in the grouping, so it does not ignore year; it returns totals per year and category, not per category alone.

Full explanation →

105

MCQmedium

You are the Power BI administrator for a large enterprise. The company has a Power BI Premium capacity with a single dataset that is used by multiple reports and dashboards. The dataset is refreshed daily at 3:00 AM, and the refresh typically completes within 2 hours. Recently, users have reported that the dataset is not showing the most recent data until after 6:00 AM. You investigate and find that the scheduled refresh is taking 4 hours to complete, and there are no errors in the refresh history. The dataset uses import mode and connects to an on-premises SQL Server data warehouse. The data model contains several large fact tables and multiple calculated tables and measures. What should you do to reduce the refresh time and ensure data is available by 5:00 AM?

A.Remove all calculated tables and measures and replace them with calculated columns in Power Query

B.Implement incremental refresh on the fact tables to refresh only new and changed data

C.Change the dataset storage mode to DirectQuery to avoid the import process

D.Install an additional on-premises data gateway and configure load balancing

AnswerB

Implementing incremental refresh on fact tables is the correct solution because it partitions the table by date and only processes partitions that are new or changed since the last refresh, dramatically reducing the amount of data pulled from the source and the storage engine workload. This requires an import-mode dataset with a date-time watermark column, RangeStart and RangeEnd parameters, and proper policy settings for archive periods; it directly targets the root cause of a prolonged refresh cycle by limiting the refresh scope to deltas instead of reprocessing the entire fact table history.

Why this answer

Implementing incremental refresh on the fact tables allows Power BI to refresh only new or changed data instead of the entire dataset each time. This significantly reduces the refresh window, especially for large fact tables, because only the latest partition (e.g., today's data) is processed. Since the scheduled refresh starts at 3:00 AM and must complete by 5:00 AM, incremental refresh can cut the refresh time from 4 hours to under 2 hours by avoiding reprocessing historical data.

Exam trap

The trap here is that candidates often choose Option C (DirectQuery) thinking it eliminates refresh time entirely, but they overlook that DirectQuery changes the entire query model and is not a direct fix for a scheduled import refresh that is simply taking too long due to data volume.

How to eliminate wrong answers

Option A is wrong because replacing calculated tables and measures with calculated columns in Power Query does not reduce refresh time; calculated columns are computed during data load and can actually increase memory and processing overhead, while measures are computed at query time and have no impact on refresh duration. Option C is wrong because changing the dataset storage mode to DirectQuery would bypass the import process entirely, but it would also eliminate the benefits of import mode (such as fast query performance) and would require the on-premises SQL Server to handle all query loads, potentially causing performance issues and breaking existing reports that rely on import-mode features like calculated tables. Option D is wrong because installing an additional on-premises data gateway and configuring load balancing improves gateway throughput and reliability but does not address the root cause of slow refresh—the full reload of large fact tables; the gateway is not the bottleneck here since there are no errors in refresh history and the issue is the volume of data being refreshed.

Full explanation →

106

MCQhard

You are reviewing a Power Query M expression that transforms column types. The 'SalesAmount' column contains values like '1,234.56' (with a comma as thousands separator). After applying this transformation, what is the likely result?

A.The transformation will result in errors for rows containing commas.

B.The column will be converted to text automatically.

C.The column will be successfully converted to numbers.

D.The transformation will ignore the comma and convert the number correctly.

AnswerA

The M expression, likely using Number.From or a table column type change, requires text to match the current locale's numeric format. Since a comma is not the decimal separator in the default en-US locale, each row containing a comma causes a conversion failure that produces an Error value in the cell. Rather than being corrected or ignored, the transformation faithfully reports the parse failure as an error, which is the expected result.

Why this answer

Power Query's default type conversion for numeric columns expects a period as the decimal separator and no thousands separator. When the 'SalesAmount' column contains values like '1,234.56' with a comma as a thousands separator, attempting to convert the column directly to a number type (e.g., using 'Change Type' or 'Table.TransformColumnTypes') will cause errors for rows containing commas, as Power Query cannot parse the comma as part of a valid number. The comma is not a recognized numeric character in the default locale, so the conversion fails.

Exam trap

The trap here is that candidates assume Power Query will automatically handle locale-specific formatting (like commas as thousands separators) during type conversion, but in reality, it fails with errors unless the data is preprocessed or the correct culture is specified.

How to eliminate wrong answers

Option B is wrong because Power Query does not automatically convert the column to text; the transformation explicitly changes the column type to a number, and if it fails, it produces errors, not a text conversion. Option C is wrong because the comma acts as a non-numeric character in the default locale, preventing successful conversion to numbers without prior data cleaning (e.g., replacing commas with empty strings). Option D is wrong because Power Query does not ignore the comma; it strictly parses the value and fails when encountering an unrecognized character, unlike some other tools that might auto-detect locale settings.

Full explanation →

107

Multi-Selectmedium

Which TWO actions can improve data refresh performance in Power BI?

Select 2 answers

A.Merge all queries into a single query.

B.Add calculated columns in Power Query instead of DAX.

C.Disable load for intermediate queries used only for reference.

D.Filter rows at the source to reduce data volume.

E.Keep all columns from the source data to avoid re-importing.

AnswersC, D

Prevents unnecessary data loading.

Why this answer

Disabling load for intermediate queries used only as reference steps prevents Power BI from materializing those tables in the data model. This reduces memory consumption and refresh time, as the engine skips loading data that isn't needed for reports or further transformations.

Exam trap

The trap here is that candidates may confuse 'disable load' with 'disable refresh' or think that merging queries (Option A) is always beneficial, when in fact it can reduce parallelism and hurt performance.

Full explanation →

108

MCQmedium

You are modeling a many-to-many relationship between 'Students' and 'Courses' via a junction table 'Enrollments'. You need to create a measure that counts the number of students enrolled in at least one course. The relationship between Students and Enrollments is one-to-many, and between Courses and Enrollments is one-to-many. What DAX measure should you use?

A.COUNTROWS(Enrollments)

B.COUNT(Courses[CourseID])

C.COUNTA(Students[StudentID])

D.DISTINCTCOUNT(Students[StudentID])

AnswerD

Counts unique students, works with many-to-many.

Why this answer

DISTINCTCOUNT(Students[StudentID]). This measure counts unique students, which correctly handles the many-to-many relationship because filters applied to 'Courses' propagate through 'Enrollments' to 'Students', ensuring only students with at least one enrollment are counted. Option A (COUNTROWS(Enrollments)) counts enrollment rows, potentially double-counting students with multiple courses.

Option B (COUNT(Courses[CourseID])) counts courses, not students. Option C (COUNTA(Students[StudentID])) counts all non-blank StudentIDs regardless of enrollment, missing the condition. DISTINCTCOUNT is the proper DAX function for distinct counts.

Full explanation →

109

MCQeasy

You have a Power BI model with a table named Sales that includes columns: OrderDate, Amount, and CustomerID. You need to create a measure that returns the total sales amount for the previous month based on the current filter context. Which DAX expression should you use?

A.CALCULATE(SUM(Sales[Amount]), PARALLELPERIOD('Date'[Date], -1, MONTH))

B.CALCULATE(SUM(Sales[Amount]), PREVIOUSMONTH('Date'[Date]))

C.CALCULATE(SUM(Sales[Amount]), DATEADD('Date'[Date], -1, MONTH))

D.CALCULATE(SUM(Sales[Amount]), NEXTMONTH('Date'[Date]))

AnswerB

PREVIOUSMONTH is the correct time-intelligence function because it returns a single-column table containing all dates from the calendar month immediately before the last date visible in the current filter context on the 'Date' table. When this table is used as a filter argument inside CALCULATE, it overrides the existing date filtering on the Sales table, so SUM(Sales[Amount]) is evaluated over exactly the prior month's dates. This is the idiomatic DAX pattern for a previous-month measure, and it avoids the shape-preserving ambiguity of PARALLELPERIOD and the forward-looking behavior of NEXTMONTH. No other period-shifting function gives as clean a one-month window as PREVIOUSMONTH in this scenario.

Why this answer

PREVIOUSMONTH returns a single month period shifted back by one month from the last date in the current filter context, which directly gives the total sales for the previous month. This measure respects the current filter context and works correctly when a proper date table is used.

Exam trap

The trap here is that candidates often confuse PREVIOUSMONTH with DATEADD or PARALLELPERIOD, not realizing that PREVIOUSMONTH is specifically designed to return a single full previous month based on the last date in context, while DATEADD shifts dates individually and PARALLELPERIOD can return multiple periods.

How to eliminate wrong answers

Option A is wrong because PARALLELPERIOD returns a set of parallel periods (e.g., entire months) but does not guarantee a single previous month; it can return multiple months if the current period spans multiple months, leading to incorrect totals. Option C is wrong because DATEADD with -1 month shifts each date by one month but does not restrict to a full previous month; it can return partial month data or overlapping periods depending on the granularity. Option D is wrong because NEXTMONTH returns the next month, not the previous month, which is the opposite of what is required.

Full explanation →

110

MCQhard

A Power BI report contains a table visual that displays employee names and their total sales. The data model includes an Employee table with columns: EmployeeID, Name, Department, and HireDate. The Sales table has columns: SaleID, EmployeeID, Amount, and SaleDate. The relationship between Employee and Sales is one-to-many. The user wants to see only employees who have made at least one sale. However, the table shows all employees, including those with no sales (blank Amount). What is the most likely reason?

A.The EmployeeID column in the Employee table is hidden.

B.The relationship is many-to-one, not one-to-many.

C.The relationship direction is set to Single from Employee to Sales.

D.There is no visual-level filter to exclude blank values.

AnswerD

To show only employees who have at least one sales record, a visual-level filter must be applied on the Amount field to exclude blank values (e.g., 'Amount is not blank' or 'Amount > 0'). Without such a filter, the table visual displays every row from the Employee dimension, even those without any related Sales rows, because Power BI's default behavior is to show all dimension rows unless a filter explicitly removes them. A visual-level filter on a measure or column from the fact table is the standard technique to restrict the visual to only employees with sales.

Why this answer

The table visual is showing all employees due to the absence of a visual-level filter to exclude blank or zero sales amounts. In Power BI, a one-to-many relationship between Employee and Sales means that employees without sales will still appear in the visual unless explicitly filtered out, as the relationship does not automatically suppress rows from the 'one' side when there are no matching rows on the 'many' side.

Exam trap

The trap here is that candidates assume a one-to-many relationship will automatically hide employees without related sales, but Power BI does not apply implicit row-level security or auto-filtering for missing related records; you must explicitly filter out blank values.

How to eliminate wrong answers

Option A is wrong because hiding the EmployeeID column does not affect the visibility of employees in the table; it only prevents that column from being displayed. Option B is wrong because the relationship is correctly described as one-to-many (one employee can have many sales), and changing it to many-to-one would be incorrect for this data model. Option C is wrong because setting the relationship direction to Single from Employee to Sales is the default and correct direction for a one-to-many relationship; it does not cause all employees to appear regardless of sales.

Full explanation →

111

MCQmedium

You need to create a Power BI data model that includes a date dimension. The source data contains a table with a Date column covering 2015-2025. You want to ensure that all dates in the model have a contiguous range for time intelligence. What should you do?

A.Hide the existing Date column and rely on auto date/time.

B.Add a calculated column for year and month from the existing Date column.

C.Create a calculated table using CALENDAR to generate a continuous date range and mark it as a date table.

D.Use the existing Date column as the date table and mark it as a date table.

AnswerC

Correct. Creating a calculated table using CALENDAR generates a contiguous range of dates, ensuring no gaps for time intelligence. (Although the option text says 'calculated column', the intended action is to create a calculated table.)

Why this answer

The CALENDAR function creates a calculated table (not a column) that generates a contiguous range of dates, which is essential for accurate time intelligence calculations such as YTD or QoQ. After creating this calculated table, you can mark it as a date table to enable DAX time intelligence functions. Although the option wording mistakenly says 'calculated column', the intent is to create a separate date table using CALENDAR, which is the proper method to ensure no gaps in the date dimension.

Exam trap

The trap is that candidates often think marking an existing date column as a date table (Option D) is sufficient, but they overlook the requirement for a contiguous range, which is critical for time intelligence to work correctly. Additionally, some may confuse CALENDAR as a calculated column function when it actually returns a table.

How to eliminate wrong answers

Option A is wrong because relying on auto date/time creates hidden date tables that are not user-defined, cannot be marked as a date table, and may not cover the full contiguous range needed for custom time intelligence. Option B is wrong because adding calculated columns for year and month does not address gaps in the date range; it only extracts parts from existing dates, leaving missing dates unhandled. Option D is wrong because using the existing Date column as the date table without ensuring contiguity can lead to gaps in the date dimension, causing time intelligence functions to return incorrect results or errors.

Full explanation →

112

MCQmedium

You are connecting to a SQL Server database using Import mode. The source table contains a column 'SalesAmount' with a few null values. You need to replace nulls with 0 before loading. What is the most efficient step to achieve this in Power Query Editor?

A.Use 'Replace Values' to replace null with 0

B.Use 'Replace Errors' with value 0

C.Use 'Fill Down' to propagate previous values

D.Add a custom column with an if statement

AnswerA

Replace Values is the correct, direct transformation because it performs an in-place, column-wise substitution of nulls with 0 in a single Power Query step. It targets the actual null placeholder (not an error) and is applied to all selected columns simultaneously, making it the most efficient and unambiguous method for this exact requirement.

Why this answer

'Replace Values' in Power Query Editor is the most efficient way to replace null values in a column with 0. It directly transforms the column in a single step without requiring additional logic or table scans, and it generates a clean M code step (Table.ReplaceValue) that operates natively on the column's nulls.

Exam trap

The trap here is that candidates often confuse 'Replace Values' with 'Replace Errors' or think nulls are errors, leading them to choose Option B, but nulls are a distinct data type (absence of value) and require a dedicated null-replacement operation.

How to eliminate wrong answers

Option B is wrong because 'Replace Errors' is designed to replace error values (e.g., #ERROR) in cells, not null values; nulls are not errors and will not be affected by this transformation. Option C is wrong because 'Fill Down' propagates the last non-null value from above, which would incorrectly replace nulls with arbitrary previous values rather than a fixed 0, and it assumes a sequential order that may not be meaningful. Option D is wrong because adding a custom column with an if statement (e.g., if [SalesAmount] = null then 0 else [SalesAmount]) creates a new column and leaves the original column unchanged, requiring an extra step to remove or replace the original column, making it less efficient than a direct replacement.

Full explanation →

113

MCQmedium

You need to combine two tables from different sources: 'Orders' from SQL Server and 'Returns' from an Excel file. Both tables have a column named 'OrderID'. You want to include all orders and only matching returns. Which join type should you use in Power Query?

A.Inner Join

B.Right Outer Join

C.Full Outer Join

D.Left Outer Join

AnswerD

Left outer join returns all rows from the first table and matching rows from the second.

Why this answer

In Power Query, a Left Outer Join returns all rows from the first (left) table ('Orders') and only the matching rows from the second (right) table ('Returns'), based on the 'OrderID' column. This matches the requirement to include all orders and only matching returns, ensuring no order is dropped even if it has no corresponding return.

Exam trap

The trap here is that candidates often confuse Left Outer Join with Right Outer Join, mistakenly thinking they need to include all returns instead of all orders, or they default to Inner Join without considering the requirement to preserve unmatched rows from the left table.

How to eliminate wrong answers

Option A is wrong because an Inner Join returns only rows where there is a match in both tables, which would exclude orders without returns. Option B is wrong because a Right Outer Join returns all rows from the right table ('Returns') and only matching rows from the left table ('Orders'), which would include all returns but not all orders. Option C is wrong because a Full Outer Join returns all rows from both tables, including non-matching rows from both sides, which would include returns without orders and is not the requirement.

Full explanation →

114

MCQhard

You are a Power BI administrator for a large enterprise. You have a Power BI semantic model that uses a single large fact table named Sales (100 million rows) and several dimension tables. The model is used by multiple departments, each with different row-level security (RLS) rules based on the SalesRegion column. You have implemented RLS using static roles. However, you notice that when users from different departments view the same report page, the query performance varies significantly. You suspect that the RLS filters are causing the performance difference. You need to investigate and optimize the RLS performance. What should you do first?

A.Increase the data model's memory limit in Premium capacity.

B.Use Power BI Performance Analyzer to capture query performance for each user role and analyze the generated DAX queries in DAX Studio.

C.Convert all RLS roles to use dynamic RLS with USERPRINCIPALNAME.

D.Remove all RLS roles and implement security at the report level using bookmarks.

AnswerB

Power BI Performance Analyzer captures per-visual query durations and the exact DAX produced, which you can then paste into DAX Studio for deep profiling. Running the same report as each RLS role (or using DAX Studio's 'Trace as Role'/'User' feature) lets you compare query plans and see how RLS filters are injected, exposing whether they cause excessive storage-engine scans or formula-engine bottlenecks. This combination is the standard way to pinpoint the exact query path responsible for role-specific slowness.

Why this answer

Using Power BI Performance Analyzer allows you to capture query performance for each user role, and analyzing the generated DAX queries in DAX Studio helps identify which queries are slow and whether RLS is the cause. This is the first logical step to diagnose the performance variation. Option A (increasing memory) may not address RLS-specific issues.

Option C (converting to dynamic RLS) is a potential optimization but not the initial diagnostic step. Option D (removing RLS and using bookmarks) is not a recommended security approach and would not investigate the root cause.

Full explanation →

115

Multi-Selecthard

Which TWO of the following are valid ways to create a date table in Power BI?

Select 2 answers

A.Using the time intelligence functions like TOTALYTD.

B.Marking an existing table as a date table.

C.Enabling the auto date/time feature.

D.Using DAX function CALENDAR or CALENDARAUTO.

E.Using Power Query with List.Dates.

AnswersD, E

Creates a date table with a range of dates.

Why this answer

The DAX functions CALENDAR and CALENDARAUTO are specifically designed to generate a single-column table of dates, which can then be used as a date table. CALENDAR requires explicit start and end dates, while CALENDARAUTO automatically scans the model to determine the date range. Both are standard methods for creating a date table in Power BI.

Exam trap

The trap here is that candidates confuse time intelligence functions (like TOTALYTD) with table creation functions, or they assume that marking a table as a date table or enabling auto date/time actually creates a new date table, when in fact those options only configure existing data.

Full explanation →

116

MCQeasy

You are importing a CSV file into Power BI. The file contains a date column with values in the format 'MM/dd/yyyy'. However, Power Query interprets the dates as 'dd/MM/yyyy'. What should you do to correctly parse the dates?

A.Change the system region settings of the Power BI service to US

B.Use the 'Using Locale' option in the Change Type step to select the appropriate locale (e.g., English (United States))

C.Change the column data type to Text and then manually replace separators

D.Split the column into day, month, and year, then combine them in the correct order

AnswerB

The 'Using Locale' option in the Change Type step (accessed via the Data Type dropdown in Power Query Editor) lets you specify a culture, such as English (United States), that determines how date strings are parsed. By selecting a locale, you override the default system regional settings for that specific transformation, ensuring that a date like '03/04/2021' is interpreted as March 4th rather than April 3rd. This is the precise, minimal solution because it applies only to the selected column and records an M expression with 'Culture' parameter, making it reproducible in subsequent data refreshes without altering any global or tenant-wide configuration.

Why this answer

Power Query's 'Using Locale' option in the Change Type step allows you to specify the regional format of the source data (e.g., English (United States) for 'MM/dd/yyyy'). This overrides Power Query's default locale-based interpretation, ensuring dates are parsed correctly without altering the data or system settings.

Exam trap

The trap here is that candidates often assume changing system region settings (Option A) will fix the issue, but Power Query's locale handling is independent of the Power BI service region, and the correct approach is to use the 'Using Locale' option within the query editor.

How to eliminate wrong answers

Option A is wrong because changing the Power BI service region settings does not affect how Power Query Desktop interprets date formats during import; locale handling is a Power Query engine feature, not a service-level setting. Option C is wrong because manually replacing separators is error-prone and unnecessary; Power Query already supports locale-aware date parsing without data transformation. Option D is wrong because splitting and recombining columns is a cumbersome workaround that introduces complexity and potential data loss, whereas the 'Using Locale' option directly solves the parsing issue.

Full explanation →

117

Multi-Selecteasy

Which TWO of the following are valid methods to combine data from multiple sources in Power BI?

Select 2 answers

A.Pivot Column

B.Append Queries

C.Unpivot Columns

D.Merge Queries

E.Group By

AnswersB, D

Appends rows from one query to another.

Why this answer

Append Queries is a valid method to combine data from multiple sources in Power BI because it stacks rows from two or more tables into a single table, which is essential when you have similar data structures across different sources (e.g., monthly sales files). This operation is performed in Power Query Editor and corresponds to a UNION operation in SQL, making it a standard data preparation technique.

Exam trap

The trap here is that candidates often confuse data transformation operations (like Pivot, Unpivot, Group By) with data combination operations (Append and Merge), leading them to select options that modify existing data rather than integrate multiple sources.

Full explanation →

118

MCQmedium

You have a Power BI workspace named Sales. You need to ensure that only users in the Finance security group can view reports in this workspace, while members of the Sales team can edit and share content. What should you do?

A.Add Finance as Viewer, Sales as Member.

B.Add Finance as Contributor, Sales as Member.

C.Add Finance as Viewer, Sales as Admin.

D.Use row-level security to restrict Finance data, add both as Member.

AnswerA

Correct. Viewer provides read-only access for Finance, Member allows Sales to edit and share.

Why this answer

Workspace roles in Power BI are designed to grant specific permissions: Viewer allows read-only access, ideal for Finance who only need to view reports; Member allows editing and sharing, which matches the Sales team's requirements. Option B is wrong because Contributor role cannot share content, which Sales needs. Option C is wrong because Admin grants full control, including managing permissions, which is unnecessary and excessive.

Option D is wrong because row-level security (RLS) controls data access within reports, not workspace-level permissions.

Exam trap

A common trap is confusing Contributor with Member. Contributor can edit but not share, while Member can both edit and share. Also, a candidate might think Viewer is insufficient for Finance, but it correctly restricts access.

Full explanation →

119

MCQhard

You are importing data from a CSV file into Power BI. The file contains a column 'SalesAmount' with values like '1,234.56' and '(987.65)' for negative amounts. You need to transform this column into a decimal number. Which sequence of Power Query steps achieves this?

A.Change Type to Decimal, then Replace Values (',' with ''), then Replace Values ('(' with '-')

B.Replace Values (',' with ''), then Replace Values ('(' with '-'), then Replace Values (')' with ''), then Change Type to Decimal

C.Replace Values (',' with ''), Change Type to Decimal, then Replace Values ('(' with '-')

D.Replace Values ('(' with '-'), Replace Values (')' with ''), Replace Values (',' with ''), then Change Type to Decimal

AnswerD

Correct order: handle negative sign first, then remove comma, then type conversion.

Why this answer

It first replaces the opening parenthesis '(' with a minus sign '-', then removes the closing parenthesis ')', then removes the comma thousands separator ',', and finally changes the data type to Decimal. This sequence ensures that the negative indicator is properly placed before the numeric value and that the string is cleanly formatted for type conversion.

Exam trap

The trap here is that candidates often try to change the data type too early, before cleaning the string, or they forget to remove the closing parenthesis after replacing the opening one, leading to conversion errors.

How to eliminate wrong answers

Option A is wrong because changing the type to Decimal before removing the comma and parentheses will cause errors, as the string '1,234.56' and '(987.65)' are not valid decimal numbers. Option B is wrong because replacing '(' with '-' before removing ')' leaves a trailing parenthesis that will cause the type conversion to fail. Option C is wrong because changing the type to Decimal before handling the parentheses will result in errors for negative values, as the string still contains parentheses.

Full explanation →

120

MCQhard

Your organization uses Microsoft Purview Information Protection to label sensitive data in Power BI datasets. You need to ensure that when a report is exported to Excel, the sensitivity label is automatically applied. What should you configure?

A.Ensure the dataset has a sensitivity label and that the export inherits the label.

B.Use data loss prevention (DLP) policies in Microsoft Purview.

C.Set a default sensitivity label on the report.

D.Enable 'Apply sensitivity labels to exported data' in the Power BI admin portal.

AnswerA

Correct. When a dataset has a sensitivity label, exports inherit it automatically, ensuring consistent protection without manual intervention.

Why this answer

When a Power BI dataset has a sensitivity label applied via Microsoft Purview Information Protection, any downstream exports (such as reports exported to Excel) automatically inherit that label. This inheritance ensures consistent protection across all outputs. Option D is incorrect because the Power BI admin portal setting 'Apply sensitivity labels to exported data' is used to apply labels when the data source does not have a label, but it does not override automatic inheritance from a labeled dataset.

Option B is incorrect because DLP policies in Microsoft Purview monitor and protect data, but they do not automatically apply sensitivity labels to exports. Option C is incorrect because setting a default sensitivity label on a report is not a feature; sensitivity labels are inherited from the dataset, not configured per report.

Full explanation →

121

MCQeasy

You need to create a relationship between two tables in Power BI. Both tables contain a column named 'ProductID', but the values in one table are integers and in the other are text. What should you do first?

A.Merge the two tables into one in Power Query.

B.Ensure both columns have the same data type, either by changing the data type in Power Query or in the model view.

C.Create a new calculated column that converts the integer to text using FORMAT.

D.Set the relationship to 'Many-to-many' to bypass the type mismatch.

AnswerB

Power BI relationships require that the key columns on both sides have identical data types; a mismatch between text and integer, for example, will prevent the relationship from being created. Changing the data type in Power Query is the preferred method because it transforms the data during load, while changing it in the Model view only alters the metadata and may not propagate back to the query. Ensuring the same data type is the foundational step before defining cardinality and cross-filter direction.

Why this answer

In Power BI, relationships require matching data types on both sides of the key columns. If one 'ProductID' column is integer and the other is text, the relationship engine cannot resolve the join because the data types are incompatible. Changing both columns to the same data type—either in Power Query (recommended for performance) or in the model view—resolves this mismatch and allows a valid relationship to be created.

Exam trap

The trap here is that candidates assume a many-to-many relationship can ignore data type mismatches, but Power BI still enforces type compatibility on the key columns used for the relationship.

How to eliminate wrong answers

Option A is wrong because merging tables in Power Query creates a single denormalized table, which is unnecessary and can lead to data duplication; the goal is to create a relationship, not to combine the tables. Option C is wrong because using FORMAT in a calculated column converts the integer to text, but this adds a redundant column and introduces performance overhead; it is better to change the data type of the column directly in Power Query or the model. Option D is wrong because a many-to-many relationship does not bypass data type mismatches; the relationship engine still requires compatible data types on both key columns, regardless of cardinality.

Full explanation →

122

MCQhard

You are configuring a Power BI dataset with incremental refresh. The above JSON shows part of the M script parameters. The dataset uses a single SQL Server data source. You need to ensure that incremental refresh works correctly. What must you do?

A.Change the connection string to use a SQL account instead of integrated security.

B.Rename the parameter 'StartDate' to 'RangeStart' and add a 'RangeEnd' parameter.

C.Enable query folding on the SQL Server source.

D.Define the parameters in the Power Query editor instead of the JSON file.

AnswerB

Incremental refresh uses two reserved parameters named RangeStart and RangeEnd to filter the data. The current parameter name is incorrect.

Why this answer

Incremental refresh in Power BI requires two special date/time parameters named 'RangeStart' and 'RangeEnd' (case-sensitive). The JSON snippet shows a parameter named 'StartDate', which is not recognized by the incremental refresh engine. Renaming it to 'RangeStart' and adding a 'RangeEnd' parameter allows Power BI to filter the data source query dynamically during refresh, ensuring only the changed or new rows are loaded.

Exam trap

The trap here is that candidates assume any date parameter name will work for incremental refresh, but Power BI strictly requires the exact names 'RangeStart' and 'RangeEnd' (case-sensitive) to enable the partitioning logic.

How to eliminate wrong answers

Option A is wrong because changing the connection string to use a SQL account instead of integrated security does not affect the incremental refresh mechanism; authentication method is unrelated to the parameter naming convention required by Power BI. Option C is wrong because query folding is automatically enabled for SQL Server sources when using native queries or direct table references; it is not a manual step that needs to be performed separately for incremental refresh to work. Option D is wrong because defining parameters in the Power Query editor is the standard method, but the JSON file shown is the correct way to store them; the issue is the parameter names, not where they are defined.

Full explanation →

123

MCQmedium

You have a Power BI dataset that includes a date table created using CALENDAR(). You need to ensure that the date table always covers the full range of dates present in the fact table, even after new data is loaded. What should you do?

A.Use a fixed start and end date in the CALENDAR function

B.Create a disconnected date table

C.Create the date table using CALENDAR(MIN('Fact'[Date]), MAX('Fact'[Date]))

D.Enable Auto Date/Time in the model

AnswerC

CALENDAR(MIN('Fact'[Date]), MAX('Fact'[Date])) generates a date table whose range is dynamically derived from the fact table's minimum and maximum dates on each refresh. This ensures the date table always covers the exact span of transactional activity, automatically expanding when new data includes later or earlier dates. Because the range is computed rather than hard-coded, it requires no manual maintenance and provides a continuous set of date keys suitable for marking as the model's date table and enabling time intelligence.

Why this answer

Using `CALENDAR(MIN('Fact'[Date]), MAX('Fact'[Date]))` dynamically computes the date range from the fact table's actual data. This ensures that when new data is loaded with dates outside the previous range, the date table automatically expands to cover the full range, maintaining referential integrity for time intelligence calculations.

Exam trap

The trap here is that candidates often choose Option A (fixed dates) because they think a static range is simpler and sufficient, but they overlook the requirement for the date table to dynamically cover the full range after new data loads, which only a dynamic CALENDAR expression can achieve.

How to eliminate wrong answers

Option A is wrong because using a fixed start and end date in the CALENDAR function creates a static date table that will not expand when new data with dates outside that fixed range is loaded, leading to missing dates and broken relationships. Option B is wrong because a disconnected date table is not related to the fact table via a relationship, so it cannot enforce referential integrity or be used for standard time intelligence functions that rely on an active relationship. Option D is wrong because enabling Auto Date/Time creates hidden date tables automatically, but these tables are not user-defined, cannot be customized, and do not guarantee coverage of the exact date range present in the fact table; they also increase model size and are not recommended for production.

Full explanation →

124

MCQmedium

You are creating a Power BI dataset from a SQL Server data warehouse. The warehouse contains a fact table with 500 million rows and dimension tables. You need to minimize the data refresh time while ensuring that the dataset meets the reporting requirements. Which approach should you recommend?

A.Create a composite model using DirectQuery for the fact table and Import for dimensions.

B.Use DirectQuery mode for the dataset.

C.Configure incremental refresh on the fact table.

D.Use Import mode but filter rows and reduce columns in Power Query to only those needed.

AnswerD

Importing only necessary data reduces the amount of data loaded, directly decreasing refresh time while still supporting fast query performance.

Why this answer

Importing only the necessary columns and rows reduces the data volume, which directly minimizes refresh time. With 500 million rows, Import mode is generally faster than DirectQuery for large fact tables in Power BI, as it avoids querying the source on every interaction. Filtering and column reduction in Power Query ensures the dataset remains lean while meeting reporting requirements.

Exam trap

The trap here is that candidates often choose incremental refresh (Option C) thinking it always reduces refresh time, but it does not address the initial full load or the need to minimize data volume; the key is to reduce the data imported, not just partition it.

How to eliminate wrong answers

Option A is wrong because a composite model with DirectQuery on the fact table would still require live queries against 500 million rows, leading to slow report performance and potential timeouts, not minimizing refresh time. Option B is wrong because DirectQuery mode does not have a refresh process; it queries the source on demand, which would be extremely slow for a 500-million-row fact table and does not reduce data transfer. Option C is wrong because incremental refresh only partitions the fact table for scheduled refreshes, but it still requires importing all historical data initially and does not minimize the initial or ongoing refresh time as effectively as reducing the data volume.

Full explanation →

125

Multi-Selectmedium

Which TWO of the following are best practices for designing a Power BI data model?

Select 2 answers

A.Use a star schema to reduce model complexity.

B.Use surrogate keys in dimension tables to link to fact tables.

C.Store dimension attributes directly in fact tables for faster queries.

D.Enable bi-directional cross-filtering on all relationships.

E.Create calculated columns instead of Power Query transformations when possible.

AnswersA, B

Star schema is the recommended design.

Why this answer

A star schema organizes data into dimension and fact tables, reducing redundancy and simplifying queries. This design improves query performance by minimizing the number of table joins and enabling efficient aggregation, which is a core best practice in Power BI data modeling.

Exam trap

The trap here is that candidates often confuse 'faster queries' with storing attributes directly in fact tables (Option C), not realizing that this increases table size and degrades performance due to higher cardinality and reduced compression efficiency.

Full explanation →

126

MCQmedium

A company has a Power BI dataset that imports data from a SQL Server database. The dataset includes a table with 10 million rows. The data model uses a single table and does not include any calculated columns or measures. The report users report that the dataset refresh takes too long. Which action should you take to improve refresh performance?

A.Increase the scheduled refresh frequency to every 15 minutes.

B.Enable Query Folding on all steps in Power Query.

C.Change the storage mode to DirectQuery.

D.Remove unused columns from the table in Power Query.

AnswerD

Reduces data volume and improves refresh speed.

Why this answer

Removing unused columns from the table in Power Query reduces the amount of data loaded into the Power BI dataset. With 10 million rows, every unnecessary column adds significant I/O and memory overhead during refresh. This directly improves refresh performance by minimizing the data volume transferred and processed.

Exam trap

The trap here is that candidates often confuse refresh performance with query performance, leading them to choose DirectQuery (Option C) which solves query latency but does not improve the import refresh time that the question explicitly targets.

How to eliminate wrong answers

Option A is wrong because increasing the scheduled refresh frequency to every 15 minutes does not improve the performance of a single refresh; it only makes refreshes happen more often, which could actually increase load on the source system. Option B is wrong because Query Folding pushes transformations back to the SQL Server, but the question states the dataset imports data from SQL Server and has no calculated columns or measures; enabling Query Folding on all steps is not a guaranteed performance improvement if the steps are already foldable, and it does not address the core issue of a large single table with 10 million rows. Option C is wrong because changing the storage mode to DirectQuery would avoid importing the data, but it shifts performance burden to query-time latency and is not a refresh performance improvement; the question specifically asks about improving dataset refresh time, not report query performance.

Full explanation →

127

Multi-Selectmedium

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

Select 2 answers

A.Use a bridge table to resolve the many-to-many relationship

B.Set cross-filter direction to both for all relationships

C.Use a composite model with DirectQuery for one of the tables

D.Use a measure that iterates over the bridge table with SUMX

E.Create a calculated table that combines the two tables

AnswersA, D

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.

Why this answer

Both using a bridge table and using a measure that iterates over it with SUMX are best practices for modeling many-to-many relationships in Power BI. A bridge table, when properly configured with many-to-many cardinality or CROSSFILTER, enables correct filtering. A SUMX measure over the bridge table ensures accurate aggregations.

Setting cross-filter direction to both (Option B) can cause ambiguous filtering and performance issues. Composite models with DirectQuery (Option C) are not a specific best practice for this scenario. Creating a calculated table (Option E) does not properly resolve the relationship and often leads to data duplication.

Full explanation →

128

MCQmedium

You are working on a Power BI project for a marketing department. You have a CSV file with customer survey responses. The file contains columns: CustomerID, SurveyDate, Response (text with ratings from 1 to 5), Comments (free text). The file is 10 MB. You need to load the data into Power BI and create a measure that calculates the average rating. However, when you load the file, you notice that the Response column is imported as text instead of whole number. Also, there are some rows with missing values in the Response column. You need to ensure the data is correctly typed and handle missing values appropriately. What is the best approach?

A.Use the 'Column from Examples' feature to create a new column with numeric values.

B.In Power Query, split the Response column by delimiter and then use the first part.

C.Use a DAX calculated column to convert text to number.

D.Change the data type of Response to whole number in Power Query, then filter out or replace null values.

AnswerD

Changing the data type of the Response column to whole number in Power Query is the correct approach because it directly addresses the underlying issue: the column is text but requires a numeric type for analysis. In Power Query, this transformation attempts to convert every value, and null values or invalid entries can be handled in the same step by filtering out invalid rows or replacing nulls with a default (e.g., 0) before loading. This is efficient, happens before data enters the model, and avoids the extra overhead of DAX calculated columns while preserving the column's identity and data lineage.

Why this answer

Power Query is the designated tool for data type transformations and null handling during the load phase. Changing the Response column's data type to Whole Number in Power Query automatically converts valid text numbers and flags errors, while filtering out or replacing null values ensures clean data before the data model is built. This approach follows the best practice of performing data cleansing in Power Query rather than in DAX, which would add unnecessary overhead and complexity.

Exam trap

The trap here is that candidates often think data type conversion can be done in DAX (Option C) because it seems simpler, but the PL-300 exam emphasizes that Power Query is the correct place for data preparation tasks like type changes and null handling, not the data model layer.

How to eliminate wrong answers

Option A is wrong because the 'Column from Examples' feature is designed for extracting or combining values based on patterns, not for bulk data type conversion; it would be inefficient and error-prone for converting a column of text numbers to numeric values. Option B is wrong because splitting the Response column by delimiter assumes the text contains a delimiter, which is not the case here (the column contains simple ratings like '1' or '5'), and it would create unnecessary columns without solving the type conversion or null handling. Option C is wrong because using a DAX calculated column to convert text to number is possible but is inefficient and violates the principle of performing data type transformations in Power Query; it also does not handle null values in the source data, which would still need to be addressed separately.

Full explanation →

129

MCQmedium

You have a Power BI data model with a fact table and multiple dimension tables. You notice that many-to-many relationships cause ambiguous results. What is the best practice to resolve this?

A.Change the relationship to one-to-one

B.Use a bidirectional cross-filter direction

C.Create a calculated table to merge the dimensions

D.Add a bridge table with appropriate relationships

AnswerD

A bridge table is the standard pattern for handling many-to-many relationships in Power BI: it holds unique combinations of the involved keys and connects to the fact table via one-to-many relationships to each dimension. This normalizes the originally ambiguous many-to-many relationship into two clear one-to-many paths, allowing filters from either dimension to propagate correctly without duplicating fact rows. It preserves each dimension's granularity and ensures that measures aggregate exactly once per relevant fact record, making it the correct modeling solution.

Why this answer

In Power BI, many-to-many relationships between fact and dimension tables can produce ambiguous results because the model cannot determine a unique filter propagation path. The best practice is to introduce a bridge table that resolves the many-to-many relationship into two one-to-many relationships, ensuring unambiguous filter context and correct aggregations.

Exam trap

The trap here is that candidates often confuse bidirectional cross-filter direction as a quick fix for many-to-many relationships, but Microsoft explicitly warns that bidirectional filtering can lead to ambiguous results and performance degradation, whereas a bridge table is the recommended pattern.

How to eliminate wrong answers

Option A is wrong because changing the relationship to one-to-one is rarely feasible in real-world data models where multiple facts naturally relate to multiple dimensions, and forcing a one-to-one would require data duplication or loss of granularity. Option B is wrong because bidirectional cross-filter direction can cause ambiguous filter propagation and performance issues, and it does not resolve the underlying logical many-to-many relationship; it often leads to circular dependencies or unexpected results. Option C is wrong because creating a calculated table to merge dimensions does not address the many-to-many relationship; it simply combines dimension attributes without resolving the cardinality mismatch, and it can introduce redundancy and maintenance challenges.

Full explanation →

130

MCQhard

You are building a Power BI semantic model that uses a large fact table from a data warehouse. The fact table has a date column and you want to create a date dimension. The organization requires that the date dimension includes all dates from 2010 to 2030, including weekends and holidays. What is the best practice for creating the date dimension?

A.Use the CALENDAR function in DAX to generate the date range

B.Mark the date column from the fact table as a date table and disable Auto Date/Time

C.Create a date dimension by using DISTINCT on the fact table's date column

D.Create a date table in Power Query by generating a list of dates from 1/1/2010 to 12/31/2030 and then add columns for attributes

AnswerD

Generating a complete list of dates in Power Query from 1/1/2010 to 12/31/2030 ensures that every day in that span exists as a row in the date table, regardless of activity in the fact table. You can then use M to add derived columns like year, month, quarter, ISO week number, and custom holiday flags by referencing a holidays table, making the solution flexible and easy to maintain. This approach follows the best practice of creating a distinct, static date dimension that supports reliable time intelligence and efficient relationships in a large semantic model.

Why this answer

It follows the best practice of creating a dedicated date dimension table in Power Query, which ensures full control over the date range (2010–2030) and allows you to add custom attributes like holidays. This approach avoids relying on the fact table's date column, which may have gaps or missing dates, and ensures the date dimension is complete and independent for robust time intelligence calculations.

Exam trap

The trap here is that candidates often choose Option C (DISTINCT on the fact table) thinking it is efficient, but they overlook that it will miss dates with no transactions, violating the requirement to include all weekends and holidays from 2010 to 2030.

How to eliminate wrong answers

Option A is wrong because the CALENDAR function in DAX creates a calculated table that is volatile and recalculates on every refresh, which can degrade performance with large models; it also lacks the ability to easily add custom columns like holidays in Power Query. Option B is wrong because marking a date column from the fact table as a date table is not recommended when the fact table may have missing dates (e.g., weekends or holidays), and disabling Auto Date/Time is a separate setting that does not create a proper date dimension. Option C is wrong because using DISTINCT on the fact table's date column will only include dates that exist in the fact table, which may omit weekends or holidays if no transactions occurred on those days, resulting in an incomplete date dimension.

Full explanation →

131

Multi-Selecthard

Which TWO are best practices for optimizing Power Query performance? (Choose two.)

Select 2 answers

A.Disable the 'Enable background refresh' option in the query settings.

B.Merge tables as early as possible in the query to combine data.

C.Keep all columns in the table to avoid missing data.

D.Filter data as early as possible in the query to reduce row counts.

E.Split columns by delimiter to normalize data.

AnswersA, D

Disabling background refresh can improve performance by preventing simultaneous refreshes.

Why this answer

Disabling 'Enable background refresh' prevents Power Query from running queries in the background while you continue working in Power BI Desktop. This ensures that query execution is synchronous, which can improve performance by avoiding resource contention and allowing you to monitor progress directly. Background refresh can cause delays and unexpected behavior when multiple queries run simultaneously, especially with large data sources.

Exam trap

The trap here is that candidates often confuse data transformation best practices (like merging or splitting columns) with performance optimization techniques, leading them to select options B or E instead of focusing on reducing data volume and controlling query execution.

Full explanation →

132

Multi-Selecthard

Which are valid ways to create a calculated table in Power BI? (Select all that apply)

Select 4 answers

A.VALUES(Customer[Country])

B.CALCULATE(SUM(Sales[Amount]), ALL(Sales))

C.FILTER(Products, Products[Color] = "Red")

D.CALENDARAUTO()

E.SUMMARIZE(Sales, Sales[ProductID], "Total", SUM(Sales[Amount]))

AnswersA, C, D, E

VALID: VALUES returns a single-column table of distinct values from a column, which can be used to create a calculated table.

Why this answer

To create a calculated table, you need a DAX expression that returns a table object. Options A, C, D, and E all return tables: VALUES returns a single-column table of distinct values; FILTER returns a filtered subset of rows; CALENDARAUTO returns a date range table; SUMMARIZE returns a grouped table with aggregations. Option B, CALCULATE, returns a scalar value, not a table, so it is invalid.

Exam trap

The trap is assuming only specialized table functions like CALENDARAUTO and SUMMARIZE are valid, while other table-returning functions like VALUES and FILTER are also perfectly valid for calculated tables. The key is to recognize that any DAX function that returns a table can be used.

Full explanation →

133

MCQmedium

You are modeling data from multiple sources: a SQL Server database for sales, an Excel file for budget, and a SharePoint list for product targets. You need to combine these into a single Power BI report. What is the recommended approach for handling data refresh?

A.Import each source into separate Power BI Desktop files and manually update.

B.Use Excel Online as the single source and import all data into it first.

C.Use Power Query to combine data from all sources into a single dataset, then schedule a daily refresh in the Power BI service.

D.Create separate datasets for each source and use composite models with DirectQuery.

AnswerC

Power Query (Get Data) provides native connectors and a rich transformation environment to merge, append, and shape datasets from disparate sources into a single, consistent model. Publishing that model once and configuring a daily scheduled refresh in the Power BI service centralizes maintenance and ensures all visuals receive updated data automatically. This approach aligns with best practices for scalable self-service BI because the refresh burden is handled by the service, not a user.

Why this answer

Power Query (Get Data) in Power BI Desktop is designed to connect to and combine data from multiple heterogeneous sources—SQL Server, Excel, and SharePoint—into a single dataset. After publishing to the Power BI service, you can configure a scheduled refresh (via an on-premises data gateway for on-premises sources) to keep the dataset up to date automatically, which is the recommended approach for recurring refreshes.

Exam trap

The trap here is that candidates often confuse composite models (DirectQuery) with import mode, thinking they can combine sources with DirectQuery and still schedule a refresh, but DirectQuery does not support scheduled refresh—it queries the source live, which is not the recommended approach for combining multiple sources into a single refreshable dataset.

How to eliminate wrong answers

Option A is wrong because manually updating separate Power BI Desktop files defeats the purpose of automation and introduces data inconsistency and extra overhead; Power BI is built for scheduled, centralized refresh. Option B is wrong because using Excel Online as an intermediary adds unnecessary complexity, potential data duplication, and a single point of failure; Power Query can directly ingest each source without an intermediate layer. Option D is wrong because composite models with DirectQuery are intended for real-time or large-scale scenarios where you need to keep data in the source, not for combining multiple sources into a single refreshable dataset; scheduled refresh is not supported with DirectQuery sources in the same way as import mode.

Full explanation →

134

Multi-Selecteasy

Which TWO of the following are valid methods to share a Power BI report with external users who do not have an internal Microsoft Entra ID account? (Select two.)

Select 2 answers

A.Publish to public web (Publish to web)

B.Deploy the report to Power BI Report Server and configure anonymous access

C.Invite them as guest users in Microsoft Entra ID (B2B)

D.Send them an email subscription with the report attached

E.Embed the report in a SharePoint Online page and share the link

AnswersB, C

Report Server can be configured for external access.

Why this answer

Deploy the report to Power BI Report Server and configure anonymous access allows external users without Microsoft Entra ID accounts to view the report via a web portal or URL. Option C is correct: Inviting them as guest users in Microsoft Entra ID (B2B) enables external users to access the report through the Power BI service using their existing email address. Option A is wrong: Publishing to public web makes the report publicly accessible to anyone on the internet, not just specific external users.

Option D is wrong: Email subscriptions send a static snapshot of the report, not an interactive experience. Option E is wrong: Embedding in SharePoint Online requires the user to have a valid Power BI license and access to the report via the Power BI service, which typically requires internal authentication.

Full explanation →

135

MCQmedium

You are developing a Power BI semantic model for an e-commerce company. The source data comes from a CSV file containing order details: OrderID, OrderDate, CustomerID, ProductID, Quantity, UnitPrice, Discount, and ShippingCost. The file is updated daily. You need to model the data to support the following analyses: 1) Total sales amount (Quantity * UnitPrice - Discount) by product and month. 2) Average shipping cost per order by customer region (CustomerRegion is in a separate table). 3) Year-over-year comparison of sales. You need to create the measures and ensure optimal performance. What should you do?

A.Use Power Query to add a calculated column for sales amount and shipping cost per order, then import.

B.Create a single table by appending the customer region to each row in the CSV using Power Query, then import.

C.Use DirectQuery on the CSV file to avoid storing data in Power BI.

D.Import both tables into Power BI, create a date table, and build measures using SUMX and time intelligence.

AnswerD

This leverages in-memory engine and efficient DAX.

Why this answer

Importing both tables into Power BI and creating a star schema with a central fact table (Orders) and dimension tables (CustomerRegion, Date) enables efficient measure calculation. The measures can use SUMX to compute sales amount (SUMX(Orders, Orders[Quantity] * Orders[UnitPrice] - Orders[Discount])) and average shipping cost per order filtered by region via relationships. A separate date table is required for time intelligence functions like SAMEPERIODLASTYEAR for year-over-year comparison.

Option A is suboptimal because adding calculated columns in Power Query increases storage and processing time; measures are preferable. Option B creates a flat denormalized table which duplicates customer region data, leading to larger model and slower performance. Option C is incorrect because DirectQuery is not supported on CSV files; Power BI requires data import or a connection to a database.

Therefore, Option D is the best approach.

Full explanation →

136

MCQhard

You are using the above KQL query as a source in Power Query for a Power BI semantic model. The query runs successfully but takes a long time to execute. You need to improve performance. What should you do?

A.Use the 'Run KQL command' option in Power Query to pass the query directly.

B.Add additional transformations in Power Query to reduce rows.

C.Enable query folding to push the query to the Kusto source.

D.Disable query folding to improve performance.

AnswerA

Using the 'Run KQL command' option in Power Query sends the Kusto query directly to the Kusto engine, which executes all filtering and aggregation server-side and returns only the final result set. This avoids pulling entire tables into the Power Query mashup engine, minimizes data transfer across the network, and lets Kusto use its native optimizations such as indexing, partitioning, and distributed execution. It is the recommended approach because compute happens at the source, not in Power Query.

Why this answer

Using the 'Run KQL command' option in Power Query sends the entire KQL query directly to Azure Data Explorer (or Kusto) for execution, allowing the Kusto engine to process and filter data at the source. This minimizes data transfer and leverages Kusto's optimized query engine, significantly improving performance compared to pulling all data into Power Query for transformation.

Exam trap

The trap here is that candidates often confuse 'query folding' (which applies to SQL-based sources like SQL Server) with the native KQL command execution in Power Query, incorrectly assuming that toggling a folding setting will push the query to Kusto when the correct approach is to use the dedicated 'Run KQL command' option.

How to eliminate wrong answers

Option B is wrong because adding additional transformations in Power Query after data is loaded does not reduce the initial data transfer; it only processes data locally, which can actually worsen performance by increasing memory and processing overhead. Option C is wrong because query folding is already implicitly enabled when using a native KQL query in Power Query; explicitly enabling it does not change behavior, and the performance gain comes from pushing the query to the source, not from a folding toggle. Option D is wrong because disabling query folding would force Power Query to pull all raw data from Kusto before applying any transformations, defeating the purpose of source-side filtering and drastically increasing load times.

Full explanation →

137

Multi-Selecthard

Which THREE factors should you consider when designing a star schema in Power BI?

Select 3 answers

A.A separate date table should be created for time intelligence.

B.Use natural keys instead of surrogate keys in dimension tables.

C.Fact tables should contain only foreign keys and numeric measures.

D.Use a snowflake schema to reduce data redundancy.

E.Dimension tables should be denormalized.

AnswersA, C, E

A dedicated date table enables time-based calculations.

Why this answer

A separate date table is required for time intelligence functions in Power BI because DAX time intelligence functions (e.g., TOTALYTD, SAMEPERIODLASTYEAR) rely on a continuous, contiguous date range with no gaps. Power BI automatically marks a table as a date table only if it contains a complete set of dates from the earliest to the latest transaction, enabling functions like DATEADD and DATESBETWEEN to work correctly across all granularities.

Exam trap

The trap here is that candidates confuse the theoretical normalization benefits of a snowflake schema (reducing redundancy) with the practical performance requirements of Power BI, where denormalization and surrogate keys are essential for optimal query execution and time intelligence calculations.

Full explanation →

138

Multi-Selectmedium

Which TWO actions can you take in Power Query Editor to improve data quality and reduce load time? (Choose two.)

Select 1 answer

A.Split columns by delimiter to create more columns.

B.Filter out empty rows.

C.Sort the data by a key column.

D.Promote headers to use the first row as column names.

E.Merge queries to combine data from multiple sources.

AnswersB

Correct. Filtering out empty rows removes unnecessary data, reducing load time and improving data quality by eliminating incomplete records.

Why this answer

Filtering out empty rows in Power Query Editor directly reduces the number of rows loaded into the data model, which decreases memory usage and improves refresh performance. This action also enhances data quality by removing incomplete records that could cause errors in measures or relationships. The other options either do not reduce load time or may increase it.

Exam trap

The trap here is that candidates often confuse actions that improve data quality (like filtering or promoting headers) with actions that merely reorganize data (like sorting or splitting), leading them to select options that do not actually reduce load time or enhance data integrity.

Full explanation →

139

MCQhard

You have a Power BI dataset that uses a DirectQuery connection to Azure Synapse Analytics. Users report that the report is slow. You need to improve query performance without changing the data source. What should you do?

A.Reduce the cardinality of calculated measures.

B.Disable row-level security (RLS) on the dataset.

C.Increase the scheduled refresh frequency.

D.Reduce the number of visuals on each report page.

AnswerD

Fewer visuals mean fewer queries to the source.

Why this answer

Reducing the number of visuals on each report page reduces the number of queries sent to the data source, improving performance for DirectQuery datasets. Option A is incorrect because reducing cardinality of measures may reduce data size but does not directly reduce the number of queries. Option B is incorrect because disabling RLS could change data access but does not significantly improve query performance.

Option C is incorrect because increasing scheduled refresh frequency applies to import mode, not DirectQuery.

Full explanation →

140

Multi-Selectmedium

Which THREE of the following are valid reasons to create a calculated table in Power BI?

Select 3 answers

A.To add a column that computes a value based on other columns in the same table.

B.To combine two tables by merging columns from one table into another.

C.To create a date table that is not available in the data source.

D.To create a disconnected table for use in what-if analysis (e.g., parameter slicers).

E.To create a summary table that pre-aggregates data for better performance.

AnswersC, D, E

CALENDAR or CALENDARAUTO can create a date table for time intelligence.

Why this answer

Calculated tables in Power BI are created using DAX and stored in memory, allowing you to generate a date table when no suitable date table exists in the data source. This is a common pattern to ensure a complete date range for time intelligence functions, as Power BI requires a continuous date table for functions like TOTALYTD or SAMEPERIODLASTYEAR.

Exam trap

The trap here is that candidates often confuse calculated tables with calculated columns or Power Query merges, thinking any table-like operation qualifies, but Power BI strictly distinguishes between row-level calculations (calculated columns) and table-level transformations (calculated tables).

Full explanation →

141

MCQmedium

You are building a Power BI data model that combines Sales data from SQL Server and Marketing data from a CSV file. The Sales table has a unique 'OrderID' column, and the Marketing table has a 'CampaignID' column. You need to create a relationship between Sales and Marketing to analyze campaign effectiveness. What should you do?

A.Use an inactive relationship between Sales and Marketing and activate it with USERELATIONSHIP in measures.

B.Create a bridge table containing unique combinations of OrderID and CampaignID.

C.Create a separate table for each campaign and relate to Sales.

D.Merge the Marketing table into the Sales table using a left outer join.

AnswerB

A bridge table is a factless fact table that stores unique combinations of OrderID and CampaignID, representing marketing touchpoints for each order. Filtering the bridge from Marketing propagates OrderIDs to Sales, while filtering from Sales propagates CampaignIDs to Marketing, preserving one-to-many relationships in both directions. This eliminates fanout and ensures sales measures are not inflated when analyzing campaigns.

Why this answer

A bridge table resolves the many-to-many relationship between Sales (OrderID) and Marketing (CampaignID). Since there is no direct key match, creating a bridge table with unique combinations of OrderID and CampaignID allows Power BI to model the relationship correctly and analyze campaign effectiveness without data duplication or ambiguity.

Exam trap

The trap here is that candidates often assume an inactive relationship or a merge can handle mismatched keys, but Power BI requires a common key column for relationships, and merging destroys the normalized model needed for accurate many-to-many analysis.

How to eliminate wrong answers

Option A is wrong because an inactive relationship requires a common key to exist between the tables; here, OrderID and CampaignID have no direct match, so an inactive relationship cannot be created. Option C is wrong because creating a separate table for each campaign would fragment the data, making it impossible to relate to Sales without a common key and violating star schema best practices. Option D is wrong because merging the Marketing table into Sales using a left outer join would create a single flat table, duplicating Sales rows for multiple campaigns and losing the ability to model the many-to-many relationship properly in Power BI.

Full explanation →

142

MCQeasy

A company has a Power BI semantic model that uses Import mode. The model contains a table with 10 million rows. The data source is a SQL Server view that takes 5 minutes to execute. The scheduled refresh is set to every hour. What is the likely impact on refresh performance?

A.Refresh will fail due to timeout on the gateway.

B.The model will automatically use incremental refresh to split the load.

C.Refresh will complete in parallel with the view execution.

D.Refresh will take at least 5 minutes plus data loading time.

AnswerD

The view execution is a fixed prerequisite for the refresh: the Power BI engine must run the source query and wait for the full 5-minute execution to complete before it receives any data to load. Only after the view returns can the data loading phase, including any transformations, indexing, and storage into the in-memory columnstore, take place. Therefore, the absolute minimum refresh duration is 5 minutes, with the actual time being that 5 minutes plus the entire downstream loading and processing time.

Why this answer

The refresh process must first execute the SQL Server view to retrieve data, which takes at least 5 minutes, and then load that data into the Import mode model. The total refresh time is the sum of the query execution time and the data loading time, so it will be at least 5 minutes plus additional time for loading 10 million rows.

Exam trap

The trap here is that candidates may assume the gateway has a default 5-minute timeout, leading them to choose Option A, but the actual default timeout is 10 minutes, and the question does not specify any custom timeout settings.

How to eliminate wrong answers

Option A is wrong because the default gateway timeout for SQL Server is 10 minutes, which is longer than the 5-minute view execution time, so a timeout is unlikely unless other factors like network latency or resource contention exist. Option B is wrong because incremental refresh is not automatic; it must be manually configured by the model designer using Power Query date-range parameters and policy settings, and it does not automatically split the load for a view that takes 5 minutes. Option C is wrong because refresh is a sequential process: the view must finish executing before any data loading can begin; there is no parallel execution between the view query and the data load in Import mode.

Full explanation →

143

MCQeasy

A Power BI developer needs to model data from two sources: an on-premises SQL Server database and a cloud-based Salesforce instance. The developer wants to create a star schema in Power BI. Which approach should the developer use to combine the data?

A.Use DirectQuery for both sources and create relationships in the model.

B.Use Power Query in Power BI Desktop to import both sources and merge/append queries as needed.

C.Use Power BI dataflows to ingest both sources and then reference them in a dataset.

D.Create a composite model using DirectQuery for SQL Server and Import for Salesforce.

AnswerB

Power Query in Power BI Desktop allows importing data from both on-premises SQL Server and cloud-based Salesforce, enabling merging/append operations to shape data into a star schema. This in-memory model supports all relationships and calculations needed.

Why this answer

Power Query in Power BI Desktop is the appropriate tool to import data from both an on-premises SQL Server database and a cloud-based Salesforce instance, allowing the developer to merge or append queries as needed to shape the data into a star schema. This approach supports combining disparate sources into a single import model, which is essential for creating a star schema with fact and dimension tables. Using Power Query ensures that all data is loaded into memory, enabling fast query performance and full modeling capabilities.

Exam trap

The trap here is that candidates may think a composite model (Option D) is the best approach for combining on-premises and cloud sources, but the question specifically asks for creating a star schema, which is most easily achieved by importing all data into a single in-memory model using Power Query, avoiding the limitations and complexity of mixed storage modes.

How to eliminate wrong answers

Option A is wrong because using DirectQuery for both sources would prevent the developer from merging or appending data at query time; DirectQuery sends queries directly to the source and does not support combining data from multiple sources in a single query unless a composite model is used, and it limits star schema design due to performance constraints. Option C is wrong because Power BI dataflows are used for data preparation and storage in the Power BI service, but they are not the primary tool for combining data within a single Power BI Desktop model; referencing dataflows in a dataset still requires import or DirectQuery, and the question asks for the approach to combine data in the model, not just ingest it. Option D is wrong because creating a composite model with DirectQuery for SQL Server and Import for Salesforce would allow combining data, but it introduces complexity with mixed storage modes, potential performance issues, and limitations on relationships (e.g., many-to-many relationships require specific configurations), and it is not the simplest or most straightforward approach for building a star schema; importing both sources is preferred for full control over data shaping.

Full explanation →

144

MCQeasy

You are importing data from a CSV file into Power BI. The file contains a column 'SalesAmount' with values like '$1,234.56'. When you load the data, the column is detected as text. What is the most efficient way to convert this column to a numeric type in Power Query?

A.Use 'Replace Values' to remove the dollar sign and comma, then change the column type to decimal.

B.Use 'Detect Data Type' and hope it automatically converts.

C.Change the column type to decimal directly and ignore errors.

D.Split the column by delimiter ',' and then convert the first part.

AnswerA

This directly cleans the text and converts to number.

Why this answer

It directly addresses the root cause: the dollar sign and comma are non-numeric characters that prevent automatic type conversion. By using 'Replace Values' to remove these characters first, you clean the data so that Power Query can then safely change the column type to decimal. This is the most efficient approach because it avoids error rows and preserves data integrity.

Exam trap

The trap here is that candidates assume Power BI's automatic data type detection or direct type conversion can handle formatted currency values, but Power Query requires explicit cleaning of non-numeric characters before conversion.

How to eliminate wrong answers

Option B is wrong because 'Detect Data Type' relies on the current column values, which still contain non-numeric characters, so it will continue to detect the column as text and not perform any conversion. Option C is wrong because changing the column type to decimal directly without cleaning will cause errors for every row containing '$' or ',', resulting in null values or query failures. Option D is wrong because splitting by comma is unnecessary and destructive; it would separate the thousands separator from the number, losing the decimal precision and requiring additional steps to recombine values.

Full explanation →

145

MCQhard

You are a Power BI developer for a financial services company. You are preparing data from multiple sources: a CSV file containing daily stock prices (ticker, date, close_price), a SQL Server database with company information (ticker, company_name, sector), and an Excel file with quarterly earnings data (ticker, quarter, earnings_per_share). The CSV file has 5 years of daily data (approx 1.3 million rows). The SQL Server table has 5000 rows. The Excel file has 20,000 rows. You need to create a data model that allows users to filter by sector, company, and date range, and to calculate moving averages of stock prices and compare earnings over time. Performance is critical. You must decide the best approach to combine and model this data. What should you do?

A.Use DirectQuery for StockPrices (CSV) and import the other tables.

B.Import only StockPrices and Company, and use the auto date/time feature; ignore Earnings data.

C.Import all tables, create a date table with CALENDAR, and establish relationships: StockPrices[Date] -> DateTable[Date], StockPrices[Ticker] -> Company[Ticker], Earnings[Ticker] -> Company[Ticker], and create a many-to-many relationship between Earnings and DateTable using a bridge table.

D.Import all tables, then in Power Query merge StockPrices with Company and Earnings into a single flat table using left outer joins.

AnswerC

Importing all tables into memory leverages Power BI's high-performance columnar compression and DAX evaluation, making queries fast even on large volumes. A dedicated date table created with CALENDAR (or CALENDARAUTO) and marked as the date table ensures accurate time intelligence and avoids the performance penalties of auto date/time. The stated relationships form a star schema: StockPrices joins to DateTable and Company, while Earnings joins to Company, creating clean filter paths. Since Earnings can have multiple records per date (across companies), a bridge table enables a many-to-many relationship between Earnings and DateTable, allowing both facts to be filtered correctly without data duplication or ambiguity.

Why this answer

Importing all tables into the in-memory VertiPaq engine ensures optimal performance for large datasets (1.3M rows) and complex calculations like moving averages. Creating a separate date table with CALENDAR enables proper time intelligence, while the bridge table resolves the many-to-many relationship between quarterly earnings and daily dates, allowing accurate filtering by sector, company, and date range without performance degradation.

Exam trap

The trap here is that candidates often choose Option D (flat table) thinking it simplifies the model, but they overlook the severe performance hit from data duplication and the inability to use star schema optimizations for time intelligence and filtering.

How to eliminate wrong answers

Option A is wrong because DirectQuery for a CSV file is not supported in Power BI; CSV files must be imported. Option B is wrong because ignoring Earnings data fails to meet the requirement of comparing earnings over time, and the auto date/time feature can degrade performance and is not recommended for large models. Option D is wrong because merging all tables into a single flat table creates a massive denormalized table (1.3M rows × multiple columns), leading to data duplication, increased storage, and slower calculations, especially for moving averages and time-based comparisons.

Full explanation →

146

Multi-Selectmedium

Which THREE are best practices for managing relationships in Power BI? (Select exactly 3.)

Select 3 answers

A.Use many-to-many relationships whenever possible to simplify the model

B.Hide foreign key columns in dimension tables to prevent misuse

C.Use single-direction cross-filtering unless bidirectional is required

D.Always set cross-filter direction to both to allow full interactivity

E.Ensure that the data types of related columns match

AnswersB, C, E

Foreign keys are not needed in report visuals and can confuse users.

Why this answer

Options B, C, and E are correct. Option B: Hiding foreign key columns in dimension tables prevents users from accidentally using them in visuals, which is a best practice. Option C: Single-direction cross-filtering is recommended to avoid ambiguity and performance issues; bidirectional should only be used when necessary.

Option E: Ensuring data types match between related columns is essential for creating valid relationships. Option A is incorrect because many-to-many relationships should be avoided when possible; they add complexity and can negatively impact performance. Option D is incorrect because setting cross-filter direction to both is not recommended by default—it can lead to ambiguous filtering and degraded performance.

Full explanation →

147

MCQeasy

You are preparing data for a Power BI report. The source data contains a column with mixed data types: some values are numbers, others are text. When loading into Power Query, the entire column is typed as text. What is the likely cause?

A.The column was imported as text because the data source is a CSV file

B.Power Query detected that the column contains text values in some rows, so it set the data type to text

C.The 'Detect data type' option was disabled in Power Query settings

D.The source data is stored as text in the database

AnswerB

Power Query's automatic data type detection examines the values in each column and, when it encounters a mixture of numeric and textual entries, it deliberately selects the text data type. This ensures every value can be represented without conversion errors, because coercing text like 'N/A' into a number would fail. In this scenario, the presence of text values in some rows explains why the column was set to text.

Why this answer

Power Query's column type detection logic examines the entire column during import. If any row contains a non-numeric value (e.g., text), Power Query defaults the entire column to text to avoid data loss or conversion errors. This is the standard behavior when mixed data types are present, regardless of the source format.

Exam trap

The trap here is that candidates assume the data source format (e.g., CSV) is the cause, but Power Query's type detection logic—not the source—is what forces the column to text when mixed data types are present.

How to eliminate wrong answers

Option A is wrong because CSV files do not inherently force a column to text; Power Query still performs type detection on CSV data, and the mixed content triggers the text fallback. Option C is wrong because the 'Detect data type' option, when disabled, would leave all columns as 'Any' type, not specifically text. Option D is wrong because even if the source database stores the column as text, Power Query would still import it as text, but the question states the column has mixed data types, implying the source itself contains both numbers and text, which is the root cause.

Full explanation →

148

MCQmedium

You are designing a Power BI model that includes a fact table with sales data and a dimension table for customers. Each customer can have multiple addresses, but you only need the primary address for analysis. The source system has a 'CustomerAddress' table with a 'IsPrimary' flag. What is the best approach to bring this into the model?

A.Use a DAX measure to filter the address table dynamically.

B.Import the entire CustomerAddress table and create an active relationship on the CustomerID column.

C.In Power Query, filter the CustomerAddress table to only include rows where IsPrimary = True, then merge with Customer.

D.Create a calculated table using SUMMARIZE to get the primary address per customer.

AnswerC

Filtering CustomerAddress to IsPrimary = True in Power Query before merging into Customer loads only one address per customer, eliminating fan-out and reducing model size. The merge creates a clean dimension table with the primary address attributes, allowing a single active relationship from the fact table to each customer's primary location. This ETL approach minimizes storage and prevents the need for complex DAX or inactive-relationship tricks.

Why this answer

It uses Power Query to filter the CustomerAddress table to only primary addresses before merging with the Customer dimension. This ensures that only the necessary rows are imported into the model, reducing data volume and avoiding complex DAX or relationship overhead. The result is a clean, single-row-per-customer dimension that directly supports analysis without runtime filtering.

Exam trap

The trap here is that candidates often choose Option B, thinking that importing the full table and using a relationship is simpler, but they overlook the need to enforce a single primary address per customer, which requires additional filtering logic that complicates the model and degrades performance.

How to eliminate wrong answers

Option A is wrong because a DAX measure cannot filter a table at the model level; it only applies dynamic filters at query time, which would not resolve the need for a single primary address per customer in the dimension table and would cause performance issues with repeated evaluation. Option B is wrong because importing the entire CustomerAddress table with an active relationship on CustomerID would create a one-to-many relationship from Customer to multiple addresses, requiring additional logic (e.g., a DAX filter or a calculated table) to isolate the primary address, which defeats the goal of a clean dimension. Option D is wrong because using SUMMARIZE to create a calculated table in DAX would work but is less efficient than Power Query filtering; it adds a calculated table to the model that is computed at refresh time and cannot leverage Power Query's native data transformation capabilities, and it may introduce subtle issues with blank rows or performance if the source table is large.

Full explanation →

149

MCQhard

A Power BI report uses a DirectQuery dataset connected to an Azure SQL Database. Users report that the report takes over 30 seconds to load. You need to improve performance without changing the data model. What should you recommend?

A.Increase the 'Maximum connections per user' setting in the Premium capacity.

B.Enable 'Reduce cardinality by using aggregation' in the dataset settings.

C.Convert the dataset to Import mode.

D.Disable 'Cross-report data binding' in the report settings.

AnswerB

Enabling 'Reduce cardinality by using aggregation' pre-aggregates data at the source, reducing the amount of data transferred and improving DirectQuery performance.

Why this answer

Enabling 'Reduce cardinality by using aggregation' can improve DirectQuery performance by pre-aggregating data. Option A is wrong because increasing the maximum connections per user might help concurrency but not single query latency. Option C is wrong because converting to Import mode changes the data model approach and is not allowed if the requirement is to not change the data model.

Option D is wrong because disabling 'Cross-report data binding' affects report interactivity but not query performance.

Full explanation →

150

Multi-Selecthard

You are preparing data from an Azure SQL Database. You need to ensure that sensitive columns (e.g., Social Security Numbers) are obfuscated in Power BI reports. Which TWO of the following approaches can you use? (Choose two.)

Select 2 answers

A.Configure dynamic data masking on the Azure SQL Database.

B.Use row-level security (RLS) in Power BI to hide sensitive columns.

C.Transform the data in Power Query by replacing sensitive values with a placeholder.

D.Use Microsoft Purview sensitivity labels to mask data.

E.Apply column-level security in Power BI Desktop.

AnswersA, C

Configuring dynamic data masking on Azure SQL Database obfuscates sensitive columns at the database engine level, applying mask functions to the result set based on the querying user's permissions. When Power BI runs a query (especially in DirectQuery or SQL passthrough), if the login lacks masking privileges, the returned data is already masked. This is a source-side defense that requires no alteration of the data model or report design.

Why this answer

Azure SQL Database Dynamic Data Masking (DDM) obfuscates sensitive data at the database query level, so when Power BI connects to the database, the masked values are automatically returned for unauthorized users. This is a server-side approach that does not require changes to the Power BI report or data model.

Exam trap

The trap here is that candidates confuse Row-Level Security (RLS) with column-level masking, not realizing that RLS only filters rows and cannot hide or obfuscate column values, while column-level security in Power BI requires Premium features and object-level security (OLS), not a standard Desktop capability.

Full explanation →

Microsoft Power BI Data Analyst PL-300 (PL-300) — Questions 76–150