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

Question 1

You are importing data from a CSV file that contains a column 'OrderDate' with dates in the format 'MM/dd/yyyy'. Some rows have invalid dates like '02/30/2023'. What is the best way to handle these errors in Power Query?

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

Accepted Answer

Use 'Replace Errors' to replace error values with null.. 'Replace Errors' in Power Query allows you to replace error values (which occur when Power Query fails to convert an invalid date like '02/30/2023' to the Date type) with null. This preserves the rest of the data and keeps the query running without interruption, while clearly marking invalid entries for later handling or analysis.

Answer

Remove rows with errors using 'Remove Rows' > 'Remove Errors'.

Answer

Change the data type to 'Date' and ignore errors.

Answer

Filter the column to exclude rows where the date is invalid after type conversion.

Question 2

You are cleaning data in Power Query. A column contains customer names with inconsistent capitalization (e.g., 'john smith', 'JANE DOE'). You need to standardize the names to proper case (first letter uppercase, rest lowercase). Which transformation should you use?

Accepted Answer

Use 'Format' > 'Capitalize Each Word'.. The 'Capitalize Each Word' transformation in Power Query converts the first letter of each word to uppercase and the rest to lowercase, which is exactly what proper case requires. This is the correct choice because it directly addresses the need to standardize inconsistent casing (e.g., 'john smith' becomes 'John Smith', 'JANE DOE' becomes 'Jane Doe').

Answer

Use 'Format' > 'Trim'.

Answer

Use 'Format' > 'Lowercase'.

Answer

Use 'Format' > 'Uppercase'.

Question 3

You are developing a Power BI semantic model that uses a large fact table from Azure Synapse Analytics. You need to optimize the model for performance. Which THREE actions should you take?

Accepted Answer

Disable auto-date/time feature for the model.. Option A is correct because disabling the Auto date/time feature prevents Power BI from creating hidden date tables for every date column, which reduces model size and memory consumption and speeds up processing and refresh. Option B is correct because integer surrogate keys compress far better in the VertiPaq engine than string keys, lowering memory usage and improving join and relationship performance. Option E is correct because removing unnecessary columns from the fact table reduces the model's memory footprint and improves compression and query performance. Option C is not appropriate because combining the fact table with dimensions into a single wide table denormalizes the star schema, increases storage and redundancy, and typically degrades performance rather than optimizing it. Option D is not appropriate because calculated columns are computed at refresh time and consume memory, whereas transformations in Power Query are performed during load and are generally preferred for performance.

Answer

Combine the fact table with dimension tables into a single wide table.

Answer

Create calculated columns in the fact table instead of in Power Query.

Question 4

You are using Copilot for Power BI to assist with data preparation. Which THREE tasks can Copilot help you with?

Accepted Answer

Generate DAX expressions for calculated columns and measures.. Copilot for Power BI is designed to accelerate data preparation and modeling by generating DAX expressions for calculated columns and measures (A), which is a core supported capability for authoring calculations from natural-language prompts. It can also create Power Query M code snippets for common transformations (B), helping users build or extend queries in the Power Query Editor. Additionally, Copilot can suggest transformations to clean and shape data (D), such as removing duplicates, changing types, or splitting columns, which directly supports data preparation. Options C and E are not Copilot tasks: incremental refresh policies and data source credentials are configured manually in Power BI Desktop or the Power BI service settings, not generated by Copilot.

Answer

Set up incremental refresh policies.

Answer

Configure data source credentials for refresh.

Question 5

You are importing data from a folder containing multiple CSV files with identical structure. You use the 'Combine files' transform in Power Query. Which TWO statements are true about this process?

Accepted Answer

Power Query creates a function that applies the same transformations to each file.. Option D is correct because when you use the 'Combine files' transform in Power Query, it generates a reusable function (typically named 'Transform File' or similar) that encapsulates the transformations applied to the sample file, and then invokes that function for every other file in the folder so each file undergoes the same steps. Option E is correct because Power Query creates a separate sample file query (often named 'Sample File' or 'Transform Sample File') that acts as a template, defining the schema and transformation logic that the function applies to all files. Option A is not correct because the sample file is not automatically chosen by alphabetical order; Power Query typically selects the first file it encounters (often based on the folder's file listing order) and lets the user pick a different sample file if needed. Option B is not correct because all files in the folder are combined, not just the sample file. Option C is not correct because the combine process is handled entirely within Power Query's M engine, not by Power Automate.

Answer

The sample file is automatically selected as the first file in alphabetical order.

Answer

Only the sample file is imported; other files are ignored.

Answer

The combine process uses Power Automate to merge files.

Question 6

You are connecting to a SharePoint folder containing 100 Excel files. Each file has a similar structure but different column names. What is the best practice to combine these files into a single table while preserving the data?

Accepted Answer

Use Power Query's 'Combine Files' feature, selecting a sample file and promoting headers, then transforming column names to a standard set.. Power Query's 'Combine Files' feature is designed specifically for this scenario: it uses a sample file to infer the transformation logic (e.g., promoting headers), then applies that logic to all files in the folder. By transforming column names to a standard set within the sample file step, you ensure consistent column names across all files, preserving data integrity while combining them into a single table.

Answer

Load each file as a separate table and create relationships in the model.

Answer

Use Power Query's 'Merge Queries' to join all files into one table.

Answer

Use 'Append Queries' to stack all files, then rename columns manually.

Question 7

You are designing a data model for a sales analysis report. The source data includes a Sales table with columns: OrderID, CustomerID, ProductID, OrderDate, Quantity, and UnitPrice. You also have a Customers table and a Products table. Which approach best optimizes query performance and storage?

Accepted Answer

Create a star schema with Sales as a fact table and Customers and Products as dimension tables.. A star schema optimizes query performance and storage in Power BI by separating transactional data (Sales fact table) from descriptive attributes (Customers and Products dimension tables). This reduces data duplication, improves compression, and enables efficient aggregations and filter propagation via one-to-many relationships, which is the recommended modeling approach for analytical workloads.

Answer

Create separate fact tables for each dimension.

Answer

Create a single flat table by joining all columns from Sales, Customers, and Products into one table.

Answer

Create a snowflake schema by normalizing Customers into multiple related tables.

Question 8

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 cloud-only data sources and does not require a gateway.. Azure SQL Database is a cloud-native data source in Power BI, and cloud data sources do not require a gateway, whether using DirectQuery or Import. The configuration shown in the JSON is for an Azure SQL Database, which is inherently cloud-only, so no gateway is needed. Option A is incorrect because key-based authentication is not supported for Azure SQL Database; using 'Key' credential type would cause a connection failure. Option B is incorrect because the JSON does not indicate single sign-on (no 'SingleSignOn' property set to true). Option C is incorrect because a cloud gateway is not required for Azure SQL Database; it is only needed for on-premises or virtual-network sources.

Answer

The dataset uses key-based authentication and does not use single sign-on.

Answer

The dataset uses single sign-on with Azure AD.

Answer

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

Question 9

You are importing data from a CSV file that contains a column 'OrderDate' with dates in the format 'MM/dd/yyyy'. Some rows have invalid dates like '02/30/2023'. What is the best way to handle these errors in Power Query?

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

Accepted Answer

In Power Query, select the column and change the data type to 'Date' using the 'Data Type' dropdown.. 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 10

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 11

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

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

Accepted Answer

Azure SQL Database. Azure SQL Database (A) is a valid Power BI data source because Power BI Desktop and the Power BI service include a native Azure SQL Database connector that uses the SQL protocol/TDS to query it directly. Oracle database (E) is also valid because Power BI ships with a dedicated Oracle connector (requiring the Oracle Data Provider for .NET, ODP.NET) to import or use DirectQuery against Oracle. SharePoint Online List (B) is not a database data source type—it is a cloud list service accessed via the SharePoint Online List connector, not a database. JSON file (C) is a flat/semi-structured file source consumed through the JSON connector, not a database. MongoDB (D) is a NoSQL document store and is not one of Power BI's built-in database connectors (it would require an ODBC driver or third-party tool), so it is not a valid database data source type here.

Answer

SharePoint Online List

Answer

JSON file

Answer

MongoDB

Question 13

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 14

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

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 query combines data from SQL Server and another data source with incompatible privacy levels.. A privacy level error in Power Query occurs when combining data from multiple sources with incompatible privacy levels. The query connects to SQL Server and performs transformations, but if it also references another source (e.g., from a previous step or additional data) with a privacy level that conflicts with SQL Server's privacy level, the Power Query firewall blocks the query. In this scenario, the most likely cause is that the query combines SQL Server data with another data source (such as a CSV file or another database) that has a privacy level set to 'Private' while SQL Server is set to 'Organizational' or 'Public', causing the error. Option D correctly identifies this. Options A and B are incorrect because a single data source cannot cause a privacy level error, and inconsistent privacy levels across environments do not trigger the firewall on a single source.

Answer

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

Answer

The privacy levels for the SQL Server data source are set inconsistently across the environment.

Answer

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