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

Question 1

You are preparing data from an Excel workbook that contains multiple sheets. Each sheet has a similar structure but different data. You need to combine all sheets into a single table in Power Query. What is the most efficient approach?

Accepted Answer

Use the 'Combine Files' approach with the workbook as a folder, then select all sheets.. Option C is correct because the 'Combine Files' approach in Power Query treats the workbook as a folder, allowing you to select all sheets and automatically combine them into a single table. This is the most efficient method when multiple sheets have a similar structure, as it uses a single transformation step and handles dynamic sheet names without manual query creation.

Answer

Load each sheet as a separate query and then use 'Append Queries' to combine them.

Answer

Use 'Merge Queries' to join the sheets based on a common column.

Answer

Create a new query that references each sheet query and then merges them.

Question 2

You are reviewing a Power Query M expression that transforms column types. The 'SalesAmount' column contains values like '1,234.56' (with a comma as thousands separator). After applying this transformation, what is the likely result?

Accepted Answer

The transformation will result in errors for rows containing commas.. Option A is correct because Power Query's default type conversion for numeric columns expects a period as the decimal separator and no thousands separator. When the 'SalesAmount' column contains values like '1,234.56' with a comma as a thousands separator, attempting to convert the column directly to a number type (e.g., using 'Change Type' or 'Table.TransformColumnTypes') will cause errors for rows containing commas, as Power Query cannot parse the comma as part of a valid number. The comma is not a recognized numeric character in the default locale, so the conversion fails.

Answer

The column will be converted to text automatically.

Answer

The column will be successfully converted to numbers.

Answer

The transformation will ignore the comma and convert the number correctly.

Question 3

You are importing data from a folder containing multiple CSV files with identical structure. You want to automatically combine all files into one table in Power Query. Which connector should you use?

Accepted Answer

Folder connector. The Folder connector is the correct choice because it is specifically designed to connect to a folder containing multiple files, and when combined with the 'Combine Files' transformation in Power Query, it automatically merges all CSV files with identical structures into a single table. This connector handles the iterative process of reading each file and appending rows without manual scripting.

Answer

Excel Workbook connector

Answer

CSV connector

Answer

Web connector

Question 4

You are preparing a data model that uses a date table. You need to ensure that the date table includes all dates from January 1, 2020 to December 31, 2025. What is the most efficient way to create this date table in Power Query?

Accepted Answer

Use the 'List.Dates' function to generate the date range.. Option B is correct because the 'List.Dates' function in Power Query M is the most efficient way to generate a contiguous range of dates directly within the query editor, without external dependencies. It creates a list of dates from a start date to an end date using a specified step (e.g., #duration(1,0,0,0) for one day), which can then be converted into a table. This approach is lightweight, fully self-contained, and avoids the overhead of importing external files or using DAX functions that are not native to Power Query.

Answer

Import a date table from an Excel file.

Answer

Use the 'Calendar' function in Power Query M.

Answer

Use the 'CALENDAR' DAX function in Power Query.

Question 5

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.. Option B is correct because 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 6

You have a Power Query query that loads data from an OData source. You need to reduce the amount of data loaded into the data model. What is the best practice?

Accepted Answer

Apply a filter in Power Query before loading.. Option C is correct because applying filters in Power Query before loading data into the data model is the best practice for reducing data volume. Power Query pushes filters down to the OData source using OData query parameters (e.g., $filter), ensuring only the required rows are retrieved from the source. This minimizes network transfer and memory usage in the data model, aligning with the principle of early filtering in the ETL process.

Answer

Apply a filter in the data model using DAX.

Answer

Use 'Enable load' option to turn off loading for the query.

Answer

Load all data and then hide columns you don't need.

Question 7

You are preparing data for a Power BI report that requires a date table with continuous dates from 2020 to 2025. Which TWO methods can you use to create this date table in Power Query?

Accepted Answer

Use the List.Dates function to generate a list of dates.. Option D is correct because the List.Dates function in Power Query M generates a continuous list of dates by specifying a start date, a count of dates, and a step duration. This list can then be converted into a table, making it ideal for creating a date table directly in Power Query without leaving the data transformation environment.

Answer

Use the 'Enter Data' feature and manually type dates.

Answer

Use the CALENDAR DAX function in a calculated table.

Answer

Reference another query that already has dates.

Question 8

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.. Option A is correct because 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 9

You are importing data from a SQL Server database. The source table has a column 'ModifiedDate' of type datetime2. In Power Query, you want to ensure that only rows modified within the last 7 days are loaded. Which THREE steps should you take?

Accepted Answer

Use a parameter for the date range and reference it in the filter.. Option B is correct because using a parameter for the date range and referencing it in the filter allows for dynamic, maintainable filtering in Power Query. This approach leverages Power Query's M language to apply a filter step that can be easily updated without modifying the query logic, ensuring only rows from the last 7 days are loaded during data refresh.

Answer

Load all rows and then use a DAX filter in the data model.

Answer

Split the column into date and time and then filter on the date part.

Question 10

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.. Option A is correct because 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 11

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

.. Option B is correct because '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 12

You are reviewing a Power Query that imports data from SQL Server. The exhibit shows the M code. The SQL query filters records after a date, then Power Query filters rows with OrderQty > 10, and then groups by ProductID. What is a potential performance issue with this approach?

Accepted Answer

The filter on OrderQty > 10 should be included in the SQL query to reduce the amount of data transferred.. Option C is correct because pushing the `OrderQty > 10` filter into the SQL query reduces the amount of data transferred from SQL Server to Power Query. In Power Query, data is loaded into memory before transformations; filtering earlier in the source query minimizes memory usage and network latency, which is a key performance optimization in Power BI data loading.

Answer

The query will fail because the SQL query uses '>' with a string.

Answer

The SQL query should use a parameter for the date instead of a hardcoded value.

Answer

The grouping should be done in SQL to reduce data volume.

Question 13

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.. Option C is correct because importing the data into Power BI and modeling it as a star schema with separate dimension tables (date, product, customer) and a sales fact table minimizes load time and memory usage through columnar compression and optimized query performance. Import mode stores data in the VertiPaq engine, which compresses data efficiently, especially when using a star schema, reducing memory footprint and enabling fast in-memory analysis. This approach balances storage efficiency with analytical flexibility for millions of rows.

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 14

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.. Option A is correct because 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 15

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.. Option D is correct because 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.