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

Question 1

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 2

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 3

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 (DirectQuery mode with an on-premises data gateway) is correct because the Power BI service cannot reach an on-premises SQL Server directly, so it must route live DirectQuery requests through an on-premises data gateway installed in the local network. Option D (Import mode with an on-premises data gateway) is correct because scheduled imports of on-premises SQL Server data into the Power BI service likewise require the on-premises data gateway to bridge the cloud service to the local database. Option B is wrong because a SharePoint Online list connector targets SharePoint lists, not SQL Server tables, and cannot serve as a SQL Server connection path. Option C is wrong because cloud credentials alone cannot traverse the corporate network boundary to reach an on-premises SQL Server without a gateway. Option E is wrong because scheduling refresh is a configuration action performed in the Power BI service (not a connection option), and Power BI Desktop itself does not run scheduled refreshes.

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 4

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 5

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

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

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

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

Question 9

You are designing a Power BI solution that ingests data from multiple sources: Azure Blob Storage, Salesforce, and an on-premises Oracle database. The data must be combined into a single semantic model. The Oracle database contains sensitive customer information that must be masked before being loaded. Which approach should you use to prepare the data?

Accepted Answer

Create a Power BI dataflow that extracts, transforms, and masks data before loading into the semantic model. Power BI dataflows provide a cloud-based ETL solution that can connect to Azure Blob Storage, Salesforce, and on-premises Oracle (via an on-premises data gateway), perform transformations including data masking, and then load the prepared data into a shared semantic model. This approach centralizes data preparation, ensures sensitive data is masked before any downstream consumption, and supports scheduled refreshes without requiring additional infrastructure.

Answer

Use DirectQuery to connect to all sources and rely on database-level masking

Answer

Import all data into Power BI Desktop and apply transformations in the Power Query Editor

Answer

Stage the data in an Azure SQL Database and use SQL Server Analysis Services to mask data

Question 10

You are importing a CSV file into Power BI. The file contains a date column with values in the format 'MM/dd/yyyy'. However, Power Query interprets the dates as 'dd/MM/yyyy'. What should you do to correctly parse the dates?

Accepted Answer

Use the 'Using Locale' option in the Change Type step to select the appropriate locale (e.g., English (United States)). Power Query's 'Using Locale' option in the Change Type step allows you to specify the regional format of the source data (e.g., English (United States) for 'MM/dd/yyyy'). This overrides Power Query's default locale-based interpretation, ensuring dates are parsed correctly without altering the data or system settings.

Answer

Change the system region settings of the Power BI service to US

Answer

Change the column data type to Text and then manually replace separators

Answer

Split the column into day, month, and year, then combine them in the correct order

Question 11

You need to combine two tables from different sources: 'Orders' from SQL Server and 'Returns' from an Excel file. Both tables have a column named 'OrderID'. You want to include all orders and only matching returns. Which join type should you use in Power Query?

Accepted Answer

Left Outer Join. In Power Query, a Left Outer Join returns all rows from the first (left) table ('Orders') and only the matching rows from the second (right) table ('Returns'), based on the 'OrderID' column. This matches the requirement to include all orders and only matching returns, ensuring no order is dropped even if it has no corresponding return.

Answer

Inner Join

Answer

Right Outer Join

Answer

Full Outer Join

Question 12

You are building a Power BI semantic model that uses a large fact table from a data warehouse. The fact table has a date column and you want to create a date dimension. The organization requires that the date dimension includes all dates from 2010 to 2030, including weekends and holidays. What is the best practice for creating the date dimension?

Accepted Answer

Create a date table in Power Query by generating a list of dates from 1/1/2010 to 12/31/2030 and then add columns for attributes. It follows the best practice of creating a dedicated date dimension table in Power Query, which ensures full control over the date range (2010–2030) and allows you to add custom attributes like holidays. This approach avoids relying on the fact table's date column, which may have gaps or missing dates, and ensures the date dimension is complete and independent for robust time intelligence calculations.

Answer

Use the CALENDAR function in DAX to generate the date range

Answer

Mark the date column from the fact table as a date table and disable Auto Date/Time

Answer

Create a date dimension by using DISTINCT on the fact table's date column

Question 13

You are connecting to an Azure SQL Database from Power BI Desktop. The database contains a view that returns thousands of rows. You only need the last 100 rows for analysis. What is the most efficient way to reduce the data loaded?

Accepted Answer

Use the 'Keep Top Rows' transformation in Power Query after applying a sort. It uses Power Query's 'Keep Top Rows' transformation after sorting the view by the desired order (e.g., descending on a date column). This approach pushes the sort and row-limiting logic to the source database via query folding, ensuring only the last 100 rows are transferred over the network, which is the most efficient method for reducing data loaded.

Answer

Write a native SQL query with a WHERE clause to limit rows

Answer

Use DirectQuery mode and add a filter in the report

Answer

Import all rows and then remove rows in Power Query

Question 14

Which TWO options are valid methods to combine multiple tables in Power Query?

Accepted Answer

Merge. In Power Query, Merge is the correct operation for combining two tables horizontally by matching rows on one or more key columns, equivalent to a SQL JOIN, and it is performed via Home > Merge Queries. Append is the correct operation for stacking tables vertically, adding the rows of one table beneath another, equivalent to SQL UNION ALL, and it is performed via Home > Append Queries. The other options are not Power Query commands: Join and Union are SQL terms (Union also implies deduplication, which Append does not do), and Concatenate is a text function (Text.Combine / & operator) for joining string values, not tables.

Answer

Join

Answer

Concatenate

Answer

Union

Question 15

Which THREE factors should you consider when designing an incremental refresh policy for a large fact table in Power BI?

Accepted Answer

The refresh policy must consider the data warehouse's maintenance windows. Option C is correct because an incremental refresh policy must be scheduled so that its refresh windows do not collide with the data warehouse's maintenance windows, since the source must be available and consistent when Power BI queries it for new or changed partitions. Option D is correct because incremental refresh works by partitioning the table in the Power BI model (typically by RangeStart/RangeEnd parameters on a date column), so only the newest partitions are refreshed while historical partitions are retained. Option E is correct because incremental refresh requires a date or datetime column in the source table to filter and define the partition boundaries; without it, Power BI cannot determine which rows are new or changed. Option A is not required, since incremental refresh is supported for Import mode (and DirectQuery is not a prerequisite). Option B is not required, as the source can be an on-premises database accessed via a data gateway, not only a cloud database.

Answer

The table must be configured for DirectQuery mode

Answer

The source data must be stored in a cloud database