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

Question 1

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.. Option C is correct because 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 2

You are designing a data model in Power BI. You have a 'Sales' table and a 'Date' table. The 'Sales' table has a 'SalesDate' column of type Date. You need to create a relationship between the tables, but the 'Date' table contains dates from 2010 to 2025, while the 'Sales' table only has data from 2020. Which type of relationship should you create to ensure optimal performance and correct filtering?

Accepted Answer

Many-to-one with single cross-filter direction from Date to Sales. Option B is correct because a many-to-one relationship with single cross-filter direction from Date to Sales ensures that filters applied to the Date table propagate to the Sales table, which is the standard star schema design. This configuration optimizes query performance by avoiding unnecessary bi-directional filtering and correctly handles the date range mismatch, as the Date table's larger range does not affect filtering of Sales data.

Answer

Many-to-one with bi-directional cross-filtering

Answer

One-to-one (1:1)

Answer

Many-to-many (M:M)

Question 3

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

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

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 6

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'.. Option C is correct because 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 7

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

Which THREE of the following are best practices for data modeling in Power BI? (Select exactly three.)

Accepted Answer

Create a separate date table for time intelligence functions.. Option C is correct because Power BI's time intelligence functions (e.g., TOTALYTD, SAMEPERIODLASTYEAR) require a separate, continuous date table marked as a date table to work correctly. Without a dedicated date table, these functions may return incorrect results or fail due to missing contiguous dates. This table should have a single date column with no gaps and be related to the fact table's date column.

Answer

Use bi-directional cross-filtering relationships for all tables.

Answer

Store calculated logic in calculated columns rather than measures when possible.

Question 9

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

Accepted Answer

SQL Server Analysis Services database. Option B is correct because Power Query in Power BI Desktop can connect directly to SQL Server Analysis Services (SSAS) databases using the Analysis Services connector, which supports both multidimensional and tabular models. This allows you to import or DirectQuery data from SSAS cubes or tabular models, leveraging MDX or DAX queries respectively.

Answer

SharePoint list

Answer

Power BI dataset

Answer

Microsoft Entra ID

Question 10

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

Refer to the exhibit. You are reviewing a Power Query script. The script fails with a 'DataSource.Error: Microsoft SQL: Login failed for user' error. Which step should you check first?

Accepted Answer

The credentials used in the Source step.. The error 'DataSource.Error: Microsoft SQL: Login failed for user' indicates an authentication failure when connecting to the SQL Server database. This occurs at the Source step, where Power Query first attempts to establish a connection using the provided credentials. Checking and correcting the credentials in the Source step is the logical first step because no subsequent data transformations (like removing columns, changing types, or filtering rows) can execute if the initial data source connection fails.

Answer

The column name in #"Removed Columns".

Answer

The data type transformation in #"Changed Type".

Answer

The filter condition in #"Filtered Rows".

Question 12

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

You are importing a large CSV file (200 MB) into Power BI Desktop. The import is very slow and sometimes fails. What should you do to improve performance?

Accepted Answer

Filter rows and columns during import using Power Query to reduce data size.. Option C is correct because filtering rows and columns during import using Power Query reduces the data volume loaded into the Power BI Desktop model, directly addressing the root cause of slow imports and failures when handling a 200 MB CSV file. Power Query's query folding capabilities push data reduction operations (e.g., removing unnecessary columns, filtering rows based on conditions) upstream, minimizing memory and processing overhead during the import phase. This approach is a standard best practice for optimizing data ingestion in Power BI Desktop, especially for large flat files.

Answer

Use Power BI Service to import the file instead.

Answer

Remove all relationships before import.

Answer

Upgrade to Power BI Premium.

Question 14

You need to combine two tables: Sales and Products, where Sales has a ProductID column and Products has a ProductKey column. The tables have a many-to-one relationship. Which Power Query transformation should you use?

Accepted Answer

Merge Queries.. Merge Queries (C) is correct because it performs a join between two tables based on matching columns, which is exactly what is needed to combine Sales and Products using ProductID and ProductKey. This transformation supports many-to-one relationships and allows you to expand related columns from the Products table into the Sales table, enabling further data analysis.

Answer

Group By.

Answer

Append Queries.

Answer

Pivot Column.

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