PL-300 Practice Test 36 — 15 Questions

Question 1

You need to combine data from two tables in Power Query that have the same columns but different row sets. Which operation should you use?

Accepted Answer

Append Queries. Append Queries is the correct operation in Power Query when you need to combine two tables with identical columns but different row sets. It stacks the rows from the second table beneath the rows from the first table, effectively performing a union of the data. This is distinct from Merge Queries, which joins columns based on a key, and Group By, which aggregates data.

Answer

Union Queries

Answer

Group By

Answer

Merge Queries

Question 2

You are a data analyst for a retail company. You have a Power BI semantic model that imports data from an on-premises SQL Server database using an on-premises data gateway. The dataset contains a table named Products with a column named Category. You need to ensure that when users open a report, they only see data for the categories they are authorized to view. You have a table named UserCategories that maps user principal names (UPNs) to categories. What should you do?

Accepted Answer

Create a role in the semantic model, add a DAX filter on the Products table using LOOKUPVALUE to match the Category to the UserCategories table based on USERPRINCIPALNAME().. Row-level security in Power BI is implemented by creating roles and defining DAX filter expressions on tables. To dynamically restrict data per user, the filter should use USERPRINCIPALNAME() to identify the current user and LOOKUPVALUE to retrieve the categories assigned to that user from the UserCategories table. This ensures each user sees only their authorized categories.

Answer

Use Power BI Desktop to create a calculated table that filters Products based on the current user, and then hide the original Products table.

Answer

Implement row-level security in the on-premises SQL Server database by creating a security policy and mapping users to categories.

Answer

Create a role in the semantic model and add a static DAX filter on the Products table for each category, such as [Category] = "Electronics".

Question 3

You are preparing a Power BI semantic model for a retail chain. The model contains a Products dimension and a Sales fact table. Management wants to analyze sales by product category and also by product subcategory, which is stored in a separate Subcategory table that relates to Products. You need to decide which relationship and modeling choices let filters flow from Subcategory through Products to Sales. (Choose two.)

Accepted Answer

Ensure the Products table has a unique value for the subcategory key on the one side of the relationship.. Filter propagation through a snowflaked dimension depends on a correctly defined one-to-many relationship with unique values on the one side and single-direction filtering along the hierarchy. Hiding keys, forcing bidirectional filtering, or marking an unrelated table as a date table does not enable the required filter flow and can introduce ambiguity or validation errors.

Answer

Set the relationship between Subcategory and Products to bidirectional cross-filtering to guarantee filters reach Sales.

Answer

Mark the Subcategory table as a date table so it participates in time intelligence.

Answer

Hide the subcategory key columns in both tables so users interact only with the descriptive names.

Question 4

You are a Power BI data analyst at a healthcare organization. You import patient encounter data from an Azure Synapse Analytics dedicated SQL pool. The data contains a column named EncounterDate of type datetime2. You need to create a Power Query transformation that adds a column showing the fiscal year, which starts on July 1. The fiscal year should be labeled as FY2024 for dates from July 1, 2023, through June 30, 2024. Which transformation should you use?

Accepted Answer

Add a custom column with the formula Date.Year(Date.AddMonths([EncounterDate], 6)) and concatenate 'FY' with the result.. To calculate a fiscal year that starts on July 1, you must shift the date by six months before extracting the year. The transformation Date.Year(Date.AddMonths([EncounterDate], 6)) correctly maps July 1 through June 30 to the appropriate fiscal year number, which can then be prefixed with 'FY' to match the required label format.

Answer

Add a custom column with the formula Date.Year([EncounterDate]) and concatenate 'FY' with the result.

Answer

Use the Date.StartOfYear function with a parameter of 7 to get the fiscal year start date, then extract the year.

Answer

Add a custom column with the formula Date.AddMonths([EncounterDate], -6) and then extract the year.

Question 5

Which THREE of the following are best practices for data preparation in Power BI to improve performance and maintainability? (Select THREE.)

Accepted Answer

Filter out unnecessary rows as early as possible in the query. Filtering out unnecessary rows early in Power Query reduces the amount of data loaded into memory and processed in subsequent transformation steps. This practice, known as early filtering, minimizes the data footprint and improves both refresh performance and report responsiveness. By applying filters as the first transformation, you leverage query folding to push the filter logic to the source database, further enhancing efficiency.

Answer

Avoid renaming columns in Power Query; use original names

Answer

Keep all columns from the source to avoid missing data

Question 6

You have a Power BI report connected to a dataset containing a table named Sales with columns OrderDate (date), ProductCategory (text), and SalesAmount (decimal). You create a report page with a line chart showing SalesAmount over OrderDate. Users want to quickly change the date granularity from daily to monthly and also switch between ProductCategory values without editing the report. The page currently has no slicers or filters. You need to add interactive controls that allow users to change the date hierarchy level and filter by product category. What should you do?

Accepted Answer

Add a date hierarchy to the line chart's X-axis and add a slicer for ProductCategory.. Using a date hierarchy on the axis enables drill up/down to change granularity, and a slicer on ProductCategory gives users an interactive filter. Both are native Power BI features that require no report editing by consumers. This combination directly addresses the need to switch date levels and filter by category.

Answer

Add a parameter to the dataset that allows switching the date granularity and use a slicer for ProductCategory.

Answer

Add a hierarchy slicer containing OrderDate and ProductCategory, and place it on the page.

Answer

Add a page-level filter for ProductCategory and enable the line chart's zoom slider to change date granularity.

Question 7

You are a Power BI administrator. The compliance team requires that all Power BI datasets and reports must be retained for seven years, even if users delete them. You need to configure the tenant to meet this requirement with minimal administrative effort. What should you do?

Accepted Answer

Enable Microsoft Purview retention policies for Power BI items.. Microsoft Purview retention policies are the correct solution because they can be applied to Power BI items to retain them for a specified period, even if users delete them. This is a tenant-level configuration that requires minimal effort and meets compliance requirements. Other options are either not valid Power BI features or do not enforce automatic retention.

Answer

Configure a backup schedule for the Power BI workspace.

Answer

Set the workspace retention policy to seven years in the workspace settings.

Answer

Instruct users to move items to a dedicated archive workspace.

Question 8

You have a Power BI report with a card visual that displays total sales. Users report that when they apply a date filter using the slicer, the card visual does not update. You have verified that the slicer is properly configured to filter the date table. What is the most likely cause?

Accepted Answer

The card visual is using a measure that ignores filter context, such as one using ALL().. The most common reason a visual does not respond to a slicer is that the measure used in the visual overrides the filter context with functions like ALL() or REMOVEFILTERS(). These functions are often used to calculate percentages of total or to ignore specific filters, but they can inadvertently cause the visual to ignore slicer selections. Checking the measure definition is the first step.

Answer

The card visual is set to 'Do not summarize' for the sales field.

Answer

The slicer is not synchronized with the card visual's page.

Answer

The relationship between the date table and the sales table is inactive.

Question 9

You are creating a Power BI semantic model for a university. The model includes a Student table with columns StudentID, Name, and EnrollmentDate. You need to create a calculated column that categorizes students into 'New' if EnrollmentDate is within the last 30 days from today, otherwise 'Existing'. Which DAX formula should you use?

Accepted Answer

IF(Student[EnrollmentDate] >= TODAY() - 30, "New", "Existing"). The correct formula must compare the enrollment date to a date 30 days before today and return 'New' or 'Existing' accordingly. Using TODAY() minus 30 days provides the cutoff date. The IF function then evaluates each row and assigns the correct category, ensuring accurate classification based on the enrollment recency.

Answer

IF(Student[EnrollmentDate] >= TODAY() - 30, "Existing", "New")

Answer

IF(Student[EnrollmentDate] >= TODAY() - 30, "New", "Existing", "Unknown")

Answer

IF(Student[EnrollmentDate] >= DATEADD(TODAY(), -30, DAY), "New", "Existing")

Question 10

You are a Power BI data analyst for a logistics company. You connect to an Excel workbook stored on a SharePoint Online site. The workbook contains a table named Shipments. You need to load only the rows where the ShipmentDate is in the current year. Which Power Query transformation should you apply?

Accepted Answer

Use the 'Filter Rows' option on the ShipmentDate column and select 'Date Filters' > 'In the Current Year'.. Power Query provides dynamic date filters such as 'In the Current Year' that automatically adjust based on the current date. Applying this filter to the ShipmentDate column ensures that only rows from the current year are loaded, and the filter remains correct as time passes without manual intervention.

Answer

Add a custom column with the formula Date.Year([ShipmentDate]) = Date.Year(DateTime.LocalNow()) and then filter that column to TRUE.

Answer

Use the 'Filter Rows' option on the ShipmentDate column and select 'Date Filters' > 'Is After' and enter the first day of the current year.

Answer

Use the 'Filter Rows' option on the ShipmentDate column and manually select the checkboxes for all dates in the current year.

Question 11

You manage a Power BI tenant. A workspace named 'Finance' contains a dataset that uses an on-premises SQL Server data source via an on-premises data gateway. The gateway is configured with a single data source that uses a SQL Server account for authentication. You need to ensure that when users view reports based on this dataset, they see only data for their own department, and the filtering must be enforced at query time without modifying the dataset. What should you do?

Accepted Answer

Configure row-level security (RLS) on the dataset and map users to roles.. Row-level security (RLS) is the correct approach because it dynamically filters data based on the user's identity at query time. It is defined within the dataset and does not require changes to the data source or gateway. Other options either duplicate content, do not enforce per-user filtering, or address column-level rather than row-level security.

Answer

Create separate reports for each department and distribute them via apps.

Answer

Configure column-level security on the dataset to hide sensitive columns.

Answer

Use the gateway's 'Add users to data source' feature to map each user to a specific SQL Server login.

Question 12

You are a data analyst for a healthcare organization. You have a Power BI report with a clustered bar chart that displays patient counts by department. The report is used by hospital administrators who need to identify which department has the highest patient count. They also want to see the exact patient count for each department without hovering. Which feature should you enable on the visual to meet this requirement?

Accepted Answer

Data labels. Data labels are the correct feature because they display the exact numeric values directly on the visual, allowing administrators to read patient counts without hovering. This meets the need for persistent, at-a-glance information. Other options either require interaction or do not show values.

Answer

Tooltips

Answer

Analytics pane

Answer

Conditional formatting

Question 13

You are a Power BI data analyst at a healthcare provider. Your semantic model contains a fact table named Visits with columns VisitID, PatientID, ProviderID, VisitDate, and ChargeAmount. You also have a dimension table named Patients with PatientID, PatientName, and PrimaryCareProviderID. You need to create a relationship between Visits and Patients so that filtering Patients by PrimaryCareProviderID correctly filters Visits. However, you discover that the Visits table has multiple rows per PatientID, and the Patients table has a unique PatientID. Which relationship should you create?

Accepted Answer

Create a one-to-many relationship from Patients[PatientID] (one side) to Visits[PatientID] (many side).. The relationship must be one-to-many from Patients to Visits because Patients[PatientID] is unique and Visits[PatientID] has duplicates. This allows filters on Patients, such as PrimaryCareProviderID, to propagate to Visits and correctly aggregate ChargeAmount. A many-to-many or reversed cardinality would not enforce referential integrity and could yield incorrect results.

Answer

Create a many-to-many relationship between Visits[PatientID] and Patients[PatientID].

Answer

Create a many-to-one relationship from Visits[PatientID] (many side) to Patients[PatientID] (one side) and set the cross-filter direction to Both.

Answer

Create a one-to-many relationship from Visits[PatientID] (one side) to Patients[PatientID] (many side).

Question 14

You are a data analyst for a healthcare provider. You have a Power BI semantic model with a fact table named Encounters and a dimension table named Patients. The Patients table contains a column PatientKey and a column MRN (medical record number). Some patients have multiple MRNs because they were registered at different clinics. You need to create a relationship between Encounters and Patients that ensures each encounter is attributed to exactly one patient record. What should you do?

Accepted Answer

Create a one-to-many relationship from Patients[PatientKey] to Encounters[PatientKey].. The Patients dimension has a unique PatientKey that is referenced by the Encounters fact table. Creating a one-to-many relationship from Patients[PatientKey] to Encounters[PatientKey] enforces referential integrity and ensures each encounter maps to exactly one patient row. Using MRN would be incorrect because it is not unique and is not present in the fact table.

Answer

Create a many-to-many relationship between Patients[MRN] and Encounters[PatientKey].

Answer

Create a bidirectional many-to-many relationship between Patients[MRN] and Encounters[MRN].

Answer

Create a one-to-many relationship from Patients[MRN] to Encounters[PatientKey].

Question 15

You are preparing data for a Power BI report. You have a query that connects to a REST API that returns JSON data. The JSON response contains a nested array of order details under an 'Orders' field. You need to expand the nested array so that each order becomes a row in the table. What should you do in Power Query?

Accepted Answer

Use the 'Expand' button on the 'Orders' column and select all fields.. Expanding the nested array column is the correct method to flatten JSON data in Power Query. The 'Expand' button appears when a column contains structured values such as lists or records. Expanding a list column creates a new row for each element, which is exactly what is needed to turn each order into a row. Other transformations like unpivot, split, or group by do not handle nested structures and would produce incorrect results.

Answer

Use the 'Group By' transformation on the 'Orders' column.

Answer

Use the 'Split Column' transformation by a delimiter.

Answer

Use the 'Unpivot Columns' transformation on the 'Orders' column.