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

Question 1

You have a Power Query transformation that combines data from multiple Excel files in a folder. Each file has a different structure. You need to identify which TWO actions will help standardize the data before combining. (Choose two.)

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Use the Combine Files function and select a sample file to define the transformation.. The Combine Files function in Power Query allows you to select a sample file to define a single transformation logic that is then applied to all files in the folder. This is essential when files have different structures, as it standardizes the data by using the sample file's schema as a template, ensuring consistent column names, data types, and transformations across all files.

Answer

Filter out rows with null values.

Answer

Merge all files into one table before cleaning.

Answer

Remove all columns except the ones you need from a single file.

Question 2

Refer to the exhibit. You are configuring a Power BI dataset with row-level security (RLS) using a JSON policy. The exhibit shows an RLS configuration. A user 'analyst@contoso.com' has access to the 'Orders' table. However, when the user views the report, no data is displayed. What is the most likely cause?

Accepted Answer

The RLS policy is missing a filter expression to allow rows.. The RLS policy shown in the exhibit lacks a filter expression that defines which rows the user is allowed to see. In Power BI, a row-level security policy must include a DAX filter that returns a table of allowed rows; without it, the policy effectively denies all rows to the user. The user 'analyst@contoso.com' has access to the 'Orders' table, but the missing filter means no rows pass the security check, resulting in an empty report.

Answer

The user's email domain does not match the data source.

Answer

The connection string uses Integrated Security, which requires a gateway.

Answer

The table name 'Orders' is misspelled.

Question 3

You are connecting to an Excel workbook stored in Microsoft SharePoint Online. You want to refresh the data in Power BI service without manual intervention. Which type of gateway is required?

Accepted Answer

No gateway is required. When connecting to an Excel workbook stored in Microsoft SharePoint Online, the data source resides entirely in the cloud. Power BI service can directly access SharePoint Online via its cloud-to-cloud connectivity using the same Microsoft Entra ID authentication context. Therefore, no on-premises data gateway is required for scheduled refresh because the data never traverses an on-premises network.

Answer

On-premises data gateway (standard mode)

Answer

On-premises data gateway (personal mode)

Answer

Virtual network (VNet) gateway

Question 4

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.. 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 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 Power BI model, which directly minimizes model size. Each column consumes memory for compression and storage, so eliminating unnecessary columns reduces the model footprint. DirectQuery (Option B) would also reduce the amount of data stored in the model, but it changes the storage mode to query the source directly, which is not the intended approach for minimizing the size of an imported data model.

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

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

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 10

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 11

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'. In Power BI, a dataflow's refresh schedule is configured at the dataflow level and applies to all entities within it. You cannot refresh the 'Sales' entity daily and the 'Product' entity hourly within the same dataflow. If different schedules are needed, separate dataflows must be created. Therefore, the premise itself is invalid, and none of the provided options accurately diagnose a real issue.

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 12

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

Accepted Answer

Merge. (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 13

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

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 15

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