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

Question 1

You have a dataset with a column 'FullName' containing values like 'John Doe'. You need to split this column into 'FirstName' and 'LastName' using the space delimiter. Which Power Query transformation should you use?

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

Accepted Answer

Split Column by Delimiter.. The 'Split Column by Delimiter' transformation in Power Query is specifically designed to divide a single text column into multiple columns based on a specified delimiter, such as a space. In this scenario, selecting the column 'FullName' and using 'Split Column > By Delimiter' with a space delimiter will correctly separate 'John Doe' into 'FirstName' (John) and 'LastName' (Doe). This is the standard approach for parsing delimited text within Power Query.

Answer

Merge Columns.

Answer

Extract Text.

Answer

Replace Values.

Question 2

You are designing a data model in Power BI. You have a Sales table and a Date table. The Date table should contain all dates from 2020 to 2025. What is the best practice for creating the Date table?

Accepted Answer

Create a separate Date table using CALENDAR function in DAX.. Using the CALENDAR function to create a separate Date table ensures a complete, contiguous date range from 2020 to 2025, independent of the Sales table. This is a best practice in Power BI for time intelligence, as it guarantees all dates are present for accurate year-over-year calculations and avoids gaps or missing dates that could occur if relying solely on transaction data.

Answer

Create a calculated table using VALUES from the Sales table's date column.

Answer

Use the Sales table's date column directly as the date dimension.

Answer

Enable the auto date/time option in Power BI settings.

Question 3

You are connecting to an Azure SQL database using DirectQuery. The database has a large table with millions of rows. Users need to see aggregated data quickly. What should you implement to improve query performance?

Accepted Answer

Create aggregations in Power BI on the large table.. Creating aggregations in Power BI on the large table allows the DirectQuery model to pre-aggregate data at the source or in Power BI, reducing the volume of data queried and improving response times for aggregated results. This is a key performance optimization for DirectQuery models with large tables, as it avoids scanning millions of rows for every query.

Answer

Increase the memory limit of the Power BI Desktop.

Answer

Use a composite model with a smaller imported table.

Answer

Add indexes to the database table.

Question 4

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?

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 5

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 6

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 7

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 8

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 9

You need to create a Power BI data model that includes a date dimension. The source data contains a table with a Date column covering 2015-2025. You want to ensure that all dates in the model have a contiguous range for time intelligence. What should you do?

Accepted Answer

Create a calculated table using CALENDAR to generate a continuous date range and mark it as a date table.. The CALENDAR function creates a calculated table (not a column) that generates a contiguous range of dates, which is essential for accurate time intelligence calculations such as YTD or QoQ. After creating this calculated table, you can mark it as a date table to enable DAX time intelligence functions. Although the option wording mistakenly says 'calculated column', the intent is to create a separate date table using CALENDAR, which is the proper method to ensure no gaps in the date dimension.

Answer

Hide the existing Date column and rely on auto date/time.

Answer

Add a calculated column for year and month from the existing Date column.

Answer

Use the existing Date column as the date table and mark it as a date table.

Question 10

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 11

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 12

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 13

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 14

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 15

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 '-')