Prepare the data practice questions

Increase the row limit in Power Query to load all rows.

Why wrong: Increasing the row limit in Power Query only expands the number of rows shown in the preview pane or loaded if you explicitly load them; it does not perform any aggregation. Loading all rows into the data model increases the volume of data stored, slowing refresh times and inflating memory usage without addressing the need for higher-level summaries. This approach fails to reduce row count and therefore does not solve performance issues caused by large transaction tables.

Remove unused columns from the query.

Why wrong: Removing unused columns from the query reduces the width of the dataset by trimming attributes that are not needed for analysis, which can lower memory footprint and import time. However, it does nothing to reduce the number of rows, so if the underlying source contains millions of sales transactions, the row count remains exactly the same. This optimization is useful for column-level efficiency but cannot address row-volume problems that hinder aggregation performance.

Import the entire table and aggregate in Power BI.

Why wrong: Importing the entire table and then aggregating in Power BI means the full transactional detail is staged in the model before any grouping or summarization occurs. This consumes significant memory during both the refresh and query execution, as Power BI must process every row to compute the aggregation. While Power BI can handle such loads for smaller datasets, it is inefficient and often impractical for high-volume sales data, making it a poor substitute for performing aggregation earlier in the data pipeline.

The 'Orders' table was reordered in Power Query.

Why wrong: Reordering columns in Power Query only changes the sequence of columns in the query output and the data model; it does not modify values, row counts, or the uniqueness of data in any column. Because freshness and integrity checks are based on values and keys, a cosmetic reorder cannot trigger a duplicate key violation. If a reorder were involved, the error would be unrelated to key constraints and would most likely appear as a column mapping issue instead.

Data type mismatch between the source and Power BI.

Why wrong: A data type mismatch between the source and Power BI would produce a type conversion or coercion error, such as 'Cannot convert value' or a null value being loaded, not a duplicate key error. Power BI's engine would attempt to cast the data to the target type, and a failure would halt the refresh with a different diagnostic. Even if multiple rows ended up with the same value after conversion, that would not be reported as a duplicate key unless the column was explicitly a key, and the error message would be different.

A calculated column is referencing the 'Orders' table.

Why wrong: A calculated column referencing the 'Orders' table—whether through RELATED, LOOKUPVALUE, or a simple DAX expression—is evaluated row by row and materialized after the table is loaded. It does not change the underlying keys, the row grain, or the uniqueness of existing columns; it only adds a new column based on current data. Therefore, it cannot introduce duplicate OrderID values or cause a primary key constraint violation. Errors from calculated columns are typically dependency or evaluation errors, not key uniqueness errors.

Merge Queries

Why wrong: Merge Queries is incorrect here because it performs a horizontal join, combining columns from two different queries based on matching key columns, much like a SQL join. This operation enriches one table with related attributes from another and can even duplicate rows if there are multiple matches, whereas the requirement is to consolidate rows. Because the question asks to combine by stacking, not by complementing columns, merge is not the appropriate choice.

Group By

Why wrong: Group By is not a method to combine two queries—it is an aggregation transformation applied within a single query. It partitions rows into distinct groups defined by one or more columns and produces summary statistics (such as count, sum, or average) for each group, thereby reducing the number of rows. This operation loses the original row-level detail and cannot return the full dataset, which disqualifies it from combining multiple tables.

Pivot Column

Why wrong: Pivot Column is a reshaping tool that takes unique values from a selected column and turns them into new columns, typically with an aggregated or literal value in the cross-section cells. It transforms a long, narrow table into a wide format, but it operates on only one query and does not bring in records from a second query. Since the requirement is to stack rows from two queries, pivoting would not achieve the desired vertical union.

Split the date column into year, month, and day columns.

Why wrong: Splitting a date column into separate year, month, and day columns destroys the actual date data type and leaves you with discrete numeric or text fields. Without a true date column, DAX time intelligence functions such as DATESYTD or SAMEPERIODLASTYEAR will not work, and you lose automatic date hierarchy features. This approach also does not introduce any fiscal-year logic; you would still need separate calculations to map months to fiscal periods, which defeats the purpose.

Use a DAX calculated table to generate fiscal year dates.

Why wrong: A DAX calculated table used to generate fiscal year dates is technically possible but is less performant than a Power Query-based date table, especially when the model contains large fact tables. Calculated tables are materialized and stored in memory at refresh time, and they recalculate whenever dependencies change, which can significantly increase load time and model size. Additionally, you have to write more DAX to generate the continuous date range and fiscal-year columns, whereas Power Query can do this more transparently and with better refresh control.

Add a calculated column in the existing table using DAX.

Why wrong: Adding a calculated column to the existing fact table with DAX is problematic because calculated columns are stored with each row in the table, inflating the model size and increasing refresh time for large tables. A fiscal year calculated column in the fact table also cannot act as a date dimension or be used with time-intelligence functions the way a standalone date table can. Furthermore, this approach forces the fiscal logic to be repeated on every row, whereas a date table centralizes that logic in one place.

Remove the top row and then split columns manually.

Why wrong: Removing the top row only discards the header or an initial malformed row; it does nothing to address rows that contain additional delimiters in the middle of the data. Splitting columns manually requires you to know the exact number of fields per row and will fail when different rows have different column counts, leaving nulls or misaligned data. This approach ignores the root cause—quoted commas being misinterpreted as field separators—and is not scalable or repeatable.

Change the file encoding from UTF-8 to ANSI.

Why wrong: Changing the file encoding from UTF-8 to ANSI affects how byte sequences are decoded into characters, not how delimiters are interpreted. The comma and quote characters are part of the ASCII range and will be recognized identically under both encodings, so the parsing behavior is unchanged. Worse, converting a UTF-8 file to ANSI can corrupt any non-ASCII characters (such as accented letters or emoji), introducing data quality issues without fixing the column structure.

Use 'Replace Values' to replace commas with semicolons.

Why wrong: Replacing commas with semicolons via 'Replace Values' operates as a blind text substitution that does not respect quoted regions. Every comma, including those that legitimately exist inside quoted strings, will be replaced, thereby altering the content of fields and potentially merging data that should remain separate. Additionally, this does not solve the root problem of inconsistent delimiter counts; it merely changes the delimiter character, leaving the extra columns issue unresolved and often corrupting the data further.

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

Why wrong: The 'Merge Queries' feature in Power Query performs a join between two tables by matching keys, adding columns from the second table to the first. It is designed for side-by-side data enrichment, not for stacking rows from many Excel files vertically. Combining multiple files that share the same schema requires the 'Combine Files' transform or the 'Append Queries' feature, so merging would neither consolidate rows nor handle more than two inputs efficiently. Thus, merging is incorrect for this file-combination scenario.

Individually import each file and append them manually.

Why wrong: Individually importing each Excel file and manually appending them is a highly inefficient approach that does not scale if the number of files changes. Each import creates a separate query, and every new or updated file requires repeating the import and modify the append step, leading to high maintenance overhead and potential errors. The entire folder-based combine workflow is designed to handle these tasks automatically, so this manual method is incorrect for a professional and reusable Power BI solution.

Change Type -> Using Locale -> Date

Why wrong: Change Type -> Using Locale -> Date is incorrect because it forces date interpretation according to the selected locale's standard order (e.g., MM/dd/yyyy for en-US), so a custom format like 'dd-MMM-yyyy' will be misinterpreted or fail to convert. It gives you no way to specify the exact pattern present in the data, and any deviation from the locale's conventions will result in errors or, worse, silently wrong dates. This option is only safe when the data already exactly matches the locale's date pattern.

Split Column -> By Number of Characters

Why wrong: Split Column -> By Number of Characters is incorrect because it fragments the date string into multiple columns of text, such as separating '2025-01-31' into '2025', '01', and '31', which does not perform any date conversion. You would then need to carefully recombine those pieces with a concatenation function and parse them back into a date, adding complexity and risking errors. The operation leaves the data as text fragments, so it fails to achieve the goal of converting the column to a proper Date type directly.

Detect Data Type

Why wrong: Detect Data Type is incorrect because it uses automatic heuristic detection based on the current locale and typical date patterns, which will not recognize custom formats like 'dd-MMM-yyyy' or 'yyyymmdd'. It often leaves such columns as text because the inference fails, or it incorrectly converts them if the format happens to match a locale pattern in a misleading way. There is no way to force a custom format during detection, so this method is unreliable for non-standard data.

Increase the scheduled refresh frequency.

Why wrong: Increasing scheduled refresh frequency controls how often the dataset is refreshed from the source, but it has no effect on how queries are executed or how quickly the in-memory model responds. A more frequent refresh merely keeps data more current; it does not reduce the cardinality of the fact table, change storage compression, or add pre-aggregated summaries. In fact, more frequent refreshes can temporarily consume CPU and memory, but they never improve the speed of individual analytical queries against the cached data.

Reduce the number of dimension tables.

Why wrong: Reducing the number of dimension tables is not a valid performance optimization because it would break the star schema relationships and lose the ability to filter or group by meaningful business attributes. In Power BI Import mode, query performance is primarily driven by the size of the fact table and the cardinality of columns, not by the count of dimension tables. Removing dimension tables would require denormalizing attributes into the fact table, which increases row width and storage, potentially degrading performance further rather than improving it.

Enable 'Load to report' for all tables.

Why wrong: Enabling 'Load to report' for all tables simply makes every table visible to report authors in the Fields pane; it does not alter how data is stored, compressed, or queried. In Import mode, all tables in the dataset are already loaded into memory regardless of whether the 'Load to report' checkbox is selected. This setting only affects the report authoring experience (e.g., preventing a table from appearing as a standalone entity), so it has zero impact on DAX query execution speed or aggregation behavior.

Unpivot Columns

Why wrong: Unpivot Columns is a table-shaping transformation that converts multiple columns into attribute-value rows, reducing a wide table into a long format. It operates at the schema level and does not parse or split the textual contents of any individual cell. Because the currency symbol and numeric amount reside within the same field, Unpivot Columns would leave that cell intact and simply move it into a Value column, making it unsuitable for separating currency from amount.

Merge Columns

Why wrong: Merge Columns performs the inverse operation: it concatenates two or more existing columns into one new column using a separator, such as a space or comma. This transformation requires multiple input columns and produces a single output column, so it cannot be used to divide the contents of one cell. In fact, merging would combine currency and amount fields into a single string, which is the opposite of the desired result, so it is not a valid approach for this scenario.

Group By

Why wrong: Group By is an aggregation operation that groups rows by one or more key columns and computes summary statistics—like sum, count, or average—for other numeric columns. It reduces the number of rows and creates a summarized table, but it has no capability to manipulate or parse text within a single cell. Grouping by the existing column would still leave the combined currency and amount value untouched, so it cannot split that value into separate meaningful fields.

Load all data to the data model without filtering.

Why wrong: Loading all data into the data model without any row or column filtering is the opposite of query optimization. Every row consumes memory, increases the size of the .pbix file, and makes refresh slower because Power Query must read, evaluate, and store every value before compression can begin. It also moves all potential transformations downstream to DAX or visuals, which is less efficient than eliminating unnecessary data in the query layer.

Increase the scheduled refresh frequency.

Why wrong: Increasing scheduled refresh frequency does not make any individual refresh run faster; it only means the existing query is executed more often. Each extra refresh still must read the source folder, run all transformations, and reload the model, which multiplies the total load on the source system and the Power BI capacity. If query performance is the problem, the fix is to reduce the amount of data or the number of transformations, not to refresh more often.

Enable 'Fast Combine' in the Power Query options.

Why wrong: Fast Combine is a privacy-level setting that, when enabled, prevents Power Query from prompting about data source privacy levels and allows it to combine data from different sources with fewer checks. It is not designed to improve query or refresh performance; the actual M code still runs the same transformations and loads the same amount of data. Moreover, turning it on can expose data to unintended sharing, so it should be changed only by an administrator after careful security review.

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

Why wrong: Whether all columns are loaded into the data model is independent of query folding. In Power Query, you can remove columns before the final load, and those column removals are incorporated into the SELECT clause of the folded SQL statement, so the query still pushes down to the source. Folding is about translating query logic, not about the breadth of columns materialized; loading only needed columns actually reduces data movement and does not hinder folding.

The query must use a DirectQuery connection.

Why wrong: Query folding occurs identically in both Import and DirectQuery storage modes. In Import mode, Power Query generates a native query that is executed against the source during refresh, and the results are cached; in DirectQuery, the model sends native queries to the source at report time, and folding applies to those queries as well. Folding is a capability of the Power Query engine's translation of M expressions, and it is not tied to a specific connectivity mode.

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

Why wrong: DirectQuery mode pushes queries to the source database at render time, so there is no import refresh to optimize; for a large fact table, every visual interaction triggers source-side aggregation, which can be slow unless the database has tuned indexes and pre-aggregated views. It also forfeits the compressed in-memory storage of Power BI, and the concepts of partition-level incremental refresh or watermark-based 'detect data changes' do not apply in DirectQuery. Thus it fails to address the goal of reducing refresh load while keeping full history available.

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

Why wrong: Applying a date filter in Power Query during Import mode does limit rows at initial load, but it is a fixed, static filter: any report interaction that needs a date outside that filter will fail because those rows were never imported. To see new dates, you must edit the query and perform a full re-import, which recreates the entire table and defeats the purpose of reducing refresh load. It also lacks the partitioning and watermarking logic of incremental refresh, so users cannot dynamically query full history without manual intervention.

Create a calculated table with only the required columns.

Why wrong: A DAX calculated table that selects only required columns is evaluated after the data model is loaded, so the entire fact table still has to be read into memory before the column pruning happens; it only reduces visible fields, not the number of rows. This provides no refresh-time savings for a large fact table, and it adds a recomputation overhead at each refresh. The row count remains unchanged, so storage and refresh time are still proportional to the full table volume.

Use Union in DAX.

Why wrong: DAX UNION is a table function that combines rows from two tables, but it operates after the data has been loaded into the model, not during the Power Query transformation stage. Using UNION forces the combination to occur in-memory during query time, which can incur performance overhead and make the data model less transparent. The recommended approach is to combine the sales tables in Power Query using Append Queries, where data is shaped and loaded once, efficiently.

Use Group By to summarize data.

Why wrong: Group By in Power Query is an aggregation operation that groups rows based on the values in one or more columns and calculates summary statistics such as totals or counts. It reduces the number of rows and removes the underlying detail, so it cannot be used to simply stack rows from two sales tables into one comprehensive dataset. Appending the two tables preserves every row and detail, which is what combining raw sales data requires.

Use Merge Queries as a new query.

Why wrong: Merge Queries performs a column-wise join, similar to a SQL JOIN, where two tables are combined by matching rows on a common key, producing a wider table with additional columns. It does not concatenate rows from different sources; instead, it enriches the left table with columns from the right table. Since the task requires stacking sales from two tables, merging would not increase the row count and would actually alter the table structure incorrectly.

Net Sales = SUM(Sales[SalesAmount]) * (1 - SUM(Sales[Discount]))

Why wrong: This expression aggregates the discount column before applying it to sales: SUM(Sales[Discount]) yields the total of all discount values, so the calculation becomes total sales times (1 - total discounts), which is mathematically nonsensical unless there is exactly one row. It also ignores row-level nulls in Discount, and because the multiplication and subtraction are done outside the row context, it does not compute net sales per transaction.

Net Sales = SUMX(Sales, Sales[SalesAmount] * (1 - Sales[Discount]))

Why wrong: Using SUMX with the raw column reference Sales[Discount] works only if Discount never contains nulls. In DAX, any arithmetic operation involving a null yields null, so a single row with a null discount will cause that row's product to be null, and SUMX will skip or return null for that row, producing an understated or incorrect total. Unlike COALESCE or IF, this expression provides no handling for missing discount values.

Net Sales = SUMX(Sales, DIVIDE(Sales[SalesAmount], 1 - Sales[Discount]))

Why wrong: DIVIDE(Sales[SalesAmount], 1 - Sales[Discount]) is algebraically incorrect for net sales: it effectively computes SalesAmount / (1 - Discount), which is a gross-up formula, not a discount formula. This would increase the result instead of reducing it. Moreover, if Discount is null, the denominator becomes null and DIVIDE returns BLANK unless you supply an alternative result, so the expression is both conceptually wrong and fragile with respect to nulls.

Convert Churn column to a numeric 0/1 column.

Why wrong: Converting Churn to numeric 0/1 is unnecessary because a text column with 'Yes'/'No' works perfectly as a slicer and in filter arguments. Numeric encoding adds no DAX advantage and can mislead users who expect categorical semantics. Keeping Churn as text also improves report readability and allows straightforward COUNTROWS('Table') measures.

Create a calculated column for AverageCharges per customer.

Why wrong: Creating a calculated column for AverageCharges per customer is wrong because that value is a measure-level aggregate, not a row-level attribute. A calculated column is evaluated per row at refresh time and ignores slicer context, so it cannot respond to filter selections. Instead, define a DAX measure using AVERAGE('Table'[MonthlyCharges]) or AVERAGEX over the relevant dimension to get dynamic averages.

Create a separate table for subscription types.

Why wrong: Creating a separate table for subscription types is over-normalization because there are only two values, and adding a dimension adds joins without meaningful benefit. A text column for subscription type is sufficient for filtering, grouping, and starring schemas; the overhead of an extra table outweighs any savings. Normalization is only worth it when the lookup table has many attributes or high variability, not for a binary categorical field.

Create aggregations on the SalesTransactions table.

Why wrong: Creating aggregations on the SalesTransactions table would precompute summary tables to accelerate report queries, but it does nothing to shorten the refresh cycle for the underlying fact table. In fact, each refresh must still load and process the full detail data, and then aggregate tables are rebuilt on top, adding extra processing time rather than reducing it. Aggregations are a query-performance technique, not a data-refresh optimization, so they are the wrong tool for this refresh-time problem.

Switch to DirectQuery mode to avoid data import.

Why wrong: Switching to DirectQuery mode eliminates the initial import and scheduled refresh, but it merely shifts the cost from refresh time to query time: every visual sends live queries to the SQL Server source, which will be extremely slow for a large retail fact table and complex DAX calculations. This also places heavy transactional load on the source database and restricts available modeling features in Power BI. Since the problem is about refresh performance, DirectQuery trades one bottleneck for another without solving the underlying issue.

Use a live connection to the SQL Server database.

Why wrong: Using a live connection to the SQL Server database (or to an Analysis Services model) similarly bypasses import, so there is no scheduled refresh; however, every report interaction is executed live against the source, meaning performance is entirely dependent on the database engine, network latency, and query concurrency. Live connections also lack support for incremental refresh policies and partition management, so you cannot apply the very technique that would solve the refresh-time problem. This approach is appropriate for lightweight reporting on small sources, not for a large volume fact table with complex queries.

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

Why wrong: Clicking "New Column" before selecting a table is problematic because the New Column command is context-sensitive—it operates on whichever table is currently active in the Fields pane. If no table is selected or the wrong table is selected, Power BI may either disable the command or add the column to an unintended or default table, corrupting your model's structure. Therefore, the table must be identified first to establish the target container for the new column, and only then can you safely initiate the column creation.

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

Why wrong: Writing the DAX formula before creating the column is impossible because the formula bar for a calculated column is not present until the column exists. The "New Column" command is what generates the column container and reveals the inline DAX editor; without it, there is no interface to type or validate the expression. This sequence also exposes a common misunderstanding: DAX for calculated columns is bound to a specific table column, so the column must be instantiated first.

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

Why wrong: Selecting the table and then writing the DAX formula before clicking "New Column" is an invalid order because the formula bar only appears after the column has been created. Even though the correct table is selected, the DAX expression cannot be entered into a non-existent column; the "New Column" action must occur before formula input. This mistake is understandable—users may think the formula can be drafted first—but in practice, the command creates the empty column and enables the editor, so it must precede the formula writing.

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

Why wrong: This is incorrect because the workspace list is generated from the tenant only after authentication; without a valid sign-in, the Power BI Desktop UI has no workspace data to display. Even if a workspace were somehow selected, the subsequent publish action would fail or require an immediate sign-in, breaking the logical dependency. Therefore, authentication must always precede workspace selection in the correct workflow.

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

Why wrong: Clicking Publish before signing in and selecting a workspace is invalid because the Publish command in Power BI Desktop requires an active session and a known destination; otherwise it prompts for sign-in in the middle of the flow. Workspace selection cannot occur before authentication because the available workspaces are loaded from the service only after login. This ordering essentially causes a broken sequence, forcing an error or a re-prompt rather than a clean publish operation.

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

Why wrong: This order is wrong because it omits the explicit workspace designation before the upload is triggered. Once signed in, the user must choose the target workspace first, after which Publish can carry out a one-step deployment; otherwise, Power BI has no specified container at the moment the publish action executes, leading to a manual or unexpected workspace prompt. Selecting the workspace after Publish introduces ambiguity and does not match the intended drag-and-drop sequence.

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

Why wrong: In Power BI Desktop, the formula bar only becomes active and bound to a new calculated table after you click the 'New Table' button on the Modeling tab. Typing a DAX expression beforehand writes to whatever object is currently selected, not to a new table definition, so the keystrokes are ignored or applied to existing context. When you subsequently click New Table, the pre-typed text is not carried over into the new table's expression; the DAX expression must be entered only after the table shell is created.

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

Why wrong: Clicking 'New Table' creates an in-progress table object with an empty expression in the formula bar, but pressing Enter immediately triggers validation of the DAX syntax before any expression has been supplied. This commits an invalid definition, producing a syntax error and leaving no editable calculated table for you to populate. The expression typed afterward occurs outside that failed commit and cannot be retroactively attached, so the DAX expression must be entered before pressing Enter to confirm the table.

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

Why wrong: This sequence is operationally impossible because the 'New Table' button is located on the Modeling tab of the Power BI ribbon. If you are not on that tab, the button is not visible or clickable, so you cannot select it before navigating to Modeling. Navigating to the Modeling tab after attempting to click New Table means the click action never reaches the intended button; once you do navigate, you still need to click New Table and then enter the DAX expression in the correct order.

CALCULATE: Adds all the numbers in a column.

Why wrong: Adding all numbers in a column is the behavior of SUM, not CALCULATE. CALCULATE's role is to alter the filter context in which an expression is evaluated—it is not an aggregation function. Any measure or expression assigned to CALCULATE must specify a scalar expression whose filters are being modified, not a numeric summation.

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

Why wrong: Returning a table of unique values from a column describes VALUES, which returns a single-column table of distinct values, not SUM. SUM aggregates numbers by addition, producing a scalar result that sums the column's values. The confusion arises because both functions operate on a column, but VALUES is a table function while SUM is a scalar aggregation.

FILTER: Evaluates an expression in a modified filter context.

Why wrong: Evaluating an expression in a modified filter context is precisely what CALCULATE does, not FILTER. FILTER constructs a row subset by testing each row of a table against a logical predicate, and its output is always a table. As an iterator, FILTER does not modify filter context; instead, it generates row context for its predicate evaluation.