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

Question 1

You are troubleshooting a Power Query transformation that groups sales data by ProductID. The query runs slowly and you suspect the filter is being applied after loading all rows. What change would improve performance by pushing the filter to the source?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Replace the first three lines with a native SQL query that includes the WHERE clause. Pushing filter logic to the source database via a native SQL query with a WHERE clause reduces the amount of data loaded into Power Query. This leverages query folding, which allows the source (e.g., SQL Server) to perform the filtering before data is transferred, significantly improving performance for large datasets.

Answer

Disable the 'Enable load' option for the SalesTable

Answer

Use CALCULATE in DAX to filter

Answer

Add a 'Table.Buffer' step after the filter

Question 2

You need to prepare data from a folder containing multiple CSV files with identical structure. What is the most efficient way to load all files into a single table?

Accepted Answer

Use the 'From Folder' data source and then click 'Combine & Transform Data'. The 'From Folder' data source in Power Query automatically detects multiple CSV files with identical structure and provides a 'Combine & Transform Data' button that merges them into a single table in one step. This is the most efficient method as it eliminates the need for manual imports or scripting, leveraging Power Query's built-in file combination logic.

Answer

Import each CSV file separately and then append them

Answer

Write a Python script in Power Query to read and combine files

Answer

Use a dataflow to connect to the folder and apply the 'Combine Files' transformation

Question 3

You are preparing a Power BI report that uses data from Azure SQL Database. The data includes a date column that needs to be used in time intelligence calculations. You want to ensure that the date column is recognized as a date table in the data model. What should you do?

Accepted Answer

In the model view, mark the table as a date table by selecting the date column.. Marking a table as a date table in the model view explicitly tells Power BI that the table contains a complete set of dates for time intelligence calculations. This ensures that DAX functions like TOTALYTD, SAMEPERIODLASTYEAR, and DATEADD work correctly by using the marked date column as the primary date reference for the model, rather than relying on auto-generated date hierarchies.

Answer

Set the data type of the date column to Date.

Answer

Use the DAX function DATEADD to create a date table.

Answer

Create a calculated column using DATEVALUE to convert the date.

Question 4

You are importing data from a CSV file that contains a column with mixed data types (numbers and text). Power BI automatically assigns the data type as Text. You need to perform numerical aggregations on this column. What should you do?

Accepted Answer

In Power Query Editor, change the data type of the column to Whole Number or Decimal Number.. Changing the column's data type to Whole Number or Decimal Number in Power Query Editor will force Power BI to interpret the numeric values as numbers, enabling aggregations like SUM or AVERAGE. Power Query Editor provides a robust transformation environment where data type changes are applied during the load process, ensuring the column is treated as numeric for all downstream calculations. This approach is more efficient and reliable than using DAX conversions, as it avoids the overhead of calculated columns and leverages Power Query's native type detection and error handling.

Answer

Split the column using a delimiter to separate numbers from text.

Answer

Create a relationship with a numeric table to enable aggregation.

Answer

Create a calculated column in DAX using VALUE() to convert the text to numbers.

Question 5

You are importing data from an Excel workbook that contains multiple sheets. Each sheet has similar structure but different data for different regions. You need to combine all sheets into a single table for analysis. What should you do?

Accepted Answer

In Power Query Editor, use Append Queries to combine the sheets.. The Append Queries operation in Power Query Editor is designed to combine rows from multiple tables or queries with similar column structures into a single table. Since each sheet in the Excel workbook contains data for different regions with the same structure, appending them stacks the rows, creating a unified dataset for analysis.

Answer

In Power Query Editor, use Merge Queries to combine the sheets.

Answer

Copy and paste the data from each sheet into a master sheet in Excel.

Answer

In Power Query Editor, use Group By to consolidate the data.

Question 6

You are preparing data for a Power BI report. The source data contains a column with values like '1,234.56' formatted as text. You need to convert this to a numeric value for calculations. What is the best approach?

Accepted Answer

In Power Query Editor, replace the comma with an empty string, then change the data type to Decimal Number.. Power Query Editor provides the most efficient and scalable method for cleaning and converting text-based numeric data. By replacing the comma with an empty string and then changing the column data type to Decimal Number, you perform the transformation directly in the data preparation layer (M language), which is optimized for performance and avoids the overhead of DAX calculated columns. This approach also ensures the data remains clean for all downstream calculations.

Answer

In Power Query Editor, split the column by comma and then use the second part.

Answer

In DAX, create a calculated column using VALUE() after removing commas.

Answer

In Power Query Editor, use the 'Clean' transform to remove non-numeric characters.

Question 7

You are building a Power BI data model with multiple fact tables and dimension tables. One of the dimension tables has a one-to-many relationship with two fact tables, but the relationships are inactive. You need to create measures that use both fact tables and the dimension table without relying on user interactions to activate relationships. What should you do?

Accepted Answer

Use the USERELATIONSHIP function in the measure definition.. The USERELATIONSHIP function in DAX allows you to temporarily activate an inactive relationship within a measure definition. This enables you to use a dimension table with multiple fact tables without changing the model's default active relationships or relying on user interactions. By specifying the inactive relationship in the measure, you can perform accurate aggregations across both fact tables while maintaining the intended data model structure.

Answer

Merge the fact tables into one table.

Answer

Create calculated columns in the dimension table to relate to each fact table.

Answer

Change both relationships to active.

Question 8

You are loading data from a SQL Server database into Power BI. You notice that the import takes a long time because the source table contains many rows. You only need a subset of rows based on a date filter. What should you do to improve performance?

Accepted Answer

Use a SQL query with a WHERE clause in the Power Query Editor.. Using a SQL query with a WHERE clause in Power Query Editor pushes the date filter down to the SQL Server database, reducing the amount of data transferred over the network and imported into Power BI. This query folding technique leverages the database engine's indexing and processing power, which is far more efficient than filtering after import. By retrieving only the necessary rows upfront, you minimize both network latency and memory consumption in Power BI.

Answer

Remove unnecessary columns in Power Query.

Answer

Load all data and then apply a filter in Power BI.

Answer

Enable incremental refresh on the dataset.

Question 9

Which TWO of the following are valid methods to combine data from multiple sources in Power BI?

Accepted Answer

Append Queries. Append Queries is a valid method to combine data from multiple sources in Power BI because it stacks rows from two or more tables into a single table, which is essential when you have similar data structures across different sources (e.g., monthly sales files). This operation is performed in Power Query Editor and corresponds to a UNION operation in SQL, making it a standard data preparation technique.

Answer

Pivot Column

Answer

Unpivot Columns

Answer

Group By

Question 10

Which THREE of the following are best practices when preparing data in Power BI for optimal performance?

Accepted Answer

Set correct data types for all columns.. Option C is correct because assigning the correct data types (for example, whole number, decimal, date/time, or text) to every column reduces memory usage in the VertiPaq engine, enables efficient compression, and prevents implicit conversions that slow down DAX calculations. Option D is correct because removing unnecessary columns and rows during import (ideally in Power Query before loading) shrinks the data model, lowers memory consumption, and speeds up refresh and query processing. Option E is correct because query folding translates Power Query transformations into native source queries (for example, SQL statements), letting the source database perform filtering, joins, and aggregations so less data is transferred and processed locally. Option A is not a best practice because merging all tables into one wide table creates redundancy, bloats the model, and destroys the star-schema relationships that Power BI optimizes for. Option B is not a best practice because calculated columns are computed and stored during refresh, consuming memory and increasing model size, whereas measures are evaluated at query time and are generally preferred for aggregations.

Answer

Merge all tables into a single table for simplicity.

Answer

Create calculated columns instead of measures when possible.

Question 11

You are a data analyst at a retail company. You are building a Power BI report to analyze sales performance across multiple stores. The source data comes from an Azure SQL Database that contains a table 'Sales' with columns: StoreID, ProductID, SaleDate, Quantity, and Amount. The database also has a 'Stores' table with StoreID and StoreName, and a 'Products' table with ProductID, ProductName, and Category. You need to create a data model that supports filtering by store, product category, and date, and also allows calculation of year-over-year sales growth. You want to minimize the model size and ensure optimal performance. The data volume is large (millions of rows). You must design the data model. What should you do?

Accepted Answer

Import Sales, Stores, and Products tables, create a separate date table using CALENDAR, and establish relationships between Sales and dimension tables.. It follows the star schema best practice: importing dimension tables (Stores, Products, a dedicated Date table) and the fact table (Sales) separately, then creating relationships. This minimizes model size by avoiding data duplication and enables efficient filtering by store, product category, and date. The separate date table is essential for accurate year-over-year calculations using DAX time intelligence functions like SAMEPERIODLASTYEAR, which require a continuous date range.

Answer

Import all tables as they are and create a single flat table by merging Sales, Stores, and Products in Power Query.

Answer

Import Sales table only and create calculated columns for StoreName and ProductName using RELATED.

Answer

Import Sales table and use the auto date/time feature for time intelligence.

Question 12

You are a Power BI developer for a financial services company. You are preparing data from multiple sources: a CSV file containing daily stock prices (ticker, date, close_price), a SQL Server database with company information (ticker, company_name, sector), and an Excel file with quarterly earnings data (ticker, quarter, earnings_per_share). The CSV file has 5 years of daily data (approx 1.3 million rows). The SQL Server table has 5000 rows. The Excel file has 20,000 rows. You need to create a data model that allows users to filter by sector, company, and date range, and to calculate moving averages of stock prices and compare earnings over time. Performance is critical. You must decide the best approach to combine and model this data. What should you do?

Accepted Answer

Import all tables, create a date table with CALENDAR, and establish relationships: StockPrices[Date] -> DateTable[Date], StockPrices[Ticker] -> Company[Ticker], Earnings[Ticker] -> Company[Ticker], and create a many-to-many relationship between Earnings and DateTable using a bridge table.. Importing all tables into the in-memory VertiPaq engine ensures optimal performance for large datasets (1.3M rows) and complex calculations like moving averages. Creating a separate date table with CALENDAR enables proper time intelligence, while the bridge table resolves the many-to-many relationship between quarterly earnings and daily dates, allowing accurate filtering by sector, company, and date range without performance degradation.

Answer

Use DirectQuery for StockPrices (CSV) and import the other tables.

Answer

Import only StockPrices and Company, and use the auto date/time feature; ignore Earnings data.

Answer

Import all tables, then in Power Query merge StockPrices with Company and Earnings into a single flat table using left outer joins.

Question 13

You are transforming data in Power Query. A column named 'SalesAmount' contains values as text with a dollar sign and thousands separator, e.g., "$1,234.56". You need to convert this column to a decimal number for analysis. What is the most efficient sequence of transformations?

Accepted Answer

Use Replace Values to remove '$' and ',', then change data type to Decimal Number.. It explicitly removes both the dollar sign and the comma using Replace Values before changing the data type to Decimal Number, ensuring proper conversion without errors. Option A is inefficient; splitting the column is unnecessary when simple replacements work. Option B would fail because Power Query cannot automatically parse currency symbols and thousands separators from text when changing data type directly. Option D appears similar but specifies 'Decimal' instead of 'Decimal Number', which is not a valid data type in Power Query, leading to an error or incorrect result.

Answer

Split the column by delimiter and keep the numeric part, then change data type.

Answer

Change data type to Decimal Number directly; Power Query will automatically clean the values.

Answer

Use Replace Values to remove '$' and ',' then change data type to Decimal.

Question 14

You are cleaning a column that contains numbers stored as text, with occasional leading/trailing spaces and currency symbols. You apply the function above to the column. However, some rows return null even though the original text appears to be a valid number, such as '$ 1,234.56'. What is the most likely cause?

Accepted Answer

The function does not remove commas and currency symbols before conversion.. Conversion functions like Number.FromText do not automatically strip currency symbols, commas, or spaces. The input '$ 1,234.56' must be cleaned (e.g., remove $, spaces, and commas) before conversion, or the conversion fails and returns null. The error handling (if any) only catches the error; it does not sanitize the input.

Answer

The try...otherwise block is not catching parsing errors.

Answer

Text.Clean removes necessary decimal separators.

Answer

The function is applied to the wrong data type column.

Question 15

You are merging two queries in Power Query. Query 'Orders' contains columns: OrderID, CustomerID, OrderDate. Query 'Customers' contains columns: CustomerID, CustomerName, Segment. You need to add the CustomerName to the Orders query. The relationship between Orders and Customers is many-to-one. Which join kind should you use?

Accepted Answer

Left Outer. The goal is to retain all rows from the Orders table while adding CustomerName from the Customers table. A Left Outer join returns all rows from the first (left) table and only matching rows from the second (right) table, filling non-matches with null. Since the relationship is many-to-one, each OrderID may have a matching CustomerID, and you want to keep every order even if a customer is missing — exactly what Left Outer does.

Answer

Inner

Answer

Right Outer

Answer

Full Outer