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

Question 1

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?

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

Accepted Answer

Implement Incremental refresh with a policy to keep the last 5 years of data and detect changes.. 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 2

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

Accepted Answer

Step 1: Select the table. Step 2: Click New Column. Step 3: Write the DAX formula.. Creating a calculated column involves selecting the table, using the New Column button, and writing a DAX expression that evaluates row by row.

Answer

Step 1: Click New Column. Step 2: Select the table. Step 3: Write the DAX formula.

Answer

Step 1: Write the DAX formula. Step 2: Select the table. Step 3: Click New Column.

Answer

Step 1: Select the table. Step 2: Write the DAX formula. Step 3: Click New Column.

Question 3

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

Accepted Answer

Sign in to Power BI, then select a workspace, then click Publish, then view the report in the Power BI service.. Publishing requires signing in, selecting a workspace, and confirming; the report then appears in the Power BI service.

Answer

Select a workspace, then sign in to Power BI, then click Publish, then view the report.

Answer

Click Publish, then select a workspace, then sign in to Power BI, then view the report.

Answer

Sign in to Power BI, then click Publish, then select a workspace, then view the report.

Question 4

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

Accepted Answer

Navigate to the Modeling tab, click New Table, enter DAX expression in the formula bar, press Enter.. Calculated tables are created via DAX expressions and can be used like regular tables, but they are computed at refresh time.

Answer

Navigate to the Modeling tab, enter DAX expression in the formula bar, click New Table, press Enter.

Answer

Navigate to the Modeling tab, click New Table, press Enter, enter DAX expression in the formula bar.

Answer

Click New Table, navigate to the Modeling tab, enter DAX expression in the formula bar, press Enter.

Question 5

Match each DAX function to its description.

Accepted Answer

CALCULATE: Evaluates an expression in a modified filter context.. Correct matches: CALCULATE modifies filter context; SUM adds column numbers; FILTER returns a subset of rows. Common confusions include swapping CALCULATE with FILTER or SUM with VALUES.

Answer

CALCULATE: Adds all the numbers in a column.

Answer

SUM: Returns a table with unique values from a column.

Answer

FILTER: Evaluates an expression in a modified filter context.

Question 6

Match each visualization type to its typical use case.

Accepted Answer

Clustered bar chart: Compare categories across groups. Different visuals are suited for different analytical tasks. Common correct pairings: Clustered bar chart compares categories, line chart shows trends, and pie chart shows proportions. Distractors swap these uses.

Answer

Clustered bar chart: Show trends over time

Answer

Line chart: Compare categories across groups

Answer

Pie chart: Show relationship between two numerical variables

Question 7

Match each Power BI concept to its definition.

Accepted Answer

Star Schema: A data model structure where fact tables are connected to dimension tables in a star-like pattern.. Star schema uses denormalized dimensions, snowflake schema normalizes dimensions further. Calculated columns are static row-by-row evaluations; measures are dynamic aggregations based on filter context.

Answer

Star Schema: A normalized schema where dimension tables are split into sub-dimensions.

Answer

Snowflake Schema: A denormalized schema where fact table connects directly to dimension tables.

Question 8

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 9

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

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 Remove Other Columns on the first file's columns. Use Remove Other Columns on the first file's columns (the sample file) after selecting only the columns that are common to all files. This defines the schema so Power Query excludes any extra columns from other files. Table.Combine with the 'Table1' parameter keeps only the columns of the first table; if that first file happens to contain an extra column, the combined output will include it and will not guarantee only common columns. Table.ColumnsOfType filters by data type, and Merge Queries as New joins tables horizontally.

Answer

Use Table.Combine with the 'Table1' parameter

Answer

Use Table.ColumnsOfType to filter common columns

Answer

Use Merge Queries as New

Question 11

You are importing data from a CSV file that contains a column 'OrderDate' with values in the format 'YYYY-MM-DD'. Power Query automatically detects the data type as Date. You need to ensure that the data type remains Date even if the source file later changes the date format to 'MM/DD/YYYY'. What should you do?

Accepted Answer

Set the data type to Date using the 'Change Type' step in Power Query. Using the 'Change Type' step in Power Query explicitly sets the data type of the 'OrderDate' column to Date. This step is part of the query's applied steps and will attempt to parse the column values as dates regardless of the source format (e.g., 'YYYY-MM-DD' or 'MM/DD/YYYY'), preserving the Date data type. Option A (splitting the column) is unnecessary and does not enforce the data type. Option B ('Using Locale' transformation) changes how dates are interpreted based on locale but does not guarantee that a changed format will still be parsed correctly if the new format conflicts with the locale. Option D (changing regional settings) only affects the editor's display, not the query's data type enforcement.

Answer

Split the column into year, month, day and then combine

Answer

Use the 'Using Locale' transformation

Answer

Change the regional settings of the Power Query editor

Question 12

You are designing a Power BI data model for a sales analysis. The source data has a table 'Orders' with columns: OrderID, CustomerID, ProductID, OrderDate, Quantity, UnitPrice. You also have a table 'Customers' with CustomerID, CustomerName, and 'Products' with ProductID, ProductName. You need to create a star schema. What should you do?

Accepted Answer

Keep Customers and Products as separate dimension tables, and Orders as the fact table. In a star schema, a single fact table (Orders) stores quantitative measures (Quantity, UnitPrice) and foreign keys (CustomerID, ProductID) that link to dimension tables (Customers, Products) containing descriptive attributes. This design optimizes query performance by reducing joins and enabling efficient aggregation, which is a best practice for Power BI data modeling.

Answer

Split Orders into multiple fact tables by year

Answer

Create a snowflake schema by normalizing Customers and Products further

Answer

Merge Customers and Products into a single dimension table

Question 13

You have a Power BI report that uses a dataset with many columns. You want to reduce the dataset size by removing columns that are not used in any report visual. What is the best practice?

Accepted Answer

Remove the columns in Power Query Editor before loading. Removing columns in Power Query Editor before they are loaded into the data model physically excludes them from the dataset, reducing its size and improving performance. This is the only method that prevents unused columns from consuming memory and storage in the in-memory VertiPaq engine.

Answer

Use the Q&A feature to exclude columns

Answer

Apply report-level filters to exclude the columns

Answer

Hide the columns in the model view

Question 14

You are importing data from a folder containing multiple CSV files with the same structure. You want to combine all files into a single table, but only include files that have been modified in the last 7 days. What Power Query transformation should you use?

Accepted Answer

Filter the folder contents by 'Date Modified' before combining. Filtering the folder contents by 'Date Modified' before combining ensures only recent files are loaded, optimizing performance. Option A is incorrect because 'Sample File' is used to preview the structure of a representative file, not to filter which files are combined. Option C is incorrect because 'Merge Queries' is for joining tables based on a key, not for appending multiple files with the same structure. Option D is incorrect because combining all files first and then filtering loads all data into memory, which is less efficient than filtering before combining.

Answer

Use 'Sample File' and manually filter

Answer

Use 'Merge Queries' with the folder

Answer

Combine files using 'Combine & Load', then filter by date modified

Question 15

You have a Power BI dataset that uses DirectQuery to an Azure Synapse Analytics dedicated SQL pool. You need to improve query performance. Which THREE actions should you take?

Accepted Answer

Create indexes on columns used in filters. Creating indexes on columns used in filters is correct because DirectQuery translates Power BI filter operations into SQL queries against the Azure Synapse Analytics dedicated SQL pool. Indexes on those columns accelerate row-level filtering by reducing the number of pages scanned, directly improving query response times.

Answer

Normalize the data warehouse tables

Answer

Switch the dataset to Import mode