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

Question 1

You have a Power BI semantic model that imports data from a SQL Server view. The view is updated every hour, but the Power BI dataset is scheduled to refresh daily at 2:00 AM. Users report that reports show stale data. You need to ensure that the dataset reflects the latest data without changing the refresh schedule. What should you do?

Accepted Answer

Change the storage mode of the table to DirectQuery.. Option D is correct because changing the storage mode to DirectQuery allows the Power BI dataset to query the SQL Server view directly at report render time, ensuring that users always see the latest data without altering the scheduled refresh frequency. DirectQuery bypasses the import process and retrieves data on demand, so the dataset reflects real-time changes from the source view.

Answer

Replace the view with a physical table in SQL Server.

Answer

Increase the scheduled refresh frequency to every 30 minutes.

Answer

Configure incremental refresh for the table.

Question 2

Refer to the exhibit. You are reviewing a Power BI data source privacy configuration. The SalesSQL data source is set to 'Organizational' and the MarketingCSV is set to 'Private'. You plan to combine these two sources in a query. What will happen when the query is executed?

Accepted Answer

The query will succeed, but the combination may be blocked or data may be limited to protect the Private source.. Option C is correct because when combining an Organizational source with a Private source, Power BI restricts data propagation to protect the Private source. The Organizational data will be limited to what can be sent to the Private source, potentially causing performance degradation or blocking the combine operation. Option A is wrong because the combination is not entirely blocked, but limited. Option B is wrong because the Private level does not override the Organizational; instead, the most restrictive applicable rules apply. Option D is wrong because the combine is not allowed at full speed.

Answer

The query will succeed, but the MarketingCSV data will be treated as Organizational.

Answer

The query will succeed without any restrictions because the combine is allowed between Organizational and Private.

Answer

The query will fail with a privacy level error because the levels are incompatible.

Question 3

You are importing data from an Excel workbook that contains multiple sheets. You only need data from the 'Sales' sheet. In Power Query Editor, what should you do to load only that sheet?

Accepted Answer

In the Navigator dialog, select the 'Sales' sheet and click 'Transform Data'.. In Power Query Editor, the Navigator dialog allows you to preview and select specific tables or sheets from a data source before loading. By selecting the 'Sales' sheet and clicking 'Transform Data', you load only that sheet into Power Query Editor for transformation, avoiding unnecessary data. This is the correct and efficient method to import a single sheet from a multi-sheet Excel workbook.

Answer

Load all sheets, then delete the queries for sheets you don't need.

Answer

Use a filter transform to exclude rows from other sheets.

Answer

Select the entire workbook and then filter out other sheets in Power Query.

Question 4

You have a Power BI dataset that uses a SQL Server data source. The SQL Server database is located in a different region, causing slow refresh performance. You need to improve the data refresh performance without changing the data source. What should you do?

Accepted Answer

Enable the 'Fast Combine' option in the Power Query privacy settings.. Option D is correct because enabling the 'Fast Combine' option in Power Query privacy settings allows Power BI to ignore privacy levels for data sources, which can significantly improve query performance by enabling more efficient query folding and reducing the overhead of separate data source isolation checks. This is particularly beneficial when dealing with a remote SQL Server database, as it minimizes the number of round trips and data transformations required during refresh.

Answer

Change the storage mode to DirectQuery to reduce data movement.

Answer

Increase the scheduled refresh frequency to reduce the amount of data loaded each time.

Answer

Install an on-premises data gateway to reduce latency.

Question 5

Refer to the exhibit. The Power Query M code imports a CSV file. The import succeeds, but the 'OrderDate' column shows some dates as null even though they appear valid in the CSV. What is the most likely cause?

Accepted Answer

The date format in the CSV is not recognized by the locale used in the transformation.. Option A is correct because the '#Changed Type' step converts the column to type date, which will fail for any value that is not a valid date, resulting in null. If the CSV contains dates in a non-standard format (e.g., 'MM/dd/yyyy' vs 'dd/MM/yyyy'), the conversion may produce null. Option B is wrong because promoted headers only affect the first row. Option C is wrong because replacing 'null' with null would not affect dates. Option D is wrong because filtering rows does not affect data types.

Answer

The 'Filtered Rows' step removes rows where Amount > 0, which may include valid dates.

Answer

The 'Promoted Headers' step incorrectly changes the data type of the OrderDate column.

Answer

The 'Replaced Value' step replaces 'null' with null, causing the date to become null.

Question 6

You are preparing data in Power BI Desktop. You have a table with a column 'CustomerID' that contains duplicate values. You need to create a relationship to another table that also has 'CustomerID'. However, the relationship requires unique values in at least one of the tables. What should you do?

Accepted Answer

Remove duplicate rows from the 'CustomerID' column in the dimension table.. Option D is correct because to create a relationship in Power BI, at least one table must have unique values in the column used for the relationship. By removing duplicate rows from the 'CustomerID' column in the dimension table (which should contain unique identifiers), you ensure that the dimension table has unique values, allowing a one-to-many relationship to be established with the fact table. This is a standard data modeling practice in Power BI to enforce referential integrity.

Answer

Create a composite key using multiple columns.

Answer

Use the 'Merge Queries' feature to combine the tables into one.

Answer

Change the data type of 'CustomerID' to text.

Question 7

Which TWO actions can you take in Power Query Editor to improve data quality and reduce load time? (Choose two.)

Accepted Answer

Filter out empty rows.. Filtering out empty rows in Power Query Editor directly reduces the number of rows loaded into the data model, which decreases memory usage and improves refresh performance. This action also enhances data quality by removing incomplete records that could cause errors in measures or relationships.

Answer

Split columns by delimiter to create more columns.

Answer

Sort the data by a key column.

Answer

Merge queries to combine data from multiple sources.

Question 8

Which THREE considerations are important when designing a star schema for a Power BI semantic model? (Choose three.)

Accepted Answer

Role-playing dimensions should be implemented by creating multiple dimension tables or using views.. Option B is correct because role-playing dimensions, such as a Date dimension used for Order Date, Ship Date, and Due Date, require multiple references to the same dimension table. In Power BI, you implement this by creating multiple dimension tables (e.g., via calculated tables or referencing the same source table) or using database views to provide unique foreign key relationships, enabling correct time intelligence and filtering.

Answer

Dimension tables should use natural keys instead of surrogate keys for better performance.

Answer

Dimensions should be normalized (snowflaked) to reduce data redundancy.

Question 9

Which TWO data sources can be used with DirectQuery mode in Power BI? (Choose two.)

Accepted Answer

SQL Server. SQL Server is correct because it is a relational database that supports DirectQuery mode in Power BI, allowing queries to be sent directly to the source without importing data. This enables real-time access to large datasets while leveraging SQL Server's query processing capabilities.

Answer

Excel workbook

Answer

CSV file

Answer

SharePoint list

Question 10

Which THREE are valid methods to handle null values in Power Query? (Choose three.)

Accepted Answer

Use the 'Fill Down' or 'Fill Up' option to propagate non-null values into null cells.. Option A is correct because the 'Fill Down' and 'Fill Up' transforms in Power Query propagate the last non-null value into adjacent null cells within a column. This is a common technique for cleaning data where nulls represent missing values that should inherit the previous or next valid entry, such as in hierarchical or grouped datasets.

Answer

Merge the table with another table that has no nulls.

Answer

Change the data type of the column to a non-nullable type.

Question 11

Which TWO are best practices for optimizing Power Query performance? (Choose two.)

Accepted Answer

Disable the 'Enable background refresh' option in the query settings.. Option A is correct because disabling 'Enable background refresh' prevents Power Query from running queries in the background while you continue working in Power BI Desktop. This ensures that query execution is synchronous, which can improve performance by avoiding resource contention and allowing you to monitor progress directly. Background refresh can cause delays and unexpected behavior when multiple queries run simultaneously, especially with large data sources.

Answer

Merge tables as early as possible in the query to combine data.

Answer

Keep all columns in the table to avoid missing data.

Answer

Split columns by delimiter to normalize data.

Question 12

Which THREE are types of Power Query transforms that can be used to clean data? (Choose three.)

Accepted Answer

Remove duplicates. Option A is correct because the 'Remove duplicates' transform in Power Query identifies and eliminates rows with identical values across selected columns, which is a fundamental data cleaning operation to ensure data integrity and avoid skewed analysis.

Answer

Group rows by a column

Answer

Merge queries

Question 13

You are preparing a Power BI dataset from a SQL Server data source. The source table has a datetime column 'OrderDate' that you need to use for time intelligence calculations. However, you notice that the data contains dates in multiple formats (e.g., '2024-01-15 14:30:00' and '01/15/2024 2:30 PM'). What is the best practice to ensure consistent date handling in Power Query?

Accepted Answer

Use the 'Detect Data Type' function in Power Query to automatically identify and convert the column.. Option B is correct because the 'Detect Data Type' function in Power Query analyzes the column's values to infer the most appropriate data type, automatically handling mixed formats like '2024-01-15 14:30:00' and '01/15/2024 2:30 PM' by converting them to a consistent datetime type. This ensures that time intelligence calculations in Power BI can rely on a uniform date/time column without manual intervention or data loss.

Answer

Load the column as text and ignore any conversion errors.

Answer

Change the column type to 'Date' in Power Query before loading.

Answer

Split the column into separate date and time columns and then combine them.

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'. 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 15

You are reviewing a Power BI data source configuration in the data source settings. The exhibit shows the JSON representation of a data source. Which issue might arise from this configuration?

Accepted Answer

The authentication kind 'Key' is not supported for Azure SQL Database, causing connection failure.. Option D is correct because Azure SQL Database does not support the 'Key' authentication kind in Power BI data source settings. Azure SQL Database requires either Windows authentication, database credentials (Username/Password), or Azure AD-based authentication (such as OAuth2 or Service Principal). The 'Key' authentication kind is typically used for Azure Storage or Cosmos DB, not for Azure SQL Database, so this configuration will cause a connection failure.

Answer

The server name contains a hyphen, which is invalid in SQL Server connection strings.

Answer

The CommandTimeout value is too low and may cause queries to time out.

Answer

The option 'CreateNavigationProperties' is set to false, which may prevent relationships from being created.