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

Question 1

You are designing a Power BI data model for sales analysis. The source data includes a table with columns: OrderID, CustomerID, ProductID, OrderDate, Quantity, and UnitPrice. You need to support time intelligence calculations (e.g., year-to-date sales) and avoid creating a separate date table. What should you do?

Accepted Answer

Use the CALENDAR function in a calculated table to create a date table and mark it as a date table.. Option A is correct because time intelligence functions in Power BI (e.g., TOTALYTD, SAMEPERIODLASTYEAR) require a contiguous, continuous date table marked as a date table. Using the CALENDAR function creates a calculated date table that meets this requirement, enabling accurate year-to-date and other time-based calculations without relying on the auto date/time feature or direct column usage.

Answer

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

Answer

Use the OrderDate column directly in time intelligence measures.

Answer

Create a calculated column for fiscal year using the OrderDate column.

Question 2

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'.. Option B is correct. Splitting by delimiter (space) at the rightmost occurrence reliably separates last name from first name. Option A is wrong because splitting by position is not reliable. Option C is wrong because Extract after delimiter picks the wrong part. Option D is wrong because Replace values doesn't split.

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 3

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.. Option D is correct because 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 4

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 column using CALENDAR to generate a continuous date range and use that.. Option C is correct because the CALENDAR function generates a contiguous range of dates, which is essential for accurate time intelligence calculations (e.g., YTD, QoQ). By creating a calculated column with CALENDAR, you ensure no gaps exist in the date dimension, even if the source data has missing dates. This approach allows you to mark the table as a date table and enable DAX time intelligence functions like TOTALYTD or SAMEPERIODLASTYEAR.

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 5

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.. Option C is correct because 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 6

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.. Option A is correct because 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 7

You are preparing data for a Power BI report. The source data has a column 'OrderDate' with date values. You need to create a date hierarchy for drill-down (Year, Quarter, Month). Which TWO actions are required?

Accepted Answer

Create a separate date table with a continuous range of dates.. Options B and D are correct. You need to mark a date table as a date table to enable time intelligence and create a date hierarchy. Creating a date table with a continuous range ensures all periods are represented. Option A is wrong because auto date/time creates hidden hierarchies but may not be sufficient for custom requirements. Option C is wrong because calculated columns do not create a hierarchy. Option E is wrong because sorting by month number is useful but not required for a hierarchy.

Answer

Enable auto date/time in Power BI Desktop.

Answer

Sort the Month column by MonthNumber to ensure proper order.

Answer

Add calculated columns for Year, Quarter, and Month to the date table.

Question 8

You are connecting Power BI to an Azure SQL Database. You need to ensure that the connection uses the most secure authentication method available. Which THREE options should you consider? (Choose three.)

Accepted Answer

Use a service principal with Microsoft Entra ID.. Option C is correct because using a service principal with Microsoft Entra ID (formerly Azure AD) enables token-based authentication for Power BI to Azure SQL Database, which is more secure than shared secrets or passwords. Service principals are non-interactive identities that can be granted fine-grained permissions, eliminating the need to store credentials and reducing the risk of credential exposure.

Answer

Use Windows authentication.

Answer

Use SQL Server authentication with a strong password.

Question 9

You have a Power Query transformation that combines data from multiple Excel files in a folder. Each file has a different structure. You need to identify which TWO actions will help standardize the data before combining. (Choose two.)

Accepted Answer

Use the Combine Files function and select a sample file to define the transformation.. Option C is correct because the Combine Files function in Power Query allows you to select a sample file to define a single transformation logic that is then applied to all files in the folder. This is essential when files have different structures, as it standardizes the data by using the sample file's schema as a template, ensuring consistent column names, data types, and transformations across all files.

Answer

Filter out rows with null values.

Answer

Merge all files into one table before cleaning.

Answer

Remove all columns except the ones you need from a single file.

Question 10

Refer to the exhibit. You are configuring a Power BI dataset with row-level security (RLS) using a JSON policy. The exhibit shows an RLS configuration. A user 'analyst@contoso.com' has access to the 'Orders' table. However, when the user views the report, no data is displayed. What is the most likely cause?

Accepted Answer

The RLS policy is missing a filter expression to allow rows.. Option C is correct because the RLS policy shown in the exhibit lacks a filter expression that defines which rows the user is allowed to see. In Power BI, a row-level security policy must include a DAX filter that returns a table of allowed rows; without it, the policy effectively denies all rows to the user. The user 'analyst@contoso.com' has access to the 'Orders' table, but the missing filter means no rows pass the security check, resulting in an empty report.

Answer

The user's email domain does not match the data source.

Answer

The connection string uses Integrated Security, which requires a gateway.

Answer

The table name 'Orders' is misspelled.

Question 11

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.. Option A is correct because 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 12

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 13

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. Option A is correct because `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 14

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. Option D is correct because it first removes the comma thousands separator, then replaces the opening parenthesis with a minus sign, removes the closing parenthesis, 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 15

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