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

Question 1

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?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

In Power Query, apply a date filter on the source query so only current year data is imported.. 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 2

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 volume of data that Power Query must read, transform, and load into the model, lowering memory and refresh cost. Option D is correct because disabling 'Enable load' on intermediate queries prevents those staging tables from being materialized into the data model, so refresh avoids loading data that no report consumes. Option E is correct because applying filters as early as possible (ideally at the source or in the first steps) reduces the number of rows flowing through subsequent transformation steps, which is the most effective way to speed up refresh. Option A is not correct because sorting rows does not improve refresh performance in Power Query; compression benefits in the VertiPaq engine come from column cardinality and encoding, not from row order. Option B is not correct because merging queries before filtering typically increases the work done on larger, unfiltered datasets; filtering first reduces the rows that must be joined.

Answer

Sort data in ascending order to improve compression.

Answer

Merge queries before filtering.

Question 3

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

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. 'Replace Values' in Power Query Editor is the most efficient way to replace null values in a column with 0. It directly transforms the column in a single step without requiring additional logic or table scans, and it generates a clean M code step (Table.ReplaceValue) that operates natively on the column's nulls.

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

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 10

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 11

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 12

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

Accepted Answer

Aggregate transaction data to daily level. Option B is correct because aggregating transaction-level rows into daily summaries collapses many individual records into far fewer rows, directly shrinking the dataset's row count and memory footprint in the Power BI model. Option D is correct because removing unused columns reduces the model's columnar cardinality and compression overhead, since Power BI's VertiPaq engine stores and compresses each column separately, so eliminating unnecessary columns lowers overall model size. Option A is incorrect because retaining all historical data increases row volume and model size rather than reducing it. Option C is incorrect because calculated columns are materialized and stored in the model, adding to its size instead of shrinking it. Option E is incorrect because DirectQuery does not reduce dataset size—it leaves data in the source and queries it on demand, and it is a connectivity mode rather than a data-reduction technique.

Answer

Include all historical data

Answer

Add calculated columns

Answer

Use DirectQuery mode

Question 13

Which TWO are best practices when preparing data for Power BI?

Accepted Answer

Promote headers if the first row contains column names. Option B is correct because promoting headers when the first row contains column names ensures Power BI treats that row as field names rather than data, giving properly named columns for modeling and visuals. Option C is correct because splitting columns that contain multiple values into separate rows normalizes the data into a proper tabular shape, which is required for accurate aggregations and relationships in Power BI. Option A is not a best practice because data types should generally be set as early as possible, not after all transformations, to avoid errors and unexpected coercion. Option D is wrong because merging queries is a valid and often efficient technique; lookups are not always preferable. Option E is wrong because unpivoting is a recommended transformation to convert wide (crosstab) data into a tall, analysis-friendly structure.

Answer

Change data type of all columns after all transformations

Answer

Avoid merging queries; always use lookups

Answer

Avoid unpivoting columns; keep data wide

Question 14

Which TWO are valid reasons to use a dataflow in Power BI when preparing data?

Accepted Answer

To handle large data volumes that exceed dataset limits. Option A is correct because dataflows store transformed data in Common Data Model (CDM) entities in Azure Data Lake Storage Gen2, so they can process and persist large data volumes that would otherwise exceed a single dataset's size or memory limits. Option B is correct because a dataflow's CDM entities can be consumed by multiple Power BI datasets, letting you define the transformation logic once and reuse the same cleansed data across many datasets instead of duplicating ETL work. Option C is not a valid reason, since dataflows that connect to on-premises sources still require an on-premises data gateway; dataflows do not eliminate that need. Option D is not correct because dataflows are refreshed on a scheduled (batch) basis and are not a mechanism for real-time streaming, which is handled by streaming datasets or push datasets. Option E is not correct because data lineage is a metadata/governance feature surfaced automatically in the workspace, not something a dataflow is used to enforce.

Answer

To avoid using an on-premises data gateway

Answer

To achieve real-time data refresh

Answer

To automatically enforce data lineage

Question 15

You have a Power BI dataset that uses data from Microsoft Excel files stored in SharePoint Online. Users report that the data is not refreshing as scheduled. You verify that the gateway is installed and running. What is the most likely cause of the refresh failure?

Accepted Answer

The data source credentials are not provided in the gateway.. Even when the gateway is installed and running, it must have valid data source credentials configured for the SharePoint Online Excel files. Without these credentials, the gateway cannot authenticate to SharePoint Online to retrieve the data, causing the scheduled refresh to fail. The gateway uses the stored credentials to connect to the data source during each refresh cycle.

Answer

The gateway is not running.

Answer

The gateway does not support SharePoint Online data sources.

Answer

The gateway is not configured to use the on-premises data source type.