Microsoft Power BI Data Analyst PL-300 (PL-300) — Questions 376–450

Selecting 'Format' > 'Capitalize Each Word' applies the M function Text.Proper to the entire column, which converts the first character of every word to uppercase and all other characters to lowercase, yielding values like 'John Smith'. In Power Query, this operates as a built-in transform that adds a new applied step for the selected column, and it recognizes spaces, punctuation, or any non-letter character as word boundaries. This is the correct choice for converting customer names from inconsistent mixed case into a readable proper-case format for a report.

In the Power BI service, workspace roles form a permission hierarchy: Admin, Member, Contributor, and Viewer. All roles except Viewer can create, edit, and publish items in the workspace; the Viewer role grants only read-only access and does not include content management rights. Because the user is assigned Viewer, the service rejects the publish attempt with an insufficient-permissions error, which directly matches the symptom.

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.

Creating a star schema by importing Sales as a fact table along with Stores, Products, and a separate date table generated via CALENDAR is the optimal design. The date table must be marked as a date table in Power BI to enable time-intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR to work predictably across fiscal and calendar periods. Relationships between Sales and the dimension tables filter facts efficiently, reduce model size through dimension normalization, and improve DAX query performance, making this the correct approach.

Replacing errors with 0 ensures that the data loads without errors and allows calculations to proceed. However, it's important to understand the cause of errors; if they are due to data quality issues, replacing with 0 might skew results. But in many cases, it's a practical way to handle errors without losing rows. This is a recommended step when errors are expected and can be safely defaulted.

DirectQuery datasets push filters to the source, and row-level security with DAX filter expressions is evaluated server-side, restricting each user to their department's rows. This satisfies the requirement that users see only data relevant to their department.

By default, slicers filter all other visuals on the page, but interactions can be modified. The table visual is likely set to 'None' for this slicer. Opening 'Edit interactions' and setting the table to be filtered will make it respond to the department selection, matching the card's behavior.

Opening the report in Power BI Desktop and refreshing the dataset forces Power Query to re-evaluate the data source schema, including renamed, removed, or added columns. After the refresh, republishing the .pbix file overwrites the dataset in the Power BI service, replacing the stale metadata and ensuring the report's visuals and calculations bind to the current schema. This is the only option that directly updates the dataset definition, not just the data values.

Selecting the 'Sales' worksheet in Navigator and clicking 'Load' directly imports only that table into Power BI, creating a single Power Query query for that worksheet. This is the most efficient path because it avoids loading unrelated worksheets, reduces memory consumption, and keeps the data model clean with only the needed fields. The Load action imports the data as a table that appears in the Fields pane, ready for use in reports, and it remains refreshable with the original workbook.

The Hour transformation from the Add Column tab creates a new column containing the hour of the day as a number from 0 to 23. This matches the requirement for an hour column and is a built-in operation. Using the Add Column tab preserves the original column and allows both derived columns to coexist.

Enabling the 'Certification' tenant setting for dataflows activates the endorsement feature that lets authorized reviewers officially certify reusable dataflows. Once certified, those dataflows are the trusted building blocks that dataset authors can be required to use, and the tenant switch is a prerequisite for applying governance policies that mandate certified dataflows. Without this setting, dataflow certification is impossible, making it the correct control for enforcing that datasets use only certified dataflows.

In a Power BI/TMSL dataset, relationships are declared as dedicated objects within the model-level "relationships" array. Each object specifies the two participating tables (e.g., Calendar and Sales), the key columns on each side, cardinality, cross-filter direction, and optional filters. Without such an object, the model has no metadata linking tables, so adding this object is the only way to establish a relationship.

In Power BI, visual interactions control whether a selected visual acts as a source filter for other visuals. If the interaction from the bar chart to the line chart is set to 'None', then selecting a bar will not propagate a filter to the line chart, leaving the trend unchanged. To make the line chart respond to region selection, the interaction must be set to 'Filter' (or 'Highlight'), which is the default for compatible visuals. This is the most likely cause of the behavior described, as the other options do not affect cross-filtering.

When you connect to a folder, Power Query's Combine Files feature treats the first file as a sample, generates a binary parse function, and applies it across all files. After combining, you typically promote the file's initial data row to headers and then use a mapping table to rename arbitrary column titles (e.g., 'Price', 'PRICE', 'pricing') to a consistent schema. This standardizes variations across workbooks and avoids duplicate or misaligned columns when loading into the data model. It also allows you to dynamically refresh as new files are added, reusing the same transformation logic.

The measure is performing aggregations at the wrong granularity; it should use SUMX to iterate over each row. Using a simple SUM for the revenue calculation multiplies the total sales by the total discount rate, rather than computing the discounted amount for each individual transaction. SUMX creates a row context, evaluates the expression for every row (e.g., Sales[SalesAmount] * (1 - Sales[Discount])), and then sums those intermediate results, ensuring accurate row-level arithmetic before aggregation.

Configuring incremental refresh on the LastModifiedDate column is the correct approach because it partitions the table by date ranges and only queries partitions that contain new or changed rows since the last refresh. Power BI stores a rolling window of historical partitions and creates new partitions for each refresh period, drastically reducing the amount of data read from the source and the time required. To implement this, you must define RangeStart and RangeEnd parameters in Power Query and set the incremental refresh policy in the dataset. This directly addresses the challenge of a 50-million-row table that needs near-real-time updates without re-loading the entire table each time.

Fill Down and Fill Up are correct null-handling techniques in Power Query. Fill Down copies the last non-null value above into subsequent null cells until another non-null value is encountered; Fill Up works in the reverse direction. This is ideal for sparse columns where nulls represent the previous known value, such as period-end totals or grouping labels, though leading or trailing nulls may remain when there is no non-null value to propagate.

Pre-aggregating in Synapse—for example via CTAS to create a month/account-type grain—shrinks the dataset from tens of billions of rows to a few thousand, so a DirectQuery connection to that aggregate answers all high-level visuals with near-instant response times. The composite model adds the original detail table (imported in a reduced form or also DirectQuery) strictly for drill-through when a user clicks a specific period/account; because drill-through filters to a single account and month first, the detail query touches a modest subset. This is the classic navigation strategy: use aggregated DirectQuery tables for high-cardinality facts, and keep fine-grained details available only when needed, which balances performance, freshness, and interactivity.

The Power BI activity log is part of the Microsoft 365 unified audit log and serves as the authoritative record for user actions such as viewing a dashboard or opening a report. This log is visible in the Microsoft 365 admin center under the audit log search, where you can filter by activities like ViewDashboard and ViewReport to see who accessed the item, when, and from which client. The audit log automatically captures the user ID, item name, and timestamp for every Power BI interaction, provided auditing is enabled in your tenant. This makes the activity log in the Microsoft 365 admin center the correct answer for auditing user access to a specific Power BI dashboard.

The 'Share content with external users' tenant setting is a Power BI admin control that, when disabled, prevents users from sharing dashboards, reports, and apps with anyone outside the organization. If this setting is turned off, the share option for external users will be unavailable or result in an error, even if the external user is already a guest in the tenant. To resolve the issue, the administrator must enable this setting in the Power BI admin portal.

SWITCH is the correct choice because it evaluates a single expression against a list of possible values and returns the corresponding result, making it ideal for creating multi-category calculated columns such as 'Low', 'Medium', or 'High'. Unlike nested IF functions, SWITCH keeps the logic linear and readable, and it can evaluate true/false conditions when used with a leading TRUE(), providing a clean row-level classification without context transition complexity.

The Phone layout view is a dedicated design canvas in Power BI Desktop where you can create a separate, mobile-optimized version of each report page. It lets you reorder, resize, and hide visuals to fit a portrait phone screen, ensuring a touch-friendly and readable experience. This is the official, supported method for tailoring reports to mobile viewing rather than relying on automatic reflow.

Live connections connect the report directly to an existing Power BI dataset or Analysis Services tabular model and expose only the model objects already defined there. Calculated columns are schema-level objects that require a storage engine and a row context to compute; the live connection provides neither, so they cannot be created inside the report. The fix is to add the column in the source semantic model and then refresh the report's metadata.

Admin can manage permissions, but it also allows viewing and editing content; there is no role that manages permissions without content access.

Alternative text for visuals in Power BI provides screen readers with a concise, meaningful description of the chart's message, not just its type. When you set alt text on a visual, it should convey the key insight or conclusion so users with visual impairments can understand the report without seeing the graphic. This is a WCAG 2.1 success criterion and a best practice because it makes the report's narrative accessible to all users, and it also helps when the report is exported or embedded where the visual itself might not render.

To apply sensitivity labels, a user's identity must have the appropriate rights defined in the label itself via Azure Information Protection (now Microsoft Purview Information Protection). This is usually accomplished by adding the user to the label's 'Viewer' or 'Editor' permission list, which grants them the right to see and modify content protected by that label. Without these rights, the label will appear in the picker but the user will receive an error when trying to apply it, because the label's encryption policy forbids them from doing so. Delegating these rights to individual users or groups is an explicit configuration action that must be performed outside of Power BI.

Removing unnecessary columns from the fact table is correct because DirectQuery operates by pushing queries back to the source, and every extraneous column widens the SELECT statement, increasing network transfer and source-side processing. A narrower fact table means fewer columns are scanned and materialized for each visual interaction, which directly reduces query latency and memory overhead. This is a standard column-pruning practice for DirectQuery performance tuning.

Setting the incremental refresh policy in the dataset settings is the central action because it defines the refresh window (e.g., how many days of data to refresh), the date column used for filtering, and options like retaining historical data. This policy tells Power BI to create time-bounded partitions for the tables and determines which partitions are refreshed in a given run. Without this policy, Power Query parameters alone cannot trigger incremental refresh.

In a one-to-many relationship between Products and Sales, every ProductID in Sales must have a matching ProductID in Products for the related attribute (e.g., Category) to be populated. If Sales contains ProductID values not present in Products, those rows participate in the relationship as unmatched orphans, so the lookup column from Products is blank. As a result, any visual that slices or groups by Category shows a blank bucket that aggregates the sales from those orphaned ProductID values.

A DLP policy must be created in Microsoft Purview and scoped to the Power BI workload to monitor and protect Power BI content. This is the foundational step to enable detection and enforcement for Power BI items. Without this policy, no DLP rules will apply to Power BI.

In Power BI Desktop, selecting the table in Model view opens the Properties pane, where the Incremental refresh control lets you define the archive period, the incremental period, and the RangeStart/RangeEnd parameters. This policy is saved into the data model and, after publishing, the Power BI service uses it to create date-based partitions and refresh only the partitions that fall within the incremental window. This is the intended, supported way to establish incremental refresh.

Append Queries is the Power Query operation that stacks the rows of one query or table below those of another, requiring both tables to have the same or compatible column structures. It performs a vertical concatenation equivalent to SQL UNION, adding the entire set of rows from the second query to the first query's existing data. This exactly satisfies the requirement to consolidate rows from an Excel workbook into a single table.

SWITCH(TRUE(), MONTH(Sales[Date]) >= 7, YEAR(Sales[Date]), YEAR(Sales[Date]) - 1) evaluates boolean conditions sequentially; for any date with month 7 or later it returns the current calendar year, which equals the fiscal year for a July-start fiscal year. For months January through June, it subtracts one, correctly assigning those dates to the prior fiscal year. This expression returns an integer, preserving numeric sorting and supporting direct use in relationships or calculated columns.

Correct. In a composite model, the large fact table typically remains in DirectQuery to avoid memory pressure, but measures that iterate row-by-row (e.g., SUMX, or time-intelligence functions that scan a period) will force the DAX engine to issue multiple or very broad SQL queries against the remote source. Each iteration may expand into a separate query or pull large result sets, causing severe latency and poor report responsiveness. Time intelligence that needs to re-evaluate the fact table for each date context is especially costly and should be avoided in DirectQuery-heavy models.

DirectQuery for the SQL Server table is correct because it keeps the 10-million-row table out of the model's memory, avoiding a long and costly import/refresh; queries are pushed to SQL Server at report time. The SharePoint list, which is small, is well-suited to Import mode because SharePoint Online does not support DirectQuery, and importing it enables fast, in-memory performance and full modeling capabilities like calculated columns and relationships.

Using a drill-down hierarchy is correct because it structures the matrix fields into expandable levels, so Power BI only loads and renders the top-level rows for the initial view. When a user expands a level, the next level is queried and displayed, which dramatically reduces the initial data load and rendering time. This approach also improves user experience by presenting a summary first and enabling context-specific exploration, while leveraging the matrix visual's native expand/collapse capability to minimize the memory and query footprint.

A box and whisker plot (box plot) summarizes a distribution using the five-number summary: minimum, first quartile, median, third quartile, and maximum, with whiskers indicating variability outside the upper and lower quartiles. This makes it excellent for showing the spread, central tendency, skewness, and potential outliers of a dataset at a glance. In Power BI, a box plot visual is available and is well-suited for distribution analysis.

Placing both Year and Quarter in the Rows field well creates a hierarchy in the matrix. By default, the matrix allows users to expand and collapse levels, so they can drill down from year to quarter. This achieves the requirement without additional configuration.

'Embed for your customers' uses an app-owns-data model, issuing an embed token so external users authenticate against your application rather than Microsoft Entra ID. This satisfies the stem's constraint that recipients lack a Power BI Pro licence, since consuming embedded analytics requires no per-user Power BI licence.

DATESINPERIOD returns a table of contiguous dates starting from a specified start date and extending for a given number of intervals (months, quarters, or years), either forward or backward. For a rolling 12-month calculation, you can use CALCULATE(SUM(Sales[Amount]), DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH)) to include exactly the trailing 12 months up to and including the current maximum date. This function is purpose-built for arbitrary date ranges anchored on a single date, making it the correct choice for this scenario.

Changing the column data type to Date in the Power Query Editor is the correct and most efficient approach because it leverages Power Query's built-in type conversion system, which parses the text representation into a true date value using the designated locale and format. To do this, you select the column, go to the Transform tab (or Home tab), and choose Data Type > Date; Power Query automatically inserts a 'Changed Type' step that records this transformation. This method is straightforward, requires no custom code, and ensures the data is correctly typed for all downstream operations like modeling, DAX calculations, and visual date hierarchies.

The 'Orders' table has a column designated as a key (likely OrderID) that must contain unique values for the model's relationships to work. When the refresh tries to load data, the VertiPaq engine checks uniqueness on that key; duplicate OrderID values in the source violate that constraint, causing the refresh to fail with a duplicate key error. This often happens when the source view or query returns repeated rows due to joins or missing DISTINCT, and it is the direct and primary cause of the reported error.

The on-premises SQL Server is a data source that lives behind your corporate firewall, so the Power BI service uses an on-premises data gateway in standard mode to connect securely. After installing the gateway and adding the data source with the appropriate credentials, you assign it to the dataset and then configure a scheduled refresh in the dataset settings. This enables Power BI to query the SQL Server and the SharePoint CSV on a recurring basis.

Descriptive alt text must explain what a visual shows—its trend, key takeaway, or data story—rather than simply naming the object. For non-decorative visuals, screen readers rely on this text as an equally effective alternative, so include the insight a sighted user would derive from the chart.

Object-level security (OLS) lets a modeler define roles that remove specific tables or columns from a user's view by setting their object permissions to None. In Power BI, OLS is implemented in the role editor (or via XMLA endpoints) where you can deny access to a column while leaving the rest of the model accessible. Since the requirement is to hide a sensitive column while allowing the rest of the dataset to remain usable, OLS is the correct mechanism.

Defining aggregations in Power BI on the DirectQuery tables is correct because Power BI can automatically redirect queries to a cached aggregation table when the query grain matches, drastically reducing load on the source and improving response times. This approach maintains the DirectQuery storage mode for detail-level queries, so users still see fresh data without a full import, and it leverages Power BI's optimization engine rather than requiring source-side changes.

A workspace app distributes the dashboard to many users at once, satisfying the large-group requirement efficiently. Viewer access grants read-only interaction, preventing edits, while app audiences cannot reshare content. This combination meets both constraints without per-user sharing overhead.

A 100% stacked bar chart is ideal for comparing proportions because each bar is scaled to the same total height (100%), so the length of each segment directly represents the percentage contribution of that category relative to the whole. This allows you to visually compare the relative distribution of parts across multiple groups on a common scale. The category axis segments are shaded distinctly, making it easy to see how the share of a particular component changes across different bars or time periods.

Drill-down mode lets users navigate the matrix hierarchy by expanding and collapsing category levels, satisfying the requirement to reveal lower-level detail on demand. Enabling it on the visual's axis fields gives the interactive expand/collapse behaviour users need.

Setting a default sensitivity label in the workspace settings is the correct, administrator-controlled method. Any new report created in that workspace inherits this label automatically, and users can still change it if they have appropriate permissions. This meets the requirement without relying on user discipline.

Power BI Pro is the correct minimum license because scheduled refresh in the Power BI service is a Pro-only feature. With a Pro license, you can create datasets, publish them to shared capacity, and configure a refresh schedule without needing Premium capacity or Premium Per User. A Pro license is sufficient for the standard, recurring data refresh scenario in shared capacity.

The dedicated mobile layout lets you redesign the page for a portrait phone screen, where you can move, resize, and hide visuals to match touch ergonomics. Replacing the scatter plot with a line chart — which has large, easily tappable categorical or time-axis points and a more natural sweep for small screens — converts a dense, drag-to-select interaction into a simple tap-to-view-measure interaction, aligning with the intended mobile consumption of the report.

ALL('Date') strips the date filter context from the Date table, so the measure aggregates every row regardless of slicer or axis selection. Removing that filter is precisely what makes the result show total sales for all dates instead of accumulating.

Enabling the 'Refresh conflict detection' setting on the dataset is the native solution for this scenario. In Power BI Premium, this setting detects when a second refresh request arrives while a refresh is already executing, and it blocks the conflicting request instead of allowing them to overlap. This eliminates the 'refresh already in progress' error while keeping both the scheduled and XMLA refresh workflows intact.

This is correct because the dimension table Patients should filter the fact table Visits. A one-to-many relationship from the dimension to the fact table with a single filter direction is the standard star schema design. It ensures that filters on patient demographics propagate to Visits, and it avoids ambiguity and performance issues associated with bidirectional filtering.

Field parameters allow users to dynamically select which fields or measures are displayed in a visual by using a slicer. This enables a single visual to change its measure based on user selection, providing a flexible and interactive experience. Field parameters are created in the modeling tab and can include measures, columns, or a combination.

Contributor allows creating, editing, and deleting content within the workspace, but does not permit adding or removing members or changing workspace settings. This matches the analyst's needs exactly. It grants the necessary publishing and editing rights without the administrative capabilities that must be withheld.

Indexing speeds up query execution.

Aggregations create a cached, in-memory summary table that DirectQuery queries hit first, so most report visuals avoid round trips to SQL Server. This satisfies the constraint of improving performance without altering the data source, since the detail tables and DirectQuery connection remain unchanged.

SAMEPERIODLASTYEAR works by shifting the current filter context back exactly one year and returning the equivalent set of dates. If the Date table does not contain those dates—for example, the table starts at January 2022 while you are filtering on 2023, so no 2022 dates are present—the function returns an empty set of dates. When a measure receives an empty date range, it evaluates to BLANK, which then propagates through any calculations like DIVIDE. To fix this, the Date table must span a contiguous range that includes all years present in the fact data, including the year being compared.

A separate date table created in Power Query is the recommended way to handle fiscal-year reporting because it provides a clean, continuously dated calendar dimension that can be marked as the date table in your model. You can add a fiscal year column with a simple conditional formula that adjusts the calendar year based on your fiscal year start month. This supports DAX time intelligence functions like TOTALYTD and DATEADD, and keeps the date dimension independent from fact tables.

High-contrast color themes in Power BI ensure that foreground data elements and backgrounds meet WCAG 2.1 contrast ratios (at least 4.5:1 for text, 3:1 for graphical objects). This directly supports users with low vision or color vision deficiencies, as it makes visual distinctions perceivable without relying on color alone. Power BI's built-in accessible color palettes, such as the High Contrast White/Black themes, automatically adjust color pairs to maintain these ratios.

The slicer is a dedicated on-page filter control that lets users choose region values; it directly cross-filters all other visuals on the report page, updating the sales numbers in real time based on the selection. Unlike drillthrough, it does not navigate away, and unlike a matrix's expansion, it actively filters the entire page's visuals. It is the correct answer because it provides interactive, immediate page-level filtering without altering the report layout.

DISTINCTCOUNT(Sales[ProductID]) evaluates the ProductID column within the current filter context and returns the number of unique, non-blank values. It automatically removes duplicates and ignores blanks, providing the exact cardinality of products sold. This is the standard DAX function for 'count of distinct instances' in scenarios like this, and it respects the existing relationship filtering from related tables.

A star schema with a sales fact table at the center and separate date and product dimension tables around it is the optimal relational design for this reporting scenario. The fact table stores quantitative measures (e.g., sales amount, quantity) and foreign keys to each dimension, creating one-to-many relationships that let Power BI filter and group by date or product efficiently. This design avoids data redundancy, supports correct grain granularity, and ensures the model can answer time-based and product-based questions independently or together. It also aligns with Power BI's VertiPaq columnar storage, which compresses dimensions well and speeds up aggregation.

The customer-managed key must exist in Azure Key Vault before Power BI can reference it. Uploading the key establishes the vault-held asymmetric key that Power BI later wraps around the dataset encryption key, which is the prerequisite step enabling BYOK rather than Microsoft-managed encryption.

Append Queries in Power Query is specifically designed to combine two or more tables by stacking their rows one after another, aligning columns by name. When you have sales data from two sources with the same structure, appending creates a single table containing all records from both sources, which is exactly what the scenario requires. It runs at data refresh time in Power Query, ensuring the combined data is loaded efficiently into the Power BI data model.

A Power Query blank query using List.Dates generates a contiguous calendar covering 2010 to 2030, and adding Year and Month columns supports the star schema. Marking it as the date table enables time intelligence, and it refreshes through the existing on-premises gateway.

Incorrect or expired data source credentials are the most frequent cause of a Power BI scheduled refresh failure. When you configure a dataset for refresh, Power BI stores the authentication values—Windows credentials, database passwords, or OAuth tokens—and if those are changed or time out, the service cannot authenticate to the source and the refresh operation fails with an error. Updating the credentials in the dataset settings under 'Edit credentials' resolves the issue, and you should verify that the account still has the same permissions.

The Analytics pane is the dedicated location in Power BI for adding statistical and analytical enhancements to a visual, such as constant lines, trend lines, and forecasting. When a line chart is selected, the Forecast option appears here, letting you extend the existing series into future periods using an exponential smoothing algorithm. This is the only pane that supports generating a forecast, making it the correct choice.

Integer keys improve join performance.

SUMMARIZE is inherently less efficient when adding measure columns, because it groups the table and evaluates extended columns in one pass, which can force multiple scans of the same data and may even produce unexpected results when extended columns are used. SUMMARIZECOLUMNS, by contrast, is optimized specifically for grouping with measures; it leverages a pre-grouping phase and enables the engine to push aggregations down to the storage engine, improving both memory usage and query time. Additionally, SUMMARIZE is considered a legacy function for this purpose, and SUMMARIZECOLUMNS is the recommended replacement in DAX.

Creating row-level security (RLS) roles with a DAX filter on the Diagnosis field dynamically restricts which rows each user or role can view. This ensures that a user assigned to a specific role only sees patient records matching authorized diagnoses, directly preventing unauthorized data exposure at the row level. RLS is evaluated at query time in the Power BI service, making it a robust access-control mechanism that works regardless of how the report is accessed.

Several AI visuals enforce hard limits on the volume of data they can process to keep the machine-learning calculations responsive. For example, the Key Influencers visual supports up to 100,000 data points (or distinct values of the analyzed field), and beyond that threshold it may return an error or require you to aggregate or filter the data. Similarly, the Decomposition Tree can become unwieldy with extremely high-cardinality dimensions, although it does not always fail hard. These limits matter for modeling and row-level visualization design, especially when working with wide or high-volume fact tables.