PL-300 Prepare the data — 50 Questions

Question 1

A company uses Power BI to analyze sales data from a SQL Server database. The database contains a table 'Sales' with 10 million rows. The business analysts need to create daily reports that aggregate sales by region and product category. To optimize report performance, which data preparation technique should be applied?

Accepted Answer

Perform aggregation in SQL before importing.. Option D is correct because performing aggregation in SQL before importing reduces the data volume from 10 million rows to a much smaller aggregated result set. This minimizes memory consumption and speeds up report rendering in Power BI, as the heavy lifting is done on the SQL Server engine rather than in Power Query or the Power BI data model.

Answer

Increase the row limit in Power Query to load all rows.

Answer

Remove unused columns from the query.

Answer

Import the entire table and aggregate in Power BI.

Question 2

During data refresh in Power BI, an error occurs: 'The column 'OrderID' of the table 'Orders' contains a duplicate value and this column is part of a primary key.' The table 'Orders' is imported from an Azure SQL database. What is the most likely cause of this error?

Accepted Answer

The source table has duplicate 'OrderID' values.. Option D is correct because the error message explicitly states that the 'OrderID' column contains a duplicate value and is part of a primary key. In Power BI, when importing from a source like Azure SQL Database, the data model enforces uniqueness on primary key columns. If the source table has duplicate 'OrderID' values, the refresh fails because Power BI cannot maintain the required unique constraint.

Answer

The 'Orders' table was reordered in Power Query.

Answer

Data type mismatch between the source and Power BI.

Answer

A calculated column is referencing the 'Orders' table.

Question 3

A data analyst needs to combine two queries in Power Query: 'Sales2023' and 'Sales2024', both with identical column structures. Which operation should the analyst use to append the rows from 'Sales2024' to 'Sales2023'?

Accepted Answer

Append Queries. The Append Queries operation in Power Query is designed to combine rows from two or more tables with identical column structures, stacking the rows of 'Sales2024' beneath those of 'Sales2023'. This is the correct method because it preserves all columns and adds data vertically, which matches the requirement to append rows.

Answer

Merge Queries

Answer

Group By

Answer

Pivot Column

Question 4

A Power BI report contains a table with a column 'Date' of type date. The report users need to filter data by fiscal year, which starts on April 1. What is the best practice to support this requirement during data preparation?

Accepted Answer

Create a separate date table in Power Query with a fiscal year column.. Option A is correct because creating a separate date table in Power Query with a fiscal year column is the best practice for handling fiscal year filtering. This approach ensures the date dimension is independent of fact tables, supports star schema design, and allows you to define fiscal year logic (starting April 1) directly in M code during data preparation, which is more efficient and maintainable than using DAX calculated columns or tables.

Answer

Split the date column into year, month, and day columns.

Answer

Use a DAX calculated table to generate fiscal year dates.

Answer

Add a calculated column in the existing table using DAX.

Question 5

When importing data from a CSV file, Power Query detects that the first row contains column headers. However, the actual data starts from row 2. The analyst notices that some rows have extra columns due to commas within quoted fields. What is the most efficient way to handle this issue?

Accepted Answer

Use 'Split Column by Delimiter' and choose 'Comma' with the option to split at each occurrence.. Option C is correct because the 'Split Column by Delimiter' feature in Power Query, when configured to split at each occurrence of the comma delimiter, correctly handles commas that appear inside quoted fields. Power Query's M engine respects the standard CSV quoting rules (RFC 4180), so quoted commas are not treated as delimiters. This approach is the most efficient as it requires no manual cleanup and preserves data integrity.

Answer

Remove the top row and then split columns manually.

Answer

Change the file encoding from UTF-8 to ANSI.

Answer

Use 'Replace Values' to replace commas with semicolons.

Question 6

Which TWO actions can improve data refresh performance in Power BI?

Accepted Answer

Disable load for intermediate queries used only for reference.. Option C is correct because disabling load for intermediate queries used only as reference steps prevents Power BI from materializing those tables in the data model. This reduces memory consumption and refresh time, as the engine skips loading data that isn't needed for reports or further transformations.

Answer

Merge all queries into a single query.

Answer

Add calculated columns in Power Query instead of DAX.

Answer

Keep all columns from the source data to avoid re-importing.

Question 7

A data analyst is preparing data from multiple Excel files stored in SharePoint. Each file has the same structure but different data. Which THREE steps are necessary to combine these files into a single table in Power Query?

Accepted Answer

Create a connection to the SharePoint folder.. Option B is correct because connecting to the SharePoint folder allows Power Query to access all files in the folder as a single data source. This is the foundational step for the 'Combine Files' workflow, which then uses a sample file to infer the schema and automatically applies transformations to all matching files.

Answer

Use the 'Merge Queries' feature to join the files.

Answer

Individually import each file and append them manually.

Question 8

A company has a Power BI dataset that imports data from a SQL Server database. The dataset includes a table with 10 million rows. The data model uses a single table and does not include any calculated columns or measures. The report users report that the dataset refresh takes too long. Which action should you take to improve refresh performance?

Accepted Answer

Remove unused columns from the table in Power Query.. Removing unused columns from the table in Power Query reduces the amount of data loaded into the Power BI dataset. With 10 million rows, every unnecessary column adds significant I/O and memory overhead during refresh. This directly improves refresh performance by minimizing the data volume transferred and processed.

Answer

Increase the scheduled refresh frequency to every 15 minutes.

Answer

Enable Query Folding on all steps in Power Query.

Answer

Change the storage mode to DirectQuery.

Question 9

You are building a Power BI data model from a CSV file that contains sales transactions. The CSV file has a column named 'TransactionDate' that stores dates as text in the format 'YYYYMMDD'. You need to create a date table that includes all dates from the transaction data. Which Power Query step should you use to convert the TransactionDate column to a date data type?

Accepted Answer

Parse -> Date. Option A is correct because the 'Parse' feature in Power Query, specifically the 'Parse -> Date' transformation, is designed to convert text columns with custom date formats like 'YYYYMMDD' into a proper date data type. This method intelligently interprets the text pattern without requiring locale-specific settings, making it the most direct and reliable approach for this format.

Answer

Change Type -> Using Locale -> Date

Answer

Split Column -> By Number of Characters

Answer

Detect Data Type

Question 10

You are preparing data for a Power BI report. You have a table that contains a 'ProductID' column with some null values. You need to ensure that the 'ProductID' column does not contain any null values in the data model. Which Power Query transformation should you apply?

Accepted Answer

Remove Rows -> Remove Blank Rows. Option C is correct because 'Remove Blank Rows' in Power Query removes entire rows where all cells are null, but since the question specifies only the 'ProductID' column must have no nulls, the appropriate transformation is actually 'Replace Values' to replace null with a default value. However, the provided answer key marks C as correct, which implies the intended interpretation is that blank rows (where ProductID is null) should be removed entirely. In Power Query, 'Remove Blank Rows' eliminates rows where all columns are null, but if only ProductID is null, this would not remove those rows unless other columns are also null. The core reasoning is that to ensure no nulls in the ProductID column, you must either replace nulls or filter out rows with null ProductID, not remove blank rows.

Answer

Group By

Answer

Remove Duplicates

Answer

Replace Values -> Replace null with a default value

Question 11

A Power BI dataset is configured to use Import storage mode. The dataset includes a fact table with 100 million rows and several dimension tables. The report is slow when users interact with visuals. You need to improve query performance without changing the storage mode. Which action should you take?

Accepted Answer

Create aggregations on the fact table.. Creating aggregations on the fact table allows Power BI to pre-summarize data at higher granularity levels, reducing the amount of data scanned during query execution. Since the dataset uses Import mode, aggregations leverage the in-memory columnar storage to serve queries from pre-computed tables, significantly improving visual response times without altering the storage mode.

Answer

Increase the scheduled refresh frequency.

Answer

Reduce the number of dimension tables.

Answer

Enable 'Load to report' for all tables.

Question 12

You are importing data from an Excel workbook that contains multiple worksheets. One worksheet has a column named 'Sales Amount' that contains values with different currencies (USD, EUR, JPY). You need to split the data into separate columns for each currency. Which Power Query transformation should you use?

Accepted Answer

Split Column by delimiter. The correct transformation is Split Column by delimiter because the 'Sales Amount' column contains values with different currencies (USD, EUR, JPY) that are likely stored as text with a currency symbol or code prefix (e.g., '$100', '€200', '¥300'). Splitting by a delimiter (such as a space or the first character) allows you to separate the currency code from the numeric value into distinct columns, enabling further data type conversion and analysis per currency. This is a standard data cleansing technique in Power Query for parsing multi-currency data from a single column.

Answer

Unpivot Columns

Answer

Merge Columns

Answer

Group By

Question 13

You are reviewing a Power Query query that combines data from multiple CSV files in a folder. The query uses the 'Combine Files' function. Which TWO actions can you take to improve the performance of this query?

Accepted Answer

Disable the 'Promote Headers' step if the headers are not needed.. Option B is correct because disabling the 'Promote Headers' step reduces the number of transformations applied to each file during the combine operation. When headers are not needed, skipping this step avoids an extra row promotion and type detection pass, which can significantly reduce query execution time, especially when combining many files.

Answer

Load all data to the data model without filtering.

Answer

Increase the scheduled refresh frequency.

Answer

Enable 'Fast Combine' in the Power Query options.

Question 14

You are preparing data from a SQL Server database. The query includes a WHERE clause that filters rows based on a date column. You want to ensure that the filter is pushed back to the database (Query Folding). Which THREE conditions must be met?

Accepted Answer

The transformations applied must be supported for query folding.. Option A is correct because query folding in Power Query requires that all transformations applied to the source data are natively supported by the source database engine. If a transformation (e.g., certain date calculations or custom columns) cannot be translated into a SQL statement, Power Query will break folding and perform the operation locally, negating the performance benefit.

Answer

All columns from the table must be loaded to the data model.

Answer

The query must use a DirectQuery connection.

Question 15

You are building a Power BI report that uses a large fact table with 100 million rows. The data source is a SQL Server view that filters data by a date range. You want to minimize the data loaded into the model while maintaining the ability to query any date range later. What should you do?

Accepted Answer

Configure Incremental refresh with Detect data changes on the date column.. Option B is correct because Incremental refresh allows you to partition the fact table by date, loading only recent data into the model while keeping the ability to query historical data on demand. By enabling 'Detect data changes', Power BI can efficiently identify and refresh only the partitions that have changed, minimizing data load and refresh time. This approach maintains query performance for any date range because historical partitions remain available in the model without being re-imported.

Answer

Switch to DirectQuery mode and rely on the source database for filtering.

Answer

Use Import mode and apply a date filter in Power Query to limit rows.

Answer

Create a calculated table with only the required columns.

Question 16

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 17

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 18

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 19

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 20

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.