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

Question 1

Which TWO are best practices when preparing data for Power BI?

Accepted Answer

Promote headers if the first row contains column names. Promoting headers is a best practice when the first row of your source data contains column names. This ensures that Power BI correctly interprets those values as field names rather than data, which is essential for accurate data modeling and reporting. The 'Promote Headers' transformation is a standard step in Power Query to clean and structure imported data.

Answer

Change data type of all columns after all transformations

Answer

Avoid merging queries; always use lookups

Answer

Avoid unpivoting columns; keep data wide

Question 2

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

Accepted Answer

SQL Server database. A is correct because Power BI Desktop includes native connectors for SQL Server databases, allowing direct import of data via T-SQL queries or table selection. This is a first-class data source in the Get Data experience, supporting Windows and SQL Server authentication.

Answer

PDF file

Answer

Word document

Question 3

Which TWO are valid reasons to use a dataflow in Power BI when preparing data?

Accepted Answer

To handle large data volumes that exceed dataset limits. Dataflows in Power BI allow you to ingest and transform large volumes of data using the Power Query engine running in the cloud, which can handle datasets that exceed the 1 GB per dataset limit in shared capacity or the 10 GB limit in Premium capacities. By storing the transformed data in Azure Data Lake Storage Gen2, dataflows enable you to work with larger data volumes without being constrained by dataset size limits.

Answer

To avoid using an on-premises data gateway

Answer

To achieve real-time data refresh

Answer

To automatically enforce data lineage

Question 4

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 5

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 6

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 7

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

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

Accepted Answer

SharePoint list. Power Query in Power BI Desktop can connect to SharePoint lists, Excel workbooks, and SQL Server Analysis Services databases. SSAS supports both live and import connections; when you choose import, Power Query is used for data transformation. Thus, it is a valid Power Query data source. The original explanation incorrectly claims SSAS bypasses Power Query.

Answer

Power BI dataset

Answer

Microsoft Entra ID

Question 14

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 15

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