PL-300 Practice Test 5 — 25 Questions

Question 1

You are importing data from a CSV file into Power BI. The file contains a column 'SalesAmount' with values like '$1,234.56'. When you load the data, the column is detected as text. What is the most efficient way to convert this column to a numeric type in Power Query?

Accepted Answer

Use 'Replace Values' to remove the dollar sign and comma, then change the column type to decimal.. Option A is correct because it directly addresses the root cause: the dollar sign and comma are non-numeric characters that prevent automatic type conversion. By using 'Replace Values' to remove these characters first, you clean the data so that Power Query can then safely change the column type to decimal. This is the most efficient approach because it avoids error rows and preserves data integrity.

Answer

Use 'Detect Data Type' and hope it automatically converts.

Answer

Change the column type to decimal directly and ignore errors.

Answer

Split the column by delimiter ',' and then convert the first part.

Question 2

You have a Power BI dataset that uses DirectQuery to an Azure SQL Database. Users complain that reports take too long to load. You suspect that the database is overwhelmed by queries. What should you do to improve performance while keeping DirectQuery?

Accepted Answer

Reduce the number of visuals per page and apply slicers to limit data.. Option A is correct because reducing the number of visuals per page and applying slicers to limit data reduces the number of DAX queries sent to the Azure SQL Database via DirectQuery. Each visual generates at least one query, so fewer visuals mean fewer concurrent queries, and slicers add WHERE clauses that reduce the result set size, lowering the load on the database.

Answer

Increase the Power BI Premium capacity size.

Answer

Create aggregations in the dataset.

Answer

Change the storage mode to Import.

Question 3

Which THREE are valid methods to handle duplicate rows in Power Query when preparing data?

Accepted Answer

Use 'Remove Duplicates' based on key columns. The correct methods to handle duplicate rows in Power Query are: B: 'Remove Duplicates' based on key columns deletes duplicate rows; C: 'Keep Duplicates' retains only duplicate rows; E: 'Group By' can aggregate duplicate rows into a single row. Options A (Reverse Rows) and D (Sort Ascending) do not handle duplicates.

Answer

Use 'Reverse Rows'

Answer

Use 'Sort Ascending'

Question 4

You are designing a Power BI solution for a retail company. The data includes point-of-sale transactions with columns: TransactionID, StoreID, ProductID, Quantity, SalesAmount, TransactionDate. The company wants to analyze sales by hour of day. What is the best way to prepare the time dimension?

Accepted Answer

Create a separate Time dimension table with a one-to-many relationship to the fact table. Option D is correct because creating a separate Time dimension table with a one-to-many relationship to the fact table follows the star schema best practice for Power BI. This approach allows efficient filtering and grouping by hour without bloating the fact table with calculated columns, and it supports time-based analysis across multiple fact tables if needed. The Time dimension can include columns like Hour, HourBucket, or PeriodOfDay, enabling intuitive slicers and drill-downs.

Answer

Add a calculated column extracting hour from TransactionDate in the fact table

Answer

Create a date dimension only and ignore time

Answer

Use DAX measures to extract hour on the fly

Question 5

Refer to the exhibit. You are reviewing a Power Query script. The script fails with a 'DataSource.Error: Microsoft SQL: Login failed for user' error. Which step should you check first?

Accepted Answer

The credentials used in the Source step.. The error 'DataSource.Error: Microsoft SQL: Login failed for user' indicates an authentication failure when connecting to the SQL Server database. This occurs at the Source step, where Power Query first attempts to establish a connection using the provided credentials. Checking and correcting the credentials in the Source step is the logical first step because no subsequent data transformations (like removing columns, changing types, or filtering rows) can execute if the initial data source connection fails.

Answer

The column name in #"Removed Columns".

Answer

The data type transformation in #"Changed Type".

Answer

The filter condition in #"Filtered Rows".

Question 6

You have a column 'ProductID' that contains integers. You need to ensure that this column is used as a key in relationships. What data type should the column have?

Accepted Answer

Whole Number.. Option C is correct because in Power BI, relationship keys must be of a data type that supports exact matching and efficient indexing. Whole Number (integer) is the optimal type for primary keys in relationships, as it ensures unique, non-decimal values that Power BI can use for fast lookups and joins without precision issues.

Answer

Decimal Number.

Answer

Text.

Answer

Binary.

Question 7

You are analyzing a Power BI dataset definition. The dataset refreshes but recently started failing with the error 'The 'OrderDate' column of the table 'Orders' has a date value that is out of range.' You need to diagnose the issue. What is the most likely cause?

Accepted Answer

The source table contains a date value that is not recognized as a valid DateTime by Power BI.. The error message indicates that a date value in the 'OrderDate' column of the 'Orders' table is out of the range supported by Power BI's DateTime type (1/1/1900 00:00:00 to 12/31/9999 23:59:59.999). This typically occurs when the source contains a date like '0001-01-01' or a future date beyond the upper bound, which Power BI cannot convert to a valid DateTime. Option A is correct because the source table has a date value that is not recognized as a valid DateTime by Power BI, causing the refresh to fail.

Answer

The columns in the dataset definition do not match the source table.

Answer

The connection string is invalid.

Answer

The M expression has a syntax error.

Question 8

You need to combine data from two tables in Power Query that have the same columns but different row sets. Which operation should you use?

Accepted Answer

Append Queries. Append Queries is the correct operation in Power Query when you need to combine two tables with identical columns but different row sets. It stacks the rows from the second table beneath the rows from the first table, effectively performing a union of the data. This is distinct from Merge Queries, which joins columns based on a key, and Group By, which aggregates data.

Answer

Union Queries

Answer

Group By

Answer

Merge Queries

Question 9

You have a table with a column 'Date' in text format (e.g., '2024-01-15'). You need to convert it to a date type. In Power Query, what is the best approach?

Accepted Answer

Change the column data type to Date in Power Query Editor.. Option D is correct because in Power Query Editor, you can simply change the data type of the column from Text to Date. Power Query automatically parses common date formats like '2024-01-15'. Option A is incorrect because splitting the column into year, month, day and then combining is unnecessarily complex and error-prone; Power Query can directly convert. Option B is incorrect because the Excel Power Query add-in is not needed; Power Query is built into Power BI Desktop. Option C is incorrect because using a DAX calculated column with DATEVALUE is not the best approach in Power Query; DAX operates on the data model after data is loaded, whereas Power Query transformations should be done before loading. Changing the data type in Power Query is the most efficient and standard method.

Answer

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

Answer

Use the Excel Power Query add-in.

Answer

Create a calculated column in DAX using DATEVALUE.

Question 10

You have a Power BI data model with a 'Sales' fact table and a 'Date' dimension. You need to create a calculated column in the 'Sales' table that shows the fiscal year based on a 'Date' column. The fiscal year starts on July 1. Which DAX expression should you use?

Accepted Answer

SWITCH(TRUE(), MONTH(Sales[Date]) >= 7, YEAR(Sales[Date]), YEAR(Sales[Date])-1). Option A is correct because it uses SWITCH with TRUE() to evaluate a logical condition: if the month of the date is July or later (MONTH >= 7), it returns the current year; otherwise, it returns the previous year. This correctly implements a fiscal year starting on July 1, as required.

Answer

YEAR(Sales[Date])

Answer

YEAR(Sales[Date]) + 1

Answer

FORMAT(Sales[Date], "YYYY")

Question 11

You are preparing data for a sales analysis report. The source system provides a table with columns: 'ProductID', 'ProductName', 'Category', 'SubCategory', 'Price', 'Cost'. You need to create a star schema. Which columns should be moved to dimension tables?

Accepted Answer

ProductID, ProductName, Category, and SubCategory. In a star schema, dimension tables contain descriptive attributes that provide context to measures. ProductID, ProductName, Category, and SubCategory are all descriptive attributes that should be moved to a dimension table (e.g., DimProduct) to reduce redundancy and enable efficient filtering and grouping. Measures like Price and Cost, however, are numeric values that typically belong in the fact table as additive or semi-additive measures.

Answer

ProductID and ProductName only

Answer

Price and Cost

Answer

ProductID, ProductName, Price, and Cost

Question 12

You are reviewing the M query above. The query connects to an Azure SQL database and filters orders after June 1, 2024. The query runs successfully in Power Query Editor, but when you close and apply, the data load fails. What is the most likely cause?

Accepted Answer

The database server is not accessible or credentials are missing.. The query runs successfully in Power Query Editor but fails during 'Close & Apply' because the editor uses a cached data preview, while the actual load requires a live connection to the Azure SQL database. The most likely cause is that the database server is inaccessible or the credentials are missing or expired at load time, which is a common connectivity issue in Power BI.

Answer

The date filter syntax is invalid.

Answer

The query has a syntax error in the let expression.

Answer

The column 'OrderDetails' does not exist.

Question 13

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 14

You are preparing data in Power BI Desktop. You have a table that includes a 'SalesAmount' column and a 'CurrencyCode' column. You need to create a measure that calculates the total sales amount in USD only, filtering out other currencies. Which DAX expression should you use?

Accepted Answer

CALCULATE(SUM(Sales[SalesAmount]), Sales[CurrencyCode] = "USD"). Option B is correct because it uses CALCULATE to modify the filter context, applying a filter that restricts the SUM of Sales[SalesAmount] to only rows where Sales[CurrencyCode] equals 'USD'. This is the standard DAX pattern for conditional aggregation in Power BI.

Answer

SUMX(FILTER(Sales, Sales[CurrencyCode] = "USD"), Sales[SalesAmount])

Answer

CALCULATE(SUM(Sales[SalesAmount]), Sales[CurrencyCode] = "USD") + 0

Answer

SUM(Sales[SalesAmount])

Question 15

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?

Accepted Answer

Split Column by Delimiter.. Option A is correct because 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 16

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 17

Your organization uses Power BI to analyze sales data stored in Azure SQL Database. The data model includes a fact table with millions of rows. To improve performance, you need to reduce the amount of data loaded into the model. Which action should you take?

Accepted Answer

Apply row-level filters in Power Query to import only relevant rows. Option A is correct because applying row-level filters in Power Query reduces the volume of data imported into the Power BI model by only loading rows that meet specific criteria. This directly minimizes the data footprint in memory, improving query and refresh performance, especially for fact tables with millions of rows stored in Azure SQL Database.

Answer

Use calculated tables in DAX to summarize data

Answer

Disable the Auto Date/Time feature

Answer

Configure incremental refresh with a date filter

Question 18

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

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 20

You are preparing data for a Power BI report. The source data contains a column with mixed data types: some values are numbers, others are text. When loading into Power Query, the entire column is typed as text. What is the likely cause?

Accepted Answer

Power Query detected that the column contains text values in some rows, so it set the data type to text. Power Query's column type detection logic examines the entire column during import. If any row contains a non-numeric value (e.g., text), Power Query defaults the entire column to text to avoid data loss or conversion errors. This is the standard behavior when mixed data types are present, regardless of the source format.

Answer

The column was imported as text because the data source is a CSV file

Answer

The 'Detect data type' option was disabled in Power Query settings

Answer

The source data is stored as text in the database