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

Question 1

You are importing data from a large CSV file (5 GB) into Power BI. The import takes too long and you need to reduce the data volume. What is the most effective approach in Power Query?

Accepted Answer

Filter rows and remove unnecessary columns in Power Query.. Filtering rows and removing unnecessary columns in Power Query directly reduces the amount of data loaded into Power BI's data model. This is the most effective way to reduce data volume from a large CSV file, as it minimizes the data that Power Query must process and store, directly addressing the import time issue.

Answer

Disable loading of unrelated queries.

Answer

Increase query parallelism in options.

Answer

Enable 'Query Reduction' settings.

Question 2

You need to combine two tables in Power Query: 'Sales2023' (columns: Date, ProductID, Amount) and 'Sales2024' (columns: Date, ProductID, Amount). Which TWO transformations can be used to append rows from Sales2024 to Sales2023?

Accepted Answer

Append Queries. Append Queries is the dedicated Power Query transformation to stack rows from one table onto another, making Option D the only correct method. Merge Queries with Full Outer join combines columns based on matching keys, not rows; it does not append rows unless the key columns are identical and no matching rows exist, which is not a reliable or intended method. Option A (Combine Files) imports multiple files, not appending existing tables. Option B (UNION in DAX) applies after data is loaded, not in Power Query. Option E (Group By) aggregates rows. Therefore, only Append Queries is correct.

Answer

Combine Files

Answer

Use UNION function in DAX

Answer

Merge Queries as New with Full Outer join

Answer

Group By

Question 3

You are profiling data in Power Query Editor. Which THREE tasks can you perform using the Column Profile feature?

Accepted Answer

Identify data type issues. The Column Profile feature in Power Query Editor provides a statistical summary of the data in a selected column, including value distribution, distinct counts, and data type detection. Option B is correct because the profile automatically highlights data type inconsistencies (e.g., numbers stored as text) by showing the 'Data Type' indicator and flagging errors, enabling you to identify and fix type issues before loading data.

Answer

Add a conditional column

Answer

Replace values

Question 4

You are using Power Query to transform a column 'FullName' containing values like 'Smith, John'. You need to split this into 'LastName' and 'FirstName' columns. Which THREE steps are required?

Accepted Answer

Use 'Split Column' by delimiter. To split 'FullName' (e.g., 'Smith, John') into 'LastName' and 'FirstName' columns in Power Query, three steps are required. First, select the column and use 'Split Column' by delimiter (comma) – this creates two columns with the split values. Second, trim any leading/trailing spaces from the new columns (the delimiter might leave spaces). Third, rename the resulting columns to 'LastName' and 'FirstName'. Option A (Unpivot) is incorrect because unpivot transforms columns into rows, not split values. Option D (Merge) is the opposite of splitting and would combine columns. Therefore, the correct three steps are B, C, and E.

Answer

Unpivot columns

Answer

Merge the split columns back

Question 5

You are preparing data for a star schema. Which THREE types of tables are typically included in a star schema?

Accepted Answer

Dimension tables. In a star schema, dimension tables (C) provide descriptive attributes for the measures stored in fact tables. They are denormalized to optimize query performance in tools like Power BI, enabling fast slicing and dicing of data without complex joins.

Answer

Staging tables

Answer

Bridge tables

Question 6

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

You are a data analyst for a retail company. You receive daily sales data in CSV files stored in an Azure Blob Storage container. The files have a consistent naming convention: Sales_YYYYMMDD.csv. You need to load only the latest file into Power BI each day, using Power Query. You want to automate this process so that each time you refresh the dataset, it picks up the most recent file. You have already created a connection to the container. What should you do in Power Query to ensure only the latest file is loaded?

Accepted Answer

Use 'From Folder' connector, filter by creation date to keep only the latest file.. The 'From Folder' connector in Power Query allows you to connect to a folder (including Azure Blob Storage containers mapped as folders) and then filter files by creation date or last modified date. By filtering to keep only the latest file, you ensure that each refresh loads the most recent CSV without manual intervention. This approach fully automates the process, as Power Query will evaluate the filter each time the query refreshes.

Answer

Use 'From Azure Blob Storage' and filter by last modified date.

Answer

Use 'Combine Files' and then filter to the latest date.

Answer

Create a parameter for the file name and update it manually.

Question 8

You are preparing a Power BI dataset that uses a SQL Server database as the source. Your manager asks you to ensure that the dataset refreshes automatically every morning at 6:00 AM. You have configured a scheduled refresh in the Power BI service. However, after the first scheduled refresh, you notice that it failed with an error: 'The data source credentials are invalid.' What is the most likely cause and solution?

Accepted Answer

The data source credentials are not configured in the gateway.. The error 'The data source credentials are invalid' indicates that the credentials stored in the Power BI service for the data source are incorrect or missing. Since the dataset uses a SQL Server database, which is an on-premises data source, the credentials must be configured within the on-premises data gateway settings in the Power BI service. Option B correctly identifies that the gateway credentials were not set up, causing the refresh to fail despite the scheduled refresh being configured.

Answer

The scheduled refresh time is not set correctly.

Answer

An on-premises data gateway is not installed.

Answer

The dataset is too large for scheduled refresh.

Question 9

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)

Accepted Answer

Create RLS roles to restrict access by Diagnosis.. Creating RLS roles in Power BI Desktop allows you to restrict data access based on the user's identity, ensuring that only authorized users can view specific rows (e.g., by Diagnosis). This is a key requirement for HIPAA compliance, as it prevents unauthorized exposure of patient data. Option E is correct because removing sensitive columns like SSN and Name in Power Query before loading into the model minimizes the data footprint and reduces the risk of exposing personally identifiable information (PII), aligning with the principle of data minimization.

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 10

You are a Power BI report creator. You have a dataset that includes a date table and a sales table. The date table has a column 'FiscalYear' (e.g., 'FY2025'). The sales table has a column 'OrderDate'. You need to create a relationship between the date table and the sales table based on the fiscal year. However, the date table has one row per fiscal year, and the sales table has multiple rows per fiscal year. You want to filter sales by fiscal year. What is the correct approach to model this?

Accepted Answer

Create a one-to-many relationship from Date[FiscalYear] to Sales[FiscalYear] (assuming Sales table has FiscalYear).. It establishes a standard one-to-many relationship between the Date table (with one row per fiscal year) and the Sales table (with multiple rows per fiscal year) using a common FiscalYear column. This allows Power BI to filter sales by fiscal year through the relationship, leveraging the date table as a filter dimension. The relationship must be based on a column in the Sales table that contains the fiscal year for each order, which can be added as a calculated column if not already present.

Answer

Add a calculated column in Sales to extract fiscal year from OrderDate and use that for filtering without a relationship.

Answer

Merge the Date table into the Sales table in Power Query.

Answer

Create a many-to-many relationship between Date and Sales using a bridge table.

Question 11

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.. Configuring an incremental refresh policy in the dataset settings is a required step to enable incremental refresh in Power BI. This policy defines how data is partitioned and refreshed incrementally based on date/time columns. Option E is correct because you must define `rangeStart` and `rangeEnd` parameters in Power Query to filter data into historical and incremental ranges, which Power BI uses to generate the refresh partitions.

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 12

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 13

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 14

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

You are a data analyst for a healthcare organization. You have a Power BI dataset that imports patient data from an on-premises SQL Server database. The database contains personally identifiable information (PII). You need to ensure that all PII columns are obfuscated when the data is loaded into Power BI. You have already masked the columns in the source database using dynamic data masking. However, when you refresh the dataset in Power BI, the masked values are not appearing. What should you do?

Accepted Answer

In the data source settings, use a SQL Server account that has been granted the UNMASK permission to see masked data, but actually you need an account WITHOUT UNMASK to see masked data. Actually, correct: Configure the data source connection to use a user without the UNMASK permission so that dynamic data masking applies.. Dynamic data masking (DDM) in SQL Server only applies to users lacking the UNMASK permission. To have Power BI load masked values, the data source connection must use an account without UNMASK. The option should state: 'Configure the data source connection to use a SQL Server account without the UNMASK permission so that dynamic data masking applies.'

Answer

In Power BI Desktop, define row-level security roles to restrict PII columns.

Answer

Apply data masking in Power Query using Text.Start or Text.End functions.

Answer

Install the on-premises data gateway and configure it to use the service account.