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

Question 1

You are reviewing a Power BI dataset configuration in the service. The JSON shows a data source for an Azure SQL Database. Which statement about the configuration is correct?

Accepted Answer

The dataset uses key-based authentication and does not use single sign-on.. Option A is correct because the JSON configuration for an Azure SQL Database data source in Power BI typically includes a credential setting that specifies authentication type. When the JSON shows a data source with a credential type of 'Basic' or 'Key' (and no 'SingleSignOn' property set to true), it indicates key-based authentication (e.g., using a username and password or service principal key) and explicitly disables single sign-on (SSO). This means the dataset does not leverage the user's Azure AD identity for data access.

Answer

The dataset uses single sign-on with Azure AD.

Answer

The dataset is configured to use a cloud gateway for direct query.

Answer

The dataset uses cloud-only data sources and does not require a gateway.

Question 2

You are importing data from a CSV file that contains a column 'Date' with values like '2026-01-15'. After loading, Power Query detects the column as type 'text'. What is the recommended step to ensure the column is treated as a date?

Accepted Answer

In Power Query, select the column and change the data type to 'Date' using the 'Data Type' dropdown.. Option A is correct because in Power Query, the recommended method to convert a text column containing date-formatted strings (like '2026-01-15') to a proper Date type is to select the column and change its data type using the 'Data Type' dropdown in the Transform tab. This ensures the column is treated as a date for downstream calculations and modeling. Power Query automatically parses the text into a date based on the locale and format of the data.

Answer

In Power BI Desktop, use the 'Format' pane to set the column as a date.

Answer

Use the 'Parse' -> 'Date' transformation in Power Query.

Answer

Use the 'Detect Data Type' button in Power Query to automatically detect all columns.

Question 3

You are troubleshooting a Power BI report that uses a DAX query to summarize sales data. The query returns rows but the TotalSales column shows blank for some ProductID/Region combinations. What is the most likely cause?

Accepted Answer

The FILTER function removes rows where Quantity <= 10 from the entire Sales table, so those combinations have no data to aggregate.. Option D is correct because the FILTER function in DAX, when used as a table filter argument in SUMMARIZECOLUMNS, applies a row-level filter to the entire Sales table before aggregation. If Quantity <= 10 for a given ProductID/Region combination, all rows for that combination are removed, leaving no data to aggregate, which results in a blank TotalSales. This is a common cause of unexpected blanks when using FILTER inside SUMMARIZECOLUMNS.

Answer

The syntax of SUMMARIZECOLUMNS is incorrect; it should use ADDCOLUMNS.

Answer

The filter condition is applied after aggregation, so it cannot filter out rows.

Answer

The relationships between tables are missing, causing blank values.

Question 4

You are loading data from an Excel workbook that has multiple sheets. Each sheet contains sales data for a different region. You need to combine all sheets into one table. What is the most efficient approach?

Accepted Answer

Use Power Query's 'From Folder' option to combine all Excel files in a folder, then select 'Combine & Transform' to use the workbook as a sample.. Option D is correct because the 'From Folder' option in Power Query is designed to combine multiple Excel files with the same structure into a single table. By pointing to a folder containing the workbook, Power Query automatically detects all sheets as separate files and uses the 'Combine & Transform' feature to union them, which is more efficient than manually appending each sheet individually.

Answer

Use 'Append Queries' for each sheet individually.

Answer

Use 'Merge Queries' to join all sheets into one.

Answer

Load each sheet as a separate table in the model and create a calculated table to union them.

Question 5

You have a Power BI dataset that refreshes daily from an on-premises SQL Server database. The refresh fails with an error 'The data source credentials cannot be used for the connection'. What is the most likely cause?

Accepted Answer

The credentials stored in the gateway for the SQL Server have changed or expired.. Option C is correct because the error 'The data source credentials cannot be used for the connection' specifically indicates that the credentials stored in the on-premises data gateway for the SQL Server data source have changed or expired. When a Power BI dataset refreshes from an on-premises SQL Server via a gateway, the gateway uses stored credentials to authenticate; if those credentials are no longer valid (e.g., password was rotated), the connection fails with this exact error.

Answer

The on-premises gateway is not running.

Answer

The dataset is using DirectQuery and the connection string is incorrect.

Answer

The SQL Server is not accessible from the cloud.

Question 6

You are designing a data model for a retail company. The source system has a Sales table with columns: Date, StoreID, ProductID, SalesAmount. You need to create a date dimension table that includes all dates from the Sales table. Which DAX expression should you use to create the date table?

Accepted Answer

DateTable = CALENDARAUTO(). Option C is correct because `CALENDARAUTO()` automatically generates a contiguous date table spanning all dates present in the model, including those from the Sales table. This function scans all date columns in the model and returns a single column of dates from the earliest to the latest, which is the simplest way to create a date dimension that covers the full range of transaction dates without needing explicit MIN/MAX references.

Answer

DateTable = VALUES(Sales[Date])

Answer

DateTable = CALENDAR(MIN(Sales[Date]), MAX(Sales[Date]))

Answer

DateTable = DISTINCT(Sales[Date])

Question 7

Which TWO of the following are valid methods to transform data in Power Query?

Accepted Answer

Use 'Unpivot Columns' to turn selected columns into attribute-value pairs.. Options B and D are correct. Option A is wrong because 'Group By' is a transformation, not a merge. Option C is wrong because 'Append' is a transformation. Option E is wrong because 'Split Column' is a transformation.

Answer

Use 'Split Column' to divide a column into multiple columns based on a delimiter.

Answer

Use 'Merge Queries' to combine rows from multiple tables based on a key.

Answer

Use 'Append Queries' to combine columns from two tables.

Question 8

Which THREE of the following are best practices for optimizing data load performance in Power BI?

Accepted Answer

Remove unnecessary columns and rows during the import process.. Option A is correct because removing unnecessary columns and rows during the import process reduces the amount of data loaded into the Power BI data model, which directly decreases memory usage and refresh time. By filtering out irrelevant data early in Power Query, you minimize the data volume that must be processed and stored, leading to faster load performance.

Answer

Split a large fact table into multiple smaller fact tables.

Answer

Use DirectQuery mode instead of Import mode to reduce data load time.

Question 9

Which TWO of the following are valid data source types for Power BI?

Accepted Answer

Azure SQL Database. Azure SQL Database is a supported data source in Power BI, allowing direct import or DirectQuery connectivity via the SQL Server connector. This enables real-time or scheduled data refresh from a cloud-based relational database, leveraging T-SQL queries for data transformation.

Answer

JSON file

Answer

MongoDB

Answer

Oracle database

Question 10

You are preparing data from a CSV file that has inconsistent date formats. Some rows use 'MM/dd/yyyy' and others use 'dd/MM/yyyy'. You need to parse all dates correctly. What is the best approach in Power Query?

Accepted Answer

Use the 'Replace Values' to standardize the date format, then change data type.. Option A is correct because 'Replace Values' allows you to standardize the inconsistent date strings (e.g., swapping day and month parts) before Power Query attempts to parse them as dates. After replacing the delimiters or reordering parts, you can change the column type to 'Date' using a consistent culture (e.g., 'en-US' for MM/dd/yyyy), ensuring all rows parse correctly regardless of original format.

Answer

Use the 'Parse' -> 'Date' transformation with a specific culture.

Answer

Use the 'Split Column' by delimiter to separate date parts.

Answer

Use the 'Detect Data Type' feature to automatically identify the format.

Question 11

You are merging two tables in Power Query: Orders and Customers. The Orders table has a CustomerID column, and the Customers table has a CustomerID column. You want to keep all rows from Orders and only matching rows from Customers. Which join kind should you use?

Accepted Answer

Left Outer. Option C (Left Outer) is correct because it returns all rows from the Orders table regardless of whether a matching CustomerID exists in the Customers table. When no match is found, the Customers columns will contain null values. This is the standard behavior for a left outer join in Power Query, which preserves the left table's rows entirely.

Answer

Full Outer

Answer

Inner

Answer

Right Outer

Question 12

You have a Power BI dataset that uses a SQL Server view as the data source. The view returns data for all customers, but you only need data for customers in the 'West' region. The view does not accept parameters. How should you filter the data?

Accepted Answer

In Power Query, apply a filter on the Region column after loading the view.. Option B is correct because applying a filter in Power Query after loading the view is the standard approach when the source view does not accept parameters. This method leverages Power Query's query folding capabilities, which push the filter down to the SQL Server, reducing data transfer and improving performance. It avoids loading unnecessary data into the model while maintaining the ability to refresh efficiently.

Answer

Use a native SQL query in Power Query with a WHERE clause to ensure query folding.

Answer

Ask the database administrator to modify the view to include a WHERE clause.

Answer

Load the entire view into the model and use a report-level filter.

Question 13

You are building a Power BI data model that combines Sales data from SQL Server and Marketing data from a CSV file. The Sales table has a unique 'OrderID' column, and the Marketing table has a 'CampaignID' column. You need to create a relationship between Sales and Marketing to analyze campaign effectiveness. What should you do?

Accepted Answer

Create a bridge table containing unique combinations of OrderID and CampaignID.. Option B is correct because a bridge table resolves the many-to-many relationship between Sales (OrderID) and Marketing (CampaignID). Since there is no direct key match, creating a bridge table with unique combinations of OrderID and CampaignID allows Power BI to model the relationship correctly and analyze campaign effectiveness without data duplication or ambiguity.

Answer

Use an inactive relationship between Sales and Marketing and activate it with USERELATIONSHIP in measures.

Answer

Create a separate table for each campaign and relate to Sales.

Answer

Merge the Marketing table into the Sales table using a left outer join.

Question 14

Refer to the exhibit. The Power Query M code connects to a SQL Server database and performs data transformation. However, the query is failing with a privacy level error. What is the most likely cause?

Accepted Answer

The privacy levels for the SQL Server data source are set inconsistently across the environment.. The privacy level error in Power Query occurs when data from sources with incompatible privacy levels is combined. Option B is correct because the most likely cause is that the SQL Server data source has its privacy level set inconsistently across the environment (e.g., one source set to 'Private' and another to 'Public'), which prevents the query from merging or appending data due to Power BI's data privacy firewall. This firewall enforces isolation between sources based on their configured privacy levels, and an inconsistency triggers the error.

Answer

The SQL Server credentials are not correctly configured in the data source settings.

Answer

The query uses a CSV file from a local folder that has a privacy level set to 'Private'.

Answer

The query combines data from SQL Server and another data source with incompatible privacy levels.

Question 15

You are designing a Power BI semantic model for a retail company. You have a 'Products' table with columns: ProductID, ProductName, Category, UnitPrice. You need to ensure that when users filter by 'Category', they see only products belonging to that category. What should you set the 'Category' column's 'Data Category' property to?

Accepted Answer

Category. The 'Data Category' property in Power BI is used to assign a semantic meaning to a column, such as 'Category', 'Geography', or 'Image URL', which helps Power BI apply appropriate default behaviors like grouping or filtering. Setting the 'Category' column's 'Data Category' to 'Category' ensures that when users filter by that column, Power BI treats it as a categorical field, displaying only products belonging to the selected category. This property does not affect the underlying data but informs the visualization layer how to interpret the column for better user experience.

Answer

Geography

Answer

Image URL

Answer

Place

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

Exhibit

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

Exhibit