PL-300 Practice Test 4 — 25 Questions

Question 1

You are importing data from a folder containing multiple CSV files with the same structure. You want to combine all files into a single table, but only include files that have been modified in the last 7 days. What Power Query transformation should you use?

Accepted Answer

Filter the folder contents by 'Date Modified' before combining. Option B is correct because filtering the folder contents by 'Date Modified' before combining ensures only recent files are loaded, optimizing performance. Option A is incorrect because 'Sample File' is used to preview the structure of a representative file, not to filter which files are combined. Option C is incorrect because 'Merge Queries' is for joining tables based on a key, not for appending multiple files with the same structure. Option D is incorrect because combining all files first and then filtering loads all data into memory, which is less efficient than filtering before combining.

Answer

Use 'Sample File' and manually filter

Answer

Use 'Merge Queries' with the folder

Answer

Combine files using 'Combine & Load', then filter by date modified

Question 2

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 3

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

You are designing a data model for a retail company. The source system has a Sales table with columns: Date, StoreID, ProductID, SalesAmount. You need to create a date dimension table that includes all dates from the Sales table. Which DAX expression should you use to create the date table?

Accepted Answer

DateTable = CALENDARAUTO(). Option C is correct because `CALENDARAUTO()` automatically generates a contiguous date table spanning all dates present in the model, including those from the Sales table. This function scans all date columns in the model and returns a single column of dates from the earliest to the latest, which is the simplest way to create a date dimension that covers the full range of transaction dates without needing explicit MIN/MAX references.

Answer

DateTable = VALUES(Sales[Date])

Answer

DateTable = CALENDAR(MIN(Sales[Date]), MAX(Sales[Date]))

Answer

DateTable = DISTINCT(Sales[Date])

Question 5

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.. Option A is correct because 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.

Question 6

You are reviewing a Power BI data source configuration in the data source settings. The exhibit shows the JSON representation of a data source. Which issue might arise from this configuration?

Accepted Answer

The authentication kind 'Key' is not supported for Azure SQL Database, causing connection failure.. Option D is correct because Azure SQL Database does not support the 'Key' authentication kind in Power BI data source settings. Azure SQL Database requires either Windows authentication, database credentials (Username/Password), or Azure AD-based authentication (such as OAuth2 or Service Principal). The 'Key' authentication kind is typically used for Azure Storage or Cosmos DB, not for Azure SQL Database, so this configuration will cause a connection failure.

Answer

The server name contains a hyphen, which is invalid in SQL Server connection strings.

Answer

The CommandTimeout value is too low and may cause queries to time out.

Answer

The option 'CreateNavigationProperties' is set to false, which may prevent relationships from being created.

Question 7

You are importing data from a CSV file that contains a column 'Date' with values in different formats (e.g., '01/15/2024', '2024-01-15'). You need to ensure all dates are recognized correctly. What should you do in Power Query?

Accepted Answer

In Power Query, set the data type to 'Date' and use 'Using Locale' to specify the correct locale for each file.. Option A is correct because Power Query's 'Using Locale' feature allows you to specify the cultural context (e.g., 'en-US' for MM/DD/YYYY or 'de-DE' for DD.MM.YYYY) when setting a column's data type to 'Date'. This ensures that dates in various formats, such as '01/15/2024' (US) and '2024-01-15' (ISO 8601), are interpreted correctly during import, as the locale dictates the expected date order and separator.

Answer

Change the data source to a SQL database.

Answer

Split the column into multiple columns and merge them back.

Answer

Use 'Replace Values' to standardize the date formats.

Question 8

You are reviewing a Power BI dataset configuration in the service. The JSON shows a data source for an Azure SQL Database. Which statement about the configuration is correct?

Accepted Answer

The dataset uses cloud-only data sources and does not require a gateway.. Option D is correct. Azure SQL Database is a cloud-native data source in Power BI, and cloud data sources do not require a gateway, whether using DirectQuery or Import. The configuration shown in the JSON is for an Azure SQL Database, which is inherently cloud-only, so no gateway is needed. Option A is incorrect because key-based authentication is not supported for Azure SQL Database; using 'Key' credential type would cause a connection failure. Option B is incorrect because the JSON does not indicate single sign-on (no 'SingleSignOn' property set to true). Option C is incorrect because a cloud gateway is not required for Azure SQL Database; it is only needed for on-premises or virtual-network sources.

Answer

The dataset uses key-based authentication and does not use single sign-on.

Answer

The dataset uses single sign-on with Azure AD.

Answer

The dataset is configured to use a cloud gateway for direct query.

Question 9

You are preparing data in Power BI Desktop. You have a table with a column 'CustomerID' that contains duplicate values. You need to create a relationship to another table that also has 'CustomerID'. However, the relationship requires unique values in at least one of the tables. What should you do?

Accepted Answer

Remove duplicate rows from the 'CustomerID' column in the dimension table.. Option D is correct because to create a relationship in Power BI, at least one table must have unique values in the column used for the relationship. By removing duplicate rows from the 'CustomerID' column in the dimension table (which should contain unique identifiers), you ensure that the dimension table has unique values, allowing a one-to-many relationship to be established with the fact table. This is a standard data modeling practice in Power BI to enforce referential integrity.

Answer

Create a composite key using multiple columns.

Answer

Use the 'Merge Queries' feature to combine the tables into one.

Answer

Change the data type of 'CustomerID' to text.

Question 10

You are importing data from a web page that contains an HTML table. Power Query detects the table, but you notice that some columns contain HTML tags. Which TWO methods can you use to remove the HTML tags from the column values?

Accepted Answer

Use 'Replace Values' to remove specific tags like
and

.. Option B is correct because 'Replace Values' allows you to manually remove specific HTML tags (e.g.,
,

) by replacing them with an empty string, which is a straightforward method for cleaning known tags. Option E is correct because the 'Html.Table' function in Power Query can parse HTML content and extract the text from a table, effectively stripping tags and returning clean text values.

Answer

Split the column by delimiter '<' and then extract the first part.

Answer

Remove the columns containing HTML tags.

Answer

Use the 'Clean' function to remove all HTML tags.

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.. Option C is correct because 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 have a Power BI dataset that uses a SQL Server view as the data source. The view returns data for all customers, but you only need data for customers in the 'West' region. The view does not accept parameters. How should you filter the data?

Accepted Answer

In Power Query, apply a filter on the Region column after loading the view.. Option B is correct because applying a filter in Power Query after loading the view is the standard approach when the source view does not accept parameters. This method leverages Power Query's query folding capabilities, which push the filter down to the SQL Server, reducing data transfer and improving performance. It avoids loading unnecessary data into the model while maintaining the ability to refresh efficiently.

Answer

Use a native SQL query in Power Query with a WHERE clause to ensure query folding.

Answer

Ask the database administrator to modify the view to include a WHERE clause.

Answer

Load the entire view into the model and use a report-level filter.

Question 13

You are reviewing a Power Query M expression that transforms column types. The 'SalesAmount' column contains values like '1,234.56' (with a comma as thousands separator). After applying this transformation, what is the likely result?

Accepted Answer

The transformation will result in errors for rows containing commas.. Option A is correct because Power Query's default type conversion for numeric columns expects a period as the decimal separator and no thousands separator. When the 'SalesAmount' column contains values like '1,234.56' with a comma as a thousands separator, attempting to convert the column directly to a number type (e.g., using 'Change Type' or 'Table.TransformColumnTypes') will cause errors for rows containing commas, as Power Query cannot parse the comma as part of a valid number. The comma is not a recognized numeric character in the default locale, so the conversion fails.

Answer

The column will be converted to text automatically.

Answer

The column will be successfully converted to numbers.

Answer

The transformation will ignore the comma and convert the number correctly.

Question 14

Which THREE actions in Power Query Editor can improve the performance of data refresh? (Select three.)

Accepted Answer

Remove columns that are not used in the report.. Option C is correct because removing unused columns reduces the amount of data loaded into the Power Query engine and the data model, directly decreasing memory usage and refresh time. Power Query processes all columns in a query step, so eliminating unnecessary columns early minimizes the data footprint for subsequent transformations and the final load.

Answer

Sort data in ascending order to improve compression.

Answer

Merge queries before filtering.

Question 15

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

Import only the required columns and apply row-level filters in Power Query.. Option D is correct because importing only the necessary columns and applying row-level filters in Power Query minimizes the amount of data loaded into Power BI, directly reducing load time and model size. Option A is incorrect because incremental refresh without filtering only loads new or changed data on subsequent refreshes but does nothing to reduce the initial full load. Option B is incorrect: query folding improves query performance by pushing transformations back to the source, but it does not reduce data volume unless combined with filters. Option C is incorrect: DirectQuery avoids importing data entirely, so there is no data load time or model size from imports, but it shifts the performance burden to live queries against the source, which may not minimize overall report response time and does not reduce the model size in terms of memory footprint (it still requires metadata). The goal is to minimize import load time and model size, which is best achieved by reducing the data volume upfront through column selection and row filtering.

Answer

Configure incremental refresh without filtering.

Answer

Enable query folding in Power Query.

Answer

Use DirectQuery instead of Import mode.

Question 16

You are building a Power BI data model that combines Sales data from SQL Server and Marketing data from a CSV file. The Sales table has a unique 'OrderID' column, and the Marketing table has a 'CampaignID' column. You need to create a relationship between Sales and Marketing to analyze campaign effectiveness. What should you do?

Accepted Answer

Create a bridge table containing unique combinations of OrderID and CampaignID.. Option B is correct because a bridge table resolves the many-to-many relationship between Sales (OrderID) and Marketing (CampaignID). Since there is no direct key match, creating a bridge table with unique combinations of OrderID and CampaignID allows Power BI to model the relationship correctly and analyze campaign effectiveness without data duplication or ambiguity.

Answer

Use an inactive relationship between Sales and Marketing and activate it with USERELATIONSHIP in measures.

Answer

Create a separate table for each campaign and relate to Sales.

Answer

Merge the Marketing table into the Sales table using a left outer join.

Question 17

You are importing data from a folder containing multiple CSV files with identical structure. You want to automatically combine all files into one table in Power Query. Which connector should you use?

Accepted Answer

Folder connector. The Folder connector is the correct choice because it is specifically designed to connect to a folder containing multiple files, and when combined with the 'Combine Files' transformation in Power Query, it automatically merges all CSV files with identical structures into a single table. This connector handles the iterative process of reading each file and appending rows without manual scripting.

Answer

Excel Workbook connector

Answer

CSV connector

Answer

Web connector

Question 18

Which TWO transformations in Power Query are most likely to cause Query Folding to stop? (Select two.)

Accepted Answer

Pivot columns.. Option A is correct because Pivot columns in Power Query often require a transformation that cannot be translated into a native SQL query, causing Query Folding to break. When you pivot a column, the engine must restructure data from rows to columns, which typically involves operations like grouping and aggregation that many source systems (e.g., SQL Server) cannot fold back to a single SELECT statement. This forces Power Query to pull all data locally before applying the pivot, stopping folding.

Answer

Filter rows based on a date column.

Answer

Remove columns.

Answer

Rename a column.

Question 19

Which TWO data sources can be used with DirectQuery mode in Power BI? (Choose two.)

Accepted Answer

SQL Server. SQL Server is correct because it is a relational database that supports DirectQuery mode in Power BI, allowing queries to be sent directly to the source without importing data. This enables real-time access to large datasets while leveraging SQL Server's query processing capabilities.

Answer

Excel workbook

Answer

CSV file

Answer

SharePoint list

Question 20

You are loading data from a folder containing multiple Excel files with identical structure. Some files have inconsistent column names due to manual edits. You need to ensure that all data is loaded correctly without errors. What should you do in Power Query?

Accepted Answer

Use the 'Combine Files' feature with a sample file, then in the transformation step, promote headers and rename columns using a mapping table.. Option A is correct because the 'Combine Files' feature in Power Query uses a sample file to infer the schema, and then you can apply transformations like promoting headers and renaming columns using a mapping table to handle inconsistent column names across files. This ensures all data loads without errors by standardizing the column names before combining.

Answer

Use 'Merge Queries' to join the files based on row position.

Answer

Change the data source to a SharePoint folder and use 'Load to Data Model' directly.

Answer

In Power Query, use 'Enter Data' to manually create the schema.