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

Question 1

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

Accepted Answer

Pivot columns.. 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 2

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

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. This creates a live connection, so reports always display the most current data from the source whenever a visual is rendered or filters are applied. It meets the near real-time requirement without needing a scheduled data refresh; data is not copied to Power BI and therefore there is no refresh latency.

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 4

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 5

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

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

Question 7

You are merging two tables in Power Query: 'Orders' and 'Customers'. You want to include only rows from Orders that have a matching CustomerID in Customers. Which join kind should you use?

Accepted Answer

Inner join. An inner join in Power Query returns only rows from both tables where there is a match on the join key. Since you want to include only rows from Orders that have a matching CustomerID in Customers, the inner join is the correct choice. It filters out any Orders rows without a corresponding CustomerID in the Customers table.

Answer

Full Outer join

Answer

Right Outer join

Answer

Left Outer join

Question 8

You have a Power BI dataset that uses DirectQuery mode. The underlying SQL view now includes a new column. What must you do to make the new column available in Power BI?

Accepted Answer

Refresh the dataset in Power BI Desktop. When a SQL view used in DirectQuery mode is updated with a new column, you must refresh the dataset in Power BI Desktop to pull the updated schema into the model. DirectQuery does not cache data, but the metadata (field list) is cached; refreshing the dataset in Power BI Desktop re-queries the source to discover the new column. Only then can you use the column in visuals or measures.

Answer

Schedule a daily refresh in the service

Answer

Create a new measure to reference the column

Answer

Publish the report again

Question 9

Refer to the exhibit. The Power Query M code connects to a SQL database and groups sales data. You notice the query is slow. Which change would most likely improve performance?

Accepted Answer

Push the Year filter into the SQL query using a native query. Pushing the Year filter into the SQL query via a native query reduces the amount of data transferred from SQL Server to Power Query. By filtering at the source, Power Query only loads the relevant rows, which minimizes memory usage and processing time. This leverages query folding, which is the most efficient way to handle large datasets in Power Query.

Answer

Remove the GroupedRows step to simplify

Answer

Remove the filter on Year to process all data

Answer

Perform grouping in SQL instead of Power Query

Question 10

You have a Power BI data model with a 'Sales' fact table and a 'Date' dimension. You need to create a calculated column in the 'Sales' table that shows the fiscal year based on a 'Date' column. The fiscal year starts on July 1. Which DAX expression should you use?

Accepted Answer

SWITCH(TRUE(), MONTH(Sales[Date]) >= 7, YEAR(Sales[Date]), YEAR(Sales[Date])-1). It uses SWITCH with TRUE() to evaluate a logical condition: if the month of the date is July or later (MONTH >= 7), it returns the current year; otherwise, it returns the previous year. This correctly implements a fiscal year starting on July 1, as required.

Answer

YEAR(Sales[Date])

Answer

YEAR(Sales[Date]) + 1

Answer

FORMAT(Sales[Date], "YYYY")

Question 11

You are importing data from an Excel workbook that has multiple worksheets. You only need data from the 'Sales' worksheet. When you connect via Power Query, all worksheets appear in the Navigator. What should you do to load only the 'Sales' worksheet?

Accepted Answer

Select the 'Sales' worksheet in Navigator and click 'Load'. In Power Query, the Navigator pane allows you to select specific worksheets or tables to load. Selecting the 'Sales' worksheet and clicking 'Load' imports only that data into your data model, avoiding unnecessary data. This is the most efficient method because it directly targets the required worksheet without extra steps.

Answer

Load all worksheets, then delete the unwanted ones

Answer

Select all worksheets and click 'Load'

Answer

Select the 'Sales' worksheet and click 'Transform Data'

Question 12

You are designing a Power BI solution for a retail company. The data includes point-of-sale transactions with columns: TransactionID, StoreID, ProductID, Quantity, SalesAmount, TransactionDate. The company wants to analyze sales by hour of day. What is the best way to prepare the time dimension?

Accepted Answer

Create a separate Time dimension table with a one-to-many relationship to the fact table. Creating a separate Time dimension table with a one-to-many relationship to the fact table follows the star schema best practice for Power BI. This approach allows efficient filtering and grouping by hour without bloating the fact table with calculated columns, and it supports time-based analysis across multiple fact tables if needed. The Time dimension can include columns like Hour, HourBucket, or PeriodOfDay, enabling intuitive slicers and drill-downs.

Answer

Add a calculated column extracting hour from TransactionDate in the fact table

Answer

Create a date dimension only and ignore time

Answer

Use DAX measures to extract hour on the fly

Question 13

You need to combine data from two tables in Power Query that have the same columns but different row sets. Which operation should you use?

Accepted Answer

Append Queries. Append Queries is the correct operation in Power Query when you need to combine two tables with identical columns but different row sets. It stacks the rows from the second table beneath the rows from the first table, effectively performing a union of the data. This is distinct from Merge Queries, which joins columns based on a key, and Group By, which aggregates data.

Answer

Union Queries

Answer

Group By

Answer

Merge Queries

Question 14

Which TWO actions can help reduce the size of a Power BI dataset when preparing data?

Accepted Answer

Aggregate transaction data to daily level. Aggregating transaction data to a daily level reduces the number of rows in the dataset, which directly decreases the storage footprint and improves refresh performance. Power BI compresses data more efficiently when cardinality is lower, and fewer rows mean smaller column dictionaries and reduced page compression overhead.

Answer

Include all historical data

Answer

Add calculated columns

Answer

Use DirectQuery mode

Question 15

Which THREE are valid methods to handle duplicate rows in Power Query when preparing data?

Accepted Answer

Use 'Remove Duplicates' based on key columns. The correct methods to handle duplicate rows in Power Query are: B: 'Remove Duplicates' based on key columns deletes duplicate rows; C: 'Keep Duplicates' retains only duplicate rows; E: 'Group By' can aggregate duplicate rows into a single row. Options A (Reverse Rows) and D (Sort Ascending) do not handle duplicates.

Answer

Use 'Reverse Rows'

Answer

Use 'Sort Ascending'