PL-300 Prepare the data Practice Questions

Free PL-300 Prepare the data practice test questions

8 questions from this domain with answers and explanations - different from the samples on the main PL-300 page. Sign up free to practice the full set.

1. Question 1Prepare the data

   You need to combine rows from two queries that have the same schema into a single table. Which Power Query operation should you use?

   • AMerge Queries
   • BTranspose
   • CAppend QueriesCorrect
   • DGroup By
   ✓ Correct answer: C

   Append Queries stacks the rows of two or more queries that share the same column schema into a single table, much like a SQL UNION. Because both queries have identical columns, appending simply adds the second query's rows beneath the first query's rows. This is the correct operation when the goal is to combine records rather than to add columns from a related table.

   Why the other options are wrong

   • AMerge Queries joins tables side by side on matching key columns to bring in additional columns, not to stack rows, so it does not combine same-schema records.
   • BTranspose rotates rows into columns and columns into rows within a single table, which does not combine two queries.
   • DGroup By aggregates rows within one query into summarized groups; it does not append rows from a second query.
2. Question 2Prepare the data

   Tailwind Traders has a product catalog CSV file where some product categories are listed as rows instead of columns. The analyst needs to transform the data so that each category becomes a separate column with corresponding sales values. Which Power Query transformation should the analyst use?

   • AGroup By
   • BTranspose
   • CPivot ColumnCorrect
   • DUnpivot Columns
   ✓ Correct answer: C

   When category names are sitting in a column as row values and you need each distinct category to become its own column with its sales as the values, Pivot Column performs exactly that rotation. It takes the attribute column (category) and the value column (sales) and spreads the categories across the top as headers. This produces the wide, crosstab shape the analyst is after.

   Why the other options are wrong

   • AGroup By aggregates rows into summary groups but does not turn category values into separate columns.
   • BTranspose flips the whole table's rows and columns indiscriminately rather than pivoting one category column into headers.
   • DUnpivot Columns does the reverse: it collapses columns into rows, which is the opposite of the requirement.
3. Question 3Prepare the data

   You have a Power Query query that references another query (Query2) using the expression 'let Source = Query2 in Source'. What is this technique called?

   • AQuery appending
   • BQuery merging
   • CQuery chaining
   • DQuery referencingCorrect
   ✓ Correct answer: D

   When a Power Query query uses another query as its source through the syntax 'let Source = Query2 in Source', this is called query referencing. The query directly references another query by name, making the second query a dependency. This technique is fundamental to building transformation pipelines where queries build upon each other, transformations are applied sequentially, and intermediate staging queries support downstream queries.

   Why the other options are wrong

   • AQuery appending combines rows from multiple tables using the Append operation.
   • BQuery merging combines columns from multiple tables based on matching keys.
   • CQuery chaining is not a specific Power Query terminology term.
4. Question 4Prepare the data

   Power Query's 'Choose Columns' transformation allows you to select which columns to keep and automatically removes all unselected columns.

   • ATrueCorrect
   • BFalse
   ✓ Correct answer: A

   Power Query's 'Choose Columns' transformation allows you to select which columns you want to keep in your query result. When you use Choose Columns, you select the columns of interest, and Power Query automatically removes all unselected columns from the output. This is a convenient way to quickly eliminate unnecessary columns without individually removing each one. The transformation maintains only the selected columns in the resulting table.

   Why the other options are wrong

   • BFalse is incorrect. The statement is true. Power Query's 'Choose Columns' transformation allows you to select which columns you want to keep in your query result. When you use Choose Columns, you select the columns of interest, and Power Query automatically removes all unselected columns from the output.
5. Question 5Prepare the data

   A data analyst discovers that a column imported from a CSV file has mixed data types: some cells contain numbers and others contain text like 'N/A'. Power Query has set the column type to 'Any'. What should the analyst do to ensure the column contains only numeric values?

   • AApply the Remove Errors transformation right away
   • BReplace 'N/A' with null, then set the type to DecimalCorrect
   • CFilter the column to exclude all the text values
   • DChange the type to Whole Number to auto-clear the errors
   ✓ Correct answer: B

   When a CSV column mixes numbers with text such as 'N/A' and Power Query types it as 'Any', the reliable fix is to first use Replace Values to convert the 'N/A' entries to null, then set the column type to Decimal Number. Replacing the text with null neutralizes the non-numeric entries before conversion, so the type change succeeds without producing errors and preserves every row of numeric data.

   Why the other options are wrong

   • AThe Remove Errors transformation deletes whole rows that already contain errors, which removes records instead of giving you the chance to convert the 'N/A' values into nulls and keep the rows.
   • CFiltering the column to exclude text values discards entire rows, potentially losing records that should be retained with a null placeholder rather than removed from the table.
   • DChanging the data type to Whole Number first does not automatically clean or remove the 'N/A' text; the conversion would instead generate type errors on those cells, leaving the column unreliable.
6. Question 6Prepare the data

   A data engineer provides the analyst with a connection string and a stored procedure named sp_GetSalesData that accepts a @Region parameter. The analyst needs to call this stored procedure from Power Query with 'West' as the parameter value. What should the analyst do?

   • AImport every table from the database, then replicate the stored procedure's logic in a DAX calculated table filtered to West
   • BCreate a Power Automate flow that calls sp_GetSalesData with 'West' and writes the results to a file Power BI imports
   • CUse the OData feed connector and append @Region=West as a query string parameter onto the database connection URL
   • DUse the SQL Server connector, enter a native SQL statement in the advanced options: EXEC sp_GetSalesData @Region = 'West'Correct
   ✓ Correct answer: D

   The SQL Server connector's advanced options accept a native SQL statement, and executing a stored procedure with EXEC and the parameter value is the supported way to call sp_GetSalesData from Power Query. This runs the procedure on the server and returns its result set directly into the query. It keeps the parameterized logic in the database while letting Power BI consume the output.

   Why the other options are wrong

   • AImporting all tables and rebuilding the logic in DAX ignores the ready-made stored procedure and adds overhead.
   • BA Power Automate flow is an unnecessary detour; the SQL Server connector can call the procedure directly.
   • CThe OData connector cannot execute a T-SQL stored procedure, and SQL parameters are not URL query strings.
7. Question 7Prepare the dataSelect all that apply

   A consultant is reviewing the error handling in queries configuration at Tailwind Traders. Which two actions should be performed to optimize the implementation? (Choose two.)

   • ADisable error handling in queries monitoring
   • BColumn quality
   • CDirectQuery vs ImportCorrect
   • DM languageCorrect
   • EDataflows
   ✓ Correct answer: C, D

   To optimize error handling in queries, you must first evaluate the DirectQuery vs Import mode as each has different error handling characteristics and requirements. M language proficiency is critical because implementing robust error handling mechanisms often requires writing custom M expressions, such as try-catch patterns and error suppression functions that can gracefully handle data inconsistencies, missing values, or source system failures.

   Why the other options are wrong

   • ADisable error handling in queries monitoring is incorrect because removing monitoring prevents detection of errors and failures, making optimization impossible.
   • BColumn quality is incorrect because it measures data quality rather than query error handling mechanisms.
   • EDataflows is incorrect because while dataflows can include error handling, they are not an optimization action for existing error handling configurations.
8. Question 8Prepare the data

   You are configuring incremental refresh on a large fact table sourced from Azure SQL Database. You define the RangeStart and RangeEnd parameters and apply a filter on the OrderDate column. After publishing, the first refresh in the service still imports the entire table instead of partitioning. What is the MOST likely cause?

   • AThe RangeStart and RangeEnd parameters were created with a Date/Time data type but a non-foldable transformation sits before the filter, so the predicate cannot fold and the source cannot partitionCorrect
   • BIncremental refresh strictly requires the two parameters to be named StartDate and EndDate instead of RangeStart and RangeEnd; using the wrong names makes the whole table reload on the first service refresh
   • CIncremental refresh only supports an on-premises SQL Server reached through a data gateway and cannot partition an Azure SQL Database source, so the entire table has to be imported on every refresh
   • DThe semantic model must first be switched into DirectQuery storage mode before any incremental refresh policy can partition the table, because Import-mode semantic models always load their source data in full
   ✓ Correct answer: A

   Incremental refresh relies on query folding: the RangeStart/RangeEnd filter must fold into the source query so the engine can request only the partitions in the refresh window. The parameters MUST be of type Date/Time, and the filter must fold to the source - if a non-foldable transformation sits before the filter, the entire table is pulled and partitioning fails. The required reserved parameter names are exactly RangeStart and RangeEnd, and incremental refresh works with cloud sources like Azure SQL provided folding is intact. Always verify the step folds (View Native Query) so the predicate translates to a server-side WHERE clause.

   Why the other options are wrong

   • BThe parameters must be named exactly RangeStart and RangeEnd, so the required names are not the problem.
   • CIncremental refresh fully supports Azure SQL Database, so the source type is not what prevents partitioning.
   • DIncremental refresh is designed for Import-mode semantic models, not DirectQuery, so this prerequisite is invented.

How Prepare the data is tested

This domain holds 193 of the 690 questions in the PL-300 bank, about 28%. The mix is 109 single-answer multiple choice, 38 multiple-response, 28 true/false, 8 yes/no scenario, 5 hotspot and 5 ordering, so it is worth practising the formats as well as the content.

Once you have a few attempts recorded, CertGrid scores every domain separately and points you at the weakest one, so you can drill Prepare the data on its own rather than re-running full-length mocks.

Other PL-300 exam domains

• Model the data186 questions
• Visualize and analyze the data176 questions
• Manage and secure Power BI135 questions
• All PL-300 practice questions690 total
• Prepare the data study notesKey concepts
• Microsoft practice examsAll Microsoft

PL-300 Prepare the data FAQ