PL-300 Prepare the data — 100 Questions

Question 1

When importing data from a CSV file, Power Query detects that the first row contains column headers. However, the actual data starts from row 2. The analyst notices that some rows have extra columns due to commas within quoted fields. What is the most efficient way to handle this issue?

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

Accepted Answer

Use 'Split Column by Delimiter' and choose 'Comma' with the option to split at each occurrence.. The 'Split Column by Delimiter' feature in Power Query, when configured to split at each occurrence of the comma delimiter, correctly handles commas that appear inside quoted fields. Power Query's M engine respects the standard CSV quoting rules (RFC 4180), so quoted commas are not treated as delimiters. This approach is the most efficient as it requires no manual cleanup and preserves data integrity.

Answer

Remove the top row and then split columns manually.

Answer

Change the file encoding from UTF-8 to ANSI.

Answer

Use 'Replace Values' to replace commas with semicolons.

Question 2

A data analyst is preparing data from multiple Excel files stored in SharePoint. Each file has the same structure but different data. Which THREE steps are necessary to combine these files into a single table in Power Query?

Accepted Answer

Create a connection to the SharePoint folder.. Connecting to the SharePoint folder allows Power Query to access all files in the folder as a single data source. This is the foundational step for the 'Combine Files' workflow, which then uses a sample file to infer the schema and automatically applies transformations to all matching files.

Answer

Use the 'Merge Queries' feature to join the files.

Answer

Individually import each file and append them manually.

Question 3

You are building a Power BI data model from a CSV file that contains sales transactions. The CSV file has a column named 'TransactionDate' that stores dates as text in the format 'YYYYMMDD'. You need to create a date table that includes all dates from the transaction data. Which Power Query step should you use to convert the TransactionDate column to a date data type?

Accepted Answer

Parse -> Date. The 'Parse' feature in Power Query, specifically the 'Parse -> Date' transformation, is designed to convert text columns with custom date formats like 'YYYYMMDD' into a proper date data type. This method intelligently interprets the text pattern without requiring locale-specific settings, making it the most direct and reliable approach for this format.

Answer

Change Type -> Using Locale -> Date

Answer

Split Column -> By Number of Characters

Answer

Detect Data Type

Question 4

You are importing data from an Excel workbook that contains multiple worksheets. One worksheet has a column named 'Sales Amount' that contains values with different currencies (USD, EUR, JPY). You need to split the data into separate columns for each currency. Which Power Query transformation should you use?

Accepted Answer

Split Column by delimiter. The correct transformation is Split Column by delimiter because the 'Sales Amount' column contains values with different currencies (USD, EUR, JPY) that are likely stored as text with a currency symbol or code prefix (e.g., '$100', '€200', '¥300'). Splitting by a delimiter (such as a space or the first character) allows you to separate the currency code from the numeric value into distinct columns, enabling further data type conversion and analysis per currency. This is a standard data cleansing technique in Power Query for parsing multi-currency data from a single column.

Answer

Unpivot Columns

Answer

Merge Columns

Answer

Group By

Question 5

You are reviewing a Power Query query that combines data from multiple CSV files in a folder. The query uses the 'Combine Files' function. Which TWO actions can you take to improve the performance of this query?

Accepted Answer

Disable the 'Promote Headers' step if the headers are not needed.. Disabling the 'Promote Headers' step reduces the number of transformations applied to each file during the combine operation. When headers are not needed, skipping this step avoids an extra row promotion and type detection pass, which can significantly reduce query execution time, especially when combining many files.

Answer

Load all data to the data model without filtering.

Answer

Increase the scheduled refresh frequency.

Answer

Enable 'Fast Combine' in the Power Query options.

Question 6

You are preparing data from a SQL Server database. The query includes a WHERE clause that filters rows based on a date column. You want to ensure that the filter is pushed back to the database (Query Folding). Which THREE conditions must be met?

Accepted Answer

The transformations applied must be supported for query folding.. Query folding in Power Query requires that all transformations applied to the source data are natively supported by the source database engine. If a transformation (e.g., certain date calculations or custom columns) cannot be translated into a SQL statement, Power Query will break folding and perform the operation locally, negating the performance benefit.

Answer

All columns from the table must be loaded to the data model.

Answer

The query must use a DirectQuery connection.

Question 7

You are building a Power BI report that uses a large fact table with 100 million rows. The data source is a SQL Server view that filters data by a date range. You want to minimize the data loaded into the model while maintaining the ability to query any date range later. What should you do?

Accepted Answer

Configure Incremental refresh with Detect data changes on the date column.. Incremental refresh partitions the fact table by date and allows Power BI to refresh only the partitions that have changed, rather than the entire table. This minimizes the amount of data loaded during each refresh cycle, while keeping all historical data that has already been loaded available for queries. To support any date range, configure the incremental refresh policy with an archive window that retains the full history (or perform an initial full load). 'Detect data changes' helps further reduce refresh time by only updating rows that changed since the last refresh.

Answer

Switch to DirectQuery mode and rely on the source database for filtering.

Answer

Use Import mode and apply a date filter in Power Query to limit rows.

Answer

Create a calculated table with only the required columns.

Question 8

You are preparing data for a Power BI report that analyzes customer churn. The source data contains the following columns: CustomerID, Churn (Yes/No), AgeGroup (Teen, Adult, Senior), SubscriptionType (Basic, Premium), MonthlyCharges, TotalCharges, TenureMonths. You need to ensure data quality and optimize the model. Which TWO actions should you take? (Choose two.)

Accepted Answer

Remove TotalCharges column if it can be calculated from MonthlyCharges and TenureMonths.. Removing derived columns like TotalCharges (which equals MonthlyCharges × TenureMonths) reduces data redundancy, improves model size, and avoids potential inconsistencies. Option D is correct because setting CustomerID as a unique key and marking it as the primary column ensures proper row-level identity, enables efficient relationships, and is a best practice for star schema modeling in Power BI.

Answer

Convert Churn column to a numeric 0/1 column.

Answer

Create a calculated column for AverageCharges per customer.

Answer

Create a separate table for subscription types.

Question 9

You are a data analyst at a retail company. You are building a Power BI report to analyze sales performance across stores. The data source is a SQL Server database with a table called 'SalesTransactions' containing 500 million rows. The table has columns: TransactionID, StoreID, ProductID, Quantity, UnitPrice, Discount, TransactionDate. You have imported the data into Power BI using Import mode. The report is slow when users filter by date or store. The initial data load took 45 minutes, and scheduled refreshes are failing because they exceed the 2-hour refresh limit. You need to reduce the refresh time and improve query performance. The business requires that users can see all historical data and that the report is always up-to-date (refreshed daily). What should you do?

Accepted Answer

Implement Incremental refresh with a policy to keep the last 5 years of data and detect changes.. Incremental refresh in Power BI allows you to partition large tables (like 500M rows) into smaller date-based ranges, refreshing only the most recent data (e.g., last 5 years) while keeping historical partitions static. This drastically reduces refresh time and avoids the 2-hour refresh limit, while still meeting the requirement for daily updates and full historical visibility. The 'detect changes' option further optimizes by only refreshing partitions where data has changed.

Answer

Create aggregations on the SalesTransactions table.

Answer

Switch to DirectQuery mode to avoid data import.

Answer

Use a live connection to the SQL Server database.

Question 10

Drag and drop the steps to create a calculated column in Power BI Desktop into the correct order.

Accepted Answer

Step 1: Select the table. Step 2: Click New Column. Step 3: Write the DAX formula.. Creating a calculated column involves selecting the table, using the New Column button, and writing a DAX expression that evaluates row by row.

Answer

Step 1: Click New Column. Step 2: Select the table. Step 3: Write the DAX formula.

Answer

Step 1: Write the DAX formula. Step 2: Select the table. Step 3: Click New Column.

Answer

Step 1: Select the table. Step 2: Write the DAX formula. Step 3: Click New Column.

Question 11

Drag and drop the steps to publish a Power BI Desktop report to the Power BI service into the correct order.

Accepted Answer

Sign in to Power BI, then select a workspace, then click Publish, then view the report in the Power BI service.. Publishing requires signing in, selecting a workspace, and confirming; the report then appears in the Power BI service.

Answer

Select a workspace, then sign in to Power BI, then click Publish, then view the report.

Answer

Click Publish, then select a workspace, then sign in to Power BI, then view the report.

Answer

Sign in to Power BI, then click Publish, then select a workspace, then view the report.

Question 12

Drag and drop the steps to create a calculated table in Power BI Desktop using DAX into the correct order.

Accepted Answer

Navigate to the Modeling tab, click New Table, enter DAX expression in the formula bar, press Enter.. Calculated tables are created via DAX expressions and can be used like regular tables, but they are computed at refresh time.

Answer

Navigate to the Modeling tab, enter DAX expression in the formula bar, click New Table, press Enter.

Answer

Navigate to the Modeling tab, click New Table, press Enter, enter DAX expression in the formula bar.

Answer

Click New Table, navigate to the Modeling tab, enter DAX expression in the formula bar, press Enter.

Question 13

Match each DAX function to its description.

Accepted Answer

CALCULATE: Evaluates an expression in a modified filter context.. Correct matches: CALCULATE modifies filter context; SUM adds column numbers; FILTER returns a subset of rows. Common confusions include swapping CALCULATE with FILTER or SUM with VALUES.

Answer

CALCULATE: Adds all the numbers in a column.

Answer

SUM: Returns a table with unique values from a column.

Answer

FILTER: Evaluates an expression in a modified filter context.

Question 14

Match each visualization type to its typical use case.

Accepted Answer

Clustered bar chart: Compare categories across groups. Different visuals are suited for different analytical tasks. Common correct pairings: Clustered bar chart compares categories, line chart shows trends, and pie chart shows proportions. Distractors swap these uses.

Answer

Clustered bar chart: Show trends over time

Answer

Line chart: Compare categories across groups

Answer

Pie chart: Show relationship between two numerical variables

Question 15

Match each Power BI concept to its definition.

Accepted Answer

Star Schema: A data model structure where fact tables are connected to dimension tables in a star-like pattern.. Star schema uses denormalized dimensions, snowflake schema normalizes dimensions further. Calculated columns are static row-by-row evaluations; measures are dynamic aggregations based on filter context.

Answer

Star Schema: A normalized schema where dimension tables are split into sub-dimensions.

Answer

Snowflake Schema: A denormalized schema where fact table connects directly to dimension tables.

Question 16

You are connecting Power BI to an Azure SQL Database. The database contains a table with 10 million rows. You need to minimize the initial load time for the report. What should you do?

Accepted Answer

Use DirectQuery mode. DirectQuery mode sends queries directly to the Azure SQL Database, avoiding the need to import 10 million rows into the Power BI model. This eliminates the initial data transfer and processing overhead, significantly reducing the initial load time for the report.

Answer

Use Import mode and create aggregations

Answer

Use Dual mode

Answer

Use Import mode with Incremental refresh

Question 17

You have a Power BI dataset that uses DirectQuery to a Snowflake data warehouse. Users report that reports are slow. You need to improve query performance without changing the data source. What should you configure?

Accepted Answer

Create aggregations in the Power BI model. Creating aggregations in the Power BI model allows pre-summarized data to be stored in memory, reducing the volume of queries sent to Snowflake via DirectQuery. This improves performance without altering the data source, as aggregations can serve high-level queries from cached data while falling back to DirectQuery for detailed queries.

Answer

Enable Query Folding in Power Query

Answer

Change the dataset to Import mode

Answer

Increase the number of concurrent queries in Power BI Service

Question 18

You are using Power Query to combine data from multiple Excel files in a SharePoint folder. Each file has a sheet named 'Sales'. The columns across files are identical but occasionally a file has extra columns. You need to ensure the combined table contains only the common columns across all files. Which Power Query step should you use?

Accepted Answer

Use Remove Other Columns on the first file's columns. Use Remove Other Columns on the first file's columns (the sample file) after selecting only the columns that are common to all files. This defines the schema so Power Query excludes any extra columns from other files. Table.Combine with the 'Table1' parameter keeps only the columns of the first table; if that first file happens to contain an extra column, the combined output will include it and will not guarantee only common columns. Table.ColumnsOfType filters by data type, and Merge Queries as New joins tables horizontally.

Answer

Use Table.Combine with the 'Table1' parameter

Answer

Use Table.ColumnsOfType to filter common columns

Answer

Use Merge Queries as New

Question 19

You are importing data from a CSV file that contains a column 'OrderDate' with values in the format 'YYYY-MM-DD'. Power Query automatically detects the data type as Date. You need to ensure that the data type remains Date even if the source file later changes the date format to 'MM/DD/YYYY'. What should you do?

Accepted Answer

Set the data type to Date using the 'Change Type' step in Power Query. The correct option is C: set the data type to Date using the 'Change Type' step in Power Query. This explicitly fixes the column's type as Date in the query's applied steps, so Power Query parses the incoming values according to the defined type rather than relying on automatic detection, which can vary with source format changes. Using 'Using Locale' (B) is only needed when the source text format is ambiguous or locale-specific, and it does not by itself guarantee a stable Date type. Splitting into year/month/day (A) is unnecessary and would not produce a Date type directly, and changing the Power Query editor's regional settings (D) affects locale interpretation globally rather than pinning the column's data type.

Answer

Split the column into year, month, day and then combine

Answer

Use the 'Using Locale' transformation

Answer

Change the regional settings of the Power Query editor

Question 20

You are preparing data from a SQL Server database. The table 'Sales' contains a column 'OrderDate' that includes both date and time (e.g., '2023-10-15 14:30:00'). You need to create a separate column for the time portion only. Which TWO Power Query transformations can you use?

Accepted Answer

Extract - Duration. The 'Extract - Duration' transformation in Power Query extracts the time portion from a datetime column by calculating the duration since midnight, effectively isolating the time component. Option E is correct because splitting the column by a space delimiter separates the date and time parts into two columns, allowing you to keep only the time portion. Both methods produce a time-only value suitable for analysis.

Answer

Extract - Year

Answer

Merge Columns

Answer

Format - Trim