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

Question 1

You are connecting Power BI to an Azure SQL Database. The database contains a table with 10 million rows. You need to minimize the initial load time for the report. What should you do?

Accepted Answer

Use DirectQuery mode. DirectQuery mode sends queries directly to the Azure SQL Database, avoiding the need to import 10 million rows into the Power BI model. This eliminates the initial data transfer and processing overhead, significantly reducing the initial load time for the report.

Answer

Use Import mode and create aggregations

Answer

Use Dual mode

Answer

Use Import mode with Incremental refresh

Question 2

You have a Power BI dataset that uses DirectQuery to a Snowflake data warehouse. Users report that reports are slow. You need to improve query performance without changing the data source. What should you configure?

Accepted Answer

Create aggregations in the Power BI model. Creating aggregations in the Power BI model allows pre-summarized data to be stored in memory, reducing the volume of queries sent to Snowflake via DirectQuery. This improves performance without altering the data source, as aggregations can serve high-level queries from cached data while falling back to DirectQuery for detailed queries.

Answer

Enable Query Folding in Power Query

Answer

Change the dataset to Import mode

Answer

Increase the number of concurrent queries in Power BI Service

Question 3

You are using Power Query to combine data from multiple Excel files in a SharePoint folder. Each file has a sheet named 'Sales'. The columns across files are identical but occasionally a file has extra columns. You need to ensure the combined table contains only the common columns across all files. Which Power Query step should you use?

Accepted Answer

Use Remove Other Columns on the first file's columns. Use Remove Other Columns on the first file's columns (the sample file) after selecting only the columns that are common to all files. This defines the schema so Power Query excludes any extra columns from other files. Table.Combine with the 'Table1' parameter keeps only the columns of the first table; if that first file happens to contain an extra column, the combined output will include it and will not guarantee only common columns. Table.ColumnsOfType filters by data type, and Merge Queries as New joins tables horizontally.

Answer

Use Table.Combine with the 'Table1' parameter

Answer

Use Table.ColumnsOfType to filter common columns

Answer

Use Merge Queries as New

Question 4

You are importing data from a CSV file that contains a column 'OrderDate' with values in the format 'YYYY-MM-DD'. Power Query automatically detects the data type as Date. You need to ensure that the data type remains Date even if the source file later changes the date format to 'MM/DD/YYYY'. What should you do?

Accepted Answer

Set the data type to Date using the 'Change Type' step in Power Query. The correct option is C: set the data type to Date using the 'Change Type' step in Power Query. This explicitly fixes the column's type as Date in the query's applied steps, so Power Query parses the incoming values according to the defined type rather than relying on automatic detection, which can vary with source format changes. Using 'Using Locale' (B) is only needed when the source text format is ambiguous or locale-specific, and it does not by itself guarantee a stable Date type. Splitting into year/month/day (A) is unnecessary and would not produce a Date type directly, and changing the Power Query editor's regional settings (D) affects locale interpretation globally rather than pinning the column's data type.

Answer

Split the column into year, month, day and then combine

Answer

Use the 'Using Locale' transformation

Answer

Change the regional settings of the Power Query editor

Question 5

You are preparing data from a SQL Server database. The table 'Sales' contains a column 'OrderDate' that includes both date and time (e.g., '2023-10-15 14:30:00'). You need to create a separate column for the time portion only. Which TWO Power Query transformations can you use?

Accepted Answer

Extract - Duration. The 'Extract - Duration' transformation in Power Query extracts the time portion from a datetime column by calculating the duration since midnight, effectively isolating the time component. Option E is correct because splitting the column by a space delimiter separates the date and time parts into two columns, allowing you to keep only the time portion. Both methods produce a time-only value suitable for analysis.

Answer

Extract - Year

Answer

Merge Columns

Answer

Format - Trim

Question 6

You have a Power BI dataset that uses DirectQuery to an Azure Synapse Analytics dedicated SQL pool. You need to improve query performance. Which THREE actions should you take?

Accepted Answer

Create indexes on columns used in filters. Option A is correct because in a DirectQuery model against a Synapse dedicated SQL pool, every visual generates a SQL query pushed to the source, so creating indexes (e.g., clustered columnstore or nonclustered indexes) on columns frequently used in filters and joins lets the dedicated SQL pool seek/scan far less data and return results faster. Option C is correct because pre-aggregated tables in the source reduce the row volume and computation the dedicated SQL pool must perform at query time, so DirectQuery visuals hit small summary tables instead of scanning large fact tables. Option D is correct because materialized views in Azure Synapse dedicated SQL pools persist precomputed result sets and are automatically maintained, giving DirectQuery queries fast access to aggregated data without recomputing joins and aggregations on every request. Option B is not appropriate because normalizing tables increases the number of joins the dedicated SQL pool must execute per DirectQuery query, which typically degrades performance in an analytical MPP warehouse. Option E is not appropriate because switching to Import mode changes the storage/query architecture entirely and is not a tuning action for a DirectQuery dataset; the scenario requires improving DirectQuery performance against the Synapse source.

Answer

Normalize the data warehouse tables

Answer

Switch the dataset to Import mode

Question 7

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 8

You are configuring a Power BI dataset with incremental refresh. The above JSON shows part of the M script parameters. The dataset uses a single SQL Server data source. You need to ensure that incremental refresh works correctly. What must you do?

Accepted Answer

Rename the parameter 'StartDate' to 'RangeStart' and add a 'RangeEnd' parameter.. Incremental refresh in Power BI requires two special date/time parameters named 'RangeStart' and 'RangeEnd' (case-sensitive). The JSON snippet shows a parameter named 'StartDate', which is not recognized by the incremental refresh engine. Renaming it to 'RangeStart' and adding a 'RangeEnd' parameter allows Power BI to filter the data source query dynamically during refresh, ensuring only the changed or new rows are loaded.

Answer

Change the connection string to use a SQL account instead of integrated security.

Answer

Enable query folding on the SQL Server source.

Answer

Define the parameters in the Power Query editor instead of the JSON file.

Question 9

You are preparing data from multiple sources for a Power BI report. You need to create a star schema with a single fact table and several dimension tables. Which of the following is a best practice when designing the data model?

Accepted Answer

Ensure each dimension table has a unique key and contains descriptive attributes.. In a star schema, dimension tables should have a unique key (surrogate or natural) and contain descriptive attributes to enable filtering and grouping in Power BI. This ensures efficient relationships with the fact table and supports intuitive report interactions. Option C directly aligns with this best practice.

Answer

Include calculated measures in dimension tables.

Answer

Normalize dimension tables into multiple related tables.

Answer

Use natural keys from the source system as the primary key in dimension tables.

Question 10

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 11

You are connecting to a data source that contains Personally Identifiable Information (PII). You need to ensure that only authorized users can view the data in Power BI reports. Which TWO actions should you take?

Accepted Answer

Define row-level security (RLS) roles in Power BI Desktop.. Row-Level Security (RLS) allows you to restrict data access at the row level based on user roles defined in Power BI Desktop. By creating roles and using DAX filters, you can ensure that only authorized users see specific rows of PII data when the report is published to the Power BI service and users are mapped to roles.

Answer

Enable encryption at rest for the dataset.

Answer

Use Power Query to mask PII columns by replacing values with '***'.

Answer

Apply sensitivity labels to the dataset.

Question 12

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.. Option D is correct because Power Query M has no built-in awareness of a custom 'nextPageToken' field, so you must author a recursive (or iterative) M function that calls Web.Contents for the initial URL, reads the nextPageToken from the JSON response, and re-invokes itself with the token appended as a query parameter until the token is null or absent. Option E is correct because Table.GenerateByPage is the standard M helper designed exactly for this pattern: you supply a 'getNextPage' function that returns the next page (or null when done), and it assembles all pages into a single table, which is the idiomatic way to implement token-based pagination. Option A is not correct because Power Query's automatic pagination detection only recognizes specific conventions such as RFC 5988 Link headers or OData nextLink, not an arbitrary 'nextPageToken' JSON property. Option B is not correct because incremental refresh only filters and partitions data by a datetime column on subsequent refreshes; it does not follow pagination tokens to retrieve all pages. Option C is not correct because DirectQuery against a REST API is not a supported Power BI connector scenario and, even conceptually, the server would not paginate on Power Query's behalf.

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 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.. Option A is correct because building a Product dimension requires extracting the product-related attributes from the flat table, deduplicating them, and assigning a surrogate key to uniquely identify each product row independently of the source business key. Option B is correct because removing duplicate rows ensures each product appears once in the dimension, and creating a relationship from the fact table to the dimension via a key (surrogate key) establishes the star schema join needed for slicing sales by product. Option C is correct because a dedicated Date dimension derived from the date column with attributes such as year, quarter, and month enables time-based analysis and is a standard star schema component. Option D is incorrect because merging Customer and Product into one table creates a snowflaked/denormalized hybrid that violates star schema design and causes redundant data and ambiguous grain. Option E is incorrect because keeping all columns in a single flat table defeats the purpose of a star schema, which requires separating dimensions from the fact table to improve query performance and usability.

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 troubleshooting a Power BI report that uses a DAX query to summarize sales data. The query returns rows but the TotalSales column shows blank for some ProductID/Region combinations. What is the most likely cause?

Accepted Answer

The FILTER function removes rows where Quantity <= 10 from the entire Sales table, so those combinations have no data to aggregate.. The FILTER function in DAX, when used as a table filter argument in SUMMARIZECOLUMNS, applies a row-level filter to the entire Sales table before aggregation. If Quantity <= 10 for a given ProductID/Region combination, all rows for that combination are removed, leaving no data to aggregate, which results in a blank TotalSales. This is a common cause of unexpected blanks when using FILTER inside SUMMARIZECOLUMNS.

Answer

The syntax of SUMMARIZECOLUMNS is incorrect; it should use ADDCOLUMNS.

Answer

The filter condition is applied after aggregation, so it cannot filter out rows.

Answer

The relationships between tables are missing, causing blank values.