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

Question 1

You are working with a large dataset in Power BI that contains sales transactions. You need to create a measure that calculates the running total of sales over time. However, the data model does not include a separate date table, and you want to minimize the model size. What is the most efficient approach?

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

Accepted Answer

Create a separate date table and use the TOTALYTD function in a measure.. Creating a separate date table and using TOTALYTD leverages Power BI's time intelligence functions, which require a continuous date table marked as a date table. This approach is efficient because time intelligence functions are optimized for performance and minimize model size by avoiding calculated columns or complex row-context iterations. Without a date table, TOTALYTD cannot function correctly, as it relies on a proper date hierarchy.

Answer

Write a measure using VAR and FILTER with ALL to compute running total.

Answer

Create calculated columns for Year and Month, then use SUMX with FILTER.

Answer

Use the EARLIER function in a calculated column to compute running total.

Question 2

You are building a Power BI data model with multiple fact tables and dimension tables. One of the dimension tables has a one-to-many relationship with two fact tables, but the relationships are inactive. You need to create measures that use both fact tables and the dimension table without relying on user interactions to activate relationships. What should you do?

Accepted Answer

Use the USERELATIONSHIP function in the measure definition.. The USERELATIONSHIP function in DAX allows you to temporarily activate an inactive relationship within a measure definition. This enables you to use a dimension table with multiple fact tables without changing the model's default active relationships or relying on user interactions. By specifying the inactive relationship in the measure, you can perform accurate aggregations across both fact tables while maintaining the intended data model structure.

Answer

Merge the fact tables into one table.

Answer

Create calculated columns in the dimension table to relate to each fact table.

Answer

Change both relationships to active.

Question 3

You are importing data from a JSON file that contains nested arrays. You need to expand the arrays into separate rows while maintaining the parent information. What should you do?

Accepted Answer

In Power Query Editor, click the expand icon on the column and select 'Expand to New Rows'.. Power Query's 'Expand to New Rows' function is specifically designed to flatten nested arrays (e.g., lists or records within a JSON column) into separate rows while duplicating the parent row's other columns. This operation uses the `Table.ExpandListColumn` or `Table.ExpandRecordColumn` M function under the hood, which unpacks each element of the array into its own row, preserving the parent context.

Answer

Use the Pivot Column feature to transform the array.

Answer

Use the Merge Queries feature to combine tables.

Answer

Use the Group By feature to flatten the data.

Question 4

You are loading data from a SQL Server database into Power BI. You notice that the import takes a long time because the source table contains many rows. You only need a subset of rows based on a date filter. What should you do to improve performance?

Accepted Answer

Use a SQL query with a WHERE clause in the Power Query Editor.. Using a SQL query with a WHERE clause in Power Query Editor pushes the date filter down to the SQL Server database, reducing the amount of data transferred over the network and imported into Power BI. This query folding technique leverages the database engine's indexing and processing power, which is far more efficient than filtering after import. By retrieving only the necessary rows upfront, you minimize both network latency and memory consumption in Power BI.

Answer

Remove unnecessary columns in Power Query.

Answer

Load all data and then apply a filter in Power BI.

Answer

Enable incremental refresh on the dataset.

Question 5

You have a Power BI data model that contains a fact table and a date dimension. The fact table has a column 'OrderDate' and the date dimension has a column 'Date'. You need to create a measure that calculates the total sales for the last complete month. The date dimension is marked as a date table. What DAX expression should you use?

Accepted Answer

CALCULATE(SUM(Sales[Amount]), PREVIOUSMONTH('Date'[Date])). PREVIOUSMONTH returns a set of dates for the previous month relative to the last date in the current filter context. Since the date dimension is marked as a date table, time intelligence functions like PREVIOUSMONTH work correctly. CALCULATE then evaluates SUM(Sales[Amount]) over that month-long period, giving total sales for the last complete month.

Answer

CALCULATE(SUM(Sales[Amount]), DATESMTD('Date'[Date]))

Answer

CALCULATE(SUM(Sales[Amount]), 'Date'[Date] = DATE(2023,1,1))

Answer

TOTALMTD(SUM(Sales[Amount]), 'Date'[Date])

Question 6

Which THREE of the following are best practices when preparing data in Power BI for optimal performance?

Accepted Answer

Set correct data types for all columns.. Setting correct data types for all columns (Option C) is a best practice because it ensures that Power BI uses the most efficient storage and processing methods. Incorrect data types can lead to increased memory usage, slower query performance, and inaccurate calculations, as Power BI must perform implicit conversions at query time.

Answer

Merge all tables into a single table for simplicity.

Answer

Create calculated columns instead of measures when possible.

Question 7

You are a business analyst at a marketing agency. You receive a weekly export from a web analytics platform as a CSV file. The file contains columns: Date, Campaign, Impressions, Clicks, Conversions. You need to create a Power BI report that shows trends over time, with the ability to filter by campaign. The CSV file is updated weekly and you want to automate the refresh. You also want to ensure that any new campaigns are automatically included in the report. You have limited experience with Power BI. What should you do?

Accepted Answer

Create a Power BI dataflow that reads the CSV file from a SharePoint folder, transforms the data (e.g., change data types), and then load the dataflow as a dataset into Power BI. Schedule the dataflow to refresh weekly.. It uses a Power BI dataflow to read the CSV from a SharePoint folder, which supports scheduled refresh without a personal gateway. The dataflow can automatically include new campaigns as they appear in the folder, and the scheduled weekly refresh ensures the report stays up to date. This approach is ideal for users with limited Power BI experience because dataflows handle transformation and refresh centrally in the Power BI Service.

Answer

Use Power Query to read the CSV from a local folder, but schedule the refresh using a personal gateway.

Answer

Import the CSV file directly into Power BI Desktop, create the report, and publish to Power BI Service. Manually re-import the CSV each week.

Answer

Enable incremental refresh on the dataset to only load new data each week.

Question 8

You are combining CSV files from a folder. The code above returns an error because some CSV files have inconsistent column names and data types. You need to modify the solution to handle variations in column structure. What should you do?

Accepted Answer

Use the Folder connector's 'Combine Files' transform, which handles schema merging automatically.. To handle variations in column structure, you must edit the generated Combine Files function to align schemas dynamically, e.g., by using Table.ColumnNames and Table.TransformColumnTypes, or use a custom function that reads all files and combines them with schema normalization. None of the provided options correctly describe this approach.

Answer

Change the delimiter to a semicolon and reconfigure the CSV parser.

Answer

Use Table.RemoveRows to remove rows that cause errors.

Answer

Add a custom column to each file to standardize data types before combining.

Question 9

You are cleaning a column that contains numbers stored as text, with occasional leading/trailing spaces and currency symbols. You apply the function above to the column. However, some rows return null even though the original text appears to be a valid number, such as '$ 1,234.56'. What is the most likely cause?

Accepted Answer

The function does not remove commas and currency symbols before conversion.. The correct answer is D because conversion functions like Number.FromText do not automatically strip currency symbols, commas, or spaces. The input '$ 1,234.56' must be cleaned (e.g., remove $, spaces, and commas) before conversion, or the conversion fails and returns null. The error handling (if any) only catches the error; it does not sanitize the input.

Answer

The try...otherwise block is not catching parsing errors.

Answer

Text.Clean removes necessary decimal separators.

Answer

The function is applied to the wrong data type column.

Question 10

You have a Sales table with columns: ProductID, Amount, Date. You create the calculated column above. However, the rank values are not as expected; all rows show 1. What is the most likely cause?

Accepted Answer

The expression SUM(Sales[Amount]) in a calculated column aggregates only the current row, resulting in the same value for all rows.. In a calculated column, SUM(Sales[Amount]) does not aggregate only the current row; instead, it calculates the sum of the entire Amount column (grand total) for every row. Because this value is identical across all rows, the RANKX function assigns a rank of 1 to each row. To achieve proper ranking, you must use a measure that respects filter context or explicitly modify the expression to remove row context using functions like ALL().

Answer

The Dense parameter forces all ranks to 1.

Answer

The DESC parameter is misspelled; it should be 'Desc'.

Answer

The RANKX function requires a measure, not a calculated column.

Question 11

You are transforming a table that contains a 'Date' column in text format (e.g., '2026-01-15'). You need to create separate columns for Year, Month, and Day. Which THREE Power Query transformations can you use? (Choose three.)

Accepted Answer

Split Column by Delimiter using '-' as the delimiter.. Splitting the 'Date' column by the '-' delimiter in Power Query separates the text into individual components (Year, Month, Day) in one step. This is a direct transformation for extracting parts from a date string in a standard format like 'YYYY-MM-DD'.

Answer

Unpivot the Date column.

Answer

Merge the Date column with itself.

Question 12

You are connecting to a large CSV file (10 GB) stored in Azure Blob Storage. You need to load the data into Power BI with optimal performance. Which THREE practices should you follow? (Choose three.)

Accepted Answer

Use Parquet format instead of CSV if possible.. Parquet is a columnar storage format that significantly reduces file size and improves query performance compared to row-based CSV. Power BI can read Parquet more efficiently, especially for large datasets, as it enables predicate pushdown and compression, reducing I/O and memory usage during data loading.

Answer

Import all columns and use Power BI to hide unused ones.

Answer

Use the on-premises data gateway to connect to Azure Blob Storage.

Question 13

You are preparing data from multiple Excel files. Each file has a different structure; some have merged cells, empty rows, and inconsistent column names. Which TWO actions should you take to clean the data in Power Query? (Choose two.)

Accepted Answer

Remove blank rows that result from merged cells.. Merged cells in Excel often introduce blank rows when the data is loaded into Power Query. Using 'Remove Blank Rows' in Power Query eliminates these extraneous rows that do not contain meaningful data, ensuring the dataset is clean and ready for transformation.

Answer

Group rows by a key column to summarize data.

Answer

Merge columns to create a single identifier.

Answer

Unpivot columns to normalize the data.

Question 14

You are a business analyst at a manufacturing company. You receive weekly CSV files from different plants. Each file contains columns: PlantID, Date, ProductID, UnitsProduced, and ScrapUnits. However, the files sometimes have missing values in the ScrapUnits column, and occasionally there are duplicate rows (same PlantID, Date, ProductID). You need to prepare a clean dataset for reporting. The requirements are:
1. Combine all CSV files from a folder into a single table.
2. Replace null values in ScrapUnits with 0.
3. Remove duplicate rows based on PlantID, Date, and ProductID, keeping the first occurrence.
4. Ensure the data types are appropriate (e.g., Date as date, UnitsProduced as whole number).

Which sequence of Power Query steps should you use?

Accepted Answer

Connect to folder, combine files, replace nulls, remove duplicates, set data types.. It follows the logical order: first connect to the folder, then combine all CSV files into a single table, then clean the data by replacing nulls in ScrapUnits with 0 and removing duplicate rows (keeping first occurrence), and finally setting appropriate data types (e.g., Date as date, UnitsProduced as whole number). This sequence ensures that replacements and deduplication apply across the combined dataset, and setting types last prevents errors from invalid values. Option A is incorrect because replacing nulls in each file before combining may miss cross-file duplicates and is less efficient. Option B is incorrect because setting data types before cleaning can cause type conversion errors if null values exist. Option C is incorrect because removing duplicates before replacing nulls could remove rows that could be fixed by null replacement.

Answer

Connect to folder, replace nulls in each file, combine files, remove duplicates, set data types.

Answer

Connect to folder, combine files, set data types, replace nulls, remove duplicates.

Answer

Connect to folder, combine files, remove duplicates, replace nulls, set data types.

Question 15

You are loading data from a SQL Server database into Power BI. The source table contains a column 'SalesAmount' with occasional NULL values. In the Power Query Editor, you need to replace NULLs with 0 before loading. What is the most efficient transformation?

Accepted Answer

Use 'Replace Values' to replace null with 0.. 'Replace Values' in Power Query Editor is specifically designed to replace null values with a specified value, such as 0. This transformation is the most efficient and direct method for handling NULLs in a column, as it operates at the query level before data is loaded into the Power BI data model.

Answer

Use 'Replace Errors' with value 0.

Answer

Use 'Fill Down' to fill nulls with the previous value.

Answer

Use 'Change Type' to convert to a non-nullable type.