PL-300 Prepare the data Practice Test 19 — 15 Questions

Question 1

You are a Power BI developer at a healthcare organization. You are building a report that must comply with HIPAA regulations. You need to ensure that patient data is not exposed to unauthorized users. You plan to use Row-Level Security (RLS) with roles defined in Power BI Desktop. However, you also need to limit the data imported into the model to only necessary columns. The source is an Azure SQL Database with a table 'Patients' containing columns: PatientID, Name, SSN, Diagnosis, AdmissionDate, DischargeDate. Which two actions should you take? (Choose TWO)

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Create RLS roles to restrict access by Diagnosis.. Option A is correct because defining RLS roles in Power BI Desktop and mapping them to users in the Power BI service enforces row-level filtering so that each user only sees the patient rows they are authorized to view, which is a core HIPAA minimum-necessary access control. Option E is correct because removing SSN and Name in Power Query before loading implements data minimization, ensuring sensitive identifiers are never imported into the semantic model where they could be exposed. Option B is not correct because storing credentials in the Power BI service is a connectivity/authentication practice, not a data-exposure control for this scenario. Option C is not correct because disabling query caching does not restrict which data is imported or who can see it. Option D is not correct because an encrypted connection protects data in transit but does not limit imported columns or enforce row-level access for report consumers.

Answer

Store data source credentials in the Power BI service.

Answer

Disable query caching for the dataset.

Answer

Use encrypted connection to the database.

Question 2

You are a data analyst for a multinational corporation. You are building a Power BI report that uses a large fact table (100 million rows) and several dimension tables. The data source is a SQL Server data warehouse. Users need to see near real-time data with a maximum latency of 15 minutes. The current import mode takes too long to refresh. You decide to use DirectQuery mode. However, queries are slow. You need to improve query performance. You consider creating aggregations in the data source. Which approach should you take in Power BI to leverage these aggregations?

Accepted Answer

Define aggregations in Power BI on the DirectQuery tables.. Power BI allows you to define aggregations on DirectQuery tables, which enables the engine to route queries to pre-aggregated data at the source (e.g., SQL Server indexed views or materialized views) when possible, significantly reducing query latency. This approach leverages the existing aggregations in the data source without changing the storage mode or importing data, maintaining near real-time freshness with a 15-minute latency requirement.

Answer

Create a composite model with an imported aggregated table.

Answer

Change the storage mode to Import for the fact table.

Answer

Create a SQL Server view that aggregates data and use it as the source.

Question 3

Which TWO actions are required when configuring a Power BI dataset to use incremental refresh?

Accepted Answer

Set the incremental refresh policy in the dataset settings.. Option E is correct because incremental refresh in Power BI requires two Power Query parameters named exactly rangeStart and rangeEnd (both of type DateTime), which define the filter window applied to the table's date column so Power BI can partition and refresh only the relevant ranges. Option D is correct because, after publishing the dataset, you must configure the incremental refresh policy (via the dataset's settings in the Power BI service, or by right-clicking the table in Power BI Desktop and selecting Incremental refresh) to specify the historical and incremental periods that determine how partitions are created and refreshed. Option A is incorrect because incremental refresh works with Import mode datasets and does not require DirectQuery storage mode. Option B is incorrect because query caching is a separate performance feature and is not a prerequisite for incremental refresh. Option C is incorrect because Power BI automatically manages the refresh history and partition metadata internally; no user-created calculated table is needed.

Answer

Set the dataset storage mode to DirectQuery.

Answer

Enable query caching on the dataset.

Answer

Create a calculated table to store the refresh history.

Question 4

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

Accepted Answer

OData feed. Power BI Dataflows are built on the Power Query Online engine, so any connector available in Power Query Online can serve as a data source. Option C (OData feed) is correct because OData is a standard REST-based protocol connector supported in Power Query Online, allowing dataflows to ingest from OData v3/v4 endpoints. Option D (Azure SQL Database) is correct because Azure SQL Database is a supported cloud database connector in Power Query Online, enabling direct query or import into the dataflow's CDM storage. Option E (SharePoint Online list) is correct because SharePoint Online lists are a native cloud connector in Power Query Online, letting dataflows pull list data via the SharePoint REST API. Option A (Power BI dataset) is not a valid dataflow source because dataflows feed into datasets, not the reverse, and Power Query Online does not expose a Power BI dataset connector. Option B (Excel file stored on local drive) is not valid because Power Query Online runs in the cloud and cannot access a local drive path; a personal gateway or uploading to OneDrive/SharePoint would be required.

Answer

Power BI dataset

Answer

Excel file stored on local drive

Question 5

You are a data analyst at a retail company. You have a Power BI semantic model that imports sales data from an Azure SQL Database. The database uses a timestamp column to track transaction time. You need to reduce the data refresh time and ensure that only the last 30 days of data are refreshed during each scheduled refresh. You have already created the necessary parameters rangeStart and rangeEnd in Power Query. What should you do next to implement incremental refresh?

Accepted Answer

In Power BI Desktop, on the model view, select the table and set the incremental refresh policy.. Incremental refresh policies are defined in Power BI Desktop on the model view, not in the service or by simply filtering in Power Query. After creating the rangeStart and rangeEnd parameters, you must select the table in the Model view, open the incremental refresh policy dialog, and configure the policy to filter data based on those parameters, ensuring only the last 30 days are refreshed.

Answer

In the Power BI service, go to the dataset settings and configure the scheduled refresh.

Answer

In Power Query Editor, apply the rangeStart and rangeEnd filters to the data and then close and apply.

Answer

In the Power BI service, create a new refresh schedule and set the incremental refresh period.

Question 6

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

You are building a Power BI report for a manufacturing company. You have a large fact table with 50 million rows in Azure SQL Database. You need to minimize the data refresh time and ensure that only new or changed rows are loaded. The source table has a LastModifiedDate column. What should you do?

Accepted Answer

Configure incremental refresh on the table using the LastModifiedDate column.. Incremental refresh in Power BI allows you to load only new or changed rows from a large fact table by filtering on a date/time column such as LastModifiedDate. This minimizes data refresh time by avoiding a full reload of all 50 million rows, and it leverages the source system's ability to efficiently query only the modified data. Power Query pushes the filter logic to Azure SQL Database via query folding, ensuring optimal performance.

Answer

Enable query folding in Power Query to push filters to the source.

Answer

Schedule a full refresh every hour.

Answer

Create a Power BI dataflow that performs a full load and then use that dataflow as a source.

Question 8

You are a data analyst for a financial services company. You have a Power BI dataset that combines data from two sources: a CSV file in SharePoint Online and an on-premises SQL Server database. The CSV file contains exchange rates that are updated daily. The SQL Server database contains transaction data. You need to ensure that the dataset can be refreshed automatically in the Power BI service. The CSV file is updated at 6:00 AM daily, and the SQL Server database is updated continuously. You have already published the report. What should you do to enable automated refresh?

Accepted Answer

Install and configure an on-premises data gateway, then set up a scheduled refresh.. The on-premises SQL Server database requires an on-premises data gateway to bridge the Power BI service with the local network. Even though the CSV file is in SharePoint Online, the dataset combines both sources; the gateway is mandatory for the SQL Server component. Without it, scheduled refresh cannot access the on-premises data, and the dataset will fail to refresh automatically.

Answer

Use Power Automate to refresh the dataset after the CSV is updated.

Answer

Enable incremental refresh for the SQL Server table to reduce refresh time.

Answer

Configure a scheduled refresh in the dataset settings. The gateway is not required because the CSV file is in SharePoint Online.

Question 9

You are creating a Power BI report for a marketing team. The data is stored in a folder of CSV files on a network drive that is accessible from your computer. You need to combine all CSV files into a single table in Power BI. The files have the same structure. What should you do in Power Query?

Accepted Answer

Use the 'Combine Files' transform after connecting to the folder.. Power Query's 'Combine Files' transform is specifically designed to handle multiple CSV files with identical schemas stored in a folder. When you connect to the folder as a data source, Power Query automatically generates a sample file query and a transformation function that applies the same steps to all files, then combines the results into a single table. This is the most efficient and recommended approach for this scenario, avoiding manual appending or merging.

Answer

Connect to each CSV file individually and use Append Queries.

Answer

Use Merge Queries to join the files based on a common column.

Answer

Change the data source settings to treat the folder as a single data source.

Question 10

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 11

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 12

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 13

You are a data analyst at an online retailer. You import a CSV file containing product reviews into Power BI Desktop. The file has a column named ReviewDate that currently loads as the Text data type, with values formatted like '2024-07-15T09:30:00Z'. You need to change this column to the Date/Time/Timezone data type, but when you select that type in Power Query, the transformation fails for many rows. You need to resolve the failure while preserving the original timestamp data. What should you do?

Accepted Answer

Replace the trailing 'Z' with '+00:00' in the column, then change the data type to Date/Time/Timezone.. The Date/Time/Timezone type in Power Query requires an explicit numeric offset such as +00:00, not the ISO 8601 Z shorthand. Replacing Z with +00:00 preserves the UTC instant and lets the conversion succeed. The other approaches either misinterpret the format, discard required time data, or produce a timezone-less result.

Answer

Add a custom column that uses DateTime.FromText on the original values and then delete the original column.

Answer

Change the column to the Date data type instead, which discards the time and timezone portions and avoids the error.

Answer

Use the Locale option in the Change Type dialog and select English (United States) before applying the Date/Time/Timezone type.

Question 14

You are a Power BI data analyst at a university. You import a table named Enrollments from an on-premises SQL Server database. The table contains a column named StudentEmail that is stored as text and sometimes has leading or trailing spaces. You need to remove these spaces so that the column can be used to create a relationship with a Students table that also has a StudentEmail column. You must perform this cleanup in Power Query, and the transformation must apply to the entire column without creating a new column. Which transformation should you use?

Accepted Answer

On the Transform tab, select Format and then Trim.. Trim is the Power Query transformation that removes leading and trailing whitespace from text values in place. Applying it from the Transform tab modifies the existing StudentEmail column, so the cleaned values can match the Students table and support the relationship. The Add Column variant would create a separate column, and Clean or Replace Values do not correctly remove only the surrounding spaces.

Answer

On the Add Column tab, select Format and then Trim.

Answer

On the Transform tab, select Replace Values and replace a space with an empty value.

Answer

On the Transform tab, select Format and then Clean.

Question 15

You are a Power BI data analyst for a subscription software company. You import a table named Subscriptions from an OData feed. The table contains a column named BillingPeriod that stores values such as 'Monthly', 'Annual', and 'Quarterly'. A report author needs a numeric column that converts each value to the number of months in the billing period (1, 12, and 3 respectively) so that revenue can be normalized. You must add this column in Power Query without changing the source system. What should you do?

Accepted Answer

Add a conditional column that maps each text value to its corresponding number of months, then change the new column's data type to Whole Number.. A conditional column in Power Query explicitly maps each billing period label to its numeric month count, which is deterministic and easy to audit. Setting the new column to Whole Number makes it usable for arithmetic in the model. The other options either rely on fragile inference, fail outright on non-numeric text, or place the logic in the wrong layer.

Answer

Use the Column From Examples feature, entering '1' next to 'Monthly' and letting Power BI infer the remaining mappings.

Answer

Create a DAX calculated column in the semantic model that uses SWITCH to translate each billing period into months.

Answer

Change the data type of the BillingPeriod column to Whole Number and rely on Power Query's automatic value conversion.