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

Question 1

You are building a Power BI semantic model that combines data from an on-premises SQL Server database and a SharePoint Online list. The SQL Server table contains 10 million rows and updates hourly. The SharePoint list contains 500 rows and updates daily. You need to minimize the data load time and ensure the model refreshes within the scheduled 30-minute window. What should you do?

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

Accepted Answer

Use DirectQuery for the SQL Server table and Import mode for the SharePoint list.. Using DirectQuery for the large SQL Server table (10M rows, hourly updates) avoids importing all rows into the model, significantly reducing data load time and memory usage. Import mode for the small SharePoint list (500 rows, daily updates) is appropriate since it loads quickly and supports full DAX functionality, while the combination keeps the total refresh within the 30-minute window.

Answer

Set both tables to DirectQuery mode.

Answer

Set the SQL Server table to Dual mode and the SharePoint list to Import mode.

Answer

Import both tables into the model and disable incremental refresh.

Question 2

You receive a Power Query error: 'Expression.Error: The key didn't match any rows in the table.' This occurs when merging two queries. What is the most likely cause?

Accepted Answer

The join column in the first table contains values that do not exist in the second table.. The error 'The key didn't match any rows in the table' occurs during a merge operation when Power Query attempts to find a matching value from the first table's join column in the second table's join column, but no match exists. This is a standard behavior for inner joins or left outer joins where the lookup fails, and it typically indicates that the first table contains values absent in the second table.

Answer

The join columns have different data types.

Answer

The second table is empty due to a permission issue.

Answer

The join columns contain duplicate values.

Question 3

You need to connect Power BI to an Excel file stored on a local network drive. The file is updated manually each morning. You want the Power BI report to always show the latest data when opened. Which data connectivity mode should you choose?

Accepted Answer

Import. (Import) is correct because Power BI must load the Excel data into its internal VertiPaq engine to support the full range of transformations and visualizations. Since the file is on a local network drive and updated manually, Import mode allows you to refresh the dataset on demand or via a scheduled refresh, ensuring the report always shows the latest data when opened. DirectQuery and Live Connection are not applicable because they require a live queryable data source (like SQL Server or Analysis Services), not a flat file.

Answer

Live Connection

Answer

Dual

Answer

DirectQuery

Question 4

You are preparing data for a Power BI report. The source data contains a column 'FullName' with values like 'John Doe'. You need to split this column into 'FirstName' and 'LastName' using Power Query. The transformation should be repeatable and not dependent on the number of spaces. What is the best approach?

Accepted Answer

Use 'Split Column by Delimiter' and choose 'Right-most delimiter'.. Using 'Split Column by Delimiter' with 'Right-most delimiter' ensures that the split occurs at the last space in the FullName column, which reliably separates the first name from the last name even if there are multiple spaces (e.g., 'John Michael Doe' would yield 'John Michael' as FirstName and 'Doe' as LastName). This approach is repeatable and does not depend on a fixed number of spaces, making it robust for varying name formats.

Answer

Use 'Split Column by Number of Characters' with a fixed position.

Answer

Use 'Replace Values' to replace space with a comma.

Answer

Use 'Extract Text After Delimiter' with a space.

Question 5

You are using Power Query to combine data from multiple CSV files in a folder. Each file has the same structure. You want to append all rows into a single table. Which Power Query function should you use?

Accepted Answer

Combine Files from the folder connector.. The 'Combine Files' transformation in Power Query is specifically designed to import and append multiple CSV files from a folder into a single table. When you connect to a folder using the 'From Folder' connector, Power Query automatically generates a 'Combine Files' step that uses the 'Table.Combine' function under the hood, which appends all rows from identically structured files into one unified table.

Answer

Group By.

Answer

Append Queries.

Answer

Merge Queries as new.

Question 6

You have a Power BI semantic model that uses Import mode with a SQL Server data source. The refresh takes over two hours. You need to reduce the refresh time while keeping data up-to-date. What is the best strategy?

Accepted Answer

Configure incremental refresh policy on the fact table.. Incremental refresh policy allows you to refresh only the most recent data (e.g., last 5 days) while keeping historical partitions unchanged, drastically reducing the amount of data loaded during each refresh. This is the most effective way to reduce refresh time in Import mode while maintaining data freshness, as it avoids re-querying the entire fact table from SQL Server.

Answer

Switch the data source to DirectQuery mode.

Answer

Remove unnecessary columns and rows from the query.

Answer

Reduce the scheduled refresh frequency to once a day.

Question 7

You are importing data from a SQL Server view into Power BI. The view contains calculated columns that are expensive to compute. You want to minimize the load on the source database during refresh. What should you do?

Accepted Answer

Import the raw data and perform transformations in Power Query.. Importing raw data and performing transformations in Power Query offloads the computational burden from the SQL Server source to Power BI's mashup engine. This minimizes load on the source database during refresh, as expensive calculated columns are not executed on SQL Server. Power Query can apply transformations after the data is extracted, reducing the need for server-side processing.

Answer

Enable query folding to push transformations to SQL Server.

Answer

Use a native SQL query in Power Query to perform calculations.

Answer

Create a materialized view in SQL Server with the calculations.

Question 8

Refer to the exhibit. You are reviewing a DAX measure in Power BI. The measure is intended to calculate total sales for the year 2024. However, when used in a visual with a slicer on 'Sales[Date]', the measure does not respect the slicer selection. What is the most likely reason?

Accepted Answer

The FILTER function overrides the slicer filter context.. The FILTER function in the measure creates a new filter context that overrides the existing slicer filter context on 'Sales[Date]'. When CALCULATE evaluates the expression, it applies the FILTER as a table modifier, which replaces any external filters on the Date column, causing the slicer to be ignored. This is a common DAX behavior where explicit filter arguments in CALCULATE take precedence over existing filter contexts.

Answer

The CALCULATE function removes all filters by default.

Answer

The measure should use ALL(Sales[Date]) to respect slicers.

Answer

The DATE function syntax is incorrect.

Question 9

Refer to the exhibit. You are configuring a scheduled refresh for a Power BI dataset. The exhibit shows the refresh schedule settings. The dataset is in a workspace in a Premium capacity. The scheduled refresh runs at 5:00 AM UTC daily. However, the refresh is failing consistently. What is the most likely cause?

Accepted Answer

The data source requires a gateway to be configured for refresh.. The exhibit shows a scheduled refresh configured for a dataset in a Premium capacity workspace, but the refresh is failing consistently. The most likely cause is that the data source requires a gateway to be configured for refresh. Even in Premium capacities, if the data source is on-premises or within a private network (e.g., SQL Server, Oracle, or SharePoint on-premises), an on-premises data gateway must be installed and configured to enable the Power BI service to connect and refresh the data. Without a gateway, the scheduled refresh will fail because the cloud service cannot directly access the local data source.

Answer

The notify option should be set to 'OnFailureOrSuccess' to get alerts.

Answer

The refresh time is outside the allowed window for Premium capacities.

Answer

The dataset is in a Premium capacity, which does not support scheduled refresh.

Question 10

You are building a Power BI data model from an Azure SQL Database. The source table contains a column 'OrderDate' of type datetime. You want to create a date table in Power Query that includes all dates from the minimum to maximum OrderDate. Which M function should you use to generate the list of dates?

Accepted Answer

List.Dates. `List.Dates` generates a list of sequential dates (type `date`) given a start date, a count of values, and a step duration. In Power Query, when you need a date table covering the range from the minimum to maximum `OrderDate`, you compute the count as `Duration.Days(MaxDate - MinDate) + 1` and use `List.Dates(MinDate, Count, #duration(1,0,0,0))`. This produces a clean list of dates without time components, ideal for a date dimension.

Answer

List.Generate

Answer

List.DateTimes

Answer

List.Range

Question 11

You are importing data from a CSV file into Power BI. The file contains a column 'SalesAmount' with values like '1,234.56' and '(987.65)' for negative amounts. You need to transform this column into a decimal number. Which sequence of Power Query steps achieves this?

Accepted Answer

Replace Values ('(' with '-'), Replace Values (')' with ''), Replace Values (',' with ''), then Change Type to Decimal. It first replaces the opening parenthesis '(' with a minus sign '-', then removes the closing parenthesis ')', then removes the comma thousands separator ',', and finally changes the data type to Decimal. This sequence ensures that the negative indicator is properly placed before the numeric value and that the string is cleanly formatted for type conversion.

Answer

Change Type to Decimal, then Replace Values (',' with ''), then Replace Values ('(' with '-')

Answer

Replace Values (',' with ''), then Replace Values ('(' with '-'), then Replace Values (')' with ''), then Change Type to Decimal

Answer

Replace Values (',' with ''), Change Type to Decimal, then Replace Values ('(' with '-')

Question 12

You are connecting to an Excel workbook stored in Microsoft SharePoint Online. You want to refresh the data in Power BI service without manual intervention. Which type of gateway is required?

Accepted Answer

No gateway is required. When connecting to an Excel workbook stored in Microsoft SharePoint Online, the data source resides entirely in the cloud. Power BI service can directly access SharePoint Online via its cloud-to-cloud connectivity using the same Microsoft Entra ID authentication context. Therefore, no on-premises data gateway is required for scheduled refresh because the data never traverses an on-premises network.

Answer

On-premises data gateway (standard mode)

Answer

On-premises data gateway (personal mode)

Answer

Virtual network (VNet) gateway

Question 13

You have a Power BI semantic model that uses DirectQuery to an Azure Synapse Analytics dedicated SQL pool. The model is used by a real-time dashboard. Users report that the dashboard is slow. You need to improve query performance without changing the source system. Which action should you take?

Accepted Answer

Reduce the number of visuals on the dashboard and apply page-level filters. Reducing the number of visuals and applying page-level filters directly reduces the number of queries sent to the Azure Synapse Analytics dedicated SQL pool via DirectQuery. Since the source system cannot be changed, the only way to improve performance is to minimize the query load from the dashboard. Page-level filters ensure that only relevant data is queried, and fewer visuals mean fewer separate queries, which collectively reduces latency.

Answer

Create aggregations on the fact table

Answer

Enable dual storage mode for all tables

Answer

Disable the 'Reduce queries' option in Power BI Desktop

Question 14

You are preparing data for a Power BI report. The source data contains a 'CustomerName' column with values like 'John, Doe'. You need to split this column into two columns: 'FirstName' and 'LastName'. The comma is used as a delimiter, but some names have a space after the comma. Which split method should you use?

Accepted Answer

Split by delimiter using comma, then use 'Trim' to remove extra spaces. Splitting by comma and then trimming extra spaces handles the inconsistent spacing after the comma (e.g., 'John, Doe' vs 'John, Doe'). Power Query's 'Split Column by Delimiter' using comma will separate the values, and the subsequent 'Trim' step removes leading/trailing spaces from the resulting columns, ensuring clean 'FirstName' and 'LastName' values without manual cleanup.

Answer

Split by number of characters using a fixed width

Answer

Split by delimiter using semicolon

Answer

Split by delimiter using comma, using 'Left-most delimiter'

Question 15

You are designing a Power BI data model for a sales analytics solution. The source data includes a 'Sales' fact table with millions of rows and dimension tables for 'Customer', 'Product', 'Date', and 'Salesperson'. You need to minimize the model size in Power BI. Which action should you take?

Accepted Answer

Remove any columns from the fact table that are not used in the model.. Removing unused columns from the fact table reduces the amount of data imported into the Power BI model, which directly minimizes model size. Each column consumes memory for compression and storage, so eliminating unnecessary columns reduces the model footprint. DirectQuery (Option B) would also reduce the amount of data stored in the model, but it changes the storage mode to query the source directly, which is not the intended approach for minimizing the size of an imported data model.

Answer

Set the 'Storage mode' of fact table to 'DirectQuery'.

Answer

Enable 'Include relationship columns' in the relationship settings.

Answer

Create a calculated column for row number.