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

Question 1

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

You are preparing data from an Excel workbook that contains multiple sheets. Each sheet has a similar structure but different data. You need to combine all sheets into a single table in Power Query. What is the most efficient approach?

Accepted Answer

Use the 'Combine Files' approach with the workbook as a folder, then select all sheets.. The 'Combine Files' approach in Power Query treats the workbook as a folder, allowing you to select all sheets and automatically combine them into a single table. This is the most efficient method when multiple sheets have a similar structure, as it uses a single transformation step and handles dynamic sheet names without manual query creation.

Answer

Load each sheet as a separate query and then use 'Append Queries' to combine them.

Answer

Use 'Merge Queries' to join the sheets based on a common column.

Answer

Create a new query that references each sheet query and then merges them.

Question 3

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

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 5

You are preparing a data model that uses a date table. You need to ensure that the date table includes all dates from January 1, 2020 to December 31, 2025. What is the most efficient way to create this date table in Power Query?

Accepted Answer

Use the 'List.Dates' function to generate the date range.. The 'List.Dates' function in Power Query M is the most efficient way to generate a contiguous range of dates directly within the query editor, without external dependencies. It creates a list of dates from a start date to an end date using a specified step (e.g., #duration(1,0,0,0) for one day), which can then be converted into a table. This approach is lightweight, fully self-contained, and avoids the overhead of importing external files or using DAX functions that are not native to Power Query.

Answer

Import a date table from an Excel file.

Answer

Use the 'Calendar' function in Power Query M.

Answer

Use the 'CALENDAR' DAX function in Power Query.

Question 6

You have a Power Query query that loads data from an OData source. You need to reduce the amount of data loaded into the data model. What is the best practice?

Accepted Answer

Apply a filter in Power Query before loading.. Applying filters in Power Query before loading data into the data model is the best practice for reducing data volume. Power Query pushes filters down to the OData source using OData query parameters (e.g., $filter), ensuring only the required rows are retrieved from the source. This minimizes network transfer and memory usage in the data model, aligning with the principle of early filtering in the ETL process.

Answer

Apply a filter in the data model using DAX.

Answer

Use 'Enable load' option to turn off loading for the query.

Answer

Load all data and then hide columns you don't need.

Question 7

You are preparing data for a Power BI report that requires a date table with continuous dates from 2020 to 2025. Which TWO methods can you use to create this date table in Power Query?

Accepted Answer

Use the List.Dates function to generate a list of dates.. The List.Dates function in Power Query M generates a continuous list of dates by specifying a start date, a count of dates, and a step duration. This list can then be converted into a table, making it ideal for creating a date table directly in Power Query without leaving the data transformation environment.

Answer

Use the 'Enter Data' feature and manually type dates.

Answer

Use the CALENDAR DAX function in a calculated table.

Answer

Reference another query that already has dates.

Question 8

You are importing data from a SQL Server database. The source table has a column 'ModifiedDate' of type datetime2. In Power Query, you want to ensure that only rows modified within the last 7 days are loaded. Which THREE steps should you take?

Accepted Answer

Use a parameter for the date range and reference it in the filter.. Using a parameter for the date range and referencing it in the filter allows for dynamic, maintainable filtering in Power Query. This approach leverages Power Query's M language to apply a filter step that can be easily updated without modifying the query logic, ensuring only rows from the last 7 days are loaded during data refresh.

Answer

Load all rows and then use a DAX filter in the data model.

Answer

Split the column into date and time and then filter on the date part.

Question 9

You are reviewing a Power Query that imports data from SQL Server. The exhibit shows the M code. The SQL query filters records after a date, then Power Query filters rows with OrderQty > 10, and then groups by ProductID. What is a potential performance issue with this approach?

Accepted Answer

The filter on OrderQty > 10 should be included in the SQL query to reduce the amount of data transferred.. Pushing the `OrderQty > 10` filter into the SQL query reduces the amount of data transferred from SQL Server to Power Query. In Power Query, data is loaded into memory before transformations; filtering earlier in the source query minimizes memory usage and network latency, which is a key performance optimization in Power BI data loading.

Answer

The query will fail because the SQL query uses '>' with a string.

Answer

The SQL query should use a parameter for the date instead of a hardcoded value.

Answer

The grouping should be done in SQL to reduce data volume.

Question 10

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

You have a table with a column 'FullName' that contains names in the format 'Last, First'. You need to split this column into 'LastName' and 'FirstName' columns. Which Power Query transformation should you use?

Accepted Answer

Split Column by Delimiter using comma.. The 'Split Column by Delimiter' transformation in Power Query is specifically designed to divide a single text column into multiple columns based on a specified delimiter, such as a comma. In this case, the 'FullName' column contains names in the 'Last, First' format, so splitting by a comma delimiter will correctly separate the last name and first name into two distinct columns.

Answer

Pivot the FullName column.

Answer

Group By the FullName column and aggregate.

Answer

Extract first characters using 'Extract' transformation.

Question 12

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. 'Replace Values' in Power Query Editor is the most efficient way to replace null values in a column with 0. It directly transforms the column in a single step without requiring additional logic or table scans, and it generates a clean M code step (Table.ReplaceValue) that operates natively on the column's nulls.

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 13

You have a Power BI dataset that uses data from Microsoft Excel files stored in SharePoint Online. Users report that the data is not refreshing as scheduled. You verify that the gateway is installed and running. What is the most likely cause of the refresh failure?

Accepted Answer

The data source credentials are not provided in the gateway.. Even when the gateway is installed and running, it must have valid data source credentials configured for the SharePoint Online Excel files. Without these credentials, the gateway cannot authenticate to SharePoint Online to retrieve the data, causing the scheduled refresh to fail. The gateway uses the stored credentials to connect to the data source during each refresh cycle.

Answer

The gateway is not running.

Answer

The gateway does not support SharePoint Online data sources.

Answer

The gateway is not configured to use the on-premises data source type.

Question 14

Which TWO of the following are valid reasons to use a calculated column instead of a measure in Power BI? (Select exactly two.)

Accepted Answer

You need to use the column in a relationship.. Calculated columns are evaluated row by row and stored in the model, making them available for use in relationships. Measures, in contrast, are evaluated at query time and cannot be used to define relationships between tables in Power BI.

Answer

You need to create a hierarchy for drill-down.

Answer

You need to perform a dynamic aggregation that changes with filters.

Answer

You need to calculate a running total.

Question 15

Your Power BI dataset uses DirectQuery to a SQL Server data warehouse. Users report that reports are slow. You need to improve performance without changing the data source. What should you do?

Accepted Answer

Create aggregated tables in Power BI using the Aggregations feature.. Creating aggregated tables in Power BI using the Aggregations feature allows you to pre-summarize data at a higher granularity while still using DirectQuery. This reduces the amount of data queried from the SQL Server data warehouse, improving report performance without changing the underlying data source or switching to Import mode.

Answer

Switch the dataset to Import mode.

Answer

Disable the 'Enable query reduction' option in Power BI Desktop.

Answer

Increase the memory limit of the on-premises data gateway.