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

Question 1

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?

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

Accepted Answer

Net Sales = SUMX(Sales, Sales[SalesAmount] * (1 - COALESCE(Sales[Discount], 0))). 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 2

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 3

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

You have a Power BI dataset that includes a date table created using CALENDAR(). You need to ensure that the date table always covers the full range of dates present in the fact table, even after new data is loaded. What should you do?

Accepted Answer

Create the date table using CALENDAR(MIN('Fact'[Date]), MAX('Fact'[Date])). Using `CALENDAR(MIN('Fact'[Date]), MAX('Fact'[Date]))` dynamically computes the date range from the fact table's actual data. This ensures that when new data is loaded with dates outside the previous range, the date table automatically expands to cover the full range, maintaining referential integrity for time intelligence calculations.

Answer

Use a fixed start and end date in the CALENDAR function

Answer

Create a disconnected date table

Answer

Enable Auto Date/Time in the model

Question 5

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 6

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 7

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. Option B is correct because Power Query's Folder data source exposes file metadata columns such as Date modified, and applying a filter on that column before invoking the Combine Files transformation restricts the combined table to only files modified within the last 7 days. This keeps the import efficient and ensures the resulting single table contains only the desired recent files. Option A is wrong because 'Sample File' merely defines the file structure and does not filter by modification date. Option C is wrong because 'Merge Queries' joins two existing queries on matching keys, not folder file metadata. Option D is wrong because filtering after combining would still load all files first, and the Date modified column is not retained in the combined result.

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 8

Your Power BI dataset uses a SQL view that joins multiple tables. You notice that some columns have null values where you expect data. You suspect the view definition has a bug. How can you verify the view's output in Power Query?

Accepted Answer

Create a new query that runs the view's SQL directly against the source. Creating a new query that runs the view's SQL directly against the source in Power Query allows you to isolate and execute the exact SQL statement, bypassing any transformations or folding issues. This lets you compare the raw output from the source with the view's expected results, directly verifying if the view definition itself contains a bug. It is the most straightforward method to confirm whether the null values originate from the view or from subsequent Power Query steps.

Answer

Check the 'Table Preview' in the data model

Answer

Use 'View Native Query' in Power Query

Answer

Use 'Data Profiling' in Power Query

Question 9

You are importing data from an Excel workbook. The workbook has multiple sheets. You want to combine two sheets that have the same columns but different row data. Which TWO Power Query operations can you use?

Accepted Answer

Append Queries. Append Queries (C) is correct because appending stacks the rows of two tables that share the same column structure, which is exactly the goal of combining two sheets with identical columns but different row data. Append Queries as New (E) is also correct because it performs the same row-stacking operation but outputs the result to a new query instead of modifying an existing one, preserving the original queries. Merge Queries (A) is incorrect because it joins tables horizontally by matching key columns, adding columns rather than rows. Group By (B) is incorrect because it aggregates rows into summary values, not concatenates datasets. Pivot Column (D) is incorrect because it reshapes data by turning unique row values into new columns, which does not combine row data from two sheets.

Answer

Merge Queries

Answer

Group By

Answer

Pivot Column

Question 10

You are reviewing a Power BI data source configuration JSON. The exhibit shows a data source definition. What is the privacy level setting for the data source 'SalesData'?

Accepted Answer

Private. The privacy level setting for the data source 'SalesData' is 'Private' because the JSON snippet shows the property "privacyLevel":"Private". In Power BI, the Private privacy level restricts data from being combined with data from other sources, ensuring that sensitive data is not inadvertently shared across queries. This setting is explicitly defined in the data source configuration and is not Organizational, None, or Public.

Answer

Organizational

Answer

None

Answer

Public

Question 11

You are reviewing a Power Query M expression in the advanced editor. The exhibit shows the query. What is the final output of this query?

Accepted Answer

A table with total sales amount per customer for orders after 2023. The query filters the Sales table to keep only rows where the OrderDate is in 2024 or later (i.e., after 2023), then groups by CustomerID, summing the SalesAmount for each customer. The final output is a table with one row per customer showing their total sales amount for orders placed after 2023.

Answer

A table with total sales amount per customer for all orders

Answer

A table with all sales records after 2023

Answer

A table with all sales records after 2023, excluding Discount column

Question 12

You are reviewing a DAX query in DAX Studio. The exhibit shows a query that returns a table. What is the purpose of the SUMMARIZE function in this query?

Accepted Answer

To group sales by product category and compute total sales. The SUMMARIZE function in DAX is used to group rows from a table based on one or more columns (here, Product Category) and then compute an aggregated value (Total Sales) using a SUM expression. The query returns a new table with one row per category and the corresponding total sales, which is the standard purpose of SUMMARIZE for grouping and aggregation.

Answer

To add a calculated column to the Sales table

Answer

To add a new row for total sales per category

Answer

To filter the Sales table by Product Category

Question 13

You are using the above KQL query as a source in Power Query for a Power BI semantic model. The query runs successfully but takes a long time to execute. You need to improve performance. What should you do?

Accepted Answer

Use the 'Run KQL command' option in Power Query to pass the query directly.. Using the 'Run KQL command' option in Power Query sends the entire KQL query directly to Azure Data Explorer (or Kusto) for execution, allowing the Kusto engine to process and filter data at the source. This minimizes data transfer and leverages Kusto's optimized query engine, significantly improving performance compared to pulling all data into Power Query for transformation.

Answer

Add additional transformations in Power Query to reduce rows.

Answer

Enable query folding to push the query to the Kusto source.

Answer

Disable query folding to improve performance.

Question 14

You are combining data from multiple Excel files stored in SharePoint Online. Each file has the same structure but different data. You need to create a solution that automatically includes new files added to the SharePoint folder without manual intervention. What should you use?

Accepted Answer

Use 'Get Data from SharePoint Online Folder' and then 'Combine files' transform.. The correct option is C: use 'Get Data from SharePoint Online Folder' and then the 'Combine files' transform. In Power Query, connecting to the SharePoint Online folder connector retrieves all files in that folder, and the 'Combine files' transform automatically applies the same parsing/template to every file, so newly added files with the same structure are included on refresh without manual work. Option A adds unnecessary copying to blob storage and still requires orchestration, while B is manual and defeats the automation requirement. Option D targets a SharePoint list rather than the folder's files and loads them separately, which does not automatically combine new files.

Answer

Use Power Automate to copy new files to a blob storage, then import from there.

Answer

Use 'Merge queries' to append each new file manually.

Answer

Use 'Get Data from SharePoint Online List' and load each file separately.

Question 15

You are designing a Power BI semantic model that uses a large fact table from Azure SQL Database. The table includes a date column. You need to ensure that the model supports time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR. What is the recommended approach?

Accepted Answer

Create a separate date table and mark it as a date table in the model.. Time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR require a separate date table with a contiguous date range marked as the date table in the model. This ensures that DAX can correctly calculate time-based aggregations across all dates, even if the fact table has gaps or missing dates. Without a marked date table, these functions may return incorrect or blank results.

Answer

Use the 'Add Calendar' function in Power Query and rely on auto-date/time.

Answer

Use DirectQuery mode and rely on the SQL Server date functions.

Answer

Use the built-in date hierarchy from the fact table's date column.