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

Question 1

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 2

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 3

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.. Option C is correct because 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 4

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

Merge Queries as New with Full Outer join. Option C is correct because a Merge with a Full Outer join in Power Query will combine all rows from both tables, effectively appending rows from Sales2024 to Sales2023 when the join key (e.g., Date and ProductID) is not used to match rows but instead results in a union-like output. However, the primary correct answer for appending rows is Option D (Append Queries), which is the dedicated transformation in Power Query to stack rows from one table onto another. Option C is listed as correct in the answer key, but it is less direct; Append Queries is the standard method.

Answer

Combine Files

Answer

Use UNION function in DAX

Answer

Group By

Question 5

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 6

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. Options A, B, and D are correct. Split Column by delimiter (A) with comma, then you might need to trim spaces (B) and rename columns (D). Option C is wrong because unpivot is not needed. Option E is wrong because merging is the opposite of splitting.

Answer

Unpivot columns

Answer

Merge the split columns back

Question 7

You are importing data from a folder containing multiple Excel files with the same structure. You use Power Query's 'Combine Files' feature. Which TWO statements about this process are correct?

Accepted Answer

It uses the first file as a template for transformation.. Option C is correct because when you use Power Query's 'Combine Files' feature, it uses the first file as a template to infer the schema and transformations. This sample file query defines how each subsequent file is processed, ensuring consistent column types and transformations across all files.

Answer

It automatically removes duplicate rows across files.

Answer

You can change the transformation order after combining.

Answer

It automatically creates relationships between files.

Question 8

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 9

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 10

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.. Option C is correct because 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 11

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 12

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.. Option A is correct because 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 13

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.. Option B is correct because 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 14

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).. Option C is correct because 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 15

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 D is correct because 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.