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

Question 1

You are examining a Power BI dataflow configuration. The dataflow is scheduled to refresh the 'Sales' entity daily and the 'Product' entity hourly. However, the 'Product' entity refresh fails every hour with an error indicating the SharePoint list data source is not accessible. You need to diagnose the issue. What is the most likely cause?

Accepted Answer

The refresh interval is too frequent for the incremental granularity of 'Day'. Option A is correct because the 'Product' entity is configured to refresh hourly, but the dataflow's incremental refresh policy is set to a 'Day' granularity. Incremental refresh in Power BI requires the refresh frequency to align with or be less frequent than the incremental period; an hourly refresh on a daily partition causes the engine to attempt to query the entire day's data each time, which can lead to timeouts or errors if the source (SharePoint list) cannot handle the repeated full scans. The error 'data source is not accessible' often masks this underlying mismatch between refresh frequency and incremental granularity.

Answer

Incremental refresh is not supported for SharePoint list data sources

Answer

The workspace ID is missing from the dataflow configuration

Answer

The on-premises data gateway is not configured for the SharePoint list

Question 2

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

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

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 5

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

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. Option B is correct because using the 'Top N' filter in Power Query pushes the filter to the source if supported, reducing data transfer. Option A is wrong because it loads all rows. Option C is wrong because DirectQuery does not reduce the load; it queries the source each time. Option D is wrong because native SQL query may still load all rows unless filtered.

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 7

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

Accepted Answer

Merge. Option C (Merge) is correct because Merge in Power Query performs a join-like operation that combines columns from two tables based on a matching key, similar to SQL JOINs. Option E (Append) is correct because Append stacks rows from multiple tables with the same columns, akin to SQL UNION ALL. Both are native Power Query operations for combining tables.

Answer

Join

Answer

Concatenate

Answer

Union

Question 8

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 incremental refresh policies must be aligned with the data warehouse's maintenance windows to avoid conflicts, such as attempting to refresh data while the source is being updated or backed up. This ensures data consistency and prevents refresh failures due to locked tables or incomplete data loads. Power BI's incremental refresh relies on the source being available and stable during the refresh window.

Answer

The table must be configured for DirectQuery mode

Answer

The source data must be stored in a cloud database

Question 9

Which TWO data source types can be used with Power BI dataflows?

Accepted Answer

SharePoint Online list. SharePoint Online list (Option D) is a correct answer because Power BI dataflows can connect to SharePoint Online lists as a data source, allowing users to ingest list data directly into a dataflow for transformation and loading into the Power BI service. Azure SQL Database (Option E) is also correct because dataflows support Azure SQL Database as a data source, enabling direct extraction of relational data from Azure SQL databases into the dataflow engine for ETL processes.

Answer

Exchange Online mailbox

Answer

PDF file

Answer

Power BI dataset

Question 10

You are reviewing a Power BI data source configuration for an Azure SQL Database. The connection uses OAuth2 authentication. However, when scheduling a refresh, the refresh fails with an error that authentication fails. What is the most likely cause?

Accepted Answer

The authority URL should point to the specific Azure AD tenant, not 'common'. When using OAuth2 authentication with Azure SQL Database in Power BI, the authority URL must point to the specific Azure AD tenant (e.g., 'https://login.microsoftonline.com/your-tenant-id') rather than the generic 'common' endpoint. The 'common' endpoint is designed for multi-tenant applications and cannot resolve the tenant-specific authentication context required for scheduled refreshes, leading to authentication failures.

Answer

The connection string has 'Encrypt=True' which is incompatible with OAuth2

Answer

The server name in the connection string is incorrect

Answer

The resource URL should be 'https://management.azure.com/'

Question 11

You are troubleshooting a Power Query transformation that groups sales data by ProductID. The query runs slowly and you suspect the filter is being applied after loading all rows. What change would improve performance by pushing the filter to the source?

Accepted Answer

Replace the first three lines with a native SQL query that includes the WHERE clause. Option D is correct because pushing filter logic to the source database via a native SQL query with a WHERE clause reduces the amount of data loaded into Power Query. This leverages query folding, which allows the source (e.g., SQL Server) to perform the filtering before data is transferred, significantly improving performance for large datasets.

Answer

Disable the 'Enable load' option for the SalesTable

Answer

Use CALCULATE in DAX to filter

Answer

Add a 'Table.Buffer' step after the filter

Question 12

You need to prepare data from a folder containing multiple CSV files with identical structure. What is the most efficient way to load all files into a single table?

Accepted Answer

Use the 'From Folder' data source and then click 'Combine & Transform Data'. Option B is correct because the 'From Folder' data source in Power Query automatically detects multiple CSV files with identical structure and provides a 'Combine & Transform Data' button that merges them into a single table in one step. This is the most efficient method as it eliminates the need for manual imports or scripting, leveraging Power Query's built-in file combination logic.

Answer

Import each CSV file separately and then append them

Answer

Write a Python script in Power Query to read and combine files

Answer

Use a dataflow to connect to the folder and apply the 'Combine Files' transformation

Question 13

You are preparing a Power BI report that uses data from Azure SQL Database. The data includes a date column that needs to be used in time intelligence calculations. You want to ensure that the date column is recognized as a date table in the data model. What should you do?

Accepted Answer

In the model view, mark the table as a date table by selecting the date column.. Option C is correct because marking a table as a date table in the model view explicitly tells Power BI that the table contains a complete set of dates for time intelligence calculations. This ensures that DAX functions like TOTALYTD, SAMEPERIODLASTYEAR, and DATEADD work correctly by using the marked date column as the primary date reference for the model, rather than relying on auto-generated date hierarchies.

Answer

Set the data type of the date column to Date.

Answer

Use the DAX function DATEADD to create a date table.

Answer

Create a calculated column using DATEVALUE to convert the date.

Question 14

You are importing data from a CSV file that contains a column with mixed data types (numbers and text). Power BI automatically assigns the data type as Text. You need to perform numerical aggregations on this column. What should you do?

Accepted Answer

In Power Query Editor, change the data type of the column to Whole Number or Decimal Number.. Option A is correct because you need to change the data type to a numeric type in Power Query Editor to perform aggregations. Option B is wrong because splitting the column does not change the data type. Option C is wrong because creating a calculated column in DAX is less efficient and may still require type conversion. Option D is wrong because the relationship is not the issue.

Answer

Split the column using a delimiter to separate numbers from text.

Answer

Create a relationship with a numeric table to enable aggregation.

Answer

Create a calculated column in DAX using VALUE() to convert the text to numbers.

Question 15

You are working with a large dataset in Power BI that contains sales transactions. You need to create a measure that calculates the running total of sales over time. However, the data model does not include a separate date table, and you want to minimize the model size. What is the most efficient approach?

Accepted Answer

Create a separate date table and use the TOTALYTD function in a measure.. Option A is correct because creating a separate date table and using TOTALYTD leverages Power BI's time intelligence functions, which require a continuous date table marked as a date table. This approach is efficient because time intelligence functions are optimized for performance and minimize model size by avoiding calculated columns or complex row-context iterations. Without a date table, TOTALYTD cannot function correctly, as it relies on a proper date hierarchy.

Answer

Write a measure using VAR and FILTER with ALL to compute running total.

Answer

Create calculated columns for Year and Month, then use SUMX with FILTER.

Answer

Use the EARLIER function in a calculated column to compute running total.

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

Exhibit

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

Exhibit