PL-300 Practice Exam 59 — 20 Questions

Question 1

You are reviewing a Power Query query that loads data from a SQL Server database. The query includes multiple steps that perform data transformation. You want to ensure that the query is optimized by pushing as many transformations as possible to the SQL Server. What should you look for?

Accepted Answer

Check for the 'Query Folding' indicators in the query editor.. Option C is correct because query folding indicators in Power Query Editor show whether transformations are being pushed to the SQL Server source. When a step shows a 'folded' icon (a table with a down arrow), it means the transformation is translated into native SQL and executed on the server, reducing data transfer and improving performance. Checking these indicators directly confirms which steps are folded and which are not, allowing you to optimize the query by reordering or rewriting steps to maximize folding.

Answer

View the native SQL query generated by Power Query.

Answer

Use the Performance Analyzer in Power BI Desktop.

Answer

Review the data preview for each step.

Question 2

You are transforming data in Power Query. A column named 'SalesAmount' contains values as text with a dollar sign and thousands separator, e.g., "$1,234.56". You need to convert this column to a decimal number for analysis. What is the most efficient sequence of transformations?

Accepted Answer

Use Replace Values to remove '$' and ',', then change data type to Decimal Number.. Option C is correct because it explicitly removes both the dollar sign and the comma before changing the data type, ensuring that Power Query can interpret the cleaned text as a decimal number without errors. Directly changing the data type (Option B) would fail because Power Query cannot automatically parse currency symbols and thousands separators from text. Option A is inefficient because splitting the column is unnecessary when simple replacements suffice. Option D is identical to C but omits the comma removal, which would leave the thousands separator and cause conversion failures.

Answer

Split the column by delimiter and keep the numeric part, then change data type.

Answer

Change data type to Decimal Number directly; Power Query will automatically clean the values.

Answer

Use Replace Values to remove '$' and ',' then change data type to Decimal.

Question 3

You are merging two queries in Power Query: 'Orders' and 'Customers'. The 'Orders' table has a 'CustomerID' column, and 'Customers' has 'CustomerID' and 'Name'. You need to bring the 'Name' into 'Orders' but only for matching CustomerIDs; unmatched rows should be removed. Which join kind should you use?

Accepted Answer

Inner. The Inner join kind in Power Query returns only rows where there is a match in both tables based on the key columns. Since the requirement is to bring the 'Name' into 'Orders' only for matching CustomerIDs and to remove unmatched rows, the Inner join is the correct choice. It ensures that only orders with a corresponding customer in the 'Customers' table are retained, and the 'Name' column is added to those matching rows.

Answer

Right Anti

Answer

Full Outer

Answer

Left Outer

Question 4

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.)

Accepted Answer

Configure dynamic data masking on the Azure SQL Database.. Option A is correct because 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.

Answer

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

Answer

Use Microsoft Purview sensitivity labels to mask data.

Answer

Apply column-level security in Power BI Desktop.

Question 5

You are preparing data from multiple Excel files. Each file has a different structure; some have merged cells, empty rows, and inconsistent column names. Which TWO actions should you take to clean the data in Power Query? (Choose two.)

Accepted Answer

Remove blank rows that result from merged cells.. Option A is correct because merged cells in Excel often introduce blank rows when the data is loaded into Power Query. Using 'Remove Blank Rows' in Power Query eliminates these extraneous rows that do not contain meaningful data, ensuring the dataset is clean and ready for transformation.

Answer

Group rows by a key column to summarize data.

Answer

Merge columns to create a single identifier.

Answer

Unpivot columns to normalize the data.

Question 6

You have a Power BI report that uses a date table connected to a fact table. You need to ensure that all dates in the fact table are covered by the date table. Which relationship property should you configure?

Accepted Answer

Assume referential integrity. Option C is correct because the 'Assume referential integrity' property, when enabled, tells Power BI that every value in the foreign key column of the fact table exists in the primary key column of the date table. This ensures that all dates in the fact table are covered by the date table, allowing Power BI to use more efficient storage and query execution (e.g., INNER JOIN semantics) rather than a full OUTER JOIN.

Answer

Make this relationship active

Answer

Cardinality

Answer

Cross filter direction

Question 7

Which THREE data sources can be used with Power BI Dataflows? (Choose three.)

Accepted Answer

OData feed. OData feeds are supported as a data source in Power BI Dataflows because Dataflows use the Power Query engine, which can connect to OData endpoints to import data. This allows you to pull data from RESTful APIs that expose OData services, making it a valid and common source for cloud-based data ingestion.

Answer

Power BI dataset

Answer

Excel file stored on local drive

Question 8

You are a Power BI developer for a healthcare organization. You are building a dataset that includes patient data from an on-premises SQL Server database. The database contains a table 'PatientVisits' with columns: PatientID, VisitDate, DiagnosisCode, and Cost. The database also has a table 'DiagnosisLookup' with DiagnosisCode and Description. You need to create a star schema in Power BI. The requirements are:
- The dataset must include a date dimension table that covers all dates from 2010 to 2030.
- The 'PatientVisits' table should be the fact table.
- Diagnosis descriptions should be in a dimension table.
- You must use Power Query to create the date dimension table using M code.
- The data refresh must be scheduled daily via the on-premises data gateway.

You have already loaded the 'PatientVisits' and 'DiagnosisLookup' tables. What should you do next to complete the star schema?

Accepted Answer

In Power Query, create a blank query that generates a date table using the List.Dates function with a custom column for Year, Month, etc. Load it into the model and mark it as a date table.. Option D is correct because the requirement explicitly states that the date dimension table must be created using M code in Power Query, and the List.Dates function is the appropriate M function to generate a continuous range of dates from 2010 to 2030. After creating the table with additional columns like Year and Month, you must load it into the model and mark it as a date table to enable time intelligence functions. This approach satisfies the need for a custom date dimension that is not dependent on DAX or auto-generated hierarchies.

Answer

Use DAX to create a date table using CALENDAR function in Power BI Desktop, then mark it as a date table.

Answer

Enable the 'Auto date/time' option in Power BI Desktop and hide the generated date hierarchy.

Answer

In the model view, create a relationship between PatientVisits[VisitDate] and DiagnosisLookup[DiagnosisCode].

Question 9

You are building a Power BI report for a logistics company. The data is stored in a CSV file on a SharePoint Online document library. The CSV file is updated daily with new rows. You need to ensure that the Power BI dataset reflects the latest data every morning at 7:00 AM. The data volume is small, so full refresh is acceptable. You have already published the report to the Power BI service. What should you do to automate the refresh?

Accepted Answer

In the Power BI service, configure a scheduled refresh for the dataset with the desired time.. Option A is correct because the Power BI service supports scheduled refresh natively for datasets that connect to cloud data sources like SharePoint Online CSV files. By configuring a scheduled refresh in the dataset settings, you can set it to run daily at 7:00 AM without any additional tools or gateways. This ensures the dataset reflects the latest data from the CSV file each morning.

Answer

Use Power Automate to trigger a refresh via the Power BI REST API every morning.

Answer

Enable incremental refresh for the dataset and set the refresh frequency to daily.

Answer

Install an on-premises data gateway and configure a scheduled refresh.

Question 10

You are a Power BI administrator at Contoso Ltd. The company has a Power BI tenant with Premium capacity. A data analyst has published a dataset that uses DirectQuery mode to a SQL Server database. Users report that the report is slow. You need to improve query performance without changing the data source. You decide to implement a composite model by adding an imported table for a frequently used dimension. After importing, you notice that the relationship between the imported table and the DirectQuery table is Many-to-Many, which causes ambiguity. What should you do to resolve the ambiguity and ensure correct filtering?

Accepted Answer

Create a bridge table with a calculated column to create a one-to-many relationship.. Option B is correct because a bridge table resolves the Many-to-Many ambiguity by creating a separate table that contains only the unique key values from both sides, enabling a star-like schema with one-to-many relationships. This ensures that filters from the imported dimension table propagate correctly to the DirectQuery fact table without ambiguity.

Answer

Remove the imported table and use only DirectQuery.

Answer

Use the CROSSFILTER function to ignore the relationship.

Answer

Change the relationship to One-to-Many (1:*).

Question 11

You are a Power BI administrator at a large enterprise. You need to ensure that all Power BI datasets in your organization use certified data sources only, to maintain data quality. You have enabled data source certification in your Microsoft Purview governance portal. Which additional step must you take to enforce that only certified data sources are used in Power BI datasets?

Accepted Answer

In the Power BI admin portal, enable the 'Require certified data sources' tenant setting.. Option A is correct because the 'Require certified data sources' tenant setting in the Power BI admin portal is the specific control that enforces datasets to use only data sources that have been certified in Microsoft Purview. Without this setting enabled, certification is merely informational and not enforced, allowing uncertified sources to be used. This setting works in conjunction with Purview's data source certification to block dataset creation or refresh if any source lacks certification.

Answer

Create dataflows that use only certified data sources and require report creators to use those dataflows.

Answer

Apply sensitivity labels to all certified data sources in Purview.

Answer

Configure workspace security to restrict dataset creation to administrators only.

Question 12

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?

Accepted Answer

CALCULATE(SUM(Sales[Amount]), PREVIOUSMONTH('Date'[Date])). Option B is correct because 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.

Answer

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

Answer

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

Answer

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

Question 13

You have a Power BI data model with a table named Products that contains columns ProductID, ProductName, and CategoryID. You also have a table named Categories with CategoryID and CategoryName. You want to ensure that when a user filters by a category, all products in that category are shown, even if the product has no sales. What type of relationship should you create between Products and Categories?

Accepted Answer

One-to-many from Categories to Products. Option D is correct because a one-to-many relationship from Categories to Products ensures that each category can be associated with multiple products, and when a user filters by a category, all products in that category are displayed regardless of whether they have sales. This is the standard star schema design where the dimension table (Categories) filters the fact table (Products) via a one-to-many relationship, preserving referential integrity and showing all products in the filtered category.

Answer

Many-to-many

Answer

Many-to-one from Products to Categories

Answer

One-to-one

Question 14

You are building a Power BI report that uses a DirectQuery connection to a SQL Server database. You need to reduce the amount of data transferred to Power BI when users interact with visuals. What should you do?

Accepted Answer

Apply filters in Power Query to reduce rows. Option B is correct because applying filters in Power Query reduces the number of rows retrieved from the SQL Server database before the data is transferred to Power BI. With a DirectQuery connection, queries are sent to the source each time a visual is interacted with, so reducing the row count at the query level minimizes network traffic and improves report performance.

Answer

Increase the query timeout setting

Answer

Disable query reductions

Answer

Load all data into the Power BI model

Question 15

You are designing a Power BI data model that includes a table named Sales with 10 million rows. You need to create a relationship between Sales and a Product dimension table. The Product table has 10,000 rows. Which configuration will provide the best query performance?

Accepted Answer

Create a one-to-many relationship from Product to Sales. Option B is correct because a one-to-many relationship from Product (the dimension table with unique values) to Sales (the fact table with many rows) is the standard star schema design. This configuration allows Power BI to use the smaller Product table to filter the larger Sales table efficiently, leveraging in-memory columnar storage and automatic aggregations for optimal query performance.

Answer

Create a many-to-one relationship from Sales to Product

Answer

Do not create a relationship; use LOOKUPVALUE in measures

Answer

Create a relationship with cross filter direction set to Both

Question 16

You have a Power BI data model with a table named Employees that includes columns: EmployeeID, ManagerID, and EmployeeName. You need to create a hierarchy that shows the reporting structure. Which type of relationship is required?

Accepted Answer

A self-referencing relationship. A self-referencing relationship (Option C) is required because the Employees table contains both EmployeeID and ManagerID, where ManagerID references EmployeeID within the same table. This allows Power BI to create a parent-child hierarchy that accurately represents the reporting structure, such as an org chart. In Power BI, this is implemented by creating a relationship from ManagerID to EmployeeID within the same table.

Answer

Many-to-many relationship

Answer

Bidirectional cross-filtering

Answer

One-to-many relationship to a separate table

Question 17

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?

Accepted Answer

The relationship between Product and Category. 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.

Answer

All relationships are equally efficient

Answer

The relationship between Sales and Customer

Answer

The relationship between Sales and Product

Question 18

You are building a data model for a retail company. The 'Sales' fact table has a column 'Discount' that is a percentage (0 to 1). You create a measure 'Total Discount Amount' = SUM(Sales[Discount]) * SUM(Sales[Quantity]) * SUM(Sales[UnitPrice]). However, the measure returns incorrect results when multiple discount percentages exist in the same filter context. What is the issue?

Accepted Answer

The measure is performing aggregations at the wrong granularity; it should use SUMX to iterate over each row.. The measure uses SUM on each column individually, which aggregates all values in the filter context before multiplying. When multiple discount percentages exist, this incorrectly multiplies the total of all discounts by the total of all quantities and total of all unit prices, rather than computing discount per row. The correct approach is to use SUMX to iterate over each row of the Sales table, calculating Discount * Quantity * UnitPrice per row and then summing those row-level results, ensuring accurate granularity.

Answer

The measure contains a circular dependency.

Answer

The measure is referencing columns from different tables without proper relationships.

Answer

The Discount column should be of type Decimal instead of Percentage.

Question 19

You are importing data from a SQL Server database into Power BI. The source table has a column 'OrderDate' of type DATETIME. You want to filter data based on the date only, ignoring time. What is the most efficient approach?

Accepted Answer

Create a calculated column in DAX using DATEVALUE(OrderDate).. Option B is correct because DATEVALUE(OrderDate) in DAX converts a datetime value to a date-only value, which is the most efficient approach when you need to filter by date while keeping the original datetime column intact for other calculations. This avoids unnecessary data transformation in Power Query and leverages DAX's optimized date handling for filtering operations.

Answer

Change the data type of the OrderDate column to 'Date' in Power Query.

Answer

Create a new column in Power Query using Date.From(OrderDate).

Answer

Use the 'Split Column' feature in Power Query to separate date and time.

Question 20

You are reviewing the partition configuration for a Power BI Import model as shown in the exhibit. The table Sales is partitioned by year. You need to modify the model to improve incremental refresh performance. What change should you make?

Accepted Answer

Configure incremental refresh policy. Configuring an incremental refresh policy (Option B) is the correct approach because it automatically manages partition creation and refresh for the Sales table based on a date/time column. This improves performance by refreshing only the most recent data (e.g., last 5 years) while keeping historical partitions unchanged, reducing refresh time and resource consumption compared to manual yearly partitions.

Answer

Increase the number of partitions to monthly

Answer

Remove all partitions and load data as a single table

Answer

Change the storage mode to DirectQuery