PL-300 Practice Exam 4 — 20 Questions

Question 1

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 filters to reduce rows directly reduces the amount of data loaded into Power BI, minimizing load time and model size. Option A is wrong because DirectQuery would avoid import but not reduce load time for queries and may impact performance. Option B is wrong because Power Query folding is automatic for SQL Server but does not reduce data volume unless combined with filters. Option C is wrong because incremental refresh requires a date column and configured policy, but does not reduce initial load.

Answer

Configure incremental refresh without filtering.

Answer

Enable query folding in Power Query.

Answer

Use DirectQuery instead of Import mode.

Question 2

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 3

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 4

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 5

You are importing data from an Excel workbook that has multiple worksheets. You only need data from the 'Sales' worksheet. When you connect via Power Query, all worksheets appear in the Navigator. What should you do to load only the 'Sales' worksheet?

Accepted Answer

Select the 'Sales' worksheet in Navigator and click 'Load'. Option A is correct because selecting the worksheet and clicking Load loads it. Option B is incorrect because Transform Data opens Power Query for transformation. Option C is incorrect because that would load all. Option D is incorrect because deleting steps is unnecessary.

Answer

Load all worksheets, then delete the unwanted ones

Answer

Select all worksheets and click 'Load'

Answer

Select the 'Sales' worksheet and click 'Transform Data'

Question 6

You are loading data from an Excel workbook that has multiple sheets. Each sheet contains sales data for a different region. You need to combine all sheets into one table. What is the most efficient approach?

Accepted Answer

Use Power Query's 'From Folder' option to combine all Excel files in a folder, then select 'Combine & Transform' to use the workbook as a sample.. Option D is correct because the 'From Folder' option in Power Query is designed to combine multiple Excel files with the same structure into a single table. By pointing to a folder containing the workbook, Power Query automatically detects all sheets as separate files and uses the 'Combine & Transform' feature to union them, which is more efficient than manually appending each sheet individually.

Answer

Use 'Append Queries' for each sheet individually.

Answer

Use 'Merge Queries' to join all sheets into one.

Answer

Load each sheet as a separate table in the model and create a calculated table to union them.

Question 7

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 8

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.

Question 9

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.. Option B is correct because the 'Combine files' transform in Power Query from a SharePoint folder automatically detects and combines all files in the folder, and when refreshed, it includes any new files. Option A is wrong because Get Data from SharePoint list does not combine files. Option C is wrong because Power Automate would require additional setup and is not native Power Query. Option D is wrong because merging queries requires manual steps each time.

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 10

You are debugging a Power Query that imports a CSV file. The exhibit shows the M code. The CSV file contains a header row and data. Some rows have a comma inside a quoted field (e.g., "Smith, John"). What issue will arise from this code?

Accepted Answer

The QuoteStyle.None option will cause commas inside quotes to be treated as delimiters.. Option B is correct because the M code uses `QuoteStyle.None`, which tells Power Query to treat commas inside quoted fields as column delimiters rather than as part of the field value. This causes rows with values like "Smith, John" to be split incorrectly, resulting in extra columns and misaligned data. The correct option for CSV files with quoted fields is `QuoteStyle.Csv`, which respects the standard CSV quoting rules.

Answer

The encoding 1252 is incorrect for the file.

Answer

The number of columns specified (5) is too many.

Answer

The Promoted Headers step will fail because the first row contains quotes.

Question 11

You are preparing data for a report that needs to be refreshed every 30 minutes to meet near real-time requirements. Which Power BI feature should you use?

Accepted Answer

Use DirectQuery mode to connect to the source database.. DirectQuery mode allows Power BI to query the source database directly without importing data, enabling near real-time refreshes by executing queries against the source every time a report is interacted with or when the dataset is refreshed. This meets the 30-minute refresh requirement without the latency of data import, as the data remains in the source and is not copied into Power BI.

Answer

Import data and schedule refresh every 30 minutes.

Answer

Use a streaming dataset with real-time data.

Answer

Use a push dataset to send data from the source.

Question 12

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

You connect to a large Azure SQL Database table with over 100 million rows. You need to create a report that shows sales by month for the current year only. Which data reduction technique should you use in Power Query to minimize data load?

Accepted Answer

In Power Query, apply a date filter on the source query so only current year data is imported.. Option B is correct because applying a date filter in Power Query at the source query level ensures that only rows from the current year are imported into the Power BI data model. This reduces the data volume from over 100 million rows to a fraction, minimizing memory usage and improving refresh performance. Power Query pushes the filter down to the Azure SQL Database using a WHERE clause in the SQL query, so only the filtered data is transferred over the network.

Answer

Import all data and then remove columns that are not needed.

Answer

Load all data and filter using a visual-level filter in the report.

Answer

Use a calculated table in DAX to filter the data.

Question 14

You are preparing data from a CSV file that contains date values in the format 'MM/dd/yyyy'. When you load the file into Power BI Desktop, the dates appear as text. What should you do to ensure the dates are recognized as date data type?

Accepted Answer

Change the column data type to Date in Power Query Editor. Option B is correct because changing the column data type to Date in Power Query Editor forces Power BI to interpret the text values as dates using the current locale settings. Since the CSV file contains dates in 'MM/dd/yyyy' format, Power Query can parse this pattern when the data type is explicitly set to Date, converting the text strings into the Date data type for proper time intelligence and filtering.

Answer

Use 'Replace Values' to replace '/' with '-'

Answer

Split the column by delimiter '/'

Answer

Promote the first row as headers

Question 15

You have a Power BI dataset that uses a SQL Server data source. The SQL Server database is located in a different region, causing slow refresh performance. You need to improve the data refresh performance without changing the data source. What should you do?

Accepted Answer

Enable the 'Fast Combine' option in the Power Query privacy settings.. Option D is correct because enabling the 'Fast Combine' option in Power Query privacy settings allows Power BI to ignore privacy levels for data sources, which can significantly improve query performance by enabling more efficient query folding and reducing the overhead of separate data source isolation checks. This is particularly beneficial when dealing with a remote SQL Server database, as it minimizes the number of round trips and data transformations required during refresh.

Answer

Change the storage mode to DirectQuery to reduce data movement.

Answer

Increase the scheduled refresh frequency to reduce the amount of data loaded each time.

Answer

Install an on-premises data gateway to reduce latency.

Question 16

You are importing data from a CSV file into Power BI. The file contains a column 'SalesAmount' with values like '$1,234.56'. When you load the data, the column is detected as text. What is the most efficient way to convert this column to a numeric type in Power Query?

Accepted Answer

Use 'Replace Values' to remove the dollar sign and comma, then change the column type to decimal.. Option A is correct because it directly addresses the root cause: the dollar sign and comma are non-numeric characters that prevent automatic type conversion. By using 'Replace Values' to remove these characters first, you clean the data so that Power Query can then safely change the column type to decimal. This is the most efficient approach because it avoids error rows and preserves data integrity.

Answer

Use 'Detect Data Type' and hope it automatically converts.

Answer

Change the column type to decimal directly and ignore errors.

Answer

Split the column by delimiter ',' and then convert the first part.

Question 17

You have a Power BI dataset that uses DirectQuery to an Azure SQL Database. Users complain that reports take too long to load. You suspect that the database is overwhelmed by queries. What should you do to improve performance while keeping DirectQuery?

Accepted Answer

Reduce the number of visuals per page and apply slicers to limit data.. Option A is correct because reducing the number of visuals per page and applying slicers to limit data reduces the number of DAX queries sent to the Azure SQL Database via DirectQuery. Each visual generates at least one query, so fewer visuals mean fewer concurrent queries, and slicers add WHERE clauses that reduce the result set size, lowering the load on the database.

Answer

Increase the Power BI Premium capacity size.

Answer

Create aggregations in the dataset.

Answer

Change the storage mode to Import.

Question 18

You are connecting to a SQL Server database using Import mode. The source table contains a column 'SalesAmount' with a few null values. You need to replace nulls with 0 before loading. What is the most efficient step to achieve this in Power Query Editor?

Accepted Answer

Use 'Replace Values' to replace null with 0. Option A is correct because 'Replace Values' can replace null with 0. Option B is incorrect because 'Fill Down' fills with previous non-null value, not 0. Option C is incorrect because 'Transform > Replace Errors' replaces errors, not nulls. Option D is incorrect because adding a custom column is less efficient.

Answer

Use 'Replace Errors' with value 0

Answer

Use 'Fill Down' to propagate previous values

Answer

Add a custom column with an if statement

Question 19

Which THREE are valid methods to handle duplicate rows in Power Query when preparing data?

Accepted Answer

Use 'Remove Duplicates' based on key columns. Options A, B, and D are correct. Remove Duplicates deletes duplicates. Group By with aggregation can combine duplicates. Keep Duplicates retains only duplicates. Option C reverses rows, not handle duplicates. Option E sorts, not handle duplicates.

Answer

Use 'Reverse Rows'

Answer

Use 'Sort Ascending'

Question 20

You are designing a Power BI solution for a retail company. The data includes point-of-sale transactions with columns: TransactionID, StoreID, ProductID, Quantity, SalesAmount, TransactionDate. The company wants to analyze sales by hour of day. What is the best way to prepare the time dimension?

Accepted Answer

Create a separate Time dimension table with a one-to-many relationship to the fact table. Option D is correct because creating a separate Time dimension table with a one-to-many relationship to the fact table follows the star schema best practice for Power BI. This approach allows efficient filtering and grouping by hour without bloating the fact table with calculated columns, and it supports time-based analysis across multiple fact tables if needed. The Time dimension can include columns like Hour, HourBucket, or PeriodOfDay, enabling intuitive slicers and drill-downs.

Answer

Add a calculated column extracting hour from TransactionDate in the fact table

Answer

Create a date dimension only and ignore time

Answer

Use DAX measures to extract hour on the fly