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

Question 1

You are designing a Power BI semantic model that uses a large fact table from Azure SQL Database. The table includes a date column. You need to ensure that the model supports time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR. What is the recommended approach?

Accepted Answer

Create a separate date table and mark it as a date table in the model.. Option D is correct because time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR require a separate date table with a contiguous date range marked as the date table in the model. This ensures that DAX can correctly calculate time-based aggregations across all dates, even if the fact table has gaps or missing dates. Without a marked date table, these functions may return incorrect or blank results.

Answer

Use the 'Add Calendar' function in Power Query and rely on auto-date/time.

Answer

Use DirectQuery mode and rely on the SQL Server date functions.

Answer

Use the built-in date hierarchy from the fact table's date column.

Question 2

You are configuring a Power BI dataset with incremental refresh. The above JSON shows part of the M script parameters. The dataset uses a single SQL Server data source. You need to ensure that incremental refresh works correctly. What must you do?

Accepted Answer

Rename the parameter 'StartDate' to 'RangeStart' and add a 'RangeEnd' parameter.. Incremental refresh in Power BI requires two special date/time parameters named 'RangeStart' and 'RangeEnd' (case-sensitive). The JSON snippet shows a parameter named 'StartDate', which is not recognized by the incremental refresh engine. Renaming it to 'RangeStart' and adding a 'RangeEnd' parameter allows Power BI to filter the data source query dynamically during refresh, ensuring only the changed or new rows are loaded.

Answer

Change the connection string to use a SQL account instead of integrated security.

Answer

Enable query folding on the SQL Server source.

Answer

Define the parameters in the Power Query editor instead of the JSON file.

Question 3

You are importing data from a CSV file that contains a column 'OrderDate' with dates in the format 'MM/dd/yyyy'. Some rows have invalid dates like '02/30/2023'. What is the best way to handle these errors in Power Query?

Accepted Answer

Use 'Replace Errors' to replace error values with null.. Option A is correct because 'Replace Errors' in Power Query allows you to replace error values (which occur when Power Query fails to convert an invalid date like '02/30/2023' to the Date type) with null. This preserves the rest of the data and keeps the query running without interruption, while clearly marking invalid entries for later handling or analysis.

Answer

Remove rows with errors using 'Remove Rows' > 'Remove Errors'.

Answer

Change the data type to 'Date' and ignore errors.

Answer

Filter the column to exclude rows where the date is invalid after type conversion.

Question 4

You are preparing data from multiple sources for a Power BI report. You need to create a star schema with a single fact table and several dimension tables. Which of the following is a best practice when designing the data model?

Accepted Answer

Ensure each dimension table has a unique key and contains descriptive attributes.. In a star schema, dimension tables should have a unique key (surrogate or natural) and contain descriptive attributes to enable filtering and grouping in Power BI. This ensures efficient relationships with the fact table and supports intuitive report interactions. Option C directly aligns with this best practice.

Answer

Include calculated measures in dimension tables.

Answer

Normalize dimension tables into multiple related tables.

Answer

Use natural keys from the source system as the primary key in dimension tables.

Question 5

You are defining a Power BI dataset using a JSON policy for deployment pipelines. The above snippet defines a table named 'Sales' with a parameterized query. When you deploy this dataset to production, the refresh fails. What is the most likely cause?

Accepted Answer

The parameter value is not provided in the dataset settings.. Option D is correct because when a Power BI dataset uses a parameterized query, the parameter value must be explicitly provided in the dataset settings under the 'Parameters' section. If the parameter value is not supplied or is missing after deployment to production, the refresh will fail because the query cannot resolve the parameter. This is a common oversight when moving datasets between pipeline stages.

Answer

The SQL source does not support parameterized queries.

Answer

The parameter type should be 'Text' instead of 'Int64'.

Answer

The mode 'Import' is not allowed with parameterized queries.

Question 6

You are cleaning data in Power Query. A column contains customer names with inconsistent capitalization (e.g., 'john smith', 'JANE DOE'). You need to standardize the names to proper case (first letter uppercase, rest lowercase). Which transformation should you use?

Accepted Answer

Use 'Format' > 'Capitalize Each Word'.. The 'Capitalize Each Word' transformation in Power Query converts the first letter of each word to uppercase and the rest to lowercase, which is exactly what proper case requires. This is the correct choice because it directly addresses the need to standardize inconsistent casing (e.g., 'john smith' becomes 'John Smith', 'JANE DOE' becomes 'Jane Doe').

Answer

Use 'Format' > 'Trim'.

Answer

Use 'Format' > 'Lowercase'.

Answer

Use 'Format' > 'Uppercase'.

Question 7

You are connecting to a data source that contains Personally Identifiable Information (PII). You need to ensure that only authorized users can view the data in Power BI reports. Which TWO actions should you take?

Accepted Answer

Define row-level security (RLS) roles in Power BI Desktop.. Options A and D are correct. Row-level security (RLS) restricts data access at the row level based on user roles. Object-level security (OLS) can hide entire tables or columns. Option B is wrong because data masking in Power Query is not a native feature; it must be done manually. Option C is wrong because sensitivity labels protect the report content but do not restrict data access. Option E is wrong because encryption at rest is a general security measure but does not control user access.

Answer

Enable encryption at rest for the dataset.

Answer

Use Power Query to mask PII columns by replacing values with '***'.

Answer

Apply sensitivity labels to the dataset.

Question 8

You are developing a Power BI semantic model that uses a large fact table from Azure Synapse Analytics. You need to optimize the model for performance. Which THREE actions should you take?

Accepted Answer

Disable auto-date/time feature for the model.. Option A is correct because disabling the auto-date/time feature prevents Power BI from automatically creating hidden date tables for each date column, which can significantly increase model size and processing time. This is especially important when working with large fact tables from Azure Synapse Analytics, as it reduces memory consumption and improves query performance by eliminating unnecessary overhead.

Answer

Combine the fact table with dimension tables into a single wide table.

Answer

Create calculated columns in the fact table instead of in Power Query.

Question 9

You are preparing data from a REST API that returns JSON. The API has a pagination mechanism using a 'nextPageToken' in the response. You need to ingest all data into Power BI. Which TWO methods can you use to handle pagination in Power Query?

Accepted Answer

Write a recursive M function that calls Web.Contents and follows the next page token.. Option D is correct because you can write a recursive M function that repeatedly calls Web.Contents, extracts the 'nextPageToken' from each JSON response, and constructs the next request URL until no token is returned. This approach gives you full control over the pagination logic, which is necessary when the API does not support Power Query's built-in pagination detection.

Answer

Enable automatic pagination detection in Power Query.

Answer

Configure incremental refresh to load data in batches.

Answer

Use DirectQuery to connect to the API and rely on the server to paginate.

Question 10

You are using Copilot for Power BI to assist with data preparation. Which THREE tasks can Copilot help you with?

Accepted Answer

Generate DAX expressions for calculated columns and measures.. Copilot for Power BI can generate DAX expressions for calculated columns and measures by interpreting natural language prompts, which helps users create complex formulas without deep DAX knowledge. This is a core feature of Copilot's AI assistance in Power BI Desktop.

Answer

Set up incremental refresh policies.

Answer

Configure data source credentials for refresh.

Question 11

You are importing data from a folder containing multiple CSV files with identical structure. You use the 'Combine files' transform in Power Query. Which TWO statements are true about this process?

Accepted Answer

Power Query creates a function that applies the same transformations to each file.. Option D is correct because when you use the 'Combine files' transform in Power Query, it automatically generates a function that applies the same transformations (e.g., promoting headers, changing data types) to each CSV file in the folder. This function is invoked for every file, ensuring consistent data shaping across all files.

Answer

The sample file is automatically selected as the first file in alphabetical order.

Answer

Only the sample file is imported; other files are ignored.

Answer

The combine process uses Power Automate to merge files.

Question 12

You are designing a data model for a sales analysis report. The source data includes a table with Sales, Product, Customer, and Date information in a single flat table. You need to transform this into a star schema with separate dimension tables. Which THREE actions should you take?

Accepted Answer

Create a Product dimension table by selecting distinct product attributes and assigning a surrogate key.. Option A is correct because creating a Product dimension table by selecting distinct product attributes and assigning a surrogate key is a fundamental step in building a star schema. This process normalizes the flat table by separating product-related attributes into a dedicated dimension, which reduces redundancy and improves query performance in Power BI. The surrogate key (e.g., an integer identity column) ensures a stable, unique identifier for each product, independent of any changes in the source system's natural keys.

Answer

Merge the Customer and Product dimensions into one table to reduce the number of tables.

Answer

Keep all columns in a single table to avoid relationships.

Question 13

You are preparing data from an on-premises SQL Server database for a Power BI report. The source table contains 10 million rows and you only need the last 3 months of data. Which approach minimizes the data load time and memory consumption in the dataset?

Accepted Answer

Use a SQL query in Power Query that includes a WHERE clause to filter the last 3 months.. Option B is correct because query folding ensures that the filter is pushed to the source database, reducing the amount of data transferred. Option A is wrong because loading all data and then filtering in Power Query still transfers the full dataset. Option C is wrong because it still imports all data. Option D is wrong because importing all data and using a filter on the report is less efficient.

Answer

Use Power Query to group by month and then filter after aggregation.

Answer

Load all data into Power Query and apply a filter on the date column using the 'Remove Rows' feature.

Answer

Load all data into Power BI and then use a report-level filter to show only the last 3 months.

Question 14

You are connecting to a SharePoint folder containing 100 Excel files. Each file has a similar structure but different column names. What is the best practice to combine these files into a single table while preserving the data?

Accepted Answer

Use Power Query's 'Combine Files' feature, selecting a sample file and promoting headers, then transforming column names to a standard set.. Option A is correct because Power Query's 'Combine Files' feature is designed specifically for this scenario: it uses a sample file to infer the transformation logic (e.g., promoting headers), then applies that logic to all files in the folder. By transforming column names to a standard set within the sample file step, you ensure consistent column names across all files, preserving data integrity while combining them into a single table.

Answer

Load each file as a separate table and create relationships in the model.

Answer

Use Power Query's 'Merge Queries' to join all files into one table.

Answer

Use 'Append Queries' to stack all files, then rename columns manually.

Question 15

You are designing a data model for a sales analysis report. The source data includes a Sales table with columns: OrderID, CustomerID, ProductID, OrderDate, Quantity, and UnitPrice. You also have a Customers table and a Products table. Which approach best optimizes query performance and storage?

Accepted Answer

Create a star schema with Sales as a fact table and Customers and Products as dimension tables.. Option A is correct because a star schema optimizes query performance and storage in Power BI by separating transactional data (Sales fact table) from descriptive attributes (Customers and Products dimension tables). This reduces data duplication, improves compression, and enables efficient aggregations and filter propagation via one-to-many relationships, which is the recommended modeling approach for analytical workloads.

Answer

Create separate fact tables for each dimension.

Answer

Create a single flat table by joining all columns from Sales, Customers, and Products into one table.

Answer

Create a snowflake schema by normalizing Customers into multiple related tables.