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

Question 1

You are loading data from an Excel workbook that has multiple sheets. Each sheet contains sales data for a different region. You need to combine all sheets into one table. What is the most efficient approach?

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

Accepted Answer

Use 'Append Queries' for each sheet individually.. Using 'Append Queries' in Power Query allows you to combine multiple queries (one per sheet) into a single table. You can load all sheets as queries, then use the Append Queries feature to union them all at once, which is efficient and avoids manual repetition. Power Query's Append operation is designed for this purpose and is more efficient than loading separate tables into the model (C) or using Merge (B) which performs joins, not unions. 'From Folder' (D) is intended for combining multiple files, not sheets within a single workbook.

Answer

Use 'Merge Queries' to join all sheets into one.

Answer

Load each sheet as a separate table in the model and create a calculated table to union them.

Answer

Use Power Query's 'From Folder' option to combine all Excel files in a folder, then select 'Combine & Transform' to use the workbook as a sample.

Question 2

You are designing a data model for a retail company. The source system has a Sales table with columns: Date, StoreID, ProductID, SalesAmount. You need to create a date dimension table that includes all dates from the Sales table. Which DAX expression should you use to create the date table?

Accepted Answer

DateTable = CALENDAR(MIN(Sales[Date]), MAX(Sales[Date])). `CALENDARAUTO()` automatically generates a contiguous date table spanning all dates present in the model, including those from the Sales table. This function scans all date columns in the model and returns a single column of dates from the earliest to the latest, which is the simplest way to create a date dimension that covers the full range of transaction dates without needing explicit MIN/MAX references.

Answer

DateTable = VALUES(Sales[Date])

Answer

DateTable = DISTINCT(Sales[Date])

Question 3

Which TWO of the following are valid methods to transform data in Power Query?

Accepted Answer

Use 'Unpivot Columns' to turn selected columns into attribute-value pairs.. Option A is correct because 'Unpivot Columns' is a core Power Query transformation that converts selected wide columns into two columns—an attribute column holding the former column headers and a value column holding the cell values—reshaping data from wide to tall format. Option B is correct because 'Split Column' is a standard Power Query transform that divides a single column into multiple columns using a specified delimiter (or by number of characters, positions, etc.), which is a data-shaping transformation. Option C is correct because 'Pivot Column' is the inverse transformation of unpivot, aggregating unique values from a column into new columns, thereby restructuring the table. Option D is not a data transformation in this sense but a join operation that combines rows from multiple tables based on a matching key, so it does not belong. Option E is incorrect on two counts: 'Append Queries' combines rows (not columns) from two tables, and combining columns is done by 'Merge Queries' or 'Combine Columns', so the description is technically wrong.

Answer

Use 'Merge Queries' to combine rows from multiple tables based on a key.

Answer

Use 'Append Queries' to combine columns from two tables.

Question 4

You are preparing data from a CSV file that has inconsistent date formats. Some rows use 'MM/dd/yyyy' and others use 'dd/MM/yyyy'. You need to parse all dates correctly. What is the best approach in Power Query?

Accepted Answer

Use the 'Replace Values' to standardize the date format, then change data type.. 'Replace Values' allows you to standardize the inconsistent date strings (e.g., swapping day and month parts) before Power Query attempts to parse them as dates. After replacing the delimiters or reordering parts, you can change the column type to 'Date' using a consistent culture (e.g., 'en-US' for MM/dd/yyyy), ensuring all rows parse correctly regardless of original format.

Answer

Use the 'Parse' -> 'Date' transformation with a specific culture.

Answer

Use the 'Split Column' by delimiter to separate date parts.

Answer

Use the 'Detect Data Type' feature to automatically identify the format.

Question 5

You have a Power BI dataset that uses a SQL Server view as the data source. The view returns data for all customers, but you only need data for customers in the 'West' region. The view does not accept parameters. How should you filter the data?

Accepted Answer

In Power Query, apply a filter on the Region column after loading the view.. Applying a filter in Power Query after loading the view is the standard approach when the source view does not accept parameters. This method leverages Power Query's query folding capabilities, which push the filter down to the SQL Server, reducing data transfer and improving performance. It avoids loading unnecessary data into the model while maintaining the ability to refresh efficiently.

Answer

Use a native SQL query in Power Query with a WHERE clause to ensure query folding.

Answer

Ask the database administrator to modify the view to include a WHERE clause.

Answer

Load the entire view into the model and use a report-level filter.

Question 6

You are designing a Power BI semantic model for a retail company. You have a 'Products' table with columns: ProductID, ProductName, Category, UnitPrice. You need to ensure that when users filter by 'Category', they see only products belonging to that category. What should you set the 'Category' column's 'Data Category' property to?

Accepted Answer

Category. The 'Data Category' property in Power BI is used to assign a semantic meaning to a column, such as 'Category', 'Geography', or 'Image URL', which helps Power BI apply appropriate default behaviors like grouping or filtering. Setting the 'Category' column's 'Data Category' to 'Category' ensures that when users filter by that column, Power BI treats it as a categorical field, displaying only products belonging to the selected category. This property does not affect the underlying data but informs the visualization layer how to interpret the column for better user experience.

Answer

Geography

Answer

Image URL

Answer

Place

Question 7

You have a Power BI semantic model that imports data from a SQL Server view. The view is updated every hour, but the Power BI dataset is scheduled to refresh daily at 2:00 AM. Users report that reports show stale data. You need to ensure that the dataset reflects the latest data without changing the refresh schedule. What should you do?

Accepted Answer

Change the storage mode of the table to DirectQuery.. Changing the storage mode to DirectQuery allows the Power BI dataset to query the SQL Server view directly at report render time, ensuring that users always see the latest data without altering the scheduled refresh frequency. DirectQuery bypasses the import process and retrieves data on demand, so the dataset reflects real-time changes from the source view.

Answer

Replace the view with a physical table in SQL Server.

Answer

Increase the scheduled refresh frequency to every 30 minutes.

Answer

Configure incremental refresh for the table.

Question 8

Refer to the exhibit. You are reviewing a Power BI data source privacy configuration. The SalesSQL data source is set to 'Organizational' and the MarketingCSV is set to 'Private'. You plan to combine these two sources in a query. What will happen when the query is executed?

Accepted Answer

The query will succeed, but the combination may be blocked or data may be limited to protect the Private source.. The correct answer is D: the query will succeed, but the combination may be blocked or data may be limited to protect the Private source. In Power BI, when combining sources with different privacy levels, the engine applies privacy-level rules that prevent data from a Private source from being exposed to other sources; because MarketingCSV is Private and SalesSQL is Organizational, the combination is allowed but Power BI may enforce isolation, such as buffering or blocking the join, to protect the Private data. Options A and B are wrong because the Private level is not silently upgraded to Organizational and the combination is not unrestricted. Option C is wrong because incompatible privacy levels do not automatically cause a hard failure; the query can still run with protective restrictions.

Answer

The query will succeed, but the MarketingCSV data will be treated as Organizational.

Answer

The query will succeed without any restrictions because the combine is allowed between Organizational and Private.

Answer

The query will fail with a privacy level error because the levels are incompatible.

Question 9

You have a Power BI dataset that uses a SQL Server data source. The SQL Server database is located in a different region, causing slow refresh performance. You need to improve the data refresh performance without changing the data source. What should you do?

Accepted Answer

Change the storage mode to DirectQuery to reduce data movement.. Changing the storage mode to DirectQuery reduces data movement by querying the source directly instead of importing all data. This can significantly improve refresh performance when dealing with a remote SQL Server database, as it eliminates the need to transfer large volumes of data across regions. Options like increasing refresh frequency (B) do not address latency, installing an on-premises gateway (C) is for on-premises sources, and enabling 'Fast Combine' (D) bypasses privacy checks and is not a recommended performance optimization for regional latency.

Answer

Increase the scheduled refresh frequency to reduce the amount of data loaded each time.

Answer

Install an on-premises data gateway to reduce latency.

Answer

Enable the 'Fast Combine' option in the Power Query privacy settings.

Question 10

Refer to the exhibit. The Power Query M code imports a CSV file. The import succeeds, but the 'OrderDate' column shows some dates as null even though they appear valid in the CSV. What is the most likely cause?

Accepted Answer

The date format in the CSV is not recognized by the locale used in the transformation.. Power Query relies on the locale of the current environment to interpret date strings. If the CSV contains dates in a format (e.g., 'dd/MM/yyyy') that does not match the locale's expected format (e.g., 'MM/dd/yyyy'), the engine cannot parse them and returns null. This is a common issue when importing data from different regional sources.

Answer

The 'Filtered Rows' step removes rows where Amount > 0, which may include valid dates.

Answer

The 'Promoted Headers' step incorrectly changes the data type of the OrderDate column.

Answer

The 'Replaced Value' step replaces 'null' with null, causing the date to become null.

Question 11

Which TWO actions can you take in Power Query Editor to improve data quality and reduce load time? (Choose two.)

Accepted Answer

Filter out empty rows.. Filtering out empty rows in Power Query Editor directly reduces the number of rows loaded into the data model, which decreases memory usage and improves refresh performance. This action also enhances data quality by removing incomplete records that could cause errors in measures or relationships. The other options either do not reduce load time or may increase it.

Answer

Split columns by delimiter to create more columns.

Answer

Sort the data by a key column.

Answer

Promote headers to use the first row as column names.

Answer

Merge queries to combine data from multiple sources.

Question 12

Which THREE considerations are important when designing a star schema for a Power BI semantic model? (Choose three.)

Accepted Answer

Role-playing dimensions should be implemented by creating multiple dimension tables or using views.. Option B is correct because role-playing dimensions (such as Order Date and Ship Date both referencing a Date dimension) must be surfaced in a star schema either by duplicating the dimension table or by using views to expose separate logical dimensions, since Power BI relationships cannot reuse a single table for multiple active roles. Option C is correct because a fact table in a star schema stores the numeric, additive measures (e.g., Sales Amount, Quantity) plus foreign keys that join to each dimension, forming the core of the model. Option D is correct because a dedicated Date dimension marked as a date table is required for Power BI time intelligence functions such as TOTALYTD, SAMEPERIODLASTYEAR, and DATEADD to work reliably. Option A is not correct because star schemas should use surrogate keys (meaningless integers) in dimension tables for compactness and stability, not natural/business keys. Option E is not correct because star schemas deliberately denormalize dimensions into flat, wide tables; snowflaking (normalizing) adds joins and hurts Power BI query performance and usability.

Answer

Dimension tables should use natural keys instead of surrogate keys for better performance.

Answer

Dimensions should be normalized (snowflaked) to reduce data redundancy.

Question 13

Which TWO are best practices for optimizing Power Query performance? (Choose two.)

Accepted Answer

Disable the 'Enable background refresh' option in the query settings.. Disabling 'Enable background refresh' prevents Power Query from running queries in the background while you continue working in Power BI Desktop. This ensures that query execution is synchronous, which can improve performance by avoiding resource contention and allowing you to monitor progress directly. Background refresh can cause delays and unexpected behavior when multiple queries run simultaneously, especially with large data sources.

Answer

Merge tables as early as possible in the query to combine data.

Answer

Keep all columns in the table to avoid missing data.

Answer

Split columns by delimiter to normalize data.

Question 14

You are preparing a Power BI dataset from a SQL Server data source. The source table has a datetime column 'OrderDate' that you need to use for time intelligence calculations. However, you notice that the data contains dates in multiple formats (e.g., '2024-01-15 14:30:00' and '01/15/2024 2:30 PM'). What is the best practice to ensure consistent date handling in Power Query?

Accepted Answer

Use the 'Detect Data Type' function in Power Query to automatically identify and convert the column.. The 'Detect Data Type' function in Power Query analyzes the column values and uses the system locale settings to parse dates in mixed formats, such as '2024-01-15 14:30:00' and '01/15/2024 2:30 PM', converting them to a consistent datetime type. This is the recommended first step for data type detection and ensures that time intelligence calculations in Power BI can rely on a uniform date/time column.

Answer

Load the column as text and ignore any conversion errors.

Answer

Change the column type to 'Date' in Power Query before loading.

Answer

Split the column into separate date and time columns and then combine them.

Question 15

You are importing data from a CSV file into Power BI. The file contains a column 'SalesAmount' with values like '$1,234.56'. When you load the data, the column is detected as text. What is the most efficient way to convert this column to a numeric type in Power Query?

Accepted Answer

Use 'Replace Values' to remove the dollar sign and comma, then change the column type to decimal.. It directly addresses the root cause: the dollar sign and comma are non-numeric characters that prevent automatic type conversion. By using 'Replace Values' to remove these characters first, you clean the data so that Power Query can then safely change the column type to decimal. This is the most efficient approach because it avoids error rows and preserves data integrity.

Answer

Use 'Detect Data Type' and hope it automatically converts.

Answer

Change the column type to decimal directly and ignore errors.

Answer

Split the column by delimiter ',' and then convert the first part.