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

Question 1

You are importing data from an Excel workbook. The workbook has multiple sheets. You want to combine two sheets that have the same columns but different row data. Which TWO Power Query operations can you use?

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

Accepted Answer

Append Queries. Append Queries and Append Queries as New are both correct because they combine rows from two or more tables with identical columns into a single table. In Power Query, 'Append' is the operation designed for stacking rows vertically, which matches the requirement of combining sheets with the same columns but different row data.

Answer

Merge Queries

Answer

Group By

Answer

Pivot Column

Question 2

You are reviewing a Power BI data source configuration JSON. The exhibit shows a data source definition. What is the privacy level setting for the data source 'SalesData'?

Accepted Answer

Private. The privacy level setting for the data source 'SalesData' is 'Private' because the JSON snippet shows the property "privacyLevel":"Private". In Power BI, the Private privacy level restricts data from being combined with data from other sources, ensuring that sensitive data is not inadvertently shared across queries. This setting is explicitly defined in the data source configuration and is not Organizational, None, or Public.

Answer

Organizational

Answer

None

Answer

Public

Question 3

You are reviewing a Power Query M expression in the advanced editor. The exhibit shows the query. What is the final output of this query?

Accepted Answer

A table with total sales amount per customer for orders after 2023. The query filters the Sales table to keep only rows where the OrderDate is in 2024 or later (i.e., after 2023), then groups by CustomerID, summing the SalesAmount for each customer. The final output is a table with one row per customer showing their total sales amount for orders placed after 2023.

Answer

A table with total sales amount per customer for all orders

Answer

A table with all sales records after 2023

Answer

A table with all sales records after 2023, excluding Discount column

Question 4

You are reviewing a DAX query in DAX Studio. The exhibit shows a query that returns a table. What is the purpose of the SUMMARIZE function in this query?

Accepted Answer

To group sales by product category and compute total sales. The SUMMARIZE function in DAX is used to group rows from a table based on one or more columns (here, Product Category) and then compute an aggregated value (Total Sales) using a SUM expression. The query returns a new table with one row per category and the corresponding total sales, which is the standard purpose of SUMMARIZE for grouping and aggregation.

Answer

To add a calculated column to the Sales table

Answer

To add a new row for total sales per category

Answer

To filter the Sales table by Product Category

Question 5

You are preparing data from a SQL Server database for a Power BI report. The database contains a table with millions of rows of sales transactions. You need to minimize the data load time and reduce the model size. What should you do?

Accepted Answer

Use DirectQuery instead of Import mode.. DirectQuery does not import data into Power BI; it queries the source database directly, so there is no data load time and the model size is minimal (only metadata). This is the most effective way to minimize both load time and model size, especially for large tables with millions of rows. Option D reduces the data volume but still requires an import, so load time and model size are reduced but not eliminated. The question asks to minimize load time and model size, making DirectQuery the best choice.

Answer

Configure incremental refresh without filtering.

Answer

Enable query folding in Power Query.

Answer

Import only the required columns and apply row-level filters in Power Query.

Question 6

You are combining data from multiple Excel files stored in SharePoint Online. Each file has the same structure but different data. You need to create a solution that automatically includes new files added to the SharePoint folder without manual intervention. What should you use?

Accepted Answer

Use 'Get Data from SharePoint Online Folder' and then 'Combine files' transform.. The 'Get Data from SharePoint Online Folder' option in Power Query retrieves all files in the folder, and the 'Combine files' transform automatically merges files with the same structure into a single table. When the query is refreshed, any new files added to the folder are automatically included without manual intervention. Option A is incorrect because Power Automate requires additional setup and is not a native Power Query solution. Option B is incorrect because 'Merge queries' requires manual steps each time a new file is added. Option D is incorrect because 'Get Data from SharePoint Online List' does not combine files; it imports list data, not file contents.

Answer

Use Power Automate to copy new files to a blob storage, then import from there.

Answer

Use 'Merge queries' to append each new file manually.

Answer

Use 'Get Data from SharePoint Online List' and load each file separately.

Question 7

You are defining a Power BI dataset using a JSON policy for deployment pipelines. The above snippet defines a table named 'Sales' with a parameterized query. When you deploy this dataset to production, the refresh fails. What is the most likely cause?

Accepted Answer

The parameter value is not provided in the dataset settings.. When a Power BI dataset uses a parameterized query, the parameter value must be explicitly provided in the dataset settings under the 'Parameters' section. If the parameter value is not supplied or is missing after deployment to production, the refresh will fail because the query cannot resolve the parameter. This is a common oversight when moving datasets between pipeline stages.

Answer

The SQL source does not support parameterized queries.

Answer

The parameter type should be 'Text' instead of 'Int64'.

Answer

The mode 'Import' is not allowed with parameterized queries.

Question 8

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 9

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.. Disabling the auto-date/time feature prevents Power BI from automatically creating hidden date tables for each date column, reducing model size and processing time. Using integer surrogate keys instead of string keys improves compression and join performance, as integers are more efficient than strings. Removing unnecessary columns from the fact table reduces the model size and improves refresh and query performance by eliminating unused data. These three actions together optimize the model for large fact tables from Azure Synapse Analytics.

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 10

You are preparing data from a REST API that returns JSON. The API has a pagination mechanism using a 'nextPageToken' in the response. You need to ingest all data into Power BI. Which TWO methods can you use to handle pagination in Power Query?

Accepted Answer

Write a recursive M function that calls Web.Contents and follows the next page token.. You can write a recursive M function that repeatedly calls Web.Contents, extracts the 'nextPageToken' from each JSON response, and constructs the next request URL until no token is returned. This approach gives you full control over the pagination logic, which is necessary when the API does not support Power Query's built-in pagination detection. Alternatively, you can use the 'Table.GenerateByPage' function in Power Query M, which is designed specifically to handle pagination by taking a function that fetches a page and returns the next page token, simplifying the iterative process.

Answer

Enable automatic pagination detection in Power Query.

Answer

Configure incremental refresh to load data in batches.

Answer

Use DirectQuery to connect to the API and rely on the server to paginate.

Question 11

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 can generate DAX expressions for calculated columns and measures by interpreting natural language prompts, which helps users create complex formulas without deep DAX knowledge. This is a core feature of Copilot's AI assistance in Power BI Desktop.

Answer

Set up incremental refresh policies.

Answer

Configure data source credentials for refresh.

Question 12

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.. When you use the 'Combine files' transform in Power Query, it automatically generates a function that applies the same transformations (e.g., promoting headers, changing data types) to each CSV file in the folder. This function is invoked for every file, ensuring consistent data shaping across all files.

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 13

You are designing a data model for a sales analysis report. The source data includes a table with Sales, Product, Customer, and Date information in a single flat table. You need to transform this into a star schema with separate dimension tables. Which THREE actions should you take?

Accepted Answer

Create a Product dimension table by selecting distinct product attributes and assigning a surrogate key.. Creating a Product dimension table by selecting distinct product attributes and assigning a surrogate key is a fundamental step in building a star schema. This process normalizes the flat table by separating product-related attributes into a dedicated dimension, which reduces redundancy and improves query performance in Power BI. The surrogate key (e.g., an integer identity column) ensures a stable, unique identifier for each product, independent of any changes in the source system's natural keys.

Answer

Merge the Customer and Product dimensions into one table to reduce the number of tables.

Answer

Keep all columns in a single table to avoid relationships.

Question 14

You are preparing data from an on-premises SQL Server database for a Power BI report. The source table contains 10 million rows and you only need the last 3 months of data. Which approach minimizes the data load time and memory consumption in the dataset?

Accepted Answer

Use a SQL query in Power Query that includes a WHERE clause to filter the last 3 months.. Query folding ensures that the filter is pushed to the source database, reducing the amount of data transferred. Option A is wrong because loading all data and then filtering in Power Query still transfers the full dataset. Option C is wrong because it still imports all data. Option D is wrong because importing all data and using a filter on the report is less efficient.

Answer

Use Power Query to group by month and then filter after aggregation.

Answer

Load all data into Power Query and apply a filter on the date column using the 'Remove Rows' feature.

Answer

Load all data into Power BI and then use a report-level filter to show only the last 3 months.

Question 15

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.