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

Applying a filter in Power Query before the data is loaded reduces the number of rows that are imported into the data model. For OData sources, the filter can often be folded into the native query sent to the server, so only matching rows traverse the network and are stored in VertiPaq. This lowers memory usage, improves refresh time, and shrinks the model footprint — the correct way to reduce data volume.

Expanding the column that holds the nested array (often a List or Record type) will create a new row for each element in the array, effectively flattening the data. This is the standard way to unnest arrays in Power Query. After expansion, related order information is repeated for each detail row, which may require further shaping.

A slicer for category establishes a filter context that the visual's Top N filter respects. When you apply a visual-level Top N filter on the product field (sorted by a measure like Total Sales) and set N=5, the visual re-evaluates the top five products *within the currently selected category*. This works because Top N is applied after slicer filtering in the query pipeline, making the ranking dynamic and context-aware, which is exactly what the user needs.

The column profile pane in Power Query Editor displays a range of statistics and data type indicators, making mismatches visually apparent—for example, a numeric column containing text values will show a 'Text' data type badge or error entries. Recognizing these issues is a primary objective of profiling so you can convert or correct types before loading. This is a capability of profiling, not a step you execute on data.

Measures are evaluated within the current filter context at query time, so they compute values dynamically and consume no physical memory for storage beyond their definition. Calculated columns, on the other hand, are computed during data refresh and stored in the model, increasing model size and refresh time. By implementing business logic in measures, you gain greater flexibility—logic can change without triggering a full refresh, and the same measure can respond differently based on how users filter or slice data, which is the intended DAX pattern.

This is the correct pattern for a dynamic 30-day churn count: CALCULATE modifies the filter context to filter rows meeting the condition, COUNTROWS counts rows in the churn fact table, and DATESINPERIOD generates a contiguous date range from the max visible date going back 30 days. Unlike a column, this measure is evaluated at query time, so it automatically respects report-level slicers, page filters, and drill-downs, and requires no storage overhead. The key is that DATESINPERIOD works only with a properly related date table.

Adding a bridge table with composite keys decomposes the many-to-many relationship into two separate one-to-many relationships: each original table relates to the bridge table on its respective key, and the bridge table stores only valid combinations. This makes the relationship graph acyclic and unambiguous, so filter propagation follows clear paths from one dimension through the bridge to the other dimension without double-counting. Composite keys in the bridge ensure that the join preserves the exact pairing of rows, which is the standard star-schema pattern for resolving many-to-many cardinality in Power BI.

Workspace roles follow a permission hierarchy, and only the Admin role includes the ability to add, remove, or change the roles of other workspace members. Since User2 is not an Admin, they cannot add a new user regardless of which non-Admin role they hold. This is why the correct answer is 'No' — the action requires Admin privileges, which User2 does not possess.

Parquet is a columnar storage format with built-in compression and predicate pushdown, meaning Power Query can read only the specific columns and row groups required instead of scanning the entire 10 GB text file. This dramatically reduces the amount of I/O and memory consumed during the Data Refresh, and also preserves the schema and data types natively, eliminating costly type-inference and parsing steps. By shifting to Parquet, you effectively move the heavy lifting to the storage engine, which is far more efficient for analytical workloads.

Writing a SQL query with a WHERE clause directly in Power Query Editor is the most efficient approach because it implements query folding—SQL Server executes the filter and returns only the rows that satisfy the predicate. This minimizes data transfer, avoids loading irrelevant rows into Power Query memory, and speeds up both the initial load and subsequent refreshes. When the source is a relational database, a native SQL query with a filtered result set is a best practice for row-level reduction at the source.

The Trim transformation removes all leading and trailing whitespace from text values. Applying it to CustomerName cleans the data without altering internal spaces, which is exactly what is needed. This transformation is available in the Power Query Editor under the Transform tab and is applied to the selected column.

Removing specific characters is a direct and efficient method; however, a more robust approach is to use Text.Select to keep only digits and the decimal point, but Replace Values is simplest given the known characters.

Dataset parameters, such as server name or database name, can be overridden per stage using deployment pipeline rules. If no rule is configured for the relevant parameter, Test inherits the parameter values from Development, causing the dataset to keep pointing to the Dev data source. Creating a parameter rule that assigns the Test SQL server/database value for that parameter is the required fix. Without this rule, the pipeline will successfully deploy the dataset but still reference the wrong environment.

A Conditional Access policy in Microsoft Entra ID is the correct mechanism to block Power BI from non-compliant devices. The policy can require a device to be marked as 'Compliant' by Microsoft Intune before allowing access; this evaluation happens at sign-in time using signals from Intune. For example, the grant control 'Require device to be marked as compliant' combined with the Power BI cloud app will deny access to devices that do not meet your organization's compliance policies. This is the standard and supported way to enforce device-based access control for Power BI.

Whole Number, which uses the 64-bit integer data type, is the optimal choice for a product ID key column. Integers provide exact, unambiguous equality comparison, allow the most efficient indexing and hashing, and compress extremely well in columnar storage, resulting in faster joins, filters, and relationships. Since the ProductID contains only integer values, Whole Number perfectly represents the domain without unnecessary conversion or storage overhead.

The on-premises data gateway stores the SQL Server credentials that the Power BI service uses during refresh. If the SQL Server password was changed or the account expired, the gateway can no longer authenticate to the database, and the refresh fails with a 'The credentials stored in the gateway are invalid' error. Re-validating and updating the credentials in the gateway data source settings resolves this issue, making it the correct root cause.

Drill-down is the native hierarchy navigation feature in Power BI visuals such as bar charts, column charts, line charts, and matrixes. When a visual has multiple fields in its axis or rows, the drill-down icons (double down arrow for next level, double up arrow for parent level) appear in the visual's upper-right corner, allowing users to expand one level at a time or return to a previous level with a back button. This behavior is specifically designed for hierarchical exploration, enabling users to start with a high-level summary and progressively drill to granular detail while staying within the same visual, exactly matching the report requirement.

Aggregations reduce the amount of data queried from the source.

Changing the data type of the OrderDate column to 'Date' in Power Query is the most efficient solution because it performs the conversion directly on the existing column during data load, using Power Query's native type system. This eliminates the time portion without adding a new column or increasing model size, and it ensures the column is properly date-typed for all downstream reports. The statement about being less explicit is misleading; altering the data type is the standard approach when you need to discard the time component.

Configuring an incremental refresh policy is the correct approach because it automatically creates and manages partitions based on a date range, typically using RangeStart and RangeEnd parameters. During each refresh, only the data that has changed or is new within the sliding window is processed, while historical partitions remain untouched, significantly reducing refresh time and resource consumption. This also enables query pruning in the Power BI service, as only relevant partitions are scanned when building visuals, making it the most efficient way to optimize refresh performance for large fact tables.

CALENDAR is the correct DAX function because it explicitly returns a single-column table containing a continuous series of all dates from a specified start date to an end date, inclusive. This generated table can be used directly as a date table or extended with calculated columns for year, month, quarter, and other date attributes. It requires two date arguments, typically defined as DATE literals or scalar expressions that resolve to dates.

This is a key feature of composite models.

Each visual on a Power BI page issues its own DAX query when the page loads. Rendering many visuals simultaneously consumes CPU, GPU, and memory, and also necessitates cross-visual interaction filters. Reducing the number of visuals per page lowers the number of parallel queries and the rendering workload, improving page load and interaction responsiveness. This is a classic report-level optimization enabled by better layout and navigation.

Slicers place interactive filter controls directly on the canvas for date, product, and region, letting users slice the same visuals across all three dimensions simultaneously. This satisfies the requirement to analyse sales data by multiple dimensions without duplicating pages or reports.

AppSource is Microsoft's official marketplace for Power BI custom visuals, where visuals are published as .pbiviz files after validation and, if certified, a thorough code review. Downloading an AppSource visual and importing it into Power BI Desktop adds a fully integrated custom visual to the visualizations pane, supporting data binding, formatting, tooltips, and report sharing. This is the correct way to obtain a visual that is not available in the built-in gallery, provided the visual is compatible with your Power BI version and tenant policy allows non-certified visuals if you choose one without certification.

This is the correct approach because a hierarchy created in Model view is a model-level object that explicitly defines parent-child relationships among the columns. Right-clicking Category and selecting 'Create hierarchy' places Category as the top level, after which you add Subcategory and ProductName as child levels, preserving each column's own granularity. A true hierarchy enables drill-down in visuals by expanding one level to the next, and it also supports functions like ISINSCOPE in DAX for level-aware calculations.

The Power BI Map visual relies on Bing Maps to geocode location values, and it can only do that if each geographic field has the correct Data Category set in the Column tools (e.g., Country, State, City). If the field is left as 'Uncategorized', Bing may interpret the values as text or fail to resolve them, so entire countries can be omitted. To fix this, select the field in the Fields pane, go to the Column tools tab, and set the Data Category accordingly. This is the most common reason a country that exists in your data does not appear on the map.

Append Queries is the correct operation. It stacks the rows from the second table beneath the rows from the first table, effectively performing a union of the data when both tables have the same columns.

Assigning correct data types (such as integer, decimal, and date) rather than generic text optimizes the compression algorithms in Power BI, leading to smaller model sizes and faster scan performance. It also ensures that DAX functions like SUM and DATE arithmetic behave predictably, and prevents implicit type conversions that can introduce errors or degrade performance.

Publishing requires the Contributor role, which grants content creation and editing rights that Viewer lacks. Since the security group holds only Viewer, the user inherits insufficient permissions. Assigning Contributor directly, or elevating the group's role, satisfies the workspace role constraint and resolves the error.

This is the correct star schema design. The Customers table is a dimension with a unique CustomerID per row, while Orders is a fact table that can contain many rows per CustomerID. A one-to-many relationship from Customers to Orders lets filters applied to customers (e.g., region, segment) automatically propagate to their orders in visualizations and measures. The single cross-filter direction ensures one-way filtering from the dimension to the fact, which is the standard, repeatable pattern that avoids ambiguity and keeps DAX calculations predictable.

Marking unused columns as hidden simplifies the report field list and keeps users focused on the required metrics, which lowers the apparent model complexity and reduces the risk of building visuals on irrelevant data. Because hidden columns remain resident in the Tabular engine unless physically removed, this practice works hand-in-hand with deleting never-used columns to actually shrink the model and speed up refresh. It is a core modeling hygiene step that also makes column-level security and role definitions easier to audit.

Placing OrderDate as a hierarchy on the X-axis lets users drill down or up between year, quarter, month, and day using the visual's drill controls. A slicer on ProductCategory provides an interactive filter. Together they satisfy the requirement without editing the report, and both are standard Power BI interactive features.

Because Patients[PatientID] is unique and Visits[PatientID] repeats, the correct cardinality is one-to-many from Patients to Visits. This relationship ensures that filtering Patients by PrimaryCareProviderID propagates to Visits, enabling accurate aggregation of ChargeAmount. It also avoids ambiguity and supports efficient query performance, as the one side acts as the lookup dimension.

This is the correct answer because it uses a single SUMX iterator over a FILTERed table, where the filter expression evaluates both conditions in one row context. Inside the filter, `CALCULATE(COUNTROWS(Orders), ALLEXCEPT(Orders, Orders[CustomerID]))` correctly counts all rows for the same customer, avoiding any interference from the date filter on the outer row, and the AND ensures only customers with more than five orders and a recent order date are included. SUMX then sums the TotalAmount across those qualifying rows, directly matching the requirement while remaining efficient and maintainable.

Performance Analyzer in Power BI Desktop is the intended diagnostic tool because it records a chronological breakdown of operations for each visual, including DAX query time, visual display time, and other report-level processes. This lets you identify whether the slowdown comes from the composite model's query engine when combining imported and DirectQuery tables or from rendering. The results can be exported to JSON for further investigation, making it the definitive answer for visual-level performance analysis.

Append Queries combines multiple tables by vertically stacking all rows from each input into a single resultant table, which is essential when consolidating datasets that share a common schema—like monthly sales exports from different regions. It aligns columns by name and fills missing values with null for any mismatched columns, making it a true row-level combination method. This is one of the two valid ways to physically combine data in Power Query.

A combo chart with clustered columns and a line is ideal because Power BI's Analytics pane allows adding an average line directly to the line component, or you can create a constant line measure for the Y-axis. Each salesperson's revenue is represented as a discrete column, while the continuous average line runs horizontally across all categories, making above/below status obvious. The dual structure aligns the column heights with the line's position, giving a clear, unambiguous comparison that bar, waterfall, and table visuals cannot achieve.

By creating a new Power Query query that executes the view's SQL statement directly against the source database, you bypass any existing transformations and fetch the exact rows and columns the view returns. This gives you an independent, unfiltered look at the view's output, allowing you to compare it against what the dataset actually uses. This is the only method listed that reliably exposes the raw view result set.

Reducing data volume improves load time.

The user effectively sees rows where Department = 'HR' OR Department = 'Executive'. Because the HR Manager role allows HR records and the Executive role allows Executive records, membership in both grants access to the union of those two sets. This is the expected additive behavior of multiple RLS roles in Power BI, where each role adds its permitted rows to the user's overall view.

The comma is a thousands separator, not a decimal point, so removing it yields a clean numeric string that Power Query can cast to Decimal Number. Direct type conversion would fail or produce errors on the comma-formatted text.

TOTALYTD(SUM(Sales[Amount]), 'Date'[Date]) is the correct time intelligence function because it evaluates the sum of Amount for dates from the start of the year up to the last date in the current filter context. It respects the Date table's continuous date range and requires a proper relationship between the Date and Sales tables. This yields a cumulative year-to-date total that updates dynamically as the user browses different dates.

Registering the Power BI tenant as a data source in Microsoft Purview is the essential first step because it establishes a connection from Purview Data Map to your organization's Power BI environment. Once registered, Purview automatically scans and catalogs metadata from all workspaces, datasets, dataflows, reports, and dashboards without requiring manual asset entry. This registration typically requires a Power BI administrator role so Purview can authenticate and retrieve the metadata.

The JSON explicitly declares "privacyLevel": "Private", so the correct answer is Private. This level tells Power Query that the source contains sensitive or confidential data, causing it to partition and rewrite queries so that no rows or columns from this source are mixed into other data sources without permission. It is the most restrictive privacy level and exactly matches the token present in the configuration file, making it the only factually defensible choice.

Build permission specifically grants the right to connect to the dataset as a source for new reports and to create new visualizations in the Power BI service or Power BI Desktop. It is the minimal permission because it satisfies the user's need to author new sales reports without exposing write capabilities or allowing resharing. With Build, the user can create and save new reports, but cannot modify the dataset or grant access to others.

Report interactivity and performance needs are critical because Import mode pre-loads data into memory via VertiPaq, enabling sub-second slicer and cross-filter responses. DirectQuery issues live queries to the source system, so performance depends on the source database's load, indexing, and connectivity speed, which can cause visible delays. For highly interactive dashboards, import or a composite model with aggregated import tables is usually superior.

A Power BI KPI visual compares a DAX measure against a defined goal (e.g., a target value in a separate measures table), giving executives an instant signal of whether strategic objectives are on track. Because every visual on an executive dashboard competes for attention, choosing only KPIs that tie directly to business goals ensures the dashboard drives decisions rather than merely displaying data. This avoids the trap of including vanity metrics that look interesting but are not actionable in the context of the company's strategy.

DATEADD shifts a date column by a specified interval, returning dates offset by days, months, quarters or years. It is a genuine time-intelligence function, satisfying the stem because it operates on dates rather than aggregating values like SUM or COUNT.

Time intelligence DAX functions, such as TOTALYTD and SAMEPERIODLASTYEAR, require a separate date table that has been marked as a date table in the model. This explicitly signals to Power BI which column holds the continuous date range, enabling proper year, quarter, month, and day filtering. Without this designated date table, time intelligence calculations will not function correctly, regardless of other model settings.

This is the correct calculated column expression because it uses direct column references in row context, so for each order row it computes Quantity multiplied by UnitPrice, then multiplies by (1 - Discount) to apply the discount proportionally. The calculation respects the percentage nature of the discount and yields the net amount for that individual line item. The result is evaluated row by row and stored physically in the table, making it available for use as a column in reports.

Marking the Date table as a date table using the Calendar icon in the Table tools ribbon is the correct approach because it explicitly identifies the Date column as the continuous set of dates that Power BI uses to enable time intelligence functions like DATESYTD, TOTALYTD, and SAMEPERIODLASTYEAR. Setting a relationship on the Date column—which is unique and contiguous—ensures proper filtering from the date dimension to the fact table, following the star schema design principle. This allows DAX calculations to correctly respect the user's selected date range and fiscal calendar, making it the only option that fully supports robust time-based reporting.

The Analytics pane adds trend lines, forecasting, and other statistical overlays to an existing visual. For a line chart of monthly sales, it inserts the trend line directly, showing overall direction without altering the underlying data model.

Azure SQL Database is a first-party relational database service in Microsoft Azure with a native Power BI connector. It supports both Import mode and DirectQuery mode through the SQL Server or Azure SQL Database connector, and it authenticates with SQL credentials or Azure Active Directory. Because it is an officially maintained connector, it qualifies as a valid data source type in Power BI.

'Export data' in the Power BI admin tenant settings controls whether users can extract the underlying dataset from a visual, such as copying values to the clipboard, downloading a .csv file, or exporting summary data to Excel. This is the only option listed that reveals the actual numbers and granular row-level or aggregated fields behind a report. Disabling this setting prevents raw data extraction through the service, though visual snapshots in other export formats may still leak information visually.

Creating a relationship from Sales to Product on ProductID and using Product[Category] inside the measure is the correct star-schema design. When the measure evaluates, category filters from the Product table propagate down the one-to-many relationship to Sales, and PowerPoint BI's in-memory engine pushes that filtering efficiently to the compressed fact table. This preserves the 50-million-row fact table at its lowest grain while allowing any product attribute to be used in measures without extra storage or materialization, making it ideal for performance and maintainability.

The root cause is that SAMEPERIODLASTYEAR requires a contiguous sequence of dates in the Calendar table to determine the prior-year period. If the Calendar table has gaps — for example, missing weekends, holidays, or incomplete year boundaries — the function cannot compute a continuous shift and returns BLANK for those months. Time-intelligence functions in DAX depend on a complete, continuous date dimension; any missing date breaks the comparison. Marking the table as a date table does not compensate for missing rows; the table must actually contain every date in the range.

This expression is syntactically and functionally identical to the other correct option, and its presence as a duplicate answer choice is a common exam design to test your ability to recognize a valid pattern when it appears more than once. The DATEDIFF function with DAY computes the number of day boundaries between the two dates, returning an integer that does not depend on any time portion, so the filter condition in CALCULATE correctly identifies all sales where the ship-date-to-order-date span is exactly seven days or less. Since CALCULATE applies its filter arguments as a row-level condition over the current filter context, the SUM of Revenue is computed only for that subset, yielding the desired total. Recognizing that this version is correct, despite being repeated, reinforces the key rule: use DATEDIFF for date-difference comparisons, not arithmetic subtraction on datetime columns.

This is the direct reason for the error: when a value in the first table's join column is not present in the second table's join column, the merge operation cannot find a matching row for that key. If the join kind requires a match or you are using a lookup-style operation, Power Query raises 'Expression.Error: The key didn't match any rows' instead of silently inserting nulls.

Pie charts can display either percentages or actual values as data labels. By changing the detail labels to show 'Value', the chart will display the number of sessions for each slice. The slices still represent proportions, but the labels show the absolute numbers, satisfying the stakeholder's request.

This is the correct design: Power BI time intelligence functions (e.g., DATESYTD, DATEADD) rely on a date table that is explicitly marked with the Mark as Date Table option, and a one-to-many relationship from the date table to the sales table ensures each date filters its associated sales rows unambiguously. Marking the date table lets the engine identify the date column for time-based calculations, while the one-to-many cardinality matches the logical model where each calendar day can appear in many fact records. This star-schema pattern supports reliable, accurate time-series reporting.

Aggregations are pre-built summary tables (often at month, quarter, or year level) that the Power BI storage engine transparently redirects to when a query matches the aggregation's grain. By answering from a compact, precomputed table instead of scanning every transaction row, the engine dramatically reduces I/O and CPU usage, which accelerates report performance with minimal impact on user interactivity or drill-down capability.

Creating a separate date table and marking it as a date table is the correct approach because it establishes a continuous, non-blank range of dates that the DAX engine explicitly recognizes for time intelligence. Marking the table via 'Mark as Date Table' sets the ‘Date’ column as the authoritative calendar reference, which enables functions like TOTALYTD, PREVIOUSYEAR, and PARALLELPERIOD to correctly compute period boundaries and offsets. This practice also supports fiscal calendars, holidays, and custom hierarchies, making it the recommended design pattern in Power BI for any model requiring robust date analysis.

The map visual is cross-filtering based on the City dimension (e.g., CityName) which is related to the Shipments table via the OriginCity foreign key. However, the table visual displays DestinationCity, which is either a different column in the Shipments table or a different dimension that is not related to the same City table. Because the filter propagates only along active relationships to the fields used in the target visual, the DestinationCity field is not affected, so the table does not update.

Selecting the named table in Navigator imports the defined table range, so headers, column names, and data types are recognized reliably. Named tables are stable references, so adding rows or columns within the table updates the query without reconfiguration, making this the most dependable approach for a well-structured worksheet.

In the formatting pane for a slicer, the 'Selection controls' section contains a 'Multi-select' toggle that directly controls whether users can pick more than one value. When this toggle is turned on, users can Ctrl+click (or tap) multiple items—optionally using checkboxes—and the report's visuals will show combined data for all selected product categories. This is the precise, built-in mechanism for enabling multiple selection on a slicer and is the correct fix for the stated requirement.

Introducing a bridge table (also called a junction or associative table) is the standard way to model a many-to-many relationship in Power BI: it stores one row for each unique pair of keys from the two original tables, and then you create two one-to-many relationships with the bridge table as the 'many' side on both. This configuration allows filter context to flow from either original table through the bridge to the other, resolving the many-to-many condition without ambiguity. For correctness, the bridge table must contain distinct composite keys and may include additional attribute columns such as weight or validity dates, and you must be careful with cross-filter direction to avoid fan-out and double-counting in measures.

Correct. Row-level security (RLS) is enforced at the dataset level by defining one or more roles in Power BI Desktop, each with a DAX filter that restricts rows based on the current user (e.g., using USERPRINCIPALNAME() or CUSTOMDATA()). This filter is applied by the Analysis Services engine for every query against the dataset, regardless of which report pages, visuals, or measures are used, so no user can ever see rows outside their role's allowed set. It is the only option here that actually slices data by security identity before results reach the reporting layer.

Merge is the correct Power Query transformation for combining tables horizontally. It matches rows between two tables based on one or more key columns, using a join kind such as Left Outer, Inner, or Full Outer. After merging, you expand the resulting nested column to bring in the desired fields from the second table. This aligns with the standard relational concept of a join, but within Power Query the precise name is 'Merge Queries'.

In the Power BI service, the correct way to propagate workspace changes to the audience is to open the workspace and click "Update app" (or "Update app" in the app editing screen). This action re-publishes the app's content, including the modified report, to all current app users without interrupting their access or requiring them to reinstall the app. It also ensures the app's metadata, navigation, and permissions remain intact. This is the standard lifecycle step after editing a report or dashboard in the source workspace.

Microsoft Purview retention policies can be applied to Power BI datasets, reports, and other items. When configured, they ensure that items are retained for the specified period even if users delete them. This is a centralized, tenant-wide solution that requires minimal ongoing effort once set up, and it meets the seven-year retention requirement.

When DIVIDE is used without an optional third argument, it returns BLANK if the denominator is zero. For these rows, SalesAmount equals zero, making the denominator of the division zero, so the result is blank rather than a numeric value. This is the intended behavior of DIVIDE and is why the column appears blank only for those specific rows.

The correct diagnosis is that the row-level security (RLS) filter defined for the user's role is returning an empty result. RLS applies a DAX filter to every visual in the report; if the user's identity (via USERNAME()) doesn't match any row in the `Sales[Region]` column, all measures become BLANK and tables show zero rows. An admin can verify this by using 'View as' in Power BI Desktop and selecting the specific role to see the same empty output.

Creating an aggregate table in Power BI that pre-aggregates sales by month and product category is the correct approach because it reduces the fact table to a much coarser grain, shrinking the number of rows that report queries must scan. By configuring this aggregate table as an aggregation group in the model, Power BI can automatically route high-level visual queries to the small summary table while reserving the detailed fact table for drill-down operations. This leverages the storage engine's in-memory columnar compression and accelerates time-intelligence calculations such as year-over-year month comparisons, directly addressing the performance bottleneck caused by large transaction-level data.