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

Question 1

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

Accepted Answer

Remove duplicates. 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 2

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.. The 'Detect Data Type' function in Power Query analyzes the column values and uses the system locale settings to parse dates in mixed formats, such as '2024-01-15 14:30:00' and '01/15/2024 2:30 PM', converting them to a consistent datetime type. This is the recommended first step for data type detection and ensures that time intelligence calculations in Power BI can rely on a uniform date/time column.

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 3

You are debugging a Power Query that imports a CSV file. The exhibit shows the M code. The CSV file contains a header row and data. Some rows have a comma inside a quoted field (e.g., "Smith, John"). What issue will arise from this code?

Accepted Answer

The QuoteStyle.None option will cause commas inside quotes to be treated as delimiters.. The M code uses `QuoteStyle.None`, which tells Power Query to treat commas inside quoted fields as column delimiters rather than as part of the field value. This causes rows with values like "Smith, John" to be split incorrectly, resulting in extra columns and misaligned data. The correct option for CSV files with quoted fields is `QuoteStyle.Csv`, which respects the standard CSV quoting rules.

Answer

The encoding 1252 is incorrect for the file.

Answer

The number of columns specified (5) is too many.

Answer

The Promoted Headers step will fail because the first row contains quotes.

Question 4

You are troubleshooting a Power Query that fails to refresh. The error message indicates a 'DataFormat.Error: Invalid cell value' for a column. Which TWO actions can help identify the problematic rows?

Accepted Answer

Apply a filter to the column to exclude errors and see which rows remain.. Applying a filter to the column and selecting 'Errors' (or deselecting errors) lets you isolate rows that contain invalid cell values. This directly shows which rows are causing the DataFormat.Error, allowing you to inspect or correct them. It is a quick, non-destructive way to identify problematic data without removing or altering other rows.

Answer

Use 'Group By' to aggregate the column.

Answer

Use 'Replace Errors' with a default value.

Answer

Change the column type to text to avoid the error.

Question 5

You are working with a Power Query that uses a merge operation between two tables. The merge is based on a column with text values, but some values have leading or trailing spaces. Which THREE steps can you take to ensure the merge works correctly?

Accepted Answer

Use the 'Transform' tab to change the case to uppercase for both columns.. Using the 'Transform' tab to change the case to uppercase for both columns ensures that the merge operation is case-insensitive, which is necessary when text values have leading or trailing spaces but also differ in case. This step normalizes the text values, making the merge more reliable by eliminating case mismatches that could cause the merge to fail or produce incorrect results.

Answer

Remove duplicate rows from both tables.

Answer

Split the column by delimiter and merge on the first part.

Question 6

You are importing data from a web page that contains an HTML table. Power Query detects the table, but you notice that some columns contain HTML tags. Which TWO methods can you use to remove the HTML tags from the column values?

Accepted Answer

Use 'Replace Values' to remove specific tags like
and

.. 'Replace Values' allows you to manually remove specific HTML tags (e.g.,
,

) by replacing them with an empty string, which is a straightforward method for cleaning known tags. Option E is correct because the 'Html.Table' function in Power Query can parse HTML content and extract the text from a table, effectively stripping tags and returning clean text values.

Answer

Split the column by delimiter '<' and then extract the first part.

Answer

Remove the columns containing HTML tags.

Answer

Use the 'Clean' function to remove all HTML tags.

Question 7

You are connecting Power BI to a SQL Server database. The database contains a table with millions of sales transactions. You need to design a data model that minimizes load time and memory usage while still allowing analysis of sales by date, product, and customer. Which modeling approach should you use?

Accepted Answer

Import the data into Power BI, creating a star schema with date, product, and customer dimension tables and the sales fact table.. Importing the data into Power BI and modeling it as a star schema minimizes query load time (the time to run interactive analyses) and memory usage through columnar compression and optimized query performance. Import mode stores data in the VertiPaq engine, which compresses data efficiently, especially with a star schema, reducing memory footprint relative to flattened tables and enabling fast in-memory analysis. While DirectQuery does not store data in Power BI (thus lower memory usage and no data refresh load time), it results in slower query performance and higher load on the source database, which is not ideal for analyzing millions of rows interactively.

Answer

Use a composite model with some tables in DirectQuery and others in Import.

Answer

Use DirectQuery mode to avoid storing data in Power BI.

Answer

Use a live connection to an existing SQL Server Analysis Services tabular model.

Question 8

You are loading data from a folder containing multiple Excel files with identical structure. Some files have inconsistent column names due to manual edits. You need to ensure that all data is loaded correctly without errors. What should you do in Power Query?

Accepted Answer

Use the 'Combine Files' feature with a sample file, then in the transformation step, promote headers and rename columns using a mapping table.. The 'Combine Files' feature in Power Query uses a sample file to infer the schema, and then you can apply transformations like promoting headers and renaming columns using a mapping table to handle inconsistent column names across files. This ensures all data loads without errors by standardizing the column names before combining.

Answer

Use 'Merge Queries' to join the files based on row position.

Answer

Change the data source to a SharePoint folder and use 'Load to Data Model' directly.

Answer

In Power Query, use 'Enter Data' to manually create the schema.

Question 9

You are reviewing a Power BI dataset definition (in JSON format). The dataset refreshes daily but takes over 2 hours. You need to reduce the refresh time while ensuring that the most recent 4 years of data is always available. What is the most effective change to the dataset?

Accepted Answer

Modify the M expression to use a native SQL query with a WHERE clause on OrderDate.. Using a native SQL query with a WHERE clause on OrderDate pushes the filtering of data to the source database, reducing the amount of data transferred and processed by Power BI. This is the most effective change to reduce refresh time while ensuring the most recent 4 years of data is available, as it leverages the database engine's indexing and query optimization capabilities.

Answer

Add an index on the OrderDate column in the source database.

Answer

Remove the Amount column from the dataset.

Answer

Implement Incremental Refresh policy with a date range filter on OrderDate.

Question 10

You are preparing data for a report that requires joining sales data from a SQL database with customer demographic data from a CSV file. The CSV file contains duplicate customer IDs. How should you handle duplicates to ensure accurate analysis?

Accepted Answer

In Power Query, remove duplicates from the CustomerID column in the CSV table before merging.. Removing duplicates from the CustomerID column in the CSV table before merging ensures a one-to-one or one-to-many relationship with the SQL sales data, preventing inflated row counts and inaccurate aggregations. In Power Query, the 'Remove Duplicates' operation keeps the first occurrence by default, which is appropriate when no other filtering criteria are specified. This step is critical for maintaining referential integrity in the data model.

Answer

Group by CustomerID and aggregate all columns.

Answer

In Power Query, use 'Merge Queries' and select 'Merge with duplicates' option.

Answer

Load both tables as-is and create a relationship in the data model.

Question 11

You need to combine data from three different SharePoint lists into a single table for analysis. The lists have different column names but contain similar data. What is the best approach in Power Query?

Accepted Answer

Use 'Append Queries' and then rename columns to match.. Append Queries is the correct approach because it stacks rows from multiple tables (SharePoint lists) into a single table, which is exactly what you need when combining data with similar structures but different column names. After appending, you can rename columns to unify the schema. This is the standard Power Query method for row-level concatenation of tables.

Answer

Use 'Unpivot Columns' to transform the data.

Answer

Use 'Group By' to combine values.

Answer

Use 'Merge Queries' on a common key column.

Question 12

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

You are importing data from a CSV file that contains a column 'Date' with values in different formats (e.g., '01/15/2024', '2024-01-15'). You need to ensure all dates are recognized correctly. What should you do in Power Query?

Accepted Answer

In Power Query, set the data type to 'Date' and use 'Using Locale' to specify the correct locale for each file.. Power Query's 'Using Locale' feature allows you to specify the cultural context (e.g., 'en-US' for MM/DD/YYYY or 'de-DE' for DD.MM.YYYY) when setting a column's data type to 'Date'. This ensures that dates in various formats, such as '01/15/2024' (US) and '2024-01-15' (ISO 8601), are interpreted correctly during import, as the locale dictates the expected date order and separator.

Answer

Change the data source to a SQL database.

Answer

Split the column into multiple columns and merge them back.

Answer

Use 'Replace Values' to standardize the date formats.

Question 14

Which TWO data sources can you connect to from Power BI Desktop using a native connector? (Select two.)

Accepted Answer

MySQL database (on-premises). Power BI Desktop includes native connectors for Azure Blob Storage, Dataverse, MySQL, Oracle, and MongoDB. Therefore, all five options are valid, making the question invalid as written. The marked answer (C and D) is not uniquely correct.

Answer

Azure Blob Storage

Answer

Dataverse

Answer

MongoDB

Question 15

Which THREE actions in Power Query Editor can improve the performance of data refresh? (Select three.)

Accepted Answer

Remove columns that are not used in the report.. Removing unused columns reduces the amount of data loaded into the Power Query engine and the data model, directly decreasing memory usage and refresh time. Power Query processes all columns in a query step, so eliminating unnecessary columns early minimizes the data footprint for subsequent transformations and the final load.

Answer

Sort data in ascending order to improve compression.

Answer

Merge queries before filtering.