Prepare the data practice questions

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?

Trap 1: Remove the top row and then split columns manually.

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.

Trap 2: Change the file encoding from UTF-8 to ANSI.

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.

Trap 3: Use 'Replace Values' to replace commas with semicolons.

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.

A
Remove the top row and then split columns manually.
Why it fails: 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.
B
Change the file encoding from UTF-8 to ANSI.
Why it fails: 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.
C
Use 'Split Column by Delimiter' and choose 'Comma' with the option to split at each occurrence.
Using 'Split Column by Delimiter' with 'Comma' and selecting 'Split at each occurrence' is the correct answer because this transformation invokes Power Query's quote-aware parser. The parser honors the standard CSV rule that commas inside quoted fields are not treated as separators, so it correctly handles rows where embedded commas create the illusion of extra columns. This approach dynamically splits each row into the actual number of logical columns, producing a clean, normalized table without manual intervention.
D
Use 'Replace Values' to replace commas with semicolons.
Why it fails: 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.

Question 2hardmulti select

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?

Trap 1: Use the 'Merge Queries' feature to join the files.

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.

Trap 2: Individually import each file and append them manually.

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.

A
Use the 'Merge Queries' feature to join the files.
Why it fails: 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.
B
Create a connection to the SharePoint folder.
Creating a connection to the SharePoint folder is the mandatory first step, as Power Query must have a data source that can enumerate all files in the SharePoint document library. This connection uses the SharePoint folder connector to access file metadata, including name, extension, and modified date, which provides the foundation for the subsequent folder query. Without this connection, there is no way to reference the remote files as a set for batch transformation. It is therefore a correct and necessary action.
C
Filter the folder query to include only the relevant Excel files.
After establishing the SharePoint folder connection, the resulting folder query lists every file in the library, so filtering ensures that only the relevant Excel files are included in the final combine step. You can filter on the 'Extension' column (e.g., .xlsx) or on 'Name' patterns to exclude non-Excel files, templates, or unrelated documents. This step prevents errors and reduces unnecessary processing by limiting the files that the 'Combine Files' transform will later ingest. It is a correct part of the ETL sequence.
D
Use the 'Combine Files' transform with the first file as a sample.
The 'Combine Files' transform is the key mechanism that automatically applies the same inferred transformation to every file in the filtered folder list. Power Query uses the first file you select as a sample to generate a structured extraction function, then runs that function against all files and appends the resulting tables into one continuous data set. This step eliminates the need to manually set up individual queries and append operations, making the process repeatable and maintainable. Therefore, using 'Combine Files' with a sample file is correct.
E
Individually import each file and append them manually.
Why it fails: 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.

Question 3hardmultiple choice

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?

Trap 1: Change Type -> Using Locale -> Date

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.

Trap 2: Split Column -> By Number of Characters

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.

Trap 3: Detect Data Type

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.

A
Parse -> Date
The Parse -> Date transformation in Power Query opens a dialog where you can specify the exact date format used in the CSV, making it possible to correctly interpret custom formats such as 'dd-MMM-yyyy' or 'yyyyMMdd'. Unlike type changes that rely on locale inference, Parse explicitly converts text into a Date value based on the format you supply, ensuring any custom format is handled correctly without misinterpretation. This is the appropriate tool when the source dates do not follow the locale's default date pattern.
B
Change Type -> Using Locale -> Date
Why it fails: 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.
C
Split Column -> By Number of Characters
Why it fails: 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.
D
Detect Data Type
Why it fails: 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.

Question 4hardmultiple choice

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?

Trap 1: Unpivot Columns

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.

Trap 2: Merge Columns

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.

Trap 3: Group By

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.

A
Unpivot Columns
Why it fails: 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.
B
Split Column by delimiter
Split Column by delimiter is the correct transformation because it parses a single text column into multiple columns based on a specified delimiter, such as a space or a custom character. In Power Query, you can choose to split at each occurrence of the delimiter or at the leftmost or rightmost delimiter, and you can even split into a specific number of columns. For a column containing combined values like 'USD 100', this operation would produce one column for the currency code and another for the numeric amount, which is exactly the required outcome.
C
Merge Columns
Why it fails: 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.
D
Group By
Why it fails: 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.

Question 5mediummulti select

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?

Trap 1: Load all data to the data model without filtering.

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.

Trap 2: Increase the scheduled refresh frequency.

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.

Trap 3: Enable 'Fast Combine' in the Power Query options.

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.

A
Load all data to the data model without filtering.
Why it fails: 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.
B
Disable the 'Promote Headers' step if the headers are not needed.
The Promote Headers step is often auto-generated when combining files, but if the source files do not actually have a header row, the step simply renames the first data row as column names and adds a transformed header row, causing extra work and the risk of misinterpreting data. Disabling the step, when headers are genuinely not needed, avoids that unnecessary scan and schema validation, letting the query keep the native column list. This cleans the M code and reduces refresh time without affecting the correctness of the result.
C
Increase the scheduled refresh frequency.
Why it fails: 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.
D
Filter the folder to include only relevant files before combining.
Filtering the folder before the combine operation, for example by setting a file name pattern or extension filter in the folder source properties, is a best practice because it stops Power Query from enumerating every file. The fewer files that are read, the fewer times the sample-file transformation must be applied, and the smaller and cleaner the combined output becomes. This is a classic example of early row reduction, which is one of the most effective performance levers in Power Query.
E
Enable 'Fast Combine' in the Power Query options.
Why it fails: 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.

Question 6hardmulti select

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?

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

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.

Trap 2: The query must use a DirectQuery connection.

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.

A
The transformations applied must be supported for query folding.
Query folding requires that every transformation in the M query can be translated into a single native SQL statement executed by the source database. Operations like filtering, joining, and simple aggregations fold, whereas steps such as adding an index column, unpivoting, or invoking custom M functions cannot be translated and force Power Query to process data locally. If even one transformation is unsupported, the chain of folding breaks, so all applied transformations must be foldable to achieve full pushdown and realize the performance benefit.
B
All columns from the table must be loaded to the data model.
Why it fails: 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.
C
The query must use a DirectQuery connection.
Why it fails: 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.
D
The query must not combine data from multiple sources.
Query folding depends on translating the entire query into a single SQL statement against one source system. If you combine data from multiple sources—for example, merging a SQL Server table with an Excel workbook or a web service—the Power Query engine must perform the merge in memory, because no single database can execute a cross-source join. That mashup step cannot be folded, so it breaks the folding chain and prevents the query from being fully pushed down to the source.
E
The source must be a relational database like SQL Server.
Query folding requires a source that can accept and execute a native query, which is typically SQL. Relational databases such as SQL Server, Oracle, and PostgreSQL support this because Power Query can generate SQL and push down operations like joins and filters. Non-relational sources, such as flat files, web APIs, or NoSQL databases, lack a standard query language that Power Query can leverage, so folding either does not occur or is limited to connector-specific pushdown.

Question 7hardmultiple choice

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?

Trap 1: Switch to DirectQuery mode and rely on the source database for…

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.

Trap 2: Use Import mode and apply a date filter in Power Query to limit…

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.

Trap 3: Create a calculated table with only the required columns.

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.

A
Switch to DirectQuery mode and rely on the source database for filtering.
Why it fails: 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.
B
Configure Incremental refresh with Detect data changes on the date column.
Configure Incremental Refresh with Detect data changes on the date column: this creates date-partitioned ranges in the Import model and uses the RangeStart/RangeEnd parameters to filter source queries at refresh time. Only new or changed partitions are refreshed, while Detect data changes applies a watermark predicate on the date column to skip rows whose date column hasn't been modified, cutting refresh time and data transfer. Full historical data remains accessible because older partitions stay intact, making this the correct approach for a large fact table.
C
Use Import mode and apply a date filter in Power Query to limit rows.
Why it fails: 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.
D
Create a calculated table with only the required columns.
Why it fails: 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.

Question 8hardmulti select

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

Trap 1: Convert Churn column to a numeric 0/1 column.

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.

Trap 2: Create a calculated column for AverageCharges per customer.

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.

Trap 3: Create a separate table for subscription types.

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.

A
Convert Churn column to a numeric 0/1 column.
Why it fails: 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.
B
Create a calculated column for AverageCharges per customer.
Why it fails: 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.
C
Remove TotalCharges column if it can be calculated from MonthlyCharges and TenureMonths.
Removing TotalCharges is correct when it is derived as MonthlyCharges multiplied by TenureMonths because storing both the source fields and a deterministic calculation wastes memory and creates risk of inconsistency. Eliminating redundant columns shrinks the data model, improves compression, and ensures that reports only rely on one source of truth. If the calculation ever changes, updating the measure or column suffices without maintaining duplicated data.
D
Set CustomerID as a unique key and mark it as the primary column.
Setting CustomerID as a unique key and marking it as the primary column ensures every row maps to exactly one customer, which is a prerequisite for building relationships to other tables. This practice establishes integrity in the data model and prevents fan-out or duplicate row expansion when using related tables. Identifying the primary key also helps Power BI use proper cardinality in relationships and improve query performance.
E
Create a separate table for subscription types.
Why it fails: 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.

Question 9hardmultiple choice

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?

Trap 1: Create aggregations on the SalesTransactions table.

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.

Trap 2: Switch to DirectQuery mode to avoid data import.

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.

Trap 3: Use a live connection to the SQL Server database.

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.

A
Create aggregations on the SalesTransactions table.
Why it fails: 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.
B
Implement Incremental refresh with a policy to keep the last 5 years of data and detect changes.
Implementing incremental refresh with a policy that keeps the last five years of data and uses change detection is the correct approach. The fact table is partitioned by date ranges, so each refresh only processes new partitions (e.g., today's data) and any partitions where the change-detection column indicates modified rows, rather than reloading the entire five-year history every time. This dramatically reduces refresh duration while preserving the required historical window, and it is the standard best practice for large fact tables in Power BI.
C
Switch to DirectQuery mode to avoid data import.
Why it fails: 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.
D
Use a live connection to the SQL Server database.
Why it fails: 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.

Question 10mediumdrag order

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

Drag or tap steps into the slots.

Steps

Order

1Step 1

2Step 2

3Step 3

4Step 4

5Step 5

Trap 1: Step 1: Click New Column

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.

Trap 2: Step 1: Write the DAX formula

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.

Trap 3: Step 1: Select the table

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.

A
Step 1: Select the table. Step 2: Click New Column. Step 3: Write the DAX formula.
The correct sequence in Power BI Desktop requires you to first select the target table in the Fields pane so the new calculated column is correctly associated with that table's data context. Next, clicking "New Column" (on the Table Tools or Modeling tab) creates a blank calculated column and opens the formula bar. Finally, you write the DAX formula, which Power BI evaluates row-by-row for every row in the selected table. This order ensures the column lands where you expect and that the expression editor is available for input.
B
Step 1: Click New Column. Step 2: Select the table. Step 3: Write the DAX formula.
Why it fails: 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.
C
Step 1: Write the DAX formula. Step 2: Select the table. Step 3: Click New Column.
Why it fails: 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.
D
Step 1: Select the table. Step 2: Write the DAX formula. Step 3: Click New Column.
Why it fails: 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.

Question 11mediumdrag order

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

Drag or tap steps into the slots.

Steps

Order

1Step 1

2Step 2

3Step 3

4Step 4

5Step 5

Trap 1: Select a workspace, then sign in to Power BI, then click Publish,…

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.

Trap 2: Click Publish, then select a workspace, then sign in to Power BI,…

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.

Trap 3: Sign in to Power BI, then click Publish, then select a workspace,…

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.

A
Sign in to Power BI, then select a workspace, then click Publish, then view the report in the Power BI service.
Signing in first establishes an authenticated session with the Power BI service, which is a mandatory prerequisite for any service-level operation. Selecting a workspace next explicitly defines the destination container where the dataset and report will be uploaded. Clicking Publish then uploads the .pbix file, and finally you view the published artifact in the service to confirm the deployment succeeded.
B
Select a workspace, then sign in to Power BI, then click Publish, then view the report.
Why it fails: 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.
C
Click Publish, then select a workspace, then sign in to Power BI, then view the report.
Why it fails: 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.
D
Sign in to Power BI, then click Publish, then select a workspace, then view the report.
Why it fails: 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.

Question 12mediumdrag order

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

Drag or tap steps into the slots.

Steps

Order

1Step 1

2Step 2

3Step 3

4Step 4

5Step 5

Trap 1: Navigate to the Modeling tab, enter DAX expression in the formula…

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.

Trap 2: Navigate to the Modeling tab, click New Table, press Enter, enter…

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.

Trap 3: Click New Table, navigate to the Modeling tab, enter DAX expression…

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.

A
Navigate to the Modeling tab, click New Table, enter DAX expression in the formula bar, press Enter.
This is the correct order because you must first access the Modeling tab to find the New Table button, then click it to open the formula bar, enter the DAX expression, and press Enter to create the table.
B
Navigate to the Modeling tab, enter DAX expression in the formula bar, click New Table, press Enter.
Why it fails: 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.
C
Navigate to the Modeling tab, click New Table, press Enter, enter DAX expression in the formula bar.
Why it fails: 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.
D
Click New Table, navigate to the Modeling tab, enter DAX expression in the formula bar, press Enter.
Why it fails: 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.

Question 13mediummatching

Match each DAX function to its description.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts

Matches

Modifies the filter context

Evaluates an expression for each row and sums the results

Returns a table that represents a subset of another table

Clears all filters from a table or column

Returns a related value from another table

Trap 1: CALCULATE: Adds all the numbers in a column.

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.

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

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.

Trap 3: FILTER: Evaluates an expression in a modified filter context.

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.

A
CALCULATE: Evaluates an expression in a modified filter context.
CALCULATE evaluates an expression within a new filter context that you dynamically define via its filter arguments. It performs context transition when moving from row context to filter context, and its filter arguments can modify, remove, or override existing filters. As a scalar function, CALCULATE is the foundational DAX tool for dynamic measures.
B
SUM: Adds all the numbers in a column.
SUM takes a single column of numeric values as input and returns the total of all non-blank values in that column. It is a basic aggregation function used primarily in measures and calculated columns, operating over direct column references rather than tables or expressions. SUM ignores logical values and text, and it cannot be used with table filters.
C
FILTER: Returns a table that represents a subset of rows from another table.
FILTER returns a table that contains only the rows from the specified table that satisfy a given Boolean expression. It is an iterator that evaluates the predicate in each row's row context, making it useful as an input to other functions like CALCULATE or for arbitrary table-level filtering. Unlike a simple filter argument, FILTER allows row-by-row logic on the entire table.
D
CALCULATE: Adds all the numbers in a column.
Why it fails: 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.
E
SUM: Returns a table with unique values from a column.
Why it fails: 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.
F
FILTER: Evaluates an expression in a modified filter context.
Why it fails: 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.

Question 14mediummatching

Match each visualization type to its typical use case.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts

Matches

Show trends over time

Show proportions of a whole

Show relationship between two variables

Compare parts of a category

Show stages in a process

Trap 1: Clustered bar chart: Show trends over time

While a clustered bar chart can place time categories on the x-axis, it is not the appropriate choice for revealing trends over time because the discrete bars interrupt the continuity of the data and fail to convey the between-point progression or slope. Unlike a line chart, which connects data points to show a clear path and rate of change, a bar chart emphasizes individual magnitudes and compares them side by side. This can make a steady upward or downward pattern harder to see, especially with many periods.

Trap 2: Line chart: Compare categories across groups

A line chart is not suited for comparing categories across groups, because it uses a continuous x-axis and implies a meaningful order and interpolation between data points. When the categories are discrete and unordered (e.g., product types or regions), connecting them with lines suggests relationships that do not exist and distorts the perception of differences. A clustered or grouped bar chart is better because it aligns bars on a common baseline, enabling accurate visual comparison of magnitudes without implying any sequence.

Trap 3: Pie chart: Show relationship between two numerical variables

A pie chart cannot show the relationship between two numerical variables because it is a univariate visual—it represents only a single measure partitioned into parts of a whole, with each slice representing a category and its contribution to a total. To examine the relationship between two numerical variables, such as sales vs. profit or age vs. income, you need a scatter plot or a scatter chart with axes for both variables, allowing you to detect correlation, clusters, and outliers. A pie chart simply has no mechanism for encoding a second numerical value per data point.

A
Clustered bar chart: Compare categories across groups
A clustered bar chart is the correct choice for comparing categories across groups because it displays multiple series side by side for each category, allowing direct visual comparison of values along a common baseline. For example, you can compare sales across multiple product lines for each region. The grouping cue helps viewers quickly see patterns and differences between the groups, making it a powerful tool for categorical data with two or more series.
B
Line chart: Show trends over time
A line chart is ideal for showing trends over time because it plots continuous data points on a horizontal time axis and connects them, making the direction, slope, and rate of change visually explicit. This format reveals patterns such as seasonality, growth, or decline, and supports multiple series for comparative trend analysis. The x-axis spacing accurately reflects time intervals, which is critical for interpreting the trend correctly.
C
Pie chart: Show proportions of a whole
A pie chart is used to show proportions of a whole, where each slice's area and arc length are proportional to the category's value as a percentage of the total. It works best when you have a single data series and a small number of categories (typically under eight), and you want to emphasize relative contribution rather than exact values. For instance, showing market share by competitor or budget allocation by department.
D
Clustered bar chart: Show trends over time
Why it fails: While a clustered bar chart can place time categories on the x-axis, it is not the appropriate choice for revealing trends over time because the discrete bars interrupt the continuity of the data and fail to convey the between-point progression or slope. Unlike a line chart, which connects data points to show a clear path and rate of change, a bar chart emphasizes individual magnitudes and compares them side by side. This can make a steady upward or downward pattern harder to see, especially with many periods.
E
Line chart: Compare categories across groups
Why it fails: A line chart is not suited for comparing categories across groups, because it uses a continuous x-axis and implies a meaningful order and interpolation between data points. When the categories are discrete and unordered (e.g., product types or regions), connecting them with lines suggests relationships that do not exist and distorts the perception of differences. A clustered or grouped bar chart is better because it aligns bars on a common baseline, enabling accurate visual comparison of magnitudes without implying any sequence.
F
Pie chart: Show relationship between two numerical variables
Why it fails: A pie chart cannot show the relationship between two numerical variables because it is a univariate visual—it represents only a single measure partitioned into parts of a whole, with each slice representing a category and its contribution to a total. To examine the relationship between two numerical variables, such as sales vs. profit or age vs. income, you need a scatter plot or a scatter chart with axes for both variables, allowing you to detect correlation, clusters, and outliers. A pie chart simply has no mechanism for encoding a second numerical value per data point.

Question 15mediummatching

Match each Power BI concept to its definition.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts

Matches

Fact and dimension tables in a denormalized structure

Uniqueness of values in a column

Link between tables based on common columns

Dynamic calculation using DAX

Static column added to a table using DAX

Trap 1: Star Schema: A normalized schema where dimension tables are split…

This statement incorrectly defines a star schema as a normalized structure where dimension tables are split into sub-dimensions. In reality, that description perfectly matches a snowflake schema, where dimensions are decomposed into multiple related tables to reduce redundancy. A star schema, by contrast, keeps each dimension table denormalized and directly joined to the fact table, avoiding extra layers of intermediate tables.

Trap 2: Snowflake Schema: A denormalized schema where fact table connects…

This statement mistakenly describes a snowflake schema as a denormalized structure with fact tables connecting directly to dimension tables. That architecture is actually a star schema, which is intentionally denormalized so that every dimension sits one join away from the fact table. A snowflake schema is the opposite: it normalizes dimensions into multiple related tables, creating longer join chains between the fact and the lowest-level dimension tables.

A
Star Schema: A data model structure where fact tables are connected to dimension tables in a star-like pattern.
A star schema is a denormalized relational design where one or more fact tables sit at the center and connect directly to surrounding dimension tables via one-to-many relationships. The fact table stores additive numeric measures and foreign keys, while dimension tables hold descriptive attributes that are not split into additional tables. This arrangement minimizes the number of joins required, simplifies user queries, and is the recommended modeling foundation for Power BI imports and composite models.
B
Snowflake Schema: A normalized form of star schema where dimension tables are further split into related tables.
A snowflake schema takes the star schema a step further by normalizing dimension tables into separate related tables, for example splitting a Product dimension into Product, Category, and Subcategory. This eliminates data redundancy and can reduce storage in some relational engines, but it also increases the number of join paths and can degrade query performance when used in Power BI. In Power BI, snowflaking often happens automatically when importing a normalized source, yet analysts frequently collapse these tables into a single star dimension to improve usability and performance.
C
Calculated Column: A column added to a table using DAX that is evaluated row by row and stored in memory.
A calculated column is a DAX expression that is evaluated row-by-row in the current row context and then physically stored in the data model's in-memory columnar storage. Because values are materialized at refresh time, calculated columns consume RAM and increase import duration, making them best reserved for static row-level classifications or lookup attributes rather than for measures. Unlike measures, calculated columns do not respond to slicer-driven filter context, so they cannot adapt dynamically to user selections.
D
Measure: A DAX formula used for calculations that aggregates data dynamically based on filter context.
A measure is a DAX expression that is evaluated dynamically at query time in response to the current filter context, meaning it automatically recalculates as users slice, dice, or filter the report. Unlike calculated columns, measures are not stored in memory; they are computed on the fly by the formula and storage engines, which makes them both memory-efficient and the preferred way to aggregate numeric data. Measures can be implicit (auto-created aggregations) or explicit (handwritten DAX), but only explicit measures give the author full control over context transitions and time intelligence.
E
Star Schema: A normalized schema where dimension tables are split into sub-dimensions.
Why it fails: This statement incorrectly defines a star schema as a normalized structure where dimension tables are split into sub-dimensions. In reality, that description perfectly matches a snowflake schema, where dimensions are decomposed into multiple related tables to reduce redundancy. A star schema, by contrast, keeps each dimension table denormalized and directly joined to the fact table, avoiding extra layers of intermediate tables.
F
Snowflake Schema: A denormalized schema where fact table connects directly to dimension tables.
Why it fails: This statement mistakenly describes a snowflake schema as a denormalized structure with fact tables connecting directly to dimension tables. That architecture is actually a star schema, which is intentionally denormalized so that every dimension sits one join away from the fact table. A snowflake schema is the opposite: it normalizes dimensions into multiple related tables, creating longer join chains between the fact and the lowest-level dimension tables.

Question 16mediummultiple choice

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?

Trap 1: Use Import mode and create aggregations

Import mode with aggregations still requires the full initial data load to populate the model before any aggregation can be built or queried, so the 10M-row refresh latency remains unchanged. Aggregations only accelerate post-load query performance by pre-summarizing data, not the load/refresh operation itself. Thus, this option fails the requirement of avoiding the slow initial import.

Trap 2: Use Dual mode

Dual mode combines Import and DirectQuery: it caches a copy of the data for use in local processing, which means the entire 10M-row dataset is still imported initially to populate that cache. The DirectQuery portion only applies to tables marked as DirectQuery, not the dual-mode table itself, so the initial refresh delay persists. This option does not solve the stated problem because the cache load is exactly the slow operation the user wants to avoid.

Trap 3: Use Import mode with Incremental refresh

Incremental refresh in Import mode still performs the initial full load of all 10M rows on the first refresh before any incremental policy can split subsequent refreshes into partitions. The first load is full by design, so the initial latency remains as long as importing the whole table. Incremental refresh only reduces the time for later refreshes, not the very first one, making it ineffective for this requirement.

A
Use Import mode and create aggregations
Why it fails: Import mode with aggregations still requires the full initial data load to populate the model before any aggregation can be built or queried, so the 10M-row refresh latency remains unchanged. Aggregations only accelerate post-load query performance by pre-summarizing data, not the load/refresh operation itself. Thus, this option fails the requirement of avoiding the slow initial import.
B
Use DirectQuery mode
DirectQuery mode bypasses a physical import entirely: Power BI sends native queries to the Azure SQL Database at report interaction time, so there is no initial data-load step to wait through. This aligns with the requirement because the report opens immediately, and the engine relies on the source's indexes and query performance. The trade-off is that every visual can trigger SQL queries, but the initial load problem is completely eliminated.
C
Use Dual mode
Why it fails: Dual mode combines Import and DirectQuery: it caches a copy of the data for use in local processing, which means the entire 10M-row dataset is still imported initially to populate that cache. The DirectQuery portion only applies to tables marked as DirectQuery, not the dual-mode table itself, so the initial refresh delay persists. This option does not solve the stated problem because the cache load is exactly the slow operation the user wants to avoid.
D
Use Import mode with Incremental refresh
Why it fails: Incremental refresh in Import mode still performs the initial full load of all 10M rows on the first refresh before any incremental policy can split subsequent refreshes into partitions. The first load is full by design, so the initial latency remains as long as importing the whole table. Incremental refresh only reduces the time for later refreshes, not the very first one, making it ineffective for this requirement.

Question 17easymultiple choice

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?

Trap 1: Enable Query Folding in Power Query

Query folding is a Power Query optimization that pushes transformation steps back to the source database as native SQL. In DirectQuery mode, however, Power Query transformations are already translated and executed in the source for every query, so there is no local data cache or in-memory processing where folding could reduce workload. Enabling or disabling folding has no effect on the number or cost of queries sent to Snowflake because DirectQuery necessarily transmits every query to the source.

Trap 2: Change the dataset to Import mode

Switching the dataset to Import mode would copy all data into Power BI's in-memory columnar store, requiring a full data refresh and abandoning DirectQuery's live, source-connected architecture. While Import mode can improve query performance, it does not optimize a DirectQuery dataset; it replaces the architecture entirely and introduces data latency, storage overhead, and refresh dependencies. Therefore, this change does not address the requirement of reducing query volume while preserving DirectQuery behavior.

Trap 3: Increase the number of concurrent queries in Power BI Service

Increasing the concurrent query limit in Power BI Service controls how many queries can run in parallel, not how quickly any individual query runs. In a DirectQuery scenario, raising concurrency would send more simultaneous queries to Snowflake, potentially increasing load and compounding performance bottlenecks rather than reducing query volume or latency. The performance issue is per-query efficiency against the source, not throughput capacity at the service tier.

A
Enable Query Folding in Power Query
Why it fails: Query folding is a Power Query optimization that pushes transformation steps back to the source database as native SQL. In DirectQuery mode, however, Power Query transformations are already translated and executed in the source for every query, so there is no local data cache or in-memory processing where folding could reduce workload. Enabling or disabling folding has no effect on the number or cost of queries sent to Snowflake because DirectQuery necessarily transmits every query to the source.
B
Change the dataset to Import mode
Why it fails: Switching the dataset to Import mode would copy all data into Power BI's in-memory columnar store, requiring a full data refresh and abandoning DirectQuery's live, source-connected architecture. While Import mode can improve query performance, it does not optimize a DirectQuery dataset; it replaces the architecture entirely and introduces data latency, storage overhead, and refresh dependencies. Therefore, this change does not address the requirement of reducing query volume while preserving DirectQuery behavior.
C
Increase the number of concurrent queries in Power BI Service
Why it fails: Increasing the concurrent query limit in Power BI Service controls how many queries can run in parallel, not how quickly any individual query runs. In a DirectQuery scenario, raising concurrency would send more simultaneous queries to Snowflake, potentially increasing load and compounding performance bottlenecks rather than reducing query volume or latency. The performance issue is per-query efficiency against the source, not throughput capacity at the service tier.
D
Create aggregations in the Power BI model
Creating aggregations in the Power BI model lets you pre-cache summary tables at a coarser grain, often by importing them into the model or pointing to pre-aggregated objects in the source. When a visual requests high-level totals, Power BI recognizes the aggregation and routes the query to the smaller cached summary rather than the full Snowflake fact table, drastically reducing the volume and cost of queries sent to the source. This is a standard optimization pattern for DirectQuery models, especially when fine-grained detail is rarely needed for every visual.

Question 18hardmultiple choice

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?

Trap 1: Use Table.Combine with the 'Table1' parameter

Table.Combine with the 'Table1' parameter is syntactically incorrect because Table.Combine expects a list of tables as its first argument, not a single table reference. Even if you passed a valid list, Table.Combine appends by matching column names but includes all columns from all tables, including non-common ones, so it would not restrict the result to columns shared by every file.

Trap 2: Use Table.ColumnsOfType to filter common columns

Table.ColumnsOfType is a table function that returns columns matching a specific data type (e.g., text, number) from a single table, not columns that are common across multiple Excel files. Even if applied to each file, it filters by data type rather than by column name, so it cannot ensure that the same set of columns is retained across all tables before appending.

Trap 3: Use Merge Queries as New

Merge Queries as New performs a horizontal join between two tables based on a matching key column, similar to a SQL JOIN, producing a combined table with columns from both sides. Appending rows from multiple Excel files requires stacking them vertically, which is done with Append Queries, not Merge; using Merge would require a key relationship that likely does not exist and would change the shape of the data.

A
Use Table.Combine with the 'Table1' parameter
Why it fails: Table.Combine with the 'Table1' parameter is syntactically incorrect because Table.Combine expects a list of tables as its first argument, not a single table reference. Even if you passed a valid list, Table.Combine appends by matching column names but includes all columns from all tables, including non-common ones, so it would not restrict the result to columns shared by every file.
B
Use Remove Other Columns on the first file's columns
Use Remove Other Columns on the first file's columns is the correct way to standardize the schema before appending. This action keeps only the column headers present in the first file and discards all others across every table, ensuring that all tables have the same column set. This is essential because append operations require matching column names; otherwise, data can become misaligned or filled with nulls.
C
Use Table.ColumnsOfType to filter common columns
Why it fails: Table.ColumnsOfType is a table function that returns columns matching a specific data type (e.g., text, number) from a single table, not columns that are common across multiple Excel files. Even if applied to each file, it filters by data type rather than by column name, so it cannot ensure that the same set of columns is retained across all tables before appending.
D
Use Merge Queries as New
Why it fails: Merge Queries as New performs a horizontal join between two tables based on a matching key column, similar to a SQL JOIN, producing a combined table with columns from both sides. Appending rows from multiple Excel files requires stacking them vertically, which is done with Append Queries, not Merge; using Merge would require a key relationship that likely does not exist and would change the shape of the data.

Question 19easymultiple choice

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?

Trap 1: Split the column into year, month, day and then combine

Splitting the column into year, month, and day then recombining adds extra transformation steps (e.g., Table.SplitColumn and Table.CombineColumns) that still depend on the original delimiter and the order of components. If the CSV's date format changes—say from YYYY-MM-DD to DD/MM/YYYY—the split will assign the wrong values to month and day, and the subsequent recombine will produce an incorrect or invalid date. This approach does not eliminate format ambiguity; it only moves the problem to an earlier parsing stage and still leaves you needing an explicit type change to create a true Date column.

Trap 2: Use the 'Using Locale' transformation

The 'Using Locale' transformation (which corresponds to Date.FromText(text, Culture) in M) only tells Power Query which regional format to assume when parsing the string; it does not guarantee that the parsed values are stored as a Date type, nor does it protect against a source file that switches formats mid-stream. Locale-based parsing still relies on the supplied culture being correct for every row—if the CSV contains a date that contradicts that culture, the conversion will fail or misinterpret the day and month. It also requires selecting a specific locale manually, which introduces maintenance overhead and a risk of mismatch whenever the data publisher changes the export format.

Trap 3: Change the regional settings of the Power Query editor

Changing the Power Query editor's regional settings (via File > Options > Regional Settings or the model locale) only alters the default culture used for all date interpretations in the session, which can unintentionally change how every text date is parsed and might mask the real format of the source file. It does not apply a type conversion to the column—the result often remains Text unless you subsequently use 'Change Type'—and it makes the query behave differently on different machines depending on their locale. This is not a robust solution for format changes because it is a global environment tweak, not a per-column parsing rule, and it can introduce inconsistency in a shared or published report.

A
Split the column into year, month, day and then combine
Why it fails: Splitting the column into year, month, and day then recombining adds extra transformation steps (e.g., Table.SplitColumn and Table.CombineColumns) that still depend on the original delimiter and the order of components. If the CSV's date format changes—say from YYYY-MM-DD to DD/MM/YYYY—the split will assign the wrong values to month and day, and the subsequent recombine will produce an incorrect or invalid date. This approach does not eliminate format ambiguity; it only moves the problem to an earlier parsing stage and still leaves you needing an explicit type change to create a true Date column.
B
Use the 'Using Locale' transformation
Why it fails: The 'Using Locale' transformation (which corresponds to Date.FromText(text, Culture) in M) only tells Power Query which regional format to assume when parsing the string; it does not guarantee that the parsed values are stored as a Date type, nor does it protect against a source file that switches formats mid-stream. Locale-based parsing still relies on the supplied culture being correct for every row—if the CSV contains a date that contradicts that culture, the conversion will fail or misinterpret the day and month. It also requires selecting a specific locale manually, which introduces maintenance overhead and a risk of mismatch whenever the data publisher changes the export format.
C
Set the data type to Date using the 'Change Type' step in Power Query
Setting the data type to Date via the 'Change Type' step (which generates M code Table.TransformColumnTypes with type date) is the direct and expected way to make a CSV text column a true Date column in Power Query. Power Query will attempt to parse the underlying string values as dates using the session's locale, and the column is then surfaced as Date to the data model, enabling date hierarchy, time intelligence, and correct filtering. This is the standard practice for importing date columns from CSV files because it explicitly assigns the target data type rather than relying on parsing heuristics alone.
D
Change the regional settings of the Power Query editor
Why it fails: Changing the Power Query editor's regional settings (via File > Options > Regional Settings or the model locale) only alters the default culture used for all date interpretations in the session, which can unintentionally change how every text date is parsed and might mask the real format of the source file. It does not apply a type conversion to the column—the result often remains Text unless you subsequently use 'Change Type'—and it makes the query behave differently on different machines depending on their locale. This is not a robust solution for format changes because it is a global environment tweak, not a per-column parsing rule, and it can introduce inconsistency in a shared or published report.

Question 20mediummulti select

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?

Trap 1: Extract - Year

Extracts year, not time.

Trap 2: Merge Columns

Combines columns, not extract time.

Trap 3: Format - Trim

Trims spaces, does not extract time.

A
Extract - Duration
Extracts time as duration from midnight.
B
Extract - Year
Why it fails: Extracts year, not time.
C
Merge Columns
Why it fails: Combines columns, not extract time.
D
Format - Trim
Why it fails: Trims spaces, does not extract time.
E
Split Column by Delimiter (space)
Splits date and time into two columns.