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

Question 1

You have a Power BI dataset that combines sales data from two Excel files: Sales2023.xlsx and Sales2024.xlsx. Both files have the same schema. You need to combine them into a single table without duplicating rows. What is the best approach in Power Query?

Accepted Answer

Use Append Queries.. Option C is correct because Append Queries in Power Query is specifically designed to combine rows from two or more tables with the same schema into a single table, stacking them vertically without duplicating rows. This operation is performed in the Power Query Editor (M language) and is the standard approach for unioning data from multiple sources during the data preparation phase, before loading into the Power BI data model.

Answer

Use Union in DAX.

Answer

Use Group By to summarize data.

Answer

Use Merge Queries as a new query.

Question 2

Refer to the exhibit. You load the Sales table into Power BI. You need to calculate the total net sales after discount (SalesAmount * (1 - Discount)). However, some rows have Null in the Discount column. What is the correct DAX measure?

Accepted Answer

Net Sales = SUMX(Sales, Sales[SalesAmount] * (1 - COALESCE(Sales[Discount], 0))). Option B is correct because it uses SUMX to iterate over each row of the Sales table, applying the calculation row-by-row, and uses COALESCE to replace any NULL in the Discount column with 0, ensuring the discount is correctly treated as zero for rows without a discount. This avoids the aggregation errors that occur when using SUM on the Discount column directly.

Answer

Net Sales = SUM(Sales[SalesAmount]) * (1 - SUM(Sales[Discount]))

Answer

Net Sales = SUMX(Sales, Sales[SalesAmount] * (1 - Sales[Discount]))

Answer

Net Sales = SUMX(Sales, DIVIDE(Sales[SalesAmount], 1 - Sales[Discount]))

Question 3

You are preparing data for a Power BI report that analyzes customer churn. The source data contains the following columns: CustomerID, Churn (Yes/No), AgeGroup (Teen, Adult, Senior), SubscriptionType (Basic, Premium), MonthlyCharges, TotalCharges, TenureMonths. You need to ensure data quality and optimize the model. Which TWO actions should you take? (Choose two.)

Accepted Answer

Remove TotalCharges column if it can be calculated from MonthlyCharges and TenureMonths.. Option C is correct because removing derived columns like TotalCharges (which equals MonthlyCharges × TenureMonths) reduces data redundancy, improves model size, and avoids potential inconsistencies. Option D is correct because setting CustomerID as a unique key and marking it as the primary column ensures proper row-level identity, enables efficient relationships, and is a best practice for star schema modeling in Power BI.

Answer

Convert Churn column to a numeric 0/1 column.

Answer

Create a calculated column for AverageCharges per customer.

Answer

Create a separate table for subscription types.

Question 4

You are creating a Power BI report from a SQL Server database that contains a table Orders with columns: OrderDate, CustomerID, ProductID, Quantity, UnitPrice. You need to build a star schema. Which THREE tables should you create? (Choose three.)

Accepted Answer

Date dimension table with date attributes.. In a star schema, dimension tables contain descriptive attributes (e.g., dates, products, customers) and are connected to a central fact table. For the Orders table, a Date dimension (B) is essential for time-based analysis, a Product dimension (C) provides product details, and a Customer dimension (D) stores customer attributes. These three dimensions normalize the data and enable efficient slicing and dicing in Power BI.

Answer

OrderDetails table with line items.

Answer

Sales fact table with measures.

Question 5

You are a data analyst at a retail company. You are building a Power BI report to analyze sales performance across stores. The data source is a SQL Server database with a table called 'SalesTransactions' containing 500 million rows. The table has columns: TransactionID, StoreID, ProductID, Quantity, UnitPrice, Discount, TransactionDate. You have imported the data into Power BI using Import mode. The report is slow when users filter by date or store. The initial data load took 45 minutes, and scheduled refreshes are failing because they exceed the 2-hour refresh limit. You need to reduce the refresh time and improve query performance. The business requires that users can see all historical data and that the report is always up-to-date (refreshed daily). What should you do?

Accepted Answer

Implement Incremental refresh with a policy to keep the last 5 years of data and detect changes.. Option B is correct because incremental refresh in Power BI allows you to partition large tables (like 500M rows) into smaller date-based ranges, refreshing only the most recent data (e.g., last 5 years) while keeping historical partitions static. This drastically reduces refresh time and avoids the 2-hour refresh limit, while still meeting the requirement for daily updates and full historical visibility. The 'detect changes' option further optimizes by only refreshing partitions where data has changed.

Answer

Create aggregations on the SalesTransactions table.

Answer

Switch to DirectQuery mode to avoid data import.

Answer

Use a live connection to the SQL Server database.

Question 6

You are working on a Power BI project for a marketing department. You have a CSV file with customer survey responses. The file contains columns: CustomerID, SurveyDate, Response (text with ratings from 1 to 5), Comments (free text). The file is 10 MB. You need to load the data into Power BI and create a measure that calculates the average rating. However, when you load the file, you notice that the Response column is imported as text instead of whole number. Also, there are some rows with missing values in the Response column. You need to ensure the data is correctly typed and handle missing values appropriately. What is the best approach?

Accepted Answer

Change the data type of Response to whole number in Power Query, then filter out or replace null values.. Option D is correct because Power Query is the designated tool for data type transformations and null handling during the load phase. Changing the Response column's data type to Whole Number in Power Query automatically converts valid text numbers and flags errors, while filtering out or replacing null values ensures clean data before the data model is built. This approach follows the best practice of performing data cleansing in Power Query rather than in DAX, which would add unnecessary overhead and complexity.

Answer

Use the 'Column from Examples' feature to create a new column with numeric values.

Answer

In Power Query, split the Response column by delimiter and then use the first part.

Answer

Use a DAX calculated column to convert text to number.

Question 7

Drag and drop the steps to create a calculated column in Power BI Desktop into the correct order.

Question 8

Drag and drop the steps to publish a Power BI Desktop report to the Power BI service into the correct order.

Question 9

Drag and drop the steps to create a calculated table in Power BI Desktop using DAX into the correct order.

Question 10

Match each DAX function to its description.

Question 11

Match each visualization type to its typical use case.

Question 12

Match each Power BI concept to its definition.

Question 13

You are connecting Power BI to an Azure SQL Database. The database contains a table with 10 million rows. You need to minimize the initial load time for the report. What should you do?

Accepted Answer

Use DirectQuery mode. DirectQuery mode sends queries directly to the Azure SQL Database, avoiding the need to import 10 million rows into the Power BI model. This eliminates the initial data transfer and processing overhead, significantly reducing the initial load time for the report.

Answer

Use Import mode and create aggregations

Answer

Use Dual mode

Answer

Use Import mode with Incremental refresh

Question 14

You have a Power BI dataset that uses DirectQuery to a Snowflake data warehouse. Users report that reports are slow. You need to improve query performance without changing the data source. What should you configure?

Accepted Answer

Create aggregations in the Power BI model. Option D is correct because creating aggregations in the Power BI model allows pre-summarized data to be stored in memory, reducing the volume of queries sent to Snowflake via DirectQuery. This improves performance without altering the data source, as aggregations can serve high-level queries from cached data while falling back to DirectQuery for detailed queries.

Answer

Enable Query Folding in Power Query

Answer

Change the dataset to Import mode

Answer

Increase the number of concurrent queries in Power BI Service

Question 15

You are using Power Query to combine data from multiple Excel files in a SharePoint folder. Each file has a sheet named 'Sales'. The columns across files are identical but occasionally a file has extra columns. You need to ensure the combined table contains only the common columns across all files. Which Power Query step should you use?

Accepted Answer

Use Table.ColumnsOfType to filter common columns. Option D is correct because Table.ColumnsOfType allows selecting only columns that appear in all tables. Option A is wrong because Table.Combine appends all columns, creating nulls for missing columns. Option B is wrong because Merge queries are for joining, not combining. Option C is wrong because Remove Other Columns removes columns not selected; you need to identify common ones first.

Answer

Use Table.Combine with the 'Table1' parameter

Answer

Use Remove Other Columns on the first file's columns

Answer

Use Merge Queries as New