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

Question 1

You are preparing data for a report that requires joining sales data from a SQL database with customer demographic data from a CSV file. The CSV file contains duplicate customer IDs. How should you handle duplicates to ensure accurate analysis?

Accepted Answer

In Power Query, remove duplicates from the CustomerID column in the CSV table before merging.. Option D is correct because removing duplicates from the CustomerID column in the CSV table before merging ensures a one-to-one or one-to-many relationship with the SQL sales data, preventing inflated row counts and inaccurate aggregations. In Power Query, the 'Remove Duplicates' operation keeps the first occurrence by default, which is appropriate when no other filtering criteria are specified. This step is critical for maintaining referential integrity in the data model.

Answer

Group by CustomerID and aggregate all columns.

Answer

In Power Query, use 'Merge Queries' and select 'Merge with duplicates' option.

Answer

Load both tables as-is and create a relationship in the data model.

Question 2

You need to combine data from three different SharePoint lists into a single table for analysis. The lists have different column names but contain similar data. What is the best approach in Power Query?

Accepted Answer

Use 'Append Queries' and then rename columns to match.. Append Queries is the correct approach because it stacks rows from multiple tables (SharePoint lists) into a single table, which is exactly what you need when combining data with similar structures but different column names. After appending, you can rename columns to unify the schema. This is the standard Power Query method for row-level concatenation of tables.

Answer

Use 'Unpivot Columns' to transform the data.

Answer

Use 'Group By' to combine values.

Answer

Use 'Merge Queries' on a common key column.

Question 3

You connect to a large Azure SQL Database table with over 100 million rows. You need to create a report that shows sales by month for the current year only. Which data reduction technique should you use in Power Query to minimize data load?

Accepted Answer

In Power Query, apply a date filter on the source query so only current year data is imported.. Option B is correct because applying a date filter in Power Query at the source query level ensures that only rows from the current year are imported into the Power BI data model. This reduces the data volume from over 100 million rows to a fraction, minimizing memory usage and improving refresh performance. Power Query pushes the filter down to the Azure SQL Database using a WHERE clause in the SQL query, so only the filtered data is transferred over the network.

Answer

Import all data and then remove columns that are not needed.

Answer

Load all data and filter using a visual-level filter in the report.

Answer

Use a calculated table in DAX to filter the data.

Question 4

You have a Power BI dataset that uses DirectQuery to an Azure SQL Database. Users complain that reports take too long to load. You suspect that the database is overwhelmed by queries. What should you do to improve performance while keeping DirectQuery?

Accepted Answer

Reduce the number of visuals per page and apply slicers to limit data.. Option A is correct because reducing the number of visuals per page and applying slicers to limit data reduces the number of DAX queries sent to the Azure SQL Database via DirectQuery. Each visual generates at least one query, so fewer visuals mean fewer concurrent queries, and slicers add WHERE clauses that reduce the result set size, lowering the load on the database.

Answer

Increase the Power BI Premium capacity size.

Answer

Create aggregations in the dataset.

Answer

Change the storage mode to Import.

Question 5

You are importing data from a CSV file that contains a column 'Date' with values in different formats (e.g., '01/15/2024', '2024-01-15'). You need to ensure all dates are recognized correctly. What should you do in Power Query?

Accepted Answer

In Power Query, set the data type to 'Date' and use 'Using Locale' to specify the correct locale for each file.. Option A is correct because Power Query's 'Using Locale' feature allows you to specify the cultural context (e.g., 'en-US' for MM/DD/YYYY or 'de-DE' for DD.MM.YYYY) when setting a column's data type to 'Date'. This ensures that dates in various formats, such as '01/15/2024' (US) and '2024-01-15' (ISO 8601), are interpreted correctly during import, as the locale dictates the expected date order and separator.

Answer

Change the data source to a SQL database.

Answer

Split the column into multiple columns and merge them back.

Answer

Use 'Replace Values' to standardize the date formats.

Question 6

Which TWO data sources can you connect to from Power BI Desktop using a native connector? (Select two.)

Accepted Answer

Azure Blob Storage. Azure Blob Storage is a correct answer because Power BI Desktop includes a native connector for Azure Blob Storage, allowing direct connection to blob containers without needing a gateway or custom script. This connector uses the Azure storage REST API to access data stored in blobs, enabling import of CSV, JSON, and Parquet files directly.

Answer

MySQL database (on-premises)

Answer

Oracle database (on-premises)

Answer

MongoDB

Question 7

Which THREE actions in Power Query Editor can improve the performance of data refresh? (Select three.)

Accepted Answer

Remove columns that are not used in the report.. Option C is correct because removing unused columns reduces the amount of data loaded into the Power Query engine and the data model, directly decreasing memory usage and refresh time. Power Query processes all columns in a query step, so eliminating unnecessary columns early minimizes the data footprint for subsequent transformations and the final load.

Answer

Sort data in ascending order to improve compression.

Answer

Merge queries before filtering.

Question 8

Which TWO transformations in Power Query are most likely to cause Query Folding to stop? (Select two.)

Accepted Answer

Pivot columns.. Option A is correct because Pivot columns in Power Query often require a transformation that cannot be translated into a native SQL query, causing Query Folding to break. When you pivot a column, the engine must restructure data from rows to columns, which typically involves operations like grouping and aggregation that many source systems (e.g., SQL Server) cannot fold back to a single SELECT statement. This forces Power Query to pull all data locally before applying the pivot, stopping folding.

Answer

Filter rows based on a date column.

Answer

Remove columns.

Answer

Rename a column.

Question 9

You review the output of a PowerShell command listing datasets in a Power BI workspace. Which dataset can be refreshed using the Power BI service and also supports push data via the REST API?

Accepted Answer

None of the datasets satisfy both conditions.. Option A is correct because none of the listed datasets can be refreshed via the Power BI service and also support push data via the REST API. Push datasets are designed for real-time streaming and do not support scheduled or on-demand refresh in the service; they are updated only by pushing new data through the REST API. Conversely, datasets that support refresh (e.g., import or DirectQuery) cannot accept push data because their data source is external and not designed for real-time REST API ingestion.

Answer

Profit Report

Answer

Inventory Dataset

Answer

Sales Analysis

Question 10

You are preparing data for a report that needs to be refreshed every 30 minutes to meet near real-time requirements. Which Power BI feature should you use?

Accepted Answer

Use DirectQuery mode to connect to the source database.. DirectQuery mode allows Power BI to query the source database directly without importing data, enabling near real-time refreshes by executing queries against the source every time a report is interacted with or when the dataset is refreshed. This meets the 30-minute refresh requirement without the latency of data import, as the data remains in the source and is not copied into Power BI.

Answer

Import data and schedule refresh every 30 minutes.

Answer

Use a streaming dataset with real-time data.

Answer

Use a push dataset to send data from the source.

Question 11

You have a table with a column 'FullName' that contains names in the format 'Last, First'. You need to split this column into 'LastName' and 'FirstName' columns. Which Power Query transformation should you use?

Accepted Answer

Split Column by Delimiter using comma.. Option B is correct because the 'Split Column by Delimiter' transformation in Power Query is specifically designed to divide a single text column into multiple columns based on a specified delimiter, such as a comma. In this case, the 'FullName' column contains names in the 'Last, First' format, so splitting by a comma delimiter will correctly separate the last name and first name into two distinct columns.

Answer

Pivot the FullName column.

Answer

Group By the FullName column and aggregate.

Answer

Extract first characters using 'Extract' transformation.

Question 12

You are analyzing a Power BI dataset definition. The dataset refreshes but recently started failing with the error 'The 'OrderDate' column of the table 'Orders' has a date value that is out of range.' You need to diagnose the issue. What is the most likely cause?

Accepted Answer

The source table contains a date value that is not recognized as a valid DateTime by Power BI.. The error message indicates that a date value in the 'OrderDate' column of the 'Orders' table is out of the range supported by Power BI's DateTime type (1/1/1900 00:00:00 to 12/31/9999 23:59:59.999). This typically occurs when the source contains a date like '0001-01-01' or a future date beyond the upper bound, which Power BI cannot convert to a valid DateTime. Option A is correct because the source table has a date value that is not recognized as a valid DateTime by Power BI, causing the refresh to fail.

Answer

The columns in the dataset definition do not match the source table.

Answer

The connection string is invalid.

Answer

The M expression has a syntax error.

Question 13

You are preparing data from a CSV file that contains date values in the format 'MM/dd/yyyy'. When you load the file into Power BI Desktop, the dates appear as text. What should you do to ensure the dates are recognized as date data type?

Accepted Answer

Change the column data type to Date in Power Query Editor. Option B is correct because changing the column data type to Date in Power Query Editor forces Power BI to interpret the text values as dates using the current locale settings. Since the CSV file contains dates in 'MM/dd/yyyy' format, Power Query can parse this pattern when the data type is explicitly set to Date, converting the text strings into the Date data type for proper time intelligence and filtering.

Answer

Use 'Replace Values' to replace '/' with '-'

Answer

Split the column by delimiter '/'

Answer

Promote the first row as headers

Question 14

You are connecting to a SQL Server database using Import mode. The source table contains a column 'SalesAmount' with a few null values. You need to replace nulls with 0 before loading. What is the most efficient step to achieve this in Power Query Editor?

Accepted Answer

Use 'Replace Values' to replace null with 0. Option A is correct because 'Replace Values' can replace null with 0. Option B is incorrect because 'Fill Down' fills with previous non-null value, not 0. Option C is incorrect because 'Transform > Replace Errors' replaces errors, not nulls. Option D is incorrect because adding a custom column is less efficient.

Answer

Use 'Replace Errors' with value 0

Answer

Use 'Fill Down' to propagate previous values

Answer

Add a custom column with an if statement

Question 15

Refer to the exhibit. The JSON policy shows configuration for an incremental refresh dataflow. You notice that the dataflow refresh takes longer than expected. Which setting should you adjust to potentially improve performance?

Accepted Answer

Increase maxParallelism to 20. Increasing maxParallelism to 20 allows the dataflow engine to process more partitions concurrently during an incremental refresh, reducing overall execution time. The default parallelism is often too low for large datasets, and raising this value can significantly improve throughput by utilizing available compute resources more efficiently.

Answer

Change jobType to 'FullRefresh' only

Answer

Reduce the pollingInterval to 15

Answer

Increase retryCount to 5