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

Question 1

You are importing data from an Excel workbook that contains multiple sheets. You only need data from the 'Sales' sheet. In Power Query Editor, what should you do to load only that sheet?

Accepted Answer

In the Navigator dialog, select the 'Sales' sheet and click 'Transform Data'.. In Power Query Editor, the Navigator dialog allows you to preview and select specific tables or sheets from a data source before loading. By selecting the 'Sales' sheet and clicking 'Transform Data', you load only that sheet into Power Query Editor for transformation, avoiding unnecessary data. This is the correct and efficient method to import a single sheet from a multi-sheet Excel workbook.

Answer

Load all sheets, then delete the queries for sheets you don't need.

Answer

Use a filter transform to exclude rows from other sheets.

Answer

Select the entire workbook and then filter out other sheets in Power Query.

Question 2

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 3

Which TWO data sources can be used with DirectQuery mode in Power BI? (Choose two.)

Accepted Answer

SQL Server. DirectQuery mode in Power BI requires a data source that supports query pass-through to a backend engine, and SQL Server (option B) qualifies because Power BI issues native T-SQL queries against the relational database in real time without importing data. Azure SQL Database (option C) is likewise a supported DirectQuery source, since it is a cloud-based SQL Server engine that accepts the same T-SQL query folding and live connection pattern. In contrast, Excel workbook (A), CSV file (D), and SharePoint list (E) are file- or list-based sources that Power BI imports rather than querying live, so they are not valid DirectQuery data sources.

Answer

Excel workbook

Answer

CSV file

Answer

SharePoint list

Question 4

Which TWO are valid methods to handle null values in Power Query? (Choose two.)

Accepted Answer

Use the 'Fill Down' or 'Fill Up' option to propagate non-null values into null cells.. 'Fill Down' and 'Fill Up' propagate the last non-null value into adjacent null cells. Option C is correct because 'Replace Values' can replace nulls with a default value. Option B is incorrect: 'Remove Blank Rows' removes rows where all cells are blank, not rows with nulls in specific columns. Option D is not a direct method for handling nulls; merging may introduce new data but does not handle existing nulls. Option E is invalid because changing to a non-nullable type causes errors.

Answer

Remove rows that contain null values using the 'Remove Rows' > 'Remove Blank Rows' option.

Answer

Merge the table with another table that has no nulls.

Answer

Change the data type of the column to a non-nullable type.

Question 5

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

Accepted Answer

Remove duplicates. Option A (Remove duplicates) is correct because Power Query provides a dedicated 'Remove Duplicates' transform that eliminates rows with identical values across selected columns, a core data-cleaning operation. Option C (Replace values) is correct because the 'Replace Values' transform substitutes specific text or numeric values (e.g., replacing 'N/A' with null), which is a standard cleansing step. Option E (Change data type) is correct because Power Query's 'Data Type' transform converts columns to the proper types (Text, Whole Number, Date, etc.), fixing type mismatches that would otherwise break calculations or loads. Option B (Group rows by a column) is not a cleaning transform but an aggregation/reshaping operation that summarizes data. Option D (Merge queries) is not a cleaning transform but a join operation that combines two queries, which is a data-shaping rather than cleansing activity.

Answer

Group rows by a column

Answer

Merge queries

Question 6

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

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 8

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

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 10

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

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