PL-300 Power BI Exam: Data Sources, Connectors & Querying (Part 6)

Question 126:

HOTSPOT – You create a Power BI dataset that contains the table shown in the exhibit. You need to make the table available as an organizational data type in Microsoft Excel. How should you configure the properties of the table?

Note: This question has been adapted for a text-based format. The exhibit would show a table properties dialog in Power BI Desktop. To see the actual exam question with images, please refer to the original source document.

• Row label: [Option]
• Key column: [Option]
• Is featured table: [Option]

Answer:
– Row label: Name (The main descriptive field for the data type).
– Key column: ID (The column that uniquely identifies each row).
– Is featured table: Yes (This must be enabled to publish the table as a data type).

Question 127:

You have a custom connector that returns ID, From, To, Subject, Body, and Has Attachments for every email sent during the past year (over 10 million records). You build a report analyzing employee networks. You need to prevent report recipients from reading the analyzed emails. The solution must minimize the model size. What should you do?

• A. From Model view, set the Subject and Body columns to Hidden.
• B. Remove the Subject and Body columns during the import.
• C. Implement row-level security (RLS) so that the report recipients can only see results based on the emails they sent.

Answer: B
To both prevent access and minimize model size, the best solution is to remove the sensitive and large text columns (Subject and Body) in Power Query before the data is imported. Hiding the columns prevents them from being seen in report view but they still exist in the model, increasing its size and posing a security risk.

Question 128:

You build a report to analyze customer transactions from a database that contains a Customer table (unique CustomerID) and a Transaction table. Which relationship should you use to link the tables?

• A. one-to-many from Transaction to Customer
• B. one-to-one between Customer and Transaction
• C. many-to-many between Customer and Transaction
• D. one-to-many from Customer to Transaction

Answer: D
A single customer can have multiple transactions, but each transaction belongs to only one customer. This defines a classic one-to-many relationship, with the ‘one’ side being the Customer table and the ‘many’ side being the Transaction table.

Question 129:

You have a Power BI query named Sales. Users only use the date part of the Sales_Date field. Only rows with a Status of “Finished” are used in analysis. You need to reduce the load times of the query without affecting the analysis. Which two actions achieve this goal?

• A. Remove the rows in which Sales[Status] has a value of Canceled.
• B. Remove Sales[Sales_Date].
• C. Change the data type of Sale[Delivery_Time] to Integer.
• D. Split Sales[Sale_Date] into separate date and time columns.
• E. Remove Sales[Canceled Date].

Answer: A, E
A: Filtering out rows that are not used for analysis (like ‘Canceled’ status) is a primary method to reduce data volume and improve performance. E: The Canceled Date column is only populated for canceled orders. Since those rows are removed in step A, this column will contain only null values for the remaining ‘Finished’ orders and can be removed to reduce model size.

Question 130:

You need to provide a user with the ability to add members to a workspace. The solution must use the principle of least privilege. Which role should you assign to the user?

• A. Viewer
• B. Admin
• C. Contributor
• D. Member

Answer: D
The Member role has all the permissions of a Contributor, plus the ability to add other users to the Member, Contributor, or Viewer roles. Since Admin is a higher privilege than needed, Member is the correct choice following the principle of least privilege.

Question 131:

You have a collection of reports for the HR department that use RLS. Each sales region has an HR manager who must see only their region’s data. HR managers must be prevented from changing the report layouts. How should you provision access for the HR managers?

• A. Publish the reports in an app and grant the HR managers access permission.
• B. Create a new workspace, copy the datasets and reports, and add the HR managers as members of the workspace.
• C. Publish the reports to a different workspace other than the one hosting the datasets.
• D. Add the HR managers as members of the existing workspace that hosts the reports and the datasets.

Answer: A
Publishing content through a Power BI App is the standard and best practice for distributing content to a broad audience of consumers. It provides a read-only experience, preventing users from changing layouts, and it fully respects the underlying RLS configured on the datasets.

Question 132:

You are creating a report in Power BI Desktop. You load a data extract that includes a free text field named col1. You need to analyze the frequency distribution of the string lengths in col1. The solution must not affect the size of the model. What should you do?

• A. In the report, add a DAX calculated column that calculates the length of col1.
• B. In the report, add a DAX function that calculates the average length of col1.
• C. From Power Query Editor, add a column that calculates the length of col1.
• D. From Power Query Editor, change the distribution for the Column profile to group by length for col1.

Answer: D
The Column Profiling tools in Power Query are designed for data exploration and analysis *before* data is loaded. Changing the column profile distribution to “Group by length” will show the required analysis directly in the editor without adding a new column to the data model, thus not affecting the model size.

Question 1 (T2):

You have a PBIX file that imports data from an Excel file on a local network file share. The Excel file was moved to a new location. You need to update the PBIX file to use the new location. What are three ways to achieve this?

• A. From the Datasets settings of the Power BI service, configure the data source credentials.
• B. From the Data source settings in Power BI Desktop, configure the file path.
• C. From Current File in Power BI Desktop, configure the Data Load settings.
• D. From Power Query Editor, use the formula bar to configure the file path for the applied step.
• E. From Advanced Editor in Power Query Editor, configure the file path in the M code.

Answer: B, D, E
B: The Data Source Settings dialog is the primary UI for managing connection strings and file paths. D: The formula bar for the “Source” step in Power Query shows the M code for that step, including the file path, which can be edited directly. E: The Advanced Editor shows the full M code for the query, where the file path can also be edited.

Question 2 (T2):

HOTSPOT – You have a folder that contains 50 JSON files. You need to use Power BI Desktop to make the metadata of the files available as a single dataset. The solution must NOT store the data of the JSON files. Which type of data source should you use, and which transformation should you perform?

Note: This question has been adapted for a text-based format. To see the actual exam question with images, please refer to the original source document.

• Data source type: [Option]
• Transformation to perform: [Option]

Answer:
– Data source type: Folder
– Transformation to perform: Remove the Content column
The “Folder” connector imports metadata about all files in a folder, including name, path, and a ‘Content’ column (which holds the binary of the file). To get only the metadata and not the data itself, you simply remove the ‘Content’ column in Power Query.

Question 3 (T2):

You have a CSV file that contains user complaints. The file contains a column named Logged in the format: 2018-12-31 at 08:59. You need to be able to analyze the complaints by the logged date and use a built-in date hierarchy. What should you do?

• A. Apply a transformation to extract the last 11 characters of the Logged column and set the data type of the new column to Date.
• B. Change the data type of the Logged column to Date.
• C. Split the Logged column by using ” at ” as the delimiter.
• D. Apply the Parse function from the Date transformations options to the Logged column.

Answer: C
Splitting the column by the ” at ” delimiter is the most robust way to separate the date part from the time part. Power Query will then correctly recognize the first resulting column as a date, which can be formatted and used with a date hierarchy.

Question 4 (T2):

DRAG DROP – You have two Excel workbooks in a OneDrive folder, each containing a table named Sales. You plan to use Power BI to combine both Sales tables and create visuals. The solution must ensure that you can publish a separate report and dataset. Which storage mode should you use for the report file and the dataset file?

Note: This question has been adapted for a text-based format. To see the actual exam question with images, please refer to the original source document.

• Report file mode: [Option]
• Dataset file mode: [Option]

Answer:
– Report file mode: Live Connection
– Dataset file mode: Import
To create a separate report and dataset, you first build a PBIX file that imports the data from OneDrive (Dataset file). After publishing it, you create a *new*, separate PBIX file that connects to that published dataset using Live Connection (Report file).

Question 5 (T2):

You have data in an Excel worksheet with a discount column that sometimes contains errors. You need to use Power Query to clean the dataset. The solution must meet these requirements: if the discount column returns an error, a discount of 0.05 must be used; all rows must be maintained. What should you do in Power Query Editor?

• A. Select Replace Errors.
• B. Edit the query in the Query Errors group.
• C. Select Remove Errors.
• D. Select Keep Errors.

Answer: A
The “Replace Errors” transformation is designed for this exact scenario. It allows you to find all error values within a column and replace them with a specified static value (in this case, 0.05) while keeping all other rows intact.

Question 6 (T2):

From Power Query Editor, you attempt to execute a query and receive the error message: “Datasource.Error: Could not find file.” What are two possible causes of the error?

• A. You do not have permissions to the file.
• B. An incorrect privacy level was used for the data source.
• C. The file is locked.
• D. The referenced file was moved to a new location.

Answer: A, D
This specific error message indicates that the path stored in the query is invalid or inaccessible. The most common reasons are that the file was moved or deleted (D), or the account running the refresh does not have file system permissions to access that path (A). A locked file typically produces a different error.

Question 7 (T2):

HOTSPOT – You attempt to use Power Query Editor to create a custom column and receive the error message shown in the exhibit. Use the drop-down menus to complete the statement.

Note: The exhibit would show an error message like “Token Eof expected.” This question has been adapted for a text-based format. To see the actual exam question with images, please refer to the original source document.

• The error is caused by a: [Option]
• To resolve the error, you should: [Option]

Answer:
– The error is caused by a: Syntax error
– To resolve the error, you should: Correct the M formula in the custom column editor

Question 8 (T2):

DRAG DROP – You use Power Query Editor to preview data that has error values in the discount column. You need to clean the query so that all rows are maintained and error values are replaced with 0.05. Which three actions should you perform in sequence?

Note: This question has been adapted for a text-based format. To see the actual exam question with images, please refer to the original source document.

Answer: The correct sequence of actions is:

1. Right-click the header of the discount column.
2. Select the Replace Errors option from the context menu.
3. In the Replace Errors dialog box, enter 0.05 and click OK.

Question 9 (T2):

You are creating a query to be used as a Country dimension in a star schema. The source data contains a Country column and a City column. You need to create the dimension, which must contain a list of unique countries. Which two actions should you perform?

• A. Delete the Country column.
• B. Remove duplicates from the table.
• C. Remove duplicates from the City column.
• D. Delete the City column.
• E. Remove duplicates from the Country column.

Answer: D, E
To create a Country dimension table, you only need the country information. D: First, remove the unnecessary ‘City’ column. E: Then, right-click the ‘Country’ column header and select “Remove Duplicates” to ensure the final table contains only a unique list of countries.

Question 10 (T2):

HOTSPOT – You have three Azure SQL databases for Development, UAT, and Production. You plan to build a single PBIX file that can be pointed to the correct database for each stage of the lifecycle. The solution must minimize administrative effort and not use deployment pipelines. What should you do?

Note: This question has been adapted for a text-based format. To see the actual exam question with images, please refer to the original source document.

• In Power Query, you should create: [Option]
• Before publishing to each workspace, you should modify the: [Option]

Answer:
– In Power Query, you should create: Parameters for the server name and database name
– Before publishing to each workspace, you should modify the: Parameter values

Question 11 (T2):

HOTSPOT – You are using Power BI Desktop to connect to an Azure SQL database. The connection is configured as shown in the exhibit. Use the drop-down menus to complete the statement.

Note: The exhibit would show the SQL Server connection dialog with “Data Connectivity mode” set to “Import”. This question has been adapted for a text-based format. To see the actual exam question with images, please refer to the original source document.

• Data will be stored: [Option]
• To see the latest data, you must: [Option]

Answer:
– Data will be stored: in the Power BI file
– To see the latest data, you must: refresh the dataset

Question 12 (T2):

You attempt to connect Power BI Desktop to a Cassandra database. From the Get Data connector list, you discover that there is no specific connector for the Cassandra database. You need to select an alternate data connector that will connect to the database. Which type of connector should you choose?

• A. Microsoft SQL Server database
• B. ODBC
• C. OLE DB
• D. OData

Answer: B
When a native connector is not available, the generic ODBC (Open Database Connectivity) connector is the standard method for connecting to a wide variety of data sources, provided a compatible ODBC driver for that source (like Cassandra) is installed on the machine.

Question 13 (T2):

DRAG DROP – You publish a dataset that contains data from an on-premises Microsoft SQL Server database. The dataset must be refreshed daily. You need to ensure the Power BI service can connect to the database and refresh the dataset. Which four actions should you perform in sequence?

Note: This question has been adapted for a text-based format. To see the actual exam question with images, please refer to the original source document.

Answer: The correct sequence of actions is:

1. Install an on-premises data gateway on a server on the local network.
2. In the Power BI service, add a new data source to the gateway for the SQL Server database.
3. In the dataset settings, map the dataset to the gateway data source.
4. Configure the scheduled refresh for the dataset.

Question 14 (T2):

A BI developer creates a dataflow in Power BI that uses DirectQuery to an on-premises SQL server, and the Enhanced Compute Engine is on. You need to use this dataflow in a report, minimizing processing operations and render times for visuals, with data from the current year up to yesterday. What should you do?

• A. Create a dataflows connection that has DirectQuery mode selected.
• B. Create a dataflows connection that has DirectQuery mode selected and configure a gateway connection for the dataset.
• C. Create a dataflows connection that has Import mode selected and schedule a daily refresh.
• D. Create a dataflows connection that has Import mode selected and create a Microsoft Power Automate solution to refresh the data hourly.

Answer: C
To minimize calculation times and render times (i.e., maximize performance), importing the data is the best option. Since the data needs to be current up to the previous day, a scheduled daily refresh perfectly meets the freshness requirement. Using DirectQuery to a dataflow would be slower for the end-user.

Question 15 (T2):

DRAG DROP – You have a folder that contains 100 CSV files. You need to make the file metadata available as a single dataset by using Power BI. The solution must NOT store the data of the CSV files. Which three actions should you perform in sequence?

Note: This question has been adapted for a text-based format. To see the actual exam question with images, please refer to the original source document.

Answer: The correct sequence of actions is:

1. From Get Data, select the Folder connector and provide the path.
2. In the Power Query Editor, right-click the header of the Content column and select Remove.
3. Click the expand button on the Attributes column header and select the desired metadata fields.

Question 16 (T2):

You have an Azure SQL database that contains sales transactions and is updated frequently. You need to generate reports from the data to detect fraudulent transactions. The data must be visible within five minutes of an update. How should you configure the data connection?

• A. Add a SQL statement.
• B. Set the Command timeout in minutes setting.
• C. Set Data Connectivity mode to Import.
• D. Set Data Connectivity mode to DirectQuery.

Answer: D
DirectQuery mode is designed for scenarios that require near real-time data. It does not import data; instead, it sends queries directly to the source database each time a user interacts with a report. This ensures the data is always as current as the source, meeting the “within five minutes” requirement. Import mode would require a refresh, which cannot happen that frequently in shared capacity.

Question 17 (T2):

HOTSPOT – You have two CSV files: Products (ProductID, ProductName) and Categories (CategoryID, CategoryName). You import them into Power Query. You need to create a single table named Product that includes all columns from both files. How should you combine the queries, and what should you do on the Categories query?

Note: This question has been adapted for a text-based format. To see the actual exam question with images, please refer to the original source document.

• Combine operation: [Option]
• Action on Categories query: [Option]

Answer:
– Combine operation: Merge Queries
– Action on Categories query: Disable load
You should merge the queries based on their common key (CategoryID). After merging, the category information will be part of the Products query. To avoid loading a redundant Categories table into the final data model, you should disable the load for the original Categories query.

Question 18 (T2):

HOTSPOT – You are profiling data in Power Query Editor. You have a column named State. The distribution and quality metrics are shown in the exhibit. Use the drop-down menus to complete the statements.

Note: The exhibit would show column profiling stats for the ‘State’ column, for example: Count: 1000, Error: 0, Empty: 50, Distinct: 52, Unique: 48. This question has been adapted for a text-based format. To see the actual exam question with images, please refer to the original source document.

• The percentage of rows that are empty is: [Option]
• The number of states that appear more than once is: [Option]

Answer:
– The percentage of rows that are empty is: 5% (Calculated as Empty Count / Total Count = 50 / 1000).
– The number of states that appear more than once is: 4 (Calculated as Distinct Count – Unique Count = 52 – 48).