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

Question 1

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?

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

Accepted Answer

The FILTER function removes rows where Quantity <= 10 from the entire Sales table, so those combinations have no data to aggregate.. 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 2

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.. 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 3

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 = CALENDAR(MIN(Sales[Date]), MAX(Sales[Date])). `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 = DISTINCT(Sales[Date])

Question 4

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.. Unpivot Columns, Split Column, and Pivot Column are all valid data transformations in Power Query. Merge Queries combines columns by matching keys, and Append Queries combines rows, so D and E are incorrect as stated. Since the question requires exactly two correct answers, having three valid transformations makes it flawed. The correct answer set should include A, B, and C, or the stem should be revised.

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 5

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

Accepted Answer

Azure SQL Database. Azure SQL Database and Oracle database are both officially supported as 'Database' data source types in Power BI, allowing direct import or DirectQuery connectivity. SharePoint Online List, while a valid connector, falls under the 'Online Services' category rather than a core data source type for certification questions. JSON is a file format and not a data source type, and MongoDB requires a custom connector and is not natively supported as a listed source type.

Answer

SharePoint Online List

Answer

JSON file

Answer

MongoDB

Question 6

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. (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 7

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.. 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 8

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

Question 9

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.. 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 10

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.. When combining an Organizational source (SalesSQL) with a Private source (MarketingCSV), Power BI applies the most restrictive privacy level for data propagation. This means the Private source's data is protected, and the combination may be blocked or data may be limited to prevent exposing private data. Option A is wrong because MarketingCSV remains Private, not Organizational. Option B is wrong because the combine is not unrestricted; restrictions apply. Option C is wrong because the query does not fail with an error; it may succeed with limitations.

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 11

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

Change the storage mode to DirectQuery to reduce data movement.. Changing the storage mode to DirectQuery reduces data movement by querying the source directly instead of importing all data. This can significantly improve refresh performance when dealing with a remote SQL Server database, as it eliminates the need to transfer large volumes of data across regions. Options like increasing refresh frequency (B) do not address latency, installing an on-premises gateway (C) is for on-premises sources, and enabling 'Fast Combine' (D) bypasses privacy checks and is not a recommended performance optimization for regional latency.

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.

Answer

Enable the 'Fast Combine' option in the Power Query privacy settings.

Question 12

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.. Power Query relies on the locale of the current environment to interpret date strings. If the CSV contains dates in a format (e.g., 'dd/MM/yyyy') that does not match the locale's expected format (e.g., 'MM/dd/yyyy'), the engine cannot parse them and returns null. This is a common issue when importing data from different regional sources.

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 13

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.. 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 14

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

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