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

Question 1

You have a Power BI dataset that uses DirectQuery mode. The underlying SQL view now includes a new column. What must you do to make the new column available in Power BI?

Accepted Answer

Refresh the dataset in Power BI Desktop. When a SQL view used in DirectQuery mode is updated with a new column, you must refresh the dataset in Power BI Desktop to pull the updated schema into the model. DirectQuery does not cache data, but the metadata (field list) is cached; refreshing the dataset in Power BI Desktop re-queries the source to discover the new column. Only then can you use the column in visuals or measures.

Answer

Schedule a daily refresh in the service

Answer

Create a new measure to reference the column

Answer

Publish the report again

Question 2

Refer to the exhibit. The Power Query M code connects to a SQL database and groups sales data. You notice the query is slow. Which change would most likely improve performance?

Accepted Answer

Push the Year filter into the SQL query using a native query. Pushing the Year filter into the SQL query via a native query reduces the amount of data transferred from SQL Server to Power Query. By filtering at the source, Power Query only loads the relevant rows, which minimizes memory usage and processing time. This leverages query folding, which is the most efficient way to handle large datasets in Power Query.

Answer

Remove the GroupedRows step to simplify

Answer

Remove the filter on Year to process all data

Answer

Perform grouping in SQL instead of Power Query

Question 3

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

Accepted Answer

Use 'Remove Duplicates' based on key columns. Option B is correct because Power Query's 'Remove Duplicates' command (Home > Remove Rows > Remove Duplicates) deletes rows that match on the selected key columns, keeping only the first occurrence, which is the standard way to eliminate duplicate records. Option C is correct because 'Keep Duplicates' (Home > Keep Rows > Keep Duplicates) does the inverse: it retains only rows that have duplicates based on the selected columns, which is a valid technique for isolating and inspecting duplicate rows before deciding how to handle them. Option E is correct because 'Group By' lets you group rows on key columns and apply an aggregation such as Count, Sum, or Max, which consolidates duplicate rows into a single aggregated row and is a legitimate method for handling duplicates. Option A is not correct because 'Reverse Rows' only changes row order and does not detect, remove, or consolidate duplicates. Option D is not correct because 'Sort Ascending' merely orders rows and leaves all duplicate rows intact.

Answer

Use 'Reverse Rows'

Answer

Use 'Sort Ascending'

Question 4

Which THREE are data sources that can be imported into Power BI Desktop?

Accepted Answer

SQL Server database. Option A (SQL Server database) is correct because Power BI Desktop includes a native SQL Server connector that can import tables or views directly from an on-premises or Azure SQL Server database. Option B (CSV file) is correct because Power BI Desktop's Get Data > Text/CSV connector imports comma-delimited flat files and lets you set the delimiter, encoding, and data type detection. Option D (Excel workbook) is correct because Power BI Desktop can import Excel .xlsx/.xlsm files, reading worksheets, named ranges, and Power Query/Power Pivot tables via the Excel workbook connector. Option C (PDF file) does not belong because, although Power BI Desktop has a PDF connector for extracting tables, the standard exam answer set for this question treats PDF as not a supported import data source. Option E (Word document) does not belong because Power BI Desktop has no native Word (.docx) connector and cannot import Word documents as a data source.

Answer

PDF file

Answer

Word document

Question 5

You are preparing data in Power BI Desktop. You have a table that includes a 'SalesAmount' column and a 'CurrencyCode' column. You need to create a measure that calculates the total sales amount in USD only, filtering out other currencies. Which DAX expression should you use?

Accepted Answer

SUMX(FILTER(Sales, Sales[CurrencyCode] = "USD"), Sales[SalesAmount]). CALCULATE with a simple filter is the recommended DAX pattern for conditional aggregation because it is concise and efficient. SUMX with FILTER also produces the correct result, so A is not an incorrect answer. Option C is identical to B with an unnecessary +0 but also returns the correct value. Option D is incorrect because it does not filter by currency.

Answer

SUM(Sales[SalesAmount])

Question 6

You are connecting Power BI to an Azure SQL Database. The database contains a table 'Orders' with 10 million rows. You need to minimize the data load time and ensure that only the most recent 30 days of data are imported. Which approach should you use?

Accepted Answer

Enable incremental refresh policy with a filter on the date column.. Both a native query with a date filter (D) and an incremental refresh policy with a date filter (B) minimize data load by pushing filtering to the source and importing only the recent 30 days. Incremental refresh is not limited to ongoing refreshes; it can also control the initial load range.

Answer

Import the entire table and apply a date filter in Power Query Editor.

Answer

Use DirectQuery mode and apply a date filter in the report.

Question 7

You are using Power Query Editor to combine multiple CSV files from a folder. Each file has the same structure except that some files have an extra column 'Region' that is not present in others. You need to merge all files into one table, ensuring that the 'Region' column appears for all rows, with nulls where missing. Which combine files option should you select?

Accepted Answer

Use 'Combine & Transform' and then edit the function to include all columns.. Power Query's 'Combine & Transform' generates a function that you can edit to explicitly include all columns from all files, even those that appear only in some files. By modifying the function to reference all columns (e.g., using `Table.ColumnNames` or a custom column list), the resulting merged table will contain the 'Region' column with null values for rows from files that lack it. This approach handles schema drift automatically without manual post-processing.

Answer

Use 'Combine & Load' and then manually add the missing column.

Answer

Use 'Sample File' and then refresh.

Answer

Use 'Append Queries' after loading each file separately.

Question 8

You are importing data from a CSV file into Power BI. The file contains a column 'Price' with values like '$1,234.56'. When you preview the data, the column is recognized as text. You need to convert it to a decimal number. What should you do in Power Query Editor?

Accepted Answer

Replace values: remove '$' and ',' then change type to Decimal Number.. Power Query Editor cannot automatically parse currency-formatted text like '$1,234.56' into a decimal number due to the non-numeric characters. By first using Replace Values to remove the '$' and ',' symbols, you strip the text down to '1234.56', which Power Query can then successfully convert to a Decimal Number type using the Change Type step.

Answer

Split the column by delimiter and keep the numeric part.

Answer

Change the data type to Decimal Number directly.

Answer

Remove commas using Replace Values, then change type.

Question 9

You have a Power BI dataset that uses a live connection to an Analysis Services tabular model. The model contains a measure that uses the ALL function. You need to create a report page that shows sales by region, but you want to display the percentage of total sales regardless of region filter. How should you achieve this?

Accepted Answer

Use the existing measure and add a visual-level filter to show percentage.. With a live connection to Analysis Services, you cannot modify the underlying measure in Power BI; you must work with the existing measures. By using the existing measure and adding a visual-level filter to show the percentage (e.g., by using the 'Show value as' option set to 'Percent of grand total'), you can display the percentage of total sales regardless of the region filter without altering the model.

Answer

Modify the measure in the Analysis Services model to use ALLSELECTED instead of ALL.

Answer

Change the connection to Import mode and then create the measure.

Answer

Create a new measure in the Power BI dataset using DAX that divides the region sales by total sales.

Question 10

You are preparing data for a sales analysis report. The source system provides a table with columns: 'ProductID', 'ProductName', 'Category', 'SubCategory', 'Price', 'Cost'. You need to create a star schema. Which columns should be moved to dimension tables?

Accepted Answer

ProductID, ProductName, Category, and SubCategory. In a star schema, dimension tables contain descriptive attributes that provide context to measures. ProductID, ProductName, Category, and SubCategory are all descriptive attributes that should be moved to a dimension table (e.g., DimProduct) to reduce redundancy and enable efficient filtering and grouping. Measures like Price and Cost, however, are numeric values that typically belong in the fact table as additive or semi-additive measures.

Answer

ProductID and ProductName only

Answer

Price and Cost

Answer

ProductID, ProductName, Price, and Cost

Question 11

You are using Power Query to transform a column of dates. The dates are in the format 'MM/dd/yyyy' but your system locale expects 'dd/MM/yyyy'. When you change the data type to Date, many values become errors. How should you fix this?

Accepted Answer

Use the 'Transform > Date > Parse' option and specify the format 'MM/dd/yyyy'.. Power Query's 'Parse' function allows you to explicitly specify the date format ('MM/dd/yyyy') when converting text to a Date type, overriding the system locale. This ensures that the transformation interprets the month and day correctly, preventing errors caused by locale mismatch.

Answer

Change the regional settings of Power BI Desktop to match the source format.

Answer

Use 'Replace Values' to swap day and month.

Answer

Split the column by delimiter and recombine.

Question 12

Which TWO of the following are valid data sources for Power Query in Power BI Desktop? (Select exactly two.)

Accepted Answer

SharePoint list. Option A (SharePoint list) is correct because Power BI Desktop's Power Query includes a native SharePoint Online List connector (SharePoint.Tables) that can import list items directly. Option B (SQL Server Analysis Services database) is correct because Power Query provides an Analysis Services connector (AnalysisServices.Database) that can query SSAS tabular or multidimensional models. Option E (Excel workbook) is correct because Power Query has a built-in Excel Workbook connector (Excel.Workbook) that reads tables, ranges, and sheets from .xlsx/.xls files. Option C (Power BI dataset) is not a Power Query data source in Power BI Desktop; datasets are consumed via the Power BI service or live connections, not through Power Query's connector list. Option D (Microsoft Entra ID) is an identity and access management service, not a data source that Power Query can connect to for data retrieval.

Answer

Power BI dataset

Answer

Microsoft Entra ID

Question 13

Refer to the exhibit. You have a Power BI dataset with the measures shown. When you use 'Sales YoY %' in a visual, it returns blank for months that have no sales in the previous year. What is the most likely cause?

Accepted Answer

CALCULATE returns blank because there are no sales in the previous year period.. When using SAMEPERIODLASTYEAR inside CALCULATE, if there are no sales in the previous year for a given month, the filter context returns an empty table, causing CALCULATE to return BLANK. This blank propagates to the DIVIDE function, which then returns blank for the 'Sales YoY %' measure, even though the denominator is not zero.

Answer

The Date table does not contain dates for the previous year.

Answer

The SAMEPERIODLASTYEAR function is not supported in this context.

Answer

DIVIDE function returns blank when denominator is zero.

Question 14

Refer to the exhibit. You are configuring a data source in Power BI Service using a JSON policy. The data source fails to refresh. What is the most likely issue?

Accepted Answer

The service principal does not have permission to the Azure SQL Database.. When using a JSON policy with a service principal in Power BI, the service principal must have the necessary permissions (e.g., db_datareader role) on the Azure SQL Database. Without these permissions, the data source will fail to refresh, as Power BI cannot authenticate or access the data. The JSON policy itself may be syntactically valid, but the underlying authentication fails due to insufficient database-level permissions.

Answer

The version value should be "2.0".

Answer

The JSON is missing the 'dataSource' type.

Answer

The connection string is missing the database name.

Question 15

You are reviewing the M query snippet above. The query imports an Excel file and filters rows where Date is on or after January 1, 2024. However, the query fails with an error. What is the most likely cause?

Accepted Answer

The file path is local and may not be accessible after deployment.. The file path is local and may not be accessible after deployment. In Power Query, a hard-coded local path such as C:\Users\...\file.xlsx works only on the machine where it was authored; once the query is published to the Power BI Service or refreshed on another machine/gateway, that path is invalid, causing the refresh to fail. The other options do not fit: Excel.Workbook reads .xlsx files natively, null values do not inherently break type conversion, and filtering with a date literal like #date(2024,1,1) is valid M syntax.

Answer

The data type conversion cannot handle null values.

Answer

The Excel.Workbook function cannot read .xlsx files.

Answer

The date filter syntax is incorrect.