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

Question 1

You have a Power BI semantic model that uses DirectQuery to an Azure Synapse Analytics dedicated SQL pool. The model is used by a real-time dashboard. Users report that the dashboard is slow. You need to improve query performance without changing the source system. Which action should you take?

Accepted Answer

Reduce the number of visuals on the dashboard and apply page-level filters. Option B is correct because reducing the number of visuals and applying page-level filters directly reduces the number of queries sent to the Azure Synapse Analytics dedicated SQL pool via DirectQuery. Since the source system cannot be changed, the only way to improve performance is to minimize the query load from the dashboard. Page-level filters ensure that only relevant data is queried, and fewer visuals mean fewer separate queries, which collectively reduces latency.

Answer

Create aggregations on the fact table

Answer

Enable dual storage mode for all tables

Answer

Disable the 'Reduce queries' option in Power BI Desktop

Question 2

Refer to the exhibit. You are reviewing the privacy levels for a Power BI semantic model that combines data from an on-premises SQL Server (SalesDB) and a local CSV file (MarketingCSV). The SQL Server data source has privacy level 'Organizational' and the CSV file has 'Private'. When you refresh the model, you encounter an error: 'A privacy violation occurred. Please check your privacy settings.' What is the most likely cause?

Accepted Answer

The CSV file's privacy level is set to 'Private', which prevents it from being combined with 'Organizational' sources.. Option B is correct because Power BI enforces privacy level restrictions when combining data from different sources. The CSV file's 'Private' privacy level prevents it from being combined with the SQL Server data source set to 'Organizational', as Power BI will not merge data across these boundaries to avoid leaking private data to organizational sources. This results in the 'privacy violation' error during refresh.

Answer

The on-premises data gateway is not configured for the CSV file.

Answer

The SQL Server connection string uses Integrated Security, which is not supported when combining data.

Answer

The SQL Server table is partitioned, causing a conflict with the CSV file.

Question 3

You are using Power Query to combine data from multiple Excel files stored in a SharePoint Online document library. Each file has the same structure. You need to ensure that the query automatically includes new files added to the library without manual updates. Which approach should you use?

Accepted Answer

Use 'Get Data from SharePoint Online Folder' and then combine files using 'Combine & Transform Data'.. Option C is correct because the 'Get Data from SharePoint Online Folder' connector in Power Query retrieves metadata for all files in the folder, and the 'Combine & Transform Data' action automatically applies a sample file transformation to all files. When new files are added to the library, refreshing the query will include them without manual intervention, as the connector dynamically reads the folder contents.

Answer

Use 'Get Data from Excel' and specify each file path manually.

Answer

Use 'Get Data from SQL Server' and write a query to read files.

Answer

Use 'Get Data from SharePoint Online List' and then expand the file content.

Question 4

You are preparing data for a Power BI report. The source data contains a 'CustomerName' column with values like 'John, Doe'. You need to split this column into two columns: 'FirstName' and 'LastName'. The comma is used as a delimiter, but some names have a space after the comma. Which split method should you use?

Accepted Answer

Split by delimiter using comma, then use 'Trim' to remove extra spaces. Option C is correct because splitting by comma and then trimming extra spaces handles the inconsistent spacing after the comma (e.g., 'John, Doe' vs 'John, Doe'). Power Query's 'Split Column by Delimiter' using comma will separate the values, and the subsequent 'Trim' step removes leading/trailing spaces from the resulting columns, ensuring clean 'FirstName' and 'LastName' values without manual cleanup.

Answer

Split by number of characters using a fixed width

Answer

Split by delimiter using semicolon

Answer

Split by delimiter using comma, using 'Left-most delimiter'

Question 5

You are designing a Power BI data model for a sales analytics solution. The source data includes a 'Sales' fact table with millions of rows and dimension tables for 'Customer', 'Product', 'Date', and 'Salesperson'. You need to minimize the model size in Power BI. Which action should you take?

Accepted Answer

Remove any columns from the fact table that are not used in the model.. Removing unused columns from the fact table reduces the amount of data imported into the VertiPaq engine, which directly minimizes the model size. Each column consumes memory for compression and storage, so eliminating unnecessary columns is the most effective way to reduce the model footprint without changing query performance or data refresh behavior.

Answer

Set the 'Storage mode' of fact table to 'DirectQuery'.

Answer

Enable 'Include relationship columns' in the relationship settings.

Answer

Create a calculated column for row number.

Question 6

You are importing data from a REST API that returns JSON. The JSON structure includes nested arrays. You need to transform the data into a flat table structure in Power Query. Which transform should you apply?

Accepted Answer

Use 'Expand to New Rows' on the list columns. Option A is correct because the 'Expand to New Rows' transform (found under the 'Transform' tab in Power Query) is specifically designed to flatten nested list columns—such as arrays within JSON—by creating a new row for each element in the list. This converts the hierarchical JSON structure into a flat table, which is the required outcome for further analysis in Power BI.

Answer

Use 'Merge Queries' to combine the nested data

Answer

Use 'Pivot Column' on the nested columns

Answer

Use 'Unpivot Columns' on the record columns

Question 7

Which TWO of the following are valid data source types in Power BI that support DirectQuery? (Select TWO.)

Accepted Answer

Snowflake. Snowflake is a cloud-based data warehouse that supports DirectQuery in Power BI, allowing queries to be sent directly to Snowflake without importing data into Power BI's in-memory engine. This is possible because Snowflake provides a SQL-based interface that Power BI can connect to via its native connector, enabling real-time querying of large datasets.

Answer

Excel workbook

Answer

SharePoint Online List

Answer

JSON file

Question 8

Which THREE of the following are best practices for data preparation in Power BI to improve performance and maintainability? (Select THREE.)

Accepted Answer

Filter out unnecessary rows as early as possible in the query. Filtering out unnecessary rows early in Power Query reduces the amount of data loaded into memory and processed in subsequent transformation steps. This practice, known as early filtering, minimizes the data footprint and improves both refresh performance and report responsiveness. By applying filters as the first transformation, you leverage query folding to push the filter logic to the source database, further enhancing efficiency.

Answer

Avoid renaming columns in Power Query; use original names

Answer

Keep all columns from the source to avoid missing data

Question 9

Which TWO of the following are valid options when connecting to an on-premises SQL Server database from Power BI service? (Select TWO.)

Accepted Answer

DirectQuery mode with an on-premises data gateway. Option A is correct because DirectQuery mode in Power BI service requires an on-premises data gateway to translate cloud-based queries into native SQL queries against the on-premises SQL Server. The gateway acts as a secure bridge, enabling real-time query execution without moving data to the cloud.

Answer

Using a SharePoint Online list connector pointing to SQL Server

Answer

DirectQuery using cloud credentials without a gateway

Answer

Scheduling refresh in Power BI Desktop

Question 10

Refer to the exhibit. You have the above Power Query M expression. You notice that the query is taking a long time to load. You suspect that query folding is not occurring for the filter on the Year column. What is the most likely reason?

Accepted Answer

The Year column is stored as text in the database, and Power Query cannot fold the numeric comparison.. Option C is correct because when the Year column is stored as text in the database, Power Query cannot fold a numeric comparison (e.g., `Year > 2020`) into the source query. Query folding requires data type compatibility between the filter predicate and the source column; a text-to-integer comparison forces Power Query to fetch all rows and apply the filter locally, causing slow performance.

Answer

The Year column is of type datetime, but the filter is an integer.

Answer

The query uses a native SQL query instead of a table reference.

Answer

The data source is using DirectQuery mode, which prevents folding.

Question 11

Refer to the exhibit. You are reviewing a Power BI data source credential configuration. The Azure Blob Storage data source uses 'Anonymous' credentials. However, the refresh fails with an error indicating that the blob container is private and requires authentication. Which change should you make?

Accepted Answer

Change credential type to 'Account Key' and provide the storage account key.. Azure Blob Storage containers that are private require authentication. The 'Account Key' credential type in Power BI uses the storage account key to authenticate via the Azure Storage REST API, which is the correct method for accessing private blob containers. Anonymous access only works when the container is configured for public access.

Answer

Change credential type to 'Service Principal' and provide the app ID and secret.

Answer

Change credential type to 'Basic' and provide the storage account name and key.

Answer

Change credential type to 'Windows' for the Azure Blob datasource.

Question 12

You are importing a large dataset from a CSV file using Power Query. The file contains 50 columns, but you only need 10 for your report. What is the most efficient way to reduce the amount of data loaded into the model?

Accepted Answer

Remove the unnecessary columns in Power Query before loading.. Option A is correct because Power Query processes data before it enters the Power BI model. Removing unnecessary columns at the query stage reduces the amount of data loaded into memory, improving performance and reducing storage. This is the most efficient approach as it minimizes the dataset size from the start, unlike post-load methods that still consume resources.

Answer

Load all columns and then hide the unnecessary ones in the report.

Answer

Use a SQL query to select only the needed columns if the data source supports it.

Answer

Create a DAX calculated table that selects only the needed columns.

Question 13

Your organization uses Power BI to analyze sales data stored in Azure SQL Database. The data model includes a fact table with millions of rows. To improve performance, you need to reduce the amount of data loaded into the model. Which action should you take?

Accepted Answer

Apply row-level filters in Power Query to import only relevant rows. Option A is correct because applying row-level filters in Power Query reduces the volume of data imported into the Power BI model by only loading rows that meet specific criteria. This directly minimizes the data footprint in memory, improving query and refresh performance, especially for fact tables with millions of rows stored in Azure SQL Database.

Answer

Use calculated tables in DAX to summarize data

Answer

Disable the Auto Date/Time feature

Answer

Configure incremental refresh with a date filter

Question 14

You are developing a Power BI semantic model that must combine data from an on-premises SQL Server database and a SharePoint Online list. The organization requires that credentials for the on-premises data source be stored securely and not shared with users. Which data connectivity approach should you use?

Accepted Answer

Use an on-premises data gateway and store the SQL Server credentials in the gateway data source settings. Option A is correct because using an on-premises data gateway with credentials stored in the gateway data source settings allows the Power BI service to securely connect to the on-premises SQL Server without exposing credentials to users. The gateway acts as a secure bridge, storing the SQL Server credentials encrypted in the gateway configuration, which satisfies the requirement that credentials not be shared with users.

Answer

Use DirectQuery with single sign-on for the SQL Server database

Answer

Export the SQL Server data to an Excel file stored in SharePoint and then import from SharePoint

Answer

Import data from SQL Server using Windows authentication embedded in the data source settings

Question 15

You are preparing data for a Power BI report. The source data contains a column with mixed data types: some values are numbers, others are text. When loading into Power Query, the entire column is typed as text. What is the likely cause?

Accepted Answer

Power Query detected that the column contains text values in some rows, so it set the data type to text. Power Query's column type detection logic examines the entire column during import. If any row contains a non-numeric value (e.g., text), Power Query defaults the entire column to text to avoid data loss or conversion errors. This is the standard behavior when mixed data types are present, regardless of the source format.

Answer

The column was imported as text because the data source is a CSV file

Answer

The 'Detect data type' option was disabled in Power Query settings

Answer

The source data is stored as text in the database