CCNA Prepare the data Questions — Page 2 of 3

MCQeasy

You are importing data from a CSV file that contains a column 'OrderDate' with dates in the format 'MM/dd/yyyy'. Some rows have invalid dates like '02/30/2023'. What is the best way to handle these errors in Power Query?

A.Use 'Replace Errors' to replace error values with null.

B.Remove rows with errors using 'Remove Rows' > 'Remove Errors'.

C.Change the data type to 'Date' and ignore errors.

D.Filter the column to exclude rows where the date is invalid after type conversion.

AnswerA

Replacing errors with null in Power Query is a non-destructive transformation that explicitly substitutes each invalid date value with a null while keeping the row intact. You apply it to the date column (Home > Replace Errors or context menu) and specify null as the replacement value. This preserves all other column values for that row and produces a clean, nullable date column that the data model handles naturally, e.g., through blanks in visuals or DAX functions like CALCULATE with filters. It also avoids the risk of load failure due to leftover errors.

Why this answer

'Replace Errors' in Power Query allows you to replace error values (which occur when Power Query fails to convert an invalid date like '02/30/2023' to the Date type) with null. This preserves the rest of the data and keeps the query running without interruption, while clearly marking invalid entries for later handling or analysis.

Exam trap

The trap here is that candidates often choose 'Remove Errors' (Option B) thinking it cleans the data, but they overlook that it deletes entire rows, which may discard valid data in other columns — a common mistake in data preparation scenarios.

How to eliminate wrong answers

Option B is wrong because 'Remove Errors' deletes entire rows containing any error, which can lead to data loss if other columns in those rows contain valid data. Option C is wrong because 'Change data type to Date and ignore errors' is not a valid Power Query operation; ignoring errors during type conversion still results in errors in the column, and there is no built-in 'ignore errors' toggle. Option D is wrong because filtering to exclude rows with invalid dates after type conversion requires the errors to be present first, and filtering on error values is not straightforward; it is more efficient to replace errors with null and then filter if needed.

Practice this question →

MCQmedium

You are reviewing a Power Query that imports data from SQL Server. The exhibit shows the M code. The SQL query filters records after a date, then Power Query filters rows with OrderQty > 10, and then groups by ProductID. What is a potential performance issue with this approach?

A.The query will fail because the SQL query uses '>' with a string.

B.The SQL query should use a parameter for the date instead of a hardcoded value.

C.The filter on OrderQty > 10 should be included in the SQL query to reduce the amount of data transferred.

D.The grouping should be done in SQL to reduce data volume.

AnswerC

Pushing the OrderQty > 10 predicate into the SQL query lets SQL Server filter rows before transfer, so Power Query receives fewer records. This reduces data shuffled across the connection, satisfying the performance constraint. Filtering after import forces the full dataset through the gateway, wasting bandwidth and memory.

Why this answer

Pushing the `OrderQty > 10` filter into the SQL query reduces the amount of data transferred from SQL Server to Power Query. In Power Query, data is loaded into memory before transformations; filtering earlier in the source query minimizes memory usage and network latency, which is a key performance optimization in Power BI data loading.

Exam trap

The trap here is that candidates focus on the date filter or grouping as the main performance issue, but the most impactful optimization is moving the row-level filter (`OrderQty > 10`) into the SQL query to reduce data transfer, which is a classic 'query folding' concept in Power Query.

How to eliminate wrong answers

Option A is wrong because the SQL query uses `'>'` with a string, but SQL Server implicitly converts the string to a date for comparison, so the query will not fail. Option B is wrong while using a parameter is a best practice for maintainability, it does not directly address the performance issue of data volume; the question asks about a potential performance issue, not code quality. Option D is wrong because grouping in SQL could reduce data volume, but the primary performance bottleneck here is the row filter on `OrderQty > 10` being applied after data transfer; grouping after filtering is less impactful than filtering earlier.

Practice this question →

MCQeasy

You are importing a large dataset from a CSV file using Power Query. The file contains 50 columns, but you only need 10 for your report. What is the most efficient way to reduce the amount of data loaded into the model?

A.Remove the unnecessary columns in Power Query before loading.

B.Load all columns and then hide the unnecessary ones in the report.

C.Use a SQL query to select only the needed columns if the data source supports it.

D.Create a DAX calculated table that selects only the needed columns.

AnswerA

Removing unnecessary columns in Power Query before loading is the most efficient method because it reduces the number of columns imported into the VertiPaq in-memory engine. Power Query transformations that discard columns are applied during the read/load cycle, so the CSV parser only passes the selected columns to the model, reducing memory usage, disk footprint, and refresh time. This early reduction also improves compression ratios and query performance across reports that reference the table.

Why this answer

Power Query processes data before it enters the Power BI model. Removing unnecessary columns at the query stage reduces the amount of data loaded into memory, improving performance and reducing storage. This is the most efficient approach as it minimizes the dataset size from the start, unlike post-load methods that still consume resources.

Exam trap

The trap here is that candidates often confuse 'hiding' columns with actually removing them, or incorrectly assume SQL-like filtering can be applied to flat files, leading them to choose options that still load unnecessary data into memory.

How to eliminate wrong answers

Option B is wrong because loading all 50 columns and hiding them still stores the full dataset in the model, wasting memory and slowing refresh times. Option C is wrong because the question specifies a CSV file, which does not support SQL queries; this option only applies to database sources like SQL Server. Option D is wrong because creating a DAX calculated table still loads the full dataset into the model first, then creates a subset in memory, which is less efficient than filtering at the import stage.

Practice this question →

MCQmedium

You are preparing data for a Power BI report. The source data contains a column 'FullName' with values like 'John Doe'. You need to split this column into 'FirstName' and 'LastName' using Power Query. The transformation should be repeatable and not dependent on the number of spaces. What is the best approach?

A.Use 'Split Column by Number of Characters' with a fixed position.

B.Use 'Split Column by Delimiter' and choose 'Right-most delimiter'.

C.Use 'Replace Values' to replace space with a comma.

D.Use 'Extract Text After Delimiter' with a space.

AnswerB

Choosing 'Split Column by Delimiter' with the 'Right-most delimiter' option is correct because it targets the final space in the name, separating the last name from all preceding text. This works reliably for variable-length strings because it does not depend on character counts or the number of spaces; even 'Mary Ann Jones' splits into 'Mary Ann' and 'Jones' as two columns. Power Query executes this by treating the last delimiter occurrence as the split point, which is exactly what is needed to isolate the surname.

Why this answer

Using 'Split Column by Delimiter' with 'Right-most delimiter' ensures that the split occurs at the last space in the FullName column, which reliably separates the first name from the last name even if there are multiple spaces (e.g., 'John Michael Doe' would yield 'John Michael' as FirstName and 'Doe' as LastName). This approach is repeatable and does not depend on a fixed number of spaces, making it robust for varying name formats.

Exam trap

The trap here is that candidates often choose 'Split Column by Delimiter' with the default 'Left-most delimiter' (which splits at the first space) or 'Extract Text After Delimiter', not realizing that names with multiple spaces require the right-most delimiter to correctly separate the last name from the rest.

How to eliminate wrong answers

Option A is wrong because 'Split Column by Number of Characters' with a fixed position assumes all names have the same character length for first and last names, which is not true for variable-length names like 'John Doe' vs. 'Alexander Hamilton'. Option C is wrong because 'Replace Values' to replace space with a comma does not split the column; it only changes the delimiter, requiring an additional split step and still not handling multiple spaces correctly. Option D is wrong because 'Extract Text After Delimiter' with a space extracts only the text after the first space, which would give 'Doe' for 'John Doe' but fail for names with middle names or multiple spaces, and it does not create both FirstName and LastName columns in one step.

Practice this question →

Multi-Selectmedium

Which TWO of the following are valid data source types in Power BI that support DirectQuery? (Select TWO.)

Select 2 answers

A.Snowflake

B.Excel workbook

C.Azure Synapse Analytics

D.SharePoint Online List

E.JSON file

AnswersA, C

Snowflake supports DirectQuery.

Why this answer

Snowflake is a cloud-based data warehouse that supports DirectQuery in Power BI, allowing queries to be sent directly to Snowflake without importing data into Power BI's in-memory engine. This is possible because Snowflake provides a SQL-based interface that Power BI can connect to via its native connector, enabling real-time querying of large datasets.

Exam trap

The trap here is that candidates often confuse file-based or list-based data sources (like Excel, JSON, or SharePoint) as being DirectQuery-capable because they can be connected to Power BI, but DirectQuery is strictly limited to relational databases and data warehouses that support live query execution.

Practice this question →

MCQeasy

You are preparing data in Power BI Desktop. You have a table with a column 'CustomerID' that contains duplicate values. You need to create a relationship to another table that also has 'CustomerID'. However, the relationship requires unique values in at least one of the tables. What should you do?

A.Create a composite key using multiple columns.

B.Use the 'Merge Queries' feature to combine the tables into one.

C.Change the data type of 'CustomerID' to text.

D.Remove duplicate rows from the 'CustomerID' column in the dimension table.

AnswerD

Removing duplicate rows from the CustomerID column in the dimension table guarantees that each customer appears exactly once, which satisfies the uniqueness requirement for the 'one' side of a one-to-many relationship. In Power Query, you can select the CustomerID column and click 'Remove Duplicates' to keep the first occurrence and delete the rest. This creates a clean dimension that can be joined correctly to the fact table via a relationship.

Why this answer

To create a relationship in Power BI, at least one table must have unique values in the column used for the relationship. By removing duplicate rows from the 'CustomerID' column in the dimension table (which should contain unique identifiers), you ensure that the dimension table has unique values, allowing a one-to-many relationship to be established with the fact table. This is a standard data modeling practice in Power BI to enforce referential integrity.

Exam trap

The trap here is that candidates often think changing data types or merging tables will solve the uniqueness issue, but Power BI specifically requires unique values in at least one table for a relationship, and only removing duplicates directly addresses that requirement.

How to eliminate wrong answers

Option A is wrong because creating a composite key does not address the requirement of having unique values in at least one table for the relationship; it only combines multiple columns to form a unique identifier, but the underlying duplicate values in 'CustomerID' would still prevent a direct relationship on that column. Option B is wrong because merging queries combines tables into a single table, which eliminates the need for a relationship but is not the correct approach when you need to maintain separate tables and create a relationship between them; it also does not resolve the uniqueness requirement for the relationship. Option C is wrong because changing the data type of 'CustomerID' to text does not remove duplicate values; it only changes the data format, and duplicates would still exist, preventing the relationship from being created.

Practice this question →

MCQmedium

You are a data analyst at an online retailer. You import a CSV file containing product reviews into Power BI Desktop. The file has a column named ReviewDate that currently loads as the Text data type, with values formatted like '2024-07-15T09:30:00Z'. You need to change this column to the Date/Time/Timezone data type, but when you select that type in Power Query, the transformation fails for many rows. You need to resolve the failure while preserving the original timestamp data. What should you do?

A.Add a custom column that uses DateTime.FromText on the original values and then delete the original column.

B.Replace the trailing 'Z' with '+00:00' in the column, then change the data type to Date/Time/Timezone.

C.Change the column to the Date data type instead, which discards the time and timezone portions and avoids the error.

D.Use the Locale option in the Change Type dialog and select English (United States) before applying the Date/Time/Timezone type.

AnswerB

Power Query's Date/Time/Timezone type expects an offset such as +00:00 rather than a Z suffix. Replacing Z with +00:00 preserves the UTC offset and allows the type conversion to succeed. This keeps the original instant intact while satisfying the parser's expected format.

Why this answer

The Date/Time/Timezone type in Power Query requires an explicit numeric offset such as +00:00, not the ISO 8601 Z shorthand. Replacing Z with +00:00 preserves the UTC instant and lets the conversion succeed. The other approaches either misinterpret the format, discard required time data, or produce a timezone-less result.

Exam trap

The trap here is assuming that any ISO 8601 timestamp string will convert directly to Date/Time/Timezone, when Power Query actually requires a numeric UTC offset rather than a trailing Z.

Practice this question →

MCQmedium

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

A.Inner

B.Left Outer

C.Right Outer

D.Full Outer

AnswerB

Left Outer join (Join Kind = Left Outer) keeps every row from the first query—orders—as the left table, and appends columns from customers only when the join key (CustomerID) matches. For orders that lack a matching customer record, the added customer name column is null, but the order row remains intact. This is the correct choice because the business need is an order-centric view where all orders must appear regardless of whether customer reference data exists.

Why this answer

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

Exam trap

The trap here is that candidates often confuse Left Outer with Inner join, thinking they must discard non-matching rows to avoid nulls, but the requirement explicitly says to add CustomerName to the Orders query, which implies preserving all orders even if a customer record is missing.

How to eliminate wrong answers

Option A is wrong because an Inner join would only return orders that have a matching customer, discarding any orders with missing or unmatched CustomerID values, which does not satisfy the requirement to add CustomerName to all orders. Option C is wrong because a Right Outer join would return all rows from the Customers table, which is not the target table; it would keep all customers even if they have no orders, and orders without a matching customer would be lost. Option D is wrong because a Full Outer join returns all rows from both tables, creating nulls on both sides for non-matches, which is unnecessary and would introduce extra rows for customers with no orders, bloating the result.

Practice this question →

MCQeasy

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

A.Import each CSV file separately and then append them

B.Use the 'From Folder' data source and then click 'Combine & Transform Data'

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

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

AnswerB

The 'From Folder' data source with 'Combine & Transform Data' is the native, dynamic solution. It prompts Power Query to generate a sample-file query, a transformation function, and a final combined output that automatically applies the same steps to every CSV in the folder. On refresh, newly added or removed files are reflected without manual intervention, and schema changes are detected and promoted automatically.

Why this answer

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

Exam trap

The trap here is that candidates may think manual appending (Option A) is simpler or that Python scripting (Option C) is a valid Power Query feature, but the exam tests knowledge of Power Query's native 'Combine Files' functionality as the most efficient and integrated method.

How to eliminate wrong answers

Option A is wrong because importing each CSV file separately and then appending them is inefficient and error-prone, requiring manual steps for each file and breaking the automated refresh capability. Option C is wrong because writing a Python script in Power Query is not natively supported; Power Query uses M language, and Python integration requires additional configuration (e.g., Python in Power BI Desktop) and is not the most efficient or standard approach for this task. Option D is wrong because using a dataflow is an overkill for a simple folder import; dataflows are designed for complex ETL processes and cloud-based transformations, not for directly loading local CSV files into a single table in Power BI Desktop.

Practice this question →

MCQeasy

You have a dataset with a column 'FullName' containing values like 'John Doe'. You need to split this column into 'FirstName' and 'LastName' using the space delimiter. Which Power Query transformation should you use?

A.Split Column by Delimiter.

B.Merge Columns.

C.Extract Text.

D.Replace Values.

AnswerA

Split Column by Delimiter directly satisfies the requirement to separate 'FullName' into 'FirstName' and 'LastName' at the space character. Power Query's split transformation supports a space delimiter with options for leftmost, rightmost, or each occurrence, producing the two new columns in a single step.

Why this answer

The 'Split Column by Delimiter' transformation in Power Query is specifically designed to divide a single text column into multiple columns based on a specified delimiter, such as a space. In this scenario, selecting the column 'FullName' and using 'Split Column > By Delimiter' with a space delimiter will correctly separate 'John Doe' into 'FirstName' (John) and 'LastName' (Doe). This is the standard approach for parsing delimited text within Power Query.

Exam trap

The trap here is that candidates may confuse 'Extract Text' with splitting, thinking it can parse delimiters, but 'Extract Text' only extracts fixed-length or positional substrings, not delimiter-based splits.

How to eliminate wrong answers

Option B is wrong because 'Merge Columns' is used to combine multiple columns into one, not to split a single column. Option C is wrong because 'Extract Text' allows you to pull out substrings based on position or length (e.g., first N characters), but it cannot dynamically split on a delimiter like a space. Option D is wrong because 'Replace Values' is designed to substitute one text value with another, not to separate a column into multiple parts.

Practice this question →

MCQeasy

You are combining data from multiple Excel files stored in SharePoint Online. Each file has the same structure but different data. You need to create a solution that automatically includes new files added to the SharePoint folder without manual intervention. What should you use?

A.Use Power Automate to copy new files to a blob storage, then import from there.

B.Use 'Merge queries' to append each new file manually.

C.Use 'Get Data from SharePoint Online Folder' and then 'Combine files' transform.

D.Use 'Get Data from SharePoint Online List' and load each file separately.

AnswerC

The SharePoint Online Folder connector retrieves metadata and binary content for all files in a document library, and the 'Combine files' transform then generates a parameterized function that uses a sample file to infer parsing logic. During each data refresh, the folder query re-enumerates the library, so any newly added Excel file is automatically detected, read, and combined with the rest using that same function. This is the intended, scalable pattern for loading multiple similarly structured Excel files from SharePoint without manual query updates.

Why this answer

The correct option is C: use 'Get Data from SharePoint Online Folder' and then the 'Combine files' transform. In Power Query, connecting to the SharePoint Online folder connector retrieves all files in that folder, and the 'Combine files' transform automatically applies the same parsing/template to every file, so newly added files with the same structure are included on refresh without manual work. Option A adds unnecessary copying to blob storage and still requires orchestration, while B is manual and defeats the automation requirement.

Option D targets a SharePoint list rather than the folder's files and loads them separately, which does not automatically combine new files.

Practice this question →

MCQhard

You are a Power BI data analyst for a subscription software company. You import a table named Subscriptions from an OData feed. The table contains a column named BillingPeriod that stores values such as 'Monthly', 'Annual', and 'Quarterly'. A report author needs a numeric column that converts each value to the number of months in the billing period (1, 12, and 3 respectively) so that revenue can be normalized. You must add this column in Power Query without changing the source system. What should you do?

A.Use the Column From Examples feature, entering '1' next to 'Monthly' and letting Power BI infer the remaining mappings.

B.Add a conditional column that maps each text value to its corresponding number of months, then change the new column's data type to Whole Number.

C.Create a DAX calculated column in the semantic model that uses SWITCH to translate each billing period into months.

D.Change the data type of the BillingPeriod column to Whole Number and rely on Power Query's automatic value conversion.

AnswerB

A conditional column built on the BillingPeriod values directly maps each known string to the required integer. After setting the new column's type to Whole Number, the model receives a numeric field suitable for calculations such as dividing annual revenue by twelve. This satisfies the requirement entirely inside Power Query without altering the OData source.

Why this answer

A conditional column in Power Query explicitly maps each billing period label to its numeric month count, which is deterministic and easy to audit. Setting the new column to Whole Number makes it usable for arithmetic in the model. The other options either rely on fragile inference, fail outright on non-numeric text, or place the logic in the wrong layer.

Exam trap

The trap here is reaching for Column From Examples because it feels faster, when a small, fixed set of labels is more reliably handled by an explicit conditional column.

Practice this question →

Multi-Selecteasy

Which THREE are types of Power Query transforms that can be used to clean data? (Choose three.)

Select 3 answers

A.Remove duplicates

B.Group rows by a column

C.Replace values

D.Merge queries

E.Change data type

AnswersA, C, E

Remove Duplicates is a row-level cleaning transform in Power Query that scans all selected columns and retains only the first occurrence of each unique combination, discarding subsequent identical rows. This directly reduces row count and eliminates redundant data that would otherwise skew aggregations, making it a genuine data-cleaning operation. Distinct from filtering or grouping, it requires no aggregation or merge logic—just deduplication based on key columns.

Why this answer

Option A (Remove duplicates) is correct because Power Query provides a dedicated 'Remove Duplicates' transform that eliminates rows with identical values across selected columns, a core data-cleaning operation. Option C (Replace values) is correct because the 'Replace Values' transform substitutes specific text or numeric values (e.g., replacing 'N/A' with null), which is a standard cleansing step. Option E (Change data type) is correct because Power Query's 'Data Type' transform converts columns to the proper types (Text, Whole Number, Date, etc.), fixing type mismatches that would otherwise break calculations or loads.

Option B (Group rows by a column) is not a cleaning transform but an aggregation/reshaping operation that summarizes data. Option D (Merge queries) is not a cleaning transform but a join operation that combines two queries, which is a data-shaping rather than cleansing activity.

Exam trap

The trap here is that candidates often confuse data preparation transforms (like merging or grouping) with data cleaning transforms, leading them to select options that are actually for data shaping or integration rather than direct data quality improvement.

Practice this question →

Multi-Selecthard

Which THREE of the following are best practices for data modeling in Power BI? (Select exactly three.)

Select 3 answers

A.Use bi-directional cross-filtering relationships for all tables.

B.Store calculated logic in calculated columns rather than measures when possible.

C.Create a separate date table for time intelligence functions.

D.Hide the primary key columns in dimension tables from report view.

E.Use a star schema design with fact and dimension tables.

AnswersC, D, E

A separate date table is a best practice because DAX time intelligence functions like DATESYTD, SAMEPERIODLASTYEAR, and TOTALYTD require a continuous, contiguous date range and a table marked as a date table to work reliably. When you mark a dedicated date table, Power BI establishes the necessary relationship and ensures that time-based calculations respect the fiscal year and other custom calendars. Without it, time intelligence can return incorrect results due to missing dates or auto-generated date hierarchies.

Why this answer

Option C is correct because a dedicated, continuous date table marked as a date table is required for reliable time intelligence functions such as TOTALYTD, SAMEPERIODLASTYEAR, and DATEADD, and it avoids gaps or duplicate dates that break those calculations. Option D is correct because primary key columns in dimension tables are used only for relationship joins, so hiding them from report view keeps the field list clean and prevents report authors from accidentally dragging surrogate keys into visuals. Option E is correct because a star schema with a central fact table surrounded by dimension tables delivers optimal VertiPaq compression, simpler DAX, and faster query performance than snowflaked or flat designs.

Option A is not a best practice because bi-directional cross-filtering on all tables creates ambiguous filter paths, hurts performance, and can produce incorrect results; it should be used sparingly and only for specific many-to-many or security scenarios. Option B is not a best practice because calculated columns are computed at refresh, consume memory, and cannot respond to slicer context, whereas measures are evaluated at query time and are the preferred way to store dynamic business logic.

Exam trap

The trap here is that candidates often think bi-directional cross-filtering is a safe default (option A) or that calculated columns are always preferable for simplicity (option B), but the exam tests the understanding that these choices degrade performance and model clarity.

Practice this question →

MCQeasy

A data analyst needs to combine two queries in Power Query: 'Sales2023' and 'Sales2024', both with identical column structures. Which operation should the analyst use to append the rows from 'Sales2024' to 'Sales2023'?

A.Append Queries

B.Merge Queries

C.Group By

D.Pivot Column

AnswerA

Append Queries is the correct tool because it stacks rows from two or more queries vertically, creating a single output table that contains every record from each input. In Power Query, this operation—equivalent to UNION ALL in SQL—is used when the inputs share a common column schema, such as merging January and February sales records. Appending does not alter existing rows or add columns; it simply lengthens the dataset.

Why this answer

The Append Queries operation in Power Query is designed to combine rows from two or more tables with identical column structures, stacking the rows of 'Sales2024' beneath those of 'Sales2023'. This is the correct method because it preserves all columns and adds data vertically, which matches the requirement to append rows.

Exam trap

The trap here is that candidates often confuse Append Queries with Merge Queries, thinking both combine data, but Merge Queries joins columns horizontally (like a SQL JOIN) while Append Queries stacks rows vertically.

How to eliminate wrong answers

Option B is wrong because Merge Queries performs a join based on matching columns (like SQL JOINs), which combines columns horizontally rather than appending rows vertically, and would require a key column to match records. Option C is wrong because Group By aggregates data by grouping rows based on a column and calculating summaries (e.g., sum, count), which does not add rows from another table. Option D is wrong because Pivot Column transforms unique values from a column into new columns, reshaping data from rows to columns, which is the opposite of appending rows.

Practice this question →

MCQeasy

You are a Power BI data analyst for a logistics company. You connect to an Excel workbook stored on a SharePoint Online site. The workbook contains a table named Shipments. You need to load only the rows where the ShipmentDate is in the current year. Which Power Query transformation should you apply?

A.Add a custom column with the formula Date.Year([ShipmentDate]) = Date.Year(DateTime.LocalNow()) and then filter that column to TRUE.

B.Use the 'Filter Rows' option on the ShipmentDate column and select 'Date Filters' > 'Is After' and enter the first day of the current year.

C.Use the 'Filter Rows' option on the ShipmentDate column and select 'Date Filters' > 'In the Current Year'.

D.Use the 'Filter Rows' option on the ShipmentDate column and manually select the checkboxes for all dates in the current year.

AnswerC

The 'In the Current Year' filter is a dynamic date filter that automatically updates based on the current date. When the report refreshes, it will include only rows where ShipmentDate falls within the current calendar year, satisfying the requirement without manual intervention.

Why this answer

Power Query provides dynamic date filters such as 'In the Current Year' that automatically adjust based on the current date. Applying this filter to the ShipmentDate column ensures that only rows from the current year are loaded, and the filter remains correct as time passes without manual intervention.

Exam trap

The trap here is using a static date filter or manual selection, which does not automatically update when the year changes, leading to stale data in the report.

Practice this question →

Multi-Selecteasy

Which THREE data sources can be used with Power BI Dataflows? (Choose three.)

Select 3 answers

A.Power BI dataset

B.Excel file stored on local drive

C.OData feed

D.Azure SQL Database

E.SharePoint Online list

AnswersC, D, E

OData (Open Data Protocol) is a native, cloud-friendly REST API standard supported by the OData connector in Power Query Online, so dataflows can easily browse entity sets from APIs like SAP Gateway or Microsoft Graph. Both OData v3 and v4 endpoints are valid, and the connector supports anonymous, Windows, and organizational authentication options.

Why this answer

Power BI Dataflows are built on the Power Query Online engine, so any connector available in Power Query Online can serve as a data source. Option C (OData feed) is correct because OData is a standard REST-based protocol connector supported in Power Query Online, allowing dataflows to ingest from OData v3/v4 endpoints. Option D (Azure SQL Database) is correct because Azure SQL Database is a supported cloud database connector in Power Query Online, enabling direct query or import into the dataflow's CDM storage.

Option E (SharePoint Online list) is correct because SharePoint Online lists are a native cloud connector in Power Query Online, letting dataflows pull list data via the SharePoint REST API. Option A (Power BI dataset) is not a valid dataflow source because dataflows feed into datasets, not the reverse, and Power Query Online does not expose a Power BI dataset connector. Option B (Excel file stored on local drive) is not valid because Power Query Online runs in the cloud and cannot access a local drive path; a personal gateway or uploading to OneDrive/SharePoint would be required.

Exam trap

The trap here is that candidates often confuse Power BI Dataflows with Power Query in Power BI Desktop, where local file sources like Excel are allowed, but Dataflows in the service strictly require cloud-accessible sources.

Practice this question →

MCQhard

You are reviewing a Power Query M expression that transforms column types. The 'SalesAmount' column contains values like '1,234.56' (with a comma as thousands separator). After applying this transformation, what is the likely result?

A.The transformation will result in errors for rows containing commas.

B.The column will be converted to text automatically.

C.The column will be successfully converted to numbers.

D.The transformation will ignore the comma and convert the number correctly.

AnswerA

The M expression, likely using Number.From or a table column type change, requires text to match the current locale's numeric format. Since a comma is not the decimal separator in the default en-US locale, each row containing a comma causes a conversion failure that produces an Error value in the cell. Rather than being corrected or ignored, the transformation faithfully reports the parse failure as an error, which is the expected result.

Why this answer

Power Query's default type conversion for numeric columns expects a period as the decimal separator and no thousands separator. When the 'SalesAmount' column contains values like '1,234.56' with a comma as a thousands separator, attempting to convert the column directly to a number type (e.g., using 'Change Type' or 'Table.TransformColumnTypes') will cause errors for rows containing commas, as Power Query cannot parse the comma as part of a valid number. The comma is not a recognized numeric character in the default locale, so the conversion fails.

Exam trap

The trap here is that candidates assume Power Query will automatically handle locale-specific formatting (like commas as thousands separators) during type conversion, but in reality, it fails with errors unless the data is preprocessed or the correct culture is specified.

How to eliminate wrong answers

Option B is wrong because Power Query does not automatically convert the column to text; the transformation explicitly changes the column type to a number, and if it fails, it produces errors, not a text conversion. Option C is wrong because the comma acts as a non-numeric character in the default locale, preventing successful conversion to numbers without prior data cleaning (e.g., replacing commas with empty strings). Option D is wrong because Power Query does not ignore the comma; it strictly parses the value and fails when encountering an unrecognized character, unlike some other tools that might auto-detect locale settings.

Practice this question →

Multi-Selectmedium

Which TWO actions can improve data refresh performance in Power BI?

Select 2 answers

A.Merge all queries into a single query.

B.Add calculated columns in Power Query instead of DAX.

C.Disable load for intermediate queries used only for reference.

D.Filter rows at the source to reduce data volume.

E.Keep all columns from the source data to avoid re-importing.

AnswersC, D

Disabling load on intermediate queries prevents their result sets from being materialised into the dataset, eliminating unnecessary storage and refresh work. Only the final query's output is loaded, so the refresh engine processes less data and completes faster, directly improving refresh performance.

Why this answer

Option C is correct because disabling load on intermediate queries that are only used for reference prevents those staging tables from being materialized into the dataset, reducing the amount of data processed and stored during refresh. Option D is correct because filtering rows at the source (for example, using query folding or a WHERE clause in the source query) reduces the volume of data transferred and loaded, which directly speeds up refresh. Option A is not correct because merging all queries into one can create unnecessary complexity and may break query folding rather than improve performance.

Option B is not correct because calculated columns in Power Query are computed during refresh and can actually slow it down compared to DAX calculated columns, which are computed at query time. Option E is not correct because keeping all source columns increases data volume and memory usage, which harms rather than improves refresh performance.

Exam trap

The trap here is that candidates may confuse 'disable load' with 'disable refresh' or think that merging queries (Option A) is always beneficial, when in fact it can reduce parallelism and hurt performance.

Practice this question →

MCQmedium

A Power BI dataset is configured to use Import storage mode. The dataset includes a fact table with 100 million rows and several dimension tables. The report is slow when users interact with visuals. You need to improve query performance without changing the storage mode. Which action should you take?

A.Create aggregations on the fact table.

B.Increase the scheduled refresh frequency.

C.Reduce the number of dimension tables.

D.Enable 'Load to report' for all tables.

AnswerA

Creating aggregations on the fact table pre-summarizes data at higher grain levels (e.g., month, category), allowing the Import storage engine to serve queries from a smaller cached table set. Because Power BI's VertiPaq columnar compression and in-memory technology can scan aggregation tables far faster than the full transaction-level fact table, this directly reduces query response time. This is the correct answer because aggregations are a proven, first-class performance feature in Import mode, not merely a side effect of refresh or schema changes.

Why this answer

Creating aggregations on the fact table allows Power BI to pre-summarize data at higher granularity levels, reducing the amount of data scanned during query execution. Since the dataset uses Import mode, aggregations leverage the in-memory columnar storage to serve queries from pre-computed tables, significantly improving visual response times without altering the storage mode.

Exam trap

The trap here is that candidates often confuse data refresh frequency (Option B) with query performance, or mistakenly think reducing dimensions (Option C) is a valid optimization, when in fact aggregations are the correct technique for speeding up Import mode queries.

How to eliminate wrong answers

Option B is wrong because increasing the scheduled refresh frequency only updates the data more often; it does not improve query performance against the existing imported data. Option C is wrong because reducing the number of dimension tables would break the star schema design, potentially causing data redundancy and incorrect relationships, and it does not directly address query speed. Option D is wrong because enabling 'Load to report' for all tables simply makes them available in the Power BI model; it has no impact on query performance and may even increase memory usage.

Practice this question →

MCQmedium

You are connecting to a SQL Server database using Import mode. The source table contains a column 'SalesAmount' with a few null values. You need to replace nulls with 0 before loading. What is the most efficient step to achieve this in Power Query Editor?

A.Use 'Replace Values' to replace null with 0

B.Use 'Replace Errors' with value 0

C.Use 'Fill Down' to propagate previous values

D.Add a custom column with an if statement

AnswerA

Replace Values is the correct, direct transformation because it performs an in-place, column-wise substitution of nulls with 0 in a single Power Query step. It targets the actual null placeholder (not an error) and is applied to all selected columns simultaneously, making it the most efficient and unambiguous method for this exact requirement.

Why this answer

'Replace Values' in Power Query Editor is the most efficient way to replace null values in a column with 0. It directly transforms the column in a single step without requiring additional logic or table scans, and it generates a clean M code step (Table.ReplaceValue) that operates natively on the column's nulls.

Exam trap

The trap here is that candidates often confuse 'Replace Values' with 'Replace Errors' or think nulls are errors, leading them to choose Option B, but nulls are a distinct data type (absence of value) and require a dedicated null-replacement operation.

How to eliminate wrong answers

Option B is wrong because 'Replace Errors' is designed to replace error values (e.g., #ERROR) in cells, not null values; nulls are not errors and will not be affected by this transformation. Option C is wrong because 'Fill Down' propagates the last non-null value from above, which would incorrectly replace nulls with arbitrary previous values rather than a fixed 0, and it assumes a sequential order that may not be meaningful. Option D is wrong because adding a custom column with an if statement (e.g., if [SalesAmount] = null then 0 else [SalesAmount]) creates a new column and leaves the original column unchanged, requiring an extra step to remove or replace the original column, making it less efficient than a direct replacement.

Practice this question →

MCQmedium

You need to combine two tables from different sources: 'Orders' from SQL Server and 'Returns' from an Excel file. Both tables have a column named 'OrderID'. You want to include all orders and only matching returns. Which join type should you use in Power Query?

A.Inner Join

B.Right Outer Join

C.Full Outer Join

D.Left Outer Join

AnswerD

A Left Outer Join returns every row from Orders plus matching Returns rows, with nulls where no return exists. This satisfies the constraint of including all orders while attaching only matching returns, since OrderID is the shared key across the SQL Server and Excel sources.

Why this answer

In Power Query, a Left Outer Join returns all rows from the first (left) table ('Orders') and only the matching rows from the second (right) table ('Returns'), based on the 'OrderID' column. This matches the requirement to include all orders and only matching returns, ensuring no order is dropped even if it has no corresponding return.

Exam trap

The trap here is that candidates often confuse Left Outer Join with Right Outer Join, mistakenly thinking they need to include all returns instead of all orders, or they default to Inner Join without considering the requirement to preserve unmatched rows from the left table.

How to eliminate wrong answers

Option A is wrong because an Inner Join returns only rows where there is a match in both tables, which would exclude orders without returns. Option B is wrong because a Right Outer Join returns all rows from the right table ('Returns') and only matching rows from the left table ('Orders'), which would include all returns but not all orders. Option C is wrong because a Full Outer Join returns all rows from both tables, including non-matching rows from both sides, which would include returns without orders and is not the requirement.

Practice this question →

MCQmedium

You are building a Power BI report for a logistics company. The data is stored in a CSV file on a SharePoint Online document library. The CSV file is updated daily with new rows. You need to ensure that the Power BI dataset reflects the latest data every morning at 7:00 AM. The data volume is small, so full refresh is acceptable. You have already published the report to the Power BI service. What should you do to automate the refresh?

A.In the Power BI service, configure a scheduled refresh for the dataset with the desired time.

B.Use Power Automate to trigger a refresh via the Power BI REST API every morning.

C.Enable incremental refresh for the dataset and set the refresh frequency to daily.

D.Install an on-premises data gateway and configure a scheduled refresh.

AnswerA

Configuring a scheduled refresh in the Power BI service is the correct approach for a SharePoint Online dataset because the source is a cloud data source. You set the desired time and frequency directly in the dataset settings, and Power BI uses the stored credentials and refresh history to run the operation automatically—no gateway or custom scripting is required. This built-in feature is the simplest, most reliable, and standard method for keeping cloud data fresh.

Why this answer

The Power BI service supports scheduled refresh natively for datasets that connect to cloud data sources like SharePoint Online CSV files. By configuring a scheduled refresh in the dataset settings, you can set it to run daily at 7:00 AM without any additional tools or gateways. This ensures the dataset reflects the latest data from the CSV file each morning.

Exam trap

The trap here is that candidates often overcomplicate the solution by choosing Power Automate or incremental refresh, not realizing that the Power BI service's built-in scheduled refresh is sufficient and the simplest option for cloud-based data sources with small data volumes.

How to eliminate wrong answers

Option B is wrong because while Power Automate can trigger a refresh via the Power BI REST API, it is unnecessary overhead for a simple daily refresh of a small dataset from a cloud source; the built-in scheduled refresh in the Power BI service is simpler and more direct. Option C is wrong because incremental refresh is designed for large datasets to partition data and refresh only new or changed rows, but the question states data volume is small and full refresh is acceptable, so incremental refresh adds complexity without benefit. Option D is wrong because an on-premises data gateway is required only for on-premises data sources (e.g., SQL Server on a local network), but the CSV file is stored in SharePoint Online, which is a cloud source accessible directly by the Power BI service without a gateway.

Practice this question →

MCQmedium

You are reviewing a Power Query M expression in the advanced editor. The exhibit shows the query. What is the final output of this query?

A.A table with total sales amount per customer for all orders

B.A table with total sales amount per customer for orders after 2023

C.A table with all sales records after 2023

D.A table with all sales records after 2023, excluding Discount column

AnswerB

This exactly matches the M expression's step sequence: a row filter on OrderDate >= 2023-01-01, a Table.SelectColumns step that removes extraneous fields (e.g., Discount, ProductID), and a Table.Group operation keyed by CustomerID that aggregates Amount with List.Sum into a new total column. The resulting table has one row per distinct CustomerID, and each value of the aggregated column represents the sum of Amount for orders placed in 2023 or later. This is the correct interpretation of the transformation chain.

Why this answer

The query filters the Sales table to keep only rows where the OrderDate is in 2024 or later (i.e., after 2023), then groups by CustomerID, summing the SalesAmount for each customer. The final output is a table with one row per customer showing their total sales amount for orders placed after 2023.

Exam trap

The trap here is that candidates often overlook the filter step and assume the query returns all records or all customers, failing to recognize that the date filter and grouping fundamentally change both the row set and the structure of the output.

How to eliminate wrong answers

Option A is wrong because it describes total sales per customer for all orders, but the query includes a filter step that removes orders from 2023 and earlier. Option C is wrong because it suggests all sales records after 2023 are returned, but the query groups the data and aggregates (sums) the sales, so individual records are not preserved. Option D is wrong because it mentions excluding a Discount column, but the query does not remove any columns; it only filters rows and then groups/aggregates.

Practice this question →

100

MCQeasy

You are importing a CSV file into Power BI. The file contains a date column with values in the format 'MM/dd/yyyy'. However, Power Query interprets the dates as 'dd/MM/yyyy'. What should you do to correctly parse the dates?

A.Change the system region settings of the Power BI service to US

B.Use the 'Using Locale' option in the Change Type step to select the appropriate locale (e.g., English (United States))

C.Change the column data type to Text and then manually replace separators

D.Split the column into day, month, and year, then combine them in the correct order

AnswerB

The 'Using Locale' option in the Change Type step (accessed via the Data Type dropdown in Power Query Editor) lets you specify a culture, such as English (United States), that determines how date strings are parsed. By selecting a locale, you override the default system regional settings for that specific transformation, ensuring that a date like '03/04/2021' is interpreted as March 4th rather than April 3rd. This is the precise, minimal solution because it applies only to the selected column and records an M expression with 'Culture' parameter, making it reproducible in subsequent data refreshes without altering any global or tenant-wide configuration.

Why this answer

Power Query's 'Using Locale' option in the Change Type step allows you to specify the regional format of the source data (e.g., English (United States) for 'MM/dd/yyyy'). This overrides Power Query's default locale-based interpretation, ensuring dates are parsed correctly without altering the data or system settings.

Exam trap

The trap here is that candidates often assume changing system region settings (Option A) will fix the issue, but Power Query's locale handling is independent of the Power BI service region, and the correct approach is to use the 'Using Locale' option within the query editor.

How to eliminate wrong answers

Option A is wrong because changing the Power BI service region settings does not affect how Power Query Desktop interprets date formats during import; locale handling is a Power Query engine feature, not a service-level setting. Option C is wrong because manually replacing separators is error-prone and unnecessary; Power Query already supports locale-aware date parsing without data transformation. Option D is wrong because splitting and recombining columns is a cumbersome workaround that introduces complexity and potential data loss, whereas the 'Using Locale' option directly solves the parsing issue.

Practice this question →

101

Multi-Selecteasy

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

Select 2 answers

A.Pivot Column

B.Append Queries

C.Unpivot Columns

D.Merge Queries

E.Group By

AnswersB, D

Appends rows from one query to another.

Why this answer

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

Exam trap

The trap here is that candidates often confuse data transformation operations (like Pivot, Unpivot, Group By) with data combination operations (Append and Merge), leading them to select options that modify existing data rather than integrate multiple sources.

Practice this question →

102

Multi-Selecteasy

Which TWO data sources can be used with DirectQuery mode in Power BI? (Choose two.)

Select 2 answers

A.Excel workbook

B.SQL Server

C.Azure SQL Database

D.CSV file

E.SharePoint list

AnswersB, C

SQL Server is a full relational database management system with a query processor that accepts T-SQL requests over the Tabular Data Stream protocol. Power BI DirectQuery works by translating visual-level queries into native SQL commands that SQL Server executes in real time, allowing row-level security and large data volumes to be handled server-side. This pushdown capability is why SQL Server is a canonical DirectQuery source.

Why this answer

DirectQuery mode in Power BI requires a data source that supports query pass-through to a backend engine, and SQL Server (option B) qualifies because Power BI issues native T-SQL queries against the relational database in real time without importing data. Azure SQL Database (option C) is likewise a supported DirectQuery source, since it is a cloud-based SQL Server engine that accepts the same T-SQL query folding and live connection pattern. In contrast, Excel workbook (A), CSV file (D), and SharePoint list (E) are file- or list-based sources that Power BI imports rather than querying live, so they are not valid DirectQuery data sources.

Exam trap

The trap here is that candidates often confuse DirectQuery with Import mode, assuming any data source can be used with DirectQuery, but only relational databases like SQL Server and Azure SQL Database support it, while file-based sources like Excel and CSV do not.

Practice this question →

103

MCQhard

You are importing data from a CSV file into Power BI. The file contains a column 'SalesAmount' with values like '1,234.56' and '(987.65)' for negative amounts. You need to transform this column into a decimal number. Which sequence of Power Query steps achieves this?

A.Change Type to Decimal, then Replace Values (',' with ''), then Replace Values ('(' with '-')

B.Replace Values (',' with ''), then Replace Values ('(' with '-'), then Replace Values (')' with ''), then Change Type to Decimal

C.Replace Values (',' with ''), Change Type to Decimal, then Replace Values ('(' with '-')

D.Replace Values ('(' with '-'), Replace Values (')' with ''), Replace Values (',' with ''), then Change Type to Decimal

AnswerD

Replacing '(' with '-' and ')' with '' first converts accounting negatives into signed values, then stripping commas removes thousands separators. Only after these text substitutions does Change Type to Decimal parse correctly, satisfying the need to transform the column into a decimal number.

Why this answer

It first replaces the opening parenthesis '(' with a minus sign '-', then removes the closing parenthesis ')', then removes the comma thousands separator ',', and finally changes the data type to Decimal. This sequence ensures that the negative indicator is properly placed before the numeric value and that the string is cleanly formatted for type conversion.

Exam trap

The trap here is that candidates often try to change the data type too early, before cleaning the string, or they forget to remove the closing parenthesis after replacing the opening one, leading to conversion errors.

How to eliminate wrong answers

Option A is wrong because changing the type to Decimal before removing the comma and parentheses will cause errors, as the string '1,234.56' and '(987.65)' are not valid decimal numbers. Option B is wrong because replacing '(' with '-' before removing ')' leaves a trailing parenthesis that will cause the type conversion to fail. Option C is wrong because changing the type to Decimal before handling the parentheses will result in errors for negative values, as the string still contains parentheses.

Practice this question →

104

MCQeasy

You are a business analyst at a manufacturing company. You receive weekly CSV files from different plants. Each file contains columns: PlantID, Date, ProductID, UnitsProduced, and ScrapUnits. However, the files sometimes have missing values in the ScrapUnits column, and occasionally there are duplicate rows (same PlantID, Date, ProductID). You need to prepare a clean dataset for reporting. The requirements are: 1. Combine all CSV files from a folder into a single table. 2. Replace null values in ScrapUnits with 0. 3. Remove duplicate rows based on PlantID, Date, and ProductID, keeping the first occurrence. 4. Ensure the data types are appropriate (e.g., Date as date, UnitsProduced as whole number). Which sequence of Power Query steps should you use?

A.Connect to folder, replace nulls in each file, combine files, remove duplicates, set data types.

B.Connect to folder, combine files, set data types, replace nulls, remove duplicates.

C.Connect to folder, combine files, remove duplicates, replace nulls, set data types.

D.Connect to folder, combine files, replace nulls, remove duplicates, set data types.

AnswerD

This order is the correct Power Query pipeline for folder-based data: combine the files into one table, replace null values with a chosen sentinel, remove duplicate rows, and finally set the data types. Combining first means all transformations are performed on the aggregated result rather than repeated per file, saving compute and avoiding sample-file issues. Replacing nulls before deduplication ensures duplicates are evaluated on normalized values, and setting types last prevents any conversion from interfering with null handling or duplicate detection.

Why this answer

Option D is correct because it follows the proper Power Query order: connect to the folder and combine the CSV files first, then replace null values in ScrapUnits with 0, then remove duplicate rows based on PlantID, Date, and ProductID keeping the first occurrence, and finally set the data types (Date as date, UnitsProduced as whole number). Replacing nulls before removing duplicates ensures that duplicate detection and the retained first row are based on complete data rather than nulls that could later change values. Setting data types last avoids type-conversion errors on text values that may still contain nulls or duplicates.

Option A replaces nulls before combining, which is inefficient and may not apply consistently across all files; Option B sets data types before replacing nulls, which can cause conversion errors; Option C removes duplicates before replacing nulls, so null ScrapUnits values could affect which row is kept.

Practice this question →

105

MCQhard

You are preparing data for a financial model. A column named 'Amount' arrives from a source system as text values such as '1.234,56' using a European format where the period is the thousands separator and the comma is the decimal separator. You need to convert these to numeric decimal values. The dataset must refresh without manual intervention as new rows arrive. What should you do?

A.Use Replace Values to remove the periods, then replace the comma with a period, then change the type to Decimal Number.

B.Leave the column as text and create a DAX measure that uses SUBSTITUTE to convert values at query time.

C.Change the column data type to Decimal Number and set the locale in the Change Type step to a European locale such as German (Germany).

D.Change the column data type to Decimal Number using the default English (United States) locale.

AnswerC

Specifying the correct locale during the type change tells Power Query how to interpret the period as a thousands separator and the comma as a decimal point. The conversion then yields accurate decimals and repeats automatically on every refresh, so new rows in the same format are handled without manual steps.

Why this answer

Setting the locale during the type change lets Power Query interpret the European number format correctly, producing accurate decimal values that persist on refresh. String replacement and default-locale conversions either corrupt values or break for edge cases, while leaving the column as text blocks numeric operations.

Exam trap

The trap here is changing the data type without setting the locale, so the engine applies the wrong separator conventions and misreads the numbers.

Practice this question →

106

Multi-Selecthard

You are using Copilot for Power BI to assist with data preparation. Which THREE tasks can Copilot help you with?

Select 3 answers

A.Generate DAX expressions for calculated columns and measures.

B.Create Power Query M code snippets for common transformations.

C.Set up incremental refresh policies.

D.Suggest transformations to clean and shape data.

E.Configure data source credentials for refresh.

AnswersA, B, D

Copilot for Power BI interprets natural language descriptions and generates syntactically valid DAX expressions for calculated columns and measures, leveraging the semantic model's schema and relationships. This capability supports data preparation by extending the model with custom logic, such as time intelligence, conditional flags, or ratio metrics, without requiring manual DAX authoring. It also assists with debugging or explaining existing DAX, making it a core feature for data modeling.

Why this answer

Copilot for Power BI is designed to accelerate data preparation and modeling by generating DAX expressions for calculated columns and measures (A), which is a core supported capability for authoring calculations from natural-language prompts. It can also create Power Query M code snippets for common transformations (B), helping users build or extend queries in the Power Query Editor. Additionally, Copilot can suggest transformations to clean and shape data (D), such as removing duplicates, changing types, or splitting columns, which directly supports data preparation.

Options C and E are not Copilot tasks: incremental refresh policies and data source credentials are configured manually in Power BI Desktop or the Power BI service settings, not generated by Copilot.

Exam trap

The trap here is that candidates may assume Copilot can automate administrative tasks like refresh policies or credential configuration, but Copilot is limited to data preparation and transformation assistance within the Power BI Desktop environment.

Practice this question →

107

MCQhard

You are a Power BI data analyst for a financial services company. You have a Power Query query that combines data from multiple Excel workbooks stored in a SharePoint folder. The workbooks have a consistent structure. You need to ensure that when new workbooks are added to the folder, the query automatically includes them upon refresh. You also need to minimize the risk of query failures due to schema changes. What should you do?

A.Use the SharePoint Folder connector, filter to the relevant files, combine binaries, and then use a transformation that selects only the required columns and handles missing columns.

B.Use the SharePoint Folder connector, filter to the relevant files, combine binaries, and then remove the 'Changed Type' step.

C.Use the SharePoint Folder connector, filter to the relevant files, and then use the 'Combine Files' button without any further transformations.

D.Use the SharePoint Folder connector, filter to the relevant files, combine binaries, and then promote headers.

AnswerA

This approach automatically includes new files because it uses the folder connector and combines binaries. By explicitly selecting only the required columns and handling missing columns (e.g., using Table.SelectColumns with MissingField.UseNull), you minimize the risk of failures due to schema changes such as added or removed columns. This is a robust pattern for combining files with potential schema evolution.

Why this answer

The most robust approach is to use the SharePoint Folder connector to automatically pick up new files, then combine the binaries. After combining, explicitly select only the columns needed and handle missing columns gracefully, for example by using Table.SelectColumns with MissingField.UseNull. This ensures that new files are included and that schema variations do not break the query.

Simply combining without these safeguards leaves the query vulnerable to schema changes.

Exam trap

The trap here is assuming that the 'Combine Files' feature automatically handles all schema changes without additional configuration.

Practice this question →

108

MCQmedium

You have a Power BI dataset that includes a date table created using CALENDAR(). You need to ensure that the date table always covers the full range of dates present in the fact table, even after new data is loaded. What should you do?

A.Use a fixed start and end date in the CALENDAR function

B.Create a disconnected date table

C.Create the date table using CALENDAR(MIN('Fact'[Date]), MAX('Fact'[Date]))

D.Enable Auto Date/Time in the model

AnswerC

CALENDAR(MIN('Fact'[Date]), MAX('Fact'[Date])) generates a date table whose range is dynamically derived from the fact table's minimum and maximum dates on each refresh. This ensures the date table always covers the exact span of transactional activity, automatically expanding when new data includes later or earlier dates. Because the range is computed rather than hard-coded, it requires no manual maintenance and provides a continuous set of date keys suitable for marking as the model's date table and enabling time intelligence.

Why this answer

Using `CALENDAR(MIN('Fact'[Date]), MAX('Fact'[Date]))` dynamically computes the date range from the fact table's actual data. This ensures that when new data is loaded with dates outside the previous range, the date table automatically expands to cover the full range, maintaining referential integrity for time intelligence calculations.

Exam trap

The trap here is that candidates often choose Option A (fixed dates) because they think a static range is simpler and sufficient, but they overlook the requirement for the date table to dynamically cover the full range after new data loads, which only a dynamic CALENDAR expression can achieve.

How to eliminate wrong answers

Option A is wrong because using a fixed start and end date in the CALENDAR function creates a static date table that will not expand when new data with dates outside that fixed range is loaded, leading to missing dates and broken relationships. Option B is wrong because a disconnected date table is not related to the fact table via a relationship, so it cannot enforce referential integrity or be used for standard time intelligence functions that rely on an active relationship. Option D is wrong because enabling Auto Date/Time creates hidden date tables automatically, but these tables are not user-defined, cannot be customized, and do not guarantee coverage of the exact date range present in the fact table; they also increase model size and are not recommended for production.

Practice this question →

109

MCQmedium

A company has a Power BI dataset that imports data from a SQL Server database. The dataset includes a table with 10 million rows. The data model uses a single table and does not include any calculated columns or measures. The report users report that the dataset refresh takes too long. Which action should you take to improve refresh performance?

A.Increase the scheduled refresh frequency to every 15 minutes.

B.Enable Query Folding on all steps in Power Query.

C.Change the storage mode to DirectQuery.

D.Remove unused columns from the table in Power Query.

AnswerD

Power Query column removal reduces the data volume loaded and compressed into the VertiPaq model, cutting refresh time proportionally. With no calculated columns or measures to preserve, dropping unused columns is the most direct lever on refresh duration for a single-table import model.

Why this answer

Removing unused columns from the table in Power Query reduces the amount of data loaded into the Power BI dataset. With 10 million rows, every unnecessary column adds significant I/O and memory overhead during refresh. This directly improves refresh performance by minimizing the data volume transferred and processed.

Exam trap

The trap here is that candidates often confuse refresh performance with query performance, leading them to choose DirectQuery (Option C) which solves query latency but does not improve the import refresh time that the question explicitly targets.

How to eliminate wrong answers

Option A is wrong because increasing the scheduled refresh frequency to every 15 minutes does not improve the performance of a single refresh; it only makes refreshes happen more often, which could actually increase load on the source system. Option B is wrong because Query Folding pushes transformations back to the SQL Server, but the question states the dataset imports data from SQL Server and has no calculated columns or measures; enabling Query Folding on all steps is not a guaranteed performance improvement if the steps are already foldable, and it does not address the core issue of a large single table with 10 million rows. Option C is wrong because changing the storage mode to DirectQuery would avoid importing the data, but it shifts performance burden to query-time latency and is not a refresh performance improvement; the question specifically asks about improving dataset refresh time, not report query performance.

Practice this question →

110

MCQeasy

You are merging two tables in Power Query: Orders and Customers. The Orders table has a CustomerID column, and the Customers table has a CustomerID column. You want to keep all rows from Orders and only matching rows from Customers. Which join kind should you use?

A.Full Outer

B.Inner

C.Left Outer

D.Right Outer

AnswerC

Left outer join in Power Query preserves every row from the left table (orders) and adds columns from the right table (customers) only where a match exists, filling non-matches with null. This guarantees that the order count remains unchanged, which is typically the primary business requirement when merging order records with customer reference data. It is the correct choice because it retains all orders while still enriching them with available customer attributes.

Why this answer

(Left Outer) is correct because it returns all rows from the Orders table regardless of whether a matching CustomerID exists in the Customers table. When no match is found, the Customers columns will contain null values. This is the standard behavior for a left outer join in Power Query, which preserves the left table's rows entirely.

Exam trap

The trap here is that candidates often confuse Left Outer with Right Outer, mistakenly thinking that 'keeping all rows from Orders' means the Orders table should be on the right side, when in Power Query the join direction is determined by which table is selected as the primary table in the Merge dialog.

How to eliminate wrong answers

Option A is wrong because a Full Outer join would return all rows from both tables, including unmatched rows from both sides, which is not what the requirement specifies. Option B is wrong because an Inner join would only return rows where CustomerID exists in both tables, discarding any Orders rows without a matching Customer in Customers. Option D is wrong because a Right Outer join would keep all rows from Customers and only matching rows from Orders, which is the opposite of the stated requirement.

Practice this question →

111

MCQmedium

You are working on a Power BI project for a marketing department. You have a CSV file with customer survey responses. The file contains columns: CustomerID, SurveyDate, Response (text with ratings from 1 to 5), Comments (free text). The file is 10 MB. You need to load the data into Power BI and create a measure that calculates the average rating. However, when you load the file, you notice that the Response column is imported as text instead of whole number. Also, there are some rows with missing values in the Response column. You need to ensure the data is correctly typed and handle missing values appropriately. What is the best approach?

A.Use the 'Column from Examples' feature to create a new column with numeric values.

B.In Power Query, split the Response column by delimiter and then use the first part.

C.Use a DAX calculated column to convert text to number.

D.Change the data type of Response to whole number in Power Query, then filter out or replace null values.

AnswerD

Changing the data type of the Response column to whole number in Power Query is the correct approach because it directly addresses the underlying issue: the column is text but requires a numeric type for analysis. In Power Query, this transformation attempts to convert every value, and null values or invalid entries can be handled in the same step by filtering out invalid rows or replacing nulls with a default (e.g., 0) before loading. This is efficient, happens before data enters the model, and avoids the extra overhead of DAX calculated columns while preserving the column's identity and data lineage.

Why this answer

Power Query is the designated tool for data type transformations and null handling during the load phase. Changing the Response column's data type to Whole Number in Power Query automatically converts valid text numbers and flags errors, while filtering out or replacing null values ensures clean data before the data model is built. This approach follows the best practice of performing data cleansing in Power Query rather than in DAX, which would add unnecessary overhead and complexity.

Exam trap

The trap here is that candidates often think data type conversion can be done in DAX (Option C) because it seems simpler, but the PL-300 exam emphasizes that Power Query is the correct place for data preparation tasks like type changes and null handling, not the data model layer.

How to eliminate wrong answers

Option A is wrong because the 'Column from Examples' feature is designed for extracting or combining values based on patterns, not for bulk data type conversion; it would be inefficient and error-prone for converting a column of text numbers to numeric values. Option B is wrong because splitting the Response column by delimiter assumes the text contains a delimiter, which is not the case here (the column contains simple ratings like '1' or '5'), and it would create unnecessary columns without solving the type conversion or null handling. Option C is wrong because using a DAX calculated column to convert text to number is possible but is inefficient and violates the principle of performing data type transformations in Power Query; it also does not handle null values in the source data, which would still need to be addressed separately.

Practice this question →

112

MCQhard

You are building a Power BI semantic model that uses a large fact table from a data warehouse. The fact table has a date column and you want to create a date dimension. The organization requires that the date dimension includes all dates from 2010 to 2030, including weekends and holidays. What is the best practice for creating the date dimension?

A.Use the CALENDAR function in DAX to generate the date range

B.Mark the date column from the fact table as a date table and disable Auto Date/Time

C.Create a date dimension by using DISTINCT on the fact table's date column

D.Create a date table in Power Query by generating a list of dates from 1/1/2010 to 12/31/2030 and then add columns for attributes

AnswerD

Generating a complete list of dates in Power Query from 1/1/2010 to 12/31/2030 ensures that every day in that span exists as a row in the date table, regardless of activity in the fact table. You can then use M to add derived columns like year, month, quarter, ISO week number, and custom holiday flags by referencing a holidays table, making the solution flexible and easy to maintain. This approach follows the best practice of creating a distinct, static date dimension that supports reliable time intelligence and efficient relationships in a large semantic model.

Why this answer

It follows the best practice of creating a dedicated date dimension table in Power Query, which ensures full control over the date range (2010–2030) and allows you to add custom attributes like holidays. This approach avoids relying on the fact table's date column, which may have gaps or missing dates, and ensures the date dimension is complete and independent for robust time intelligence calculations.

Exam trap

The trap here is that candidates often choose Option C (DISTINCT on the fact table) thinking it is efficient, but they overlook that it will miss dates with no transactions, violating the requirement to include all weekends and holidays from 2010 to 2030.

How to eliminate wrong answers

Option A is wrong because the CALENDAR function in DAX creates a calculated table that is volatile and recalculates on every refresh, which can degrade performance with large models; it also lacks the ability to easily add custom columns like holidays in Power Query. Option B is wrong because marking a date column from the fact table as a date table is not recommended when the fact table may have missing dates (e.g., weekends or holidays), and disabling Auto Date/Time is a separate setting that does not create a proper date dimension. Option C is wrong because using DISTINCT on the fact table's date column will only include dates that exist in the fact table, which may omit weekends or holidays if no transactions occurred on those days, resulting in an incomplete date dimension.

Practice this question →

113

MCQeasy

You are connecting Power BI to a SQL Server database. The database contains a table with millions of sales transactions. You need to design a data model that minimizes load time and memory usage while still allowing analysis of sales by date, product, and customer. Which modeling approach should you use?

A.Use a composite model with some tables in DirectQuery and others in Import.

B.Use DirectQuery mode to avoid storing data in Power BI.

C.Import the data into Power BI, creating a star schema with date, product, and customer dimension tables and the sales fact table.

D.Use a live connection to an existing SQL Server Analysis Services tabular model.

AnswerC

Import mode is the right choice here because you get Power BI's in-memory columnstore engine, which compresses the data and makes slicers and filters nearly instantaneous. Building a proper star schema with separate date, product, and customer dimensions plus a sales fact table gives you clean one-to-many relationships and lets you write efficient DAX measures. This design minimizes memory usage while maximizing query performance.

Why this answer

Importing the data into Power BI and modeling it as a star schema minimizes query load time (the time to run interactive analyses) and memory usage through columnar compression and optimized query performance. Import mode stores data in the VertiPaq engine, which compresses data efficiently, especially with a star schema, reducing memory footprint relative to flattened tables and enabling fast in-memory analysis. While DirectQuery does not store data in Power BI (thus lower memory usage and no data refresh load time), it results in slower query performance and higher load on the source database, which is not ideal for analyzing millions of rows interactively.

Exam trap

The trap is that candidates often choose DirectQuery (Option B) thinking it saves memory and load time because it doesn't import data. However, this overlooks that Import mode with a star schema provides better query performance and memory efficiency through compression for large datasets, while DirectQuery increases query latency and source system load.

How to eliminate wrong answers

Option A is wrong because a composite model with some tables in DirectQuery and others in Import introduces complexity and potential performance overhead from cross-source query federation, which does not inherently minimize load time or memory usage compared to a fully imported star schema. Option B is wrong because DirectQuery mode avoids storing data in Power BI, but it does not minimize load time or memory usage; instead, it shifts query execution to the source database, which can lead to slower performance for interactive analysis and does not leverage Power BI's in-memory compression. Option D is wrong because a live connection to an existing SSAS tabular model does not minimize load time or memory usage in Power BI itself—it delegates processing to SSAS, which may not be optimized for the specific star schema design needed, and adds dependency on an external service without reducing Power BI's resource consumption.

Practice this question →

114

MCQhard

You are using the above KQL query as a source in Power Query for a Power BI semantic model. The query runs successfully but takes a long time to execute. You need to improve performance. What should you do?

A.Use the 'Run KQL command' option in Power Query to pass the query directly.

B.Add additional transformations in Power Query to reduce rows.

C.Enable query folding to push the query to the Kusto source.

D.Disable query folding to improve performance.

AnswerA

Using the 'Run KQL command' option in Power Query sends the Kusto query directly to the Kusto engine, which executes all filtering and aggregation server-side and returns only the final result set. This avoids pulling entire tables into the Power Query mashup engine, minimizes data transfer across the network, and lets Kusto use its native optimizations such as indexing, partitioning, and distributed execution. It is the recommended approach because compute happens at the source, not in Power Query.

Why this answer

Using the 'Run KQL command' option in Power Query sends the entire KQL query directly to Azure Data Explorer (or Kusto) for execution, allowing the Kusto engine to process and filter data at the source. This minimizes data transfer and leverages Kusto's optimized query engine, significantly improving performance compared to pulling all data into Power Query for transformation.

Exam trap

The trap here is that candidates often confuse 'query folding' (which applies to SQL-based sources like SQL Server) with the native KQL command execution in Power Query, incorrectly assuming that toggling a folding setting will push the query to Kusto when the correct approach is to use the dedicated 'Run KQL command' option.

How to eliminate wrong answers

Option B is wrong because adding additional transformations in Power Query after data is loaded does not reduce the initial data transfer; it only processes data locally, which can actually worsen performance by increasing memory and processing overhead. Option C is wrong because query folding is already implicitly enabled when using a native KQL query in Power Query; explicitly enabling it does not change behavior, and the performance gain comes from pushing the query to the source, not from a folding toggle. Option D is wrong because disabling query folding would force Power Query to pull all raw data from Kusto before applying any transformations, defeating the purpose of source-side filtering and drastically increasing load times.

Practice this question →

115

MCQmedium

You are importing data from a large CSV file (5 GB) into Power BI. The import takes too long and you need to reduce the data volume. What is the most effective approach in Power Query?

A.Disable loading of unrelated queries.

B.Increase query parallelism in options.

C.Filter rows and remove unnecessary columns in Power Query.

D.Enable 'Query Reduction' settings.

AnswerC

In the Power Query Editor, filtering rows (e.g., removing irrelevant records) and removing unnecessary columns before the final load directly shrinks the amount of data written into the Power BI data model. This is the correct approach because it reduces both row count and column cardinality, thereby lowering memory usage, improving refresh speed, and making the model more efficient for DAX calculations. Practically, you should also consider setting data types and reducing cardinality of columns during the same step.

Why this answer

Filtering rows and removing unnecessary columns in Power Query directly reduces the amount of data loaded into Power BI's data model. This is the most effective way to reduce data volume from a large CSV file, as it minimizes the data that Power Query must process and store, directly addressing the import time issue.

Exam trap

The trap here is that candidates often confuse 'Query Reduction' settings (which affect report interaction queries) with data volume reduction during import, leading them to incorrectly select Option D.

How to eliminate wrong answers

Option A is wrong because disabling loading of unrelated queries does not reduce the data volume of the specific CSV file being imported; it only prevents other queries from being loaded into the model, which does not address the size of the target CSV. Option B is wrong because increasing query parallelism in Power Query options can improve processing speed by running multiple queries concurrently, but it does not reduce the data volume of the large CSV file; it may even increase resource contention without addressing the root cause of excessive data. Option D is wrong because 'Query Reduction' settings in Power BI are designed to optimize report performance by reducing the number of queries sent to the data source during report interaction (e.g., disabling cross-highlighting), not to reduce the volume of data imported during the initial data load from a CSV file.

Practice this question →

116

MCQmedium

You are importing a large CSV file (200 MB) into Power BI Desktop. The import is very slow and sometimes fails. What should you do to improve performance?

A.Use Power BI Service to import the file instead.

B.Remove all relationships before import.

C.Filter rows and columns during import using Power Query to reduce data size.

D.Upgrade to Power BI Premium.

AnswerC

Power Query serves as the extraction and transformation engine that feeds the Power BI data model. By applying row filters and removing unneeded columns in Power Query, you prevent the full 200MB CSV from ever being stored in the in-memory columnar engine. This reduces the data volume that must be processed, loaded, and compressed, directly accelerating the import step and reducing the final model size and memory footprint.

Why this answer

Filtering rows and columns via Power Query is the correct approach to reduce the data loaded into the Power BI Desktop model. However, query folding does not apply to CSV files — it is a technique for database sources. For CSV, Power Query reads the entire file but still reduces memory and processing overhead by discarding unnecessary columns and rows before loading into the model.

This is a standard best practice for large flat files.

Exam trap

The trap here is that candidates often assume upgrading to Premium or using the Service will magically fix performance issues, but the PL-300 exam emphasizes that data reduction during import (via Power Query filtering) is the primary technique to optimize large file ingestion in Power BI Desktop.

How to eliminate wrong answers

Option A is wrong because Power BI Service does not import CSV files directly; it relies on Power BI Desktop or dataflows to prepare and publish data, and the import speed bottleneck is typically local memory and processing, not the destination service. Option B is wrong because removing all relationships before import does not reduce the data size or improve import performance; relationships are metadata that affect query performance after loading, not the initial data ingestion from a CSV file. Option D is wrong because upgrading to Power BI Premium increases capacity limits (e.g., dataset size up to 400 GB) but does not inherently speed up the import of a 200 MB CSV file in Power BI Desktop; the import process is constrained by local resources and data volume, not licensing tier.

Practice this question →

117

MCQmedium

You are building a Power BI data model that combines Sales data from SQL Server and Marketing data from a CSV file. The Sales table has a unique 'OrderID' column, and the Marketing table has a 'CampaignID' column. You need to create a relationship between Sales and Marketing to analyze campaign effectiveness. What should you do?

A.Use an inactive relationship between Sales and Marketing and activate it with USERELATIONSHIP in measures.

B.Create a bridge table containing unique combinations of OrderID and CampaignID.

C.Create a separate table for each campaign and relate to Sales.

D.Merge the Marketing table into the Sales table using a left outer join.

AnswerB

A bridge table is a factless fact table that stores unique combinations of OrderID and CampaignID, representing marketing touchpoints for each order. Filtering the bridge from Marketing propagates OrderIDs to Sales, while filtering from Sales propagates CampaignIDs to Marketing, preserving one-to-many relationships in both directions. This eliminates fanout and ensures sales measures are not inflated when analyzing campaigns.

Why this answer

A bridge table resolves the many-to-many relationship between Sales (OrderID) and Marketing (CampaignID). Since there is no direct key match, creating a bridge table with unique combinations of OrderID and CampaignID allows Power BI to model the relationship correctly and analyze campaign effectiveness without data duplication or ambiguity.

Exam trap

The trap here is that candidates often assume an inactive relationship or a merge can handle mismatched keys, but Power BI requires a common key column for relationships, and merging destroys the normalized model needed for accurate many-to-many analysis.

How to eliminate wrong answers

Option A is wrong because an inactive relationship requires a common key to exist between the tables; here, OrderID and CampaignID have no direct match, so an inactive relationship cannot be created. Option C is wrong because creating a separate table for each campaign would fragment the data, making it impossible to relate to Sales without a common key and violating star schema best practices. Option D is wrong because merging the Marketing table into Sales using a left outer join would create a single flat table, duplicating Sales rows for multiple campaigns and losing the ability to model the many-to-many relationship properly in Power BI.

Practice this question →

118

Multi-Selecteasy

Which TWO of the following are valid options when connecting to an on-premises SQL Server database from Power BI service? (Select TWO.)

Select 2 answers

A.DirectQuery mode with an on-premises data gateway

B.Using a SharePoint Online list connector pointing to SQL Server

C.DirectQuery using cloud credentials without a gateway

D.Import mode with an on-premises data gateway

E.Scheduling refresh in Power BI Desktop

AnswersA, D

DirectQuery mode is a live connection mode in which the Power BI service sends DAX queries to the source on every interaction instead of importing a copy of the data. For an on-premises SQL Server source, the service cannot route those queries directly across the corporate firewall; an on-premises data gateway must be installed within the network to authenticate to the SQL Server and forward the query results back to the cloud. Thus, a gateway is an absolute prerequisite for using DirectQuery in the Power BI service against on-premises relational databases.

Why this answer

Option A (DirectQuery mode with an on-premises data gateway) is correct because the Power BI service cannot reach an on-premises SQL Server directly, so it must route live DirectQuery requests through an on-premises data gateway installed in the local network. Option D (Import mode with an on-premises data gateway) is correct because scheduled imports of on-premises SQL Server data into the Power BI service likewise require the on-premises data gateway to bridge the cloud service to the local database. Option B is wrong because a SharePoint Online list connector targets SharePoint lists, not SQL Server tables, and cannot serve as a SQL Server connection path.

Option C is wrong because cloud credentials alone cannot traverse the corporate network boundary to reach an on-premises SQL Server without a gateway. Option E is wrong because scheduling refresh is a configuration action performed in the Power BI service (not a connection option), and Power BI Desktop itself does not run scheduled refreshes.

Exam trap

The trap here is that candidates often confuse 'connection mode' (DirectQuery vs. Import) with 'authentication method' (cloud credentials vs. gateway), mistakenly thinking DirectQuery can bypass the gateway for on-premises sources.

Practice this question →

119

Multi-Selecteasy

You are preparing data from multiple Excel files. Each file has a different structure; some have merged cells, empty rows, and inconsistent column names. Which TWO actions should you take to clean the data in Power Query? (Choose two.)

Select 2 answers

A.Remove blank rows that result from merged cells.

B.Group rows by a key column to summarize data.

C.Merge columns to create a single identifier.

D.Unpivot columns to normalize the data.

E.Promote the first row to headers.

AnswersA, E

In Excel, a merged cell spanning multiple rows leaves the non-top rows as blank cells that Power Query interprets as null records. These artificial blanks inflate row counts and break downstream transformations, so the Remove Blank Rows transformation must be applied to drop any row where all columns are null. This restores a clean, rectangular table where each record corresponds to an actual observation.

Why this answer

Option A is correct because merged cells in Excel often produce null or blank rows when imported into Power Query, and removing these blank rows (Home > Remove Rows > Remove Blank Rows) eliminates the structural noise so downstream transformations operate on valid records. Option E is correct because inconsistent column names across files typically mean the real headers are sitting in the first data row after import; using Home > Use First Row as Headers (Promote Headers) renames the columns correctly so each file's schema aligns before appending. Option B is not appropriate because grouping and summarizing is an aggregation step, not a cleaning step, and would collapse the detail rows needed for consolidation.

Option C is not appropriate because merging columns creates a concatenated identifier, which does not address inconsistent names, blanks, or merged-cell artifacts. Option D is not appropriate because unpivoting is used to normalize wide/crosstab layouts into attribute-value pairs, which is unrelated to fixing headers or removing blank rows in this scenario.

Exam trap

The trap here is that candidates often confuse data-cleaning actions (like removing blank rows and promoting headers) with data-transformation actions (like grouping, merging, or unpivoting), leading them to select options that reshape data rather than fix structural inconsistencies.

Practice this question →

120

MCQhard

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

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

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

C.Import all tables, create a date table with CALENDAR, and establish relationships: StockPrices[Date] -> DateTable[Date], StockPrices[Ticker] -> Company[Ticker], Earnings[Ticker] -> Company[Ticker], and create a many-to-many relationship between Earnings and DateTable using a bridge table.

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

AnswerC

Importing all tables into memory leverages Power BI's high-performance columnar compression and DAX evaluation, making queries fast even on large volumes. A dedicated date table created with CALENDAR (or CALENDARAUTO) and marked as the date table ensures accurate time intelligence and avoids the performance penalties of auto date/time. The stated relationships form a star schema: StockPrices joins to DateTable and Company, while Earnings joins to Company, creating clean filter paths. Since Earnings can have multiple records per date (across companies), a bridge table enables a many-to-many relationship between Earnings and DateTable, allowing both facts to be filtered correctly without data duplication or ambiguity.

Why this answer

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

Exam trap

The trap here is that candidates often choose Option D (flat table) thinking it simplifies the model, but they overlook the severe performance hit from data duplication and the inability to use star schema optimizations for time intelligence and filtering.

How to eliminate wrong answers

Option A is wrong because DirectQuery for a CSV file is not supported in Power BI; CSV files must be imported. Option B is wrong because ignoring Earnings data fails to meet the requirement of comparing earnings over time, and the auto date/time feature can degrade performance and is not recommended for large models. Option D is wrong because merging all tables into a single flat table creates a massive denormalized table (1.3M rows × multiple columns), leading to data duplication, increased storage, and slower calculations, especially for moving averages and time-based comparisons.

Practice this question →

121

MCQeasy

You are preparing data for a Power BI report. The source data contains a column with mixed data types: some values are numbers, others are text. When loading into Power Query, the entire column is typed as text. What is the likely cause?

A.The column was imported as text because the data source is a CSV file

B.Power Query detected that the column contains text values in some rows, so it set the data type to text

C.The 'Detect data type' option was disabled in Power Query settings

D.The source data is stored as text in the database

AnswerB

Power Query's automatic data type detection examines the values in each column and, when it encounters a mixture of numeric and textual entries, it deliberately selects the text data type. This ensures every value can be represented without conversion errors, because coercing text like 'N/A' into a number would fail. In this scenario, the presence of text values in some rows explains why the column was set to text.

Why this answer

Power Query's column type detection logic examines the entire column during import. If any row contains a non-numeric value (e.g., text), Power Query defaults the entire column to text to avoid data loss or conversion errors. This is the standard behavior when mixed data types are present, regardless of the source format.

Exam trap

The trap here is that candidates assume the data source format (e.g., CSV) is the cause, but Power Query's type detection logic—not the source—is what forces the column to text when mixed data types are present.

How to eliminate wrong answers

Option A is wrong because CSV files do not inherently force a column to text; Power Query still performs type detection on CSV data, and the mixed content triggers the text fallback. Option C is wrong because the 'Detect data type' option, when disabled, would leave all columns as 'Any' type, not specifically text. Option D is wrong because even if the source database stores the column as text, Power Query would still import it as text, but the question states the column has mixed data types, implying the source itself contains both numbers and text, which is the root cause.

Practice this question →

122

MCQeasy

You are cleaning data in Power Query. A column contains customer names with inconsistent capitalization (e.g., 'john smith', 'JANE DOE'). You need to standardize the names to proper case (first letter uppercase, rest lowercase). Which transformation should you use?

A.Use 'Format' > 'Trim'.

B.Use 'Format' > 'Capitalize Each Word'.

C.Use 'Format' > 'Lowercase'.

D.Use 'Format' > 'Uppercase'.

AnswerB

Selecting 'Format' > 'Capitalize Each Word' applies the M function Text.Proper to the entire column, which converts the first character of every word to uppercase and all other characters to lowercase, yielding values like 'John Smith'. In Power Query, this operates as a built-in transform that adds a new applied step for the selected column, and it recognizes spaces, punctuation, or any non-letter character as word boundaries. This is the correct choice for converting customer names from inconsistent mixed case into a readable proper-case format for a report.

Why this answer

The 'Capitalize Each Word' transformation in Power Query converts the first letter of each word to uppercase and the rest to lowercase, which is exactly what proper case requires. This is the correct choice because it directly addresses the need to standardize inconsistent casing (e.g., 'john smith' becomes 'John Smith', 'JANE DOE' becomes 'Jane Doe').

Exam trap

The trap here is that candidates may confuse 'Capitalize Each Word' with 'Uppercase' or 'Lowercase', thinking any casing transformation will suffice, but only 'Capitalize Each Word' produces the specific proper case format required.

How to eliminate wrong answers

Option A is wrong because 'Trim' only removes leading and trailing whitespace from text, it does not alter character casing. Option C is wrong because 'Lowercase' converts all characters to lowercase (e.g., 'JANE DOE' becomes 'jane doe'), which does not achieve the required first-letter uppercase format. Option D is wrong because 'Uppercase' converts all characters to uppercase (e.g., 'john smith' becomes 'JOHN SMITH'), which does not produce proper case.

Practice this question →

123

Multi-Selecthard

You are preparing data from an Azure SQL Database. You need to ensure that sensitive columns (e.g., Social Security Numbers) are obfuscated in Power BI reports. Which TWO of the following approaches can you use? (Choose two.)

Select 2 answers

A.Configure dynamic data masking on the Azure SQL Database.

B.Use row-level security (RLS) in Power BI to hide sensitive columns.

C.Transform the data in Power Query by replacing sensitive values with a placeholder.

D.Use Microsoft Purview sensitivity labels to mask data.

E.Apply column-level security in Power BI Desktop.

AnswersA, C

Configuring dynamic data masking on Azure SQL Database obfuscates sensitive columns at the database engine level, applying mask functions to the result set based on the querying user's permissions. When Power BI runs a query (especially in DirectQuery or SQL passthrough), if the login lacks masking privileges, the returned data is already masked. This is a source-side defense that requires no alteration of the data model or report design.

Why this answer

Azure SQL Database Dynamic Data Masking (DDM) obfuscates sensitive data at the database query level, so when Power BI connects to the database, the masked values are automatically returned for unauthorized users. This is a server-side approach that does not require changes to the Power BI report or data model.

Exam trap

The trap here is that candidates confuse Row-Level Security (RLS) with column-level masking, not realizing that RLS only filters rows and cannot hide or obfuscate column values, while column-level security in Power BI requires Premium features and object-level security (OLS), not a standard Desktop capability.

Practice this question →

124

MCQmedium

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

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

B.Import Sales, Stores, and Products tables, create a separate date table using CALENDAR, and establish relationships between Sales and dimension tables.

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

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

AnswerB

Creating a star schema by importing Sales as a fact table along with Stores, Products, and a separate date table generated via CALENDAR is the optimal design. The date table must be marked as a date table in Power BI to enable time-intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR to work predictably across fiscal and calendar periods. Relationships between Sales and the dimension tables filter facts efficiently, reduce model size through dimension normalization, and improve DAX query performance, making this the correct approach.

Why this answer

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

Exam trap

The trap here is that candidates often choose Option A (flat table) thinking it simplifies the model, not realizing that star schema design is essential for performance and compression in large datasets, and that Power BI's query folding can handle joins efficiently without merging.

How to eliminate wrong answers

Option A is wrong because merging all tables into a single flat table in Power Query creates massive data duplication (repeating StoreName and ProductName for every sales row), drastically increasing model size and degrading performance with millions of rows. Option C is wrong because importing only the Sales table and using calculated columns with RELATED forces Power BI to store the dimension data within the fact table, bloating the model and losing the benefits of separate dimension tables for filtering and compression. Option D is wrong because relying on the auto date/time feature creates hidden, auto-generated date tables that are not customizable, cannot support proper year-over-year calculations with DAX time intelligence, and can increase model size unnecessarily for large datasets.

Practice this question →

125

Multi-Selectmedium

You are using Power Query to clean and transform data from a SQL Server database. You have a table 'Orders' with columns 'OrderID', 'CustomerID', 'OrderDate', and 'TotalAmount'. You need to ensure that the data is properly typed and that any errors are handled. Which two actions should you perform? (Choose two.)

Select 2 answers

A.Use the 'Replace Errors' feature to replace errors in 'TotalAmount' with 0.

B.Remove any rows with errors in the 'TotalAmount' column.

C.Filter out any rows where 'CustomerID' is null.

D.Change the data type of 'OrderID' to Text.

E.Change the data type of 'OrderDate' to Date and 'TotalAmount' to Decimal Number.

AnswersA, E

Replacing errors with 0 ensures that the data loads without errors and allows calculations to proceed. However, it's important to understand the cause of errors; if they are due to data quality issues, replacing with 0 might skew results. But in many cases, it's a practical way to handle errors without losing rows. This is a recommended step when errors are expected and can be safely defaulted.

Why this answer

Setting correct data types for OrderDate and TotalAmount ensures accurate analysis, and replacing errors in TotalAmount with 0 handles data quality issues without losing rows. These two actions together prepare the data for reliable reporting. The other options either discard data unnecessarily or change types without benefit.

Exam trap

The trap here is thinking that removing rows with errors is always better than handling them, but losing data can be more detrimental than replacing errors with a default value.

Practice this question →

126

MCQmedium

You are importing data from an Excel workbook that has multiple worksheets. You only need data from the 'Sales' worksheet. When you connect via Power Query, all worksheets appear in the Navigator. What should you do to load only the 'Sales' worksheet?

A.Load all worksheets, then delete the unwanted ones

B.Select the 'Sales' worksheet in Navigator and click 'Load'

C.Select all worksheets and click 'Load'

D.Select the 'Sales' worksheet and click 'Transform Data'

AnswerB

Selecting the 'Sales' worksheet in Navigator and clicking 'Load' directly imports only that table into Power BI, creating a single Power Query query for that worksheet. This is the most efficient path because it avoids loading unrelated worksheets, reduces memory consumption, and keeps the data model clean with only the needed fields. The Load action imports the data as a table that appears in the Fields pane, ready for use in reports, and it remains refreshable with the original workbook.

Why this answer

In Power Query, the Navigator pane allows you to select specific worksheets or tables to load. Selecting the 'Sales' worksheet and clicking 'Load' imports only that data into your data model, avoiding unnecessary data. This is the most efficient method because it directly targets the required worksheet without extra steps.

Exam trap

The trap here is that candidates may think they need to use 'Transform Data' to filter or select specific data, but the Navigator itself provides the selection capability, and 'Load' directly imports the chosen data without requiring transformation first.

How to eliminate wrong answers

Option A is wrong because loading all worksheets and then deleting unwanted ones is inefficient and introduces unnecessary data into the model, which can cause performance issues and clutter. Option C is wrong because selecting all worksheets and clicking 'Load' would import every worksheet, not just the 'Sales' data, defeating the purpose. Option D is wrong because selecting the 'Sales' worksheet and clicking 'Transform Data' opens the Power Query Editor for transformation, but does not load the data into the model until you explicitly apply and load; the question asks to load only the 'Sales' worksheet, not to transform it first.

Practice this question →

127

Multi-Selectmedium

You are a data analyst for a utility company. You import a table named MeterReadings from an OData feed. The table contains a column named ReadingTimestamp that includes date and time. You need to create two new columns: one that contains only the date and one that contains only the hour of the day as a number (0–23). You want to use built-in Power Query transformations from the Add Column tab without writing custom formulas. Which two transformations should you use? (Choose two.)

Select 2 answers

A.Select ReadingTimestamp, then on the Add Column tab choose Time and select Hour.

B.Select ReadingTimestamp, then on the Add Column tab choose Date and select Year.

C.Select ReadingTimestamp, then on the Add Column tab choose Date and select Date Only.

D.Select ReadingTimestamp, then on the Add Column tab choose Time and select Duration.

E.Select ReadingTimestamp, then on the Add Column tab choose Date and select Month.

AnswersA, C

The Hour transformation from the Add Column tab creates a new column containing the hour of the day as a number from 0 to 23. This matches the requirement for an hour column and is a built-in operation. Using the Add Column tab preserves the original column and allows both derived columns to coexist.

Why this answer

To create a date-only column and an hour column from a timestamp, you use the Date Only transformation and the Hour transformation, both available on the Add Column tab. These are built-in, code-free operations that add new columns while keeping the original timestamp. Other date or time transformations extract different components and do not meet the stated requirements.

Exam trap

The trap here is choosing transformations that extract a single component, such as Year or Month, when the scenario asks for a full date-only column and an hour column.

Practice this question →

128

MCQmedium

You are loading data from a folder containing multiple Excel files with identical structure. Some files have inconsistent column names due to manual edits. You need to ensure that all data is loaded correctly without errors. What should you do in Power Query?

A.Use the 'Combine Files' feature with a sample file, then in the transformation step, promote headers and rename columns using a mapping table.

B.Use 'Merge Queries' to join the files based on row position.

C.Change the data source to a SharePoint folder and use 'Load to Data Model' directly.

D.In Power Query, use 'Enter Data' to manually create the schema.

AnswerA

When you connect to a folder, Power Query's Combine Files feature treats the first file as a sample, generates a binary parse function, and applies it across all files. After combining, you typically promote the file's initial data row to headers and then use a mapping table to rename arbitrary column titles (e.g., 'Price', 'PRICE', 'pricing') to a consistent schema. This standardizes variations across workbooks and avoids duplicate or misaligned columns when loading into the data model. It also allows you to dynamically refresh as new files are added, reusing the same transformation logic.

Why this answer

The 'Combine Files' feature in Power Query uses a sample file to infer the schema, and then you can apply transformations like promoting headers and renaming columns using a mapping table to handle inconsistent column names across files. This ensures all data loads without errors by standardizing the column names before combining.

Exam trap

The trap here is that candidates assume 'Combine Files' works automatically without any transformation steps, overlooking the need to handle inconsistent column names, which leads to errors during data load.

How to eliminate wrong answers

Option B is wrong because 'Merge Queries' joins tables based on matching columns or row positions, but it does not resolve inconsistent column names across multiple files; it would fail or produce incorrect results if column names differ. Option C is wrong because changing the data source to a SharePoint folder and using 'Load to Data Model' directly does not address the column name inconsistency; Power Query would still encounter errors when combining files with mismatched headers. Option D is wrong because 'Enter Data' manually creates a static table schema, which cannot dynamically adapt to multiple Excel files with varying column names, and it does not automate the loading process.

Practice this question →

129

MCQmedium

You are building a Power BI report for a manufacturing company. You have a large fact table with 50 million rows in Azure SQL Database. You need to minimize the data refresh time and ensure that only new or changed rows are loaded. The source table has a LastModifiedDate column. What should you do?

A.Enable query folding in Power Query to push filters to the source.

B.Configure incremental refresh on the table using the LastModifiedDate column.

C.Schedule a full refresh every hour.

D.Create a Power BI dataflow that performs a full load and then use that dataflow as a source.

AnswerB

Configuring incremental refresh on the LastModifiedDate column is the correct approach because it partitions the table by date ranges and only queries partitions that contain new or changed rows since the last refresh. Power BI stores a rolling window of historical partitions and creates new partitions for each refresh period, drastically reducing the amount of data read from the source and the time required. To implement this, you must define RangeStart and RangeEnd parameters in Power Query and set the incremental refresh policy in the dataset. This directly addresses the challenge of a 50-million-row table that needs near-real-time updates without re-loading the entire table each time.

Why this answer

Incremental refresh in Power BI allows you to load only new or changed rows from a large fact table by filtering on a date/time column such as LastModifiedDate. This minimizes data refresh time by avoiding a full reload of all 50 million rows, and it leverages the source system's ability to efficiently query only the modified data. Power Query pushes the filter logic to Azure SQL Database via query folding, ensuring optimal performance.

Exam trap

The trap here is that candidates often confuse query folding with incremental refresh, thinking that enabling query folding alone will automatically load only new rows, but query folding only optimizes the pushdown of existing filters—it does not create the filtering logic needed for incremental loading.

How to eliminate wrong answers

Option A is wrong because enabling query folding alone does not limit the data loaded to only new or changed rows; it only ensures that filters are pushed to the source, but without incremental refresh, Power Query would still attempt to load the entire table on each refresh. Option C is wrong because scheduling a full refresh every hour would reload all 50 million rows each time, which is inefficient and contradicts the requirement to minimize refresh time. Option D is wrong because creating a dataflow that performs a full load and then using that dataflow as a source does not reduce the initial data volume or refresh time; it simply adds an extra layer without addressing the need for incremental loading.

Practice this question →

130

Multi-Selectmedium

Which TWO are valid methods to handle null values in Power Query? (Choose two.)

Select 2 answers

A.Use the 'Fill Down' or 'Fill Up' option to propagate non-null values into null cells.

B.Remove rows that contain null values using the 'Remove Rows' > 'Remove Blank Rows' option.

C.Replace null values with a default value using the 'Replace Values' transform.

D.Merge the table with another table that has no nulls.

E.Change the data type of the column to a non-nullable type.

AnswersA, C

Fill Down and Fill Up are correct null-handling techniques in Power Query. Fill Down copies the last non-null value above into subsequent null cells until another non-null value is encountered; Fill Up works in the reverse direction. This is ideal for sparse columns where nulls represent the previous known value, such as period-end totals or grouping labels, though leading or trailing nulls may remain when there is no non-null value to propagate.

Why this answer

'Fill Down' and 'Fill Up' propagate the last non-null value into adjacent null cells. Option C is correct because 'Replace Values' can replace nulls with a default value. Option B is incorrect: 'Remove Blank Rows' removes rows where all cells are blank, not rows with nulls in specific columns.

Option D is not a direct method for handling nulls; merging may introduce new data but does not handle existing nulls. Option E is invalid because changing to a non-nullable type causes errors.

Exam trap

Candidates often mistakenly believe that 'Remove Blank Rows' handles null values, but it only removes rows that are entirely blank. To remove rows with nulls in specific columns, use filtering or 'Remove Rows' > 'Remove Duplicates' is not applicable. The correct methods are Fill, Replace, or filtering.

Practice this question →

131

Multi-Selecteasy

Which TWO actions are required when configuring a Power BI dataset to use incremental refresh?

Select 2 answers

A.Set the dataset storage mode to DirectQuery.

B.Enable query caching on the dataset.

C.Create a calculated table to store the refresh history.

D.Set the incremental refresh policy in the dataset settings.

E.Define rangeStart and rangeEnd parameters in Power Query.

AnswersD, E

Setting the incremental refresh policy in the dataset settings is the central action because it defines the refresh window (e.g., how many days of data to refresh), the date column used for filtering, and options like retaining historical data. This policy tells Power BI to create time-bounded partitions for the tables and determines which partitions are refreshed in a given run. Without this policy, Power Query parameters alone cannot trigger incremental refresh.

Why this answer

Option E is correct because incremental refresh in Power BI requires two Power Query parameters named exactly rangeStart and rangeEnd (both of type DateTime), which define the filter window applied to the table's date column so Power BI can partition and refresh only the relevant ranges. Option D is correct because, after publishing the dataset, you must configure the incremental refresh policy (via the dataset's settings in the Power BI service, or by right-clicking the table in Power BI Desktop and selecting Incremental refresh) to specify the historical and incremental periods that determine how partitions are created and refreshed. Option A is incorrect because incremental refresh works with Import mode datasets and does not require DirectQuery storage mode.

Option B is incorrect because query caching is a separate performance feature and is not a prerequisite for incremental refresh. Option C is incorrect because Power BI automatically manages the refresh history and partition metadata internally; no user-created calculated table is needed.

Exam trap

The trap here is that candidates often confuse the required steps (defining parameters and setting the policy) with optional or unrelated features like query caching or storage mode changes, leading them to select options A or B.

Practice this question →

132

MCQmedium

You are a data analyst at a retail company. You have a Power BI semantic model that imports sales data from an Azure SQL Database. The database uses a timestamp column to track transaction time. You need to reduce the data refresh time and ensure that only the last 30 days of data are refreshed during each scheduled refresh. You have already created the necessary parameters rangeStart and rangeEnd in Power Query. What should you do next to implement incremental refresh?

A.In the Power BI service, go to the dataset settings and configure the scheduled refresh.

B.In Power Query Editor, apply the rangeStart and rangeEnd filters to the data and then close and apply.

C.In the Power BI service, create a new refresh schedule and set the incremental refresh period.

D.In Power BI Desktop, on the model view, select the table and set the incremental refresh policy.

AnswerD

In Power BI Desktop, selecting the table in Model view opens the Properties pane, where the Incremental refresh control lets you define the archive period, the incremental period, and the RangeStart/RangeEnd parameters. This policy is saved into the data model and, after publishing, the Power BI service uses it to create date-based partitions and refresh only the partitions that fall within the incremental window. This is the intended, supported way to establish incremental refresh.

Why this answer

Incremental refresh policies are defined in Power BI Desktop on the model view, not in the service or by simply filtering in Power Query. After creating the rangeStart and rangeEnd parameters, you must select the table in the Model view, open the incremental refresh policy dialog, and configure the policy to filter data based on those parameters, ensuring only the last 30 days are refreshed.

Exam trap

The trap here is that candidates confuse filtering in Power Query Editor with setting an incremental refresh policy, not realizing that only the latter creates the partitioned refresh behavior required to reduce data refresh time.

How to eliminate wrong answers

Option A is wrong because configuring scheduled refresh in the Power BI service only sets the refresh frequency; it does not implement incremental refresh filtering. Option B is wrong because applying rangeStart and rangeEnd filters in Power Query Editor without setting an incremental refresh policy will still refresh the entire dataset, not just the last 30 days. Option C is wrong because creating a new refresh schedule in the Power BI service does not define incremental refresh; the policy must be set in Power BI Desktop before publishing.

Practice this question →

133

Multi-Selecteasy

You are importing data from an Excel workbook. The workbook has multiple sheets. You want to combine two sheets that have the same columns but different row data. Which TWO Power Query operations can you use?

Select 2 answers

A.Merge Queries

B.Group By

C.Append Queries

D.Pivot Column

E.Append Queries as New

AnswersC, E

Append Queries is the Power Query operation that stacks the rows of one query or table below those of another, requiring both tables to have the same or compatible column structures. It performs a vertical concatenation equivalent to SQL UNION, adding the entire set of rows from the second query to the first query's existing data. This exactly satisfies the requirement to consolidate rows from an Excel workbook into a single table.

Why this answer

Append Queries (C) is correct because appending stacks the rows of two tables that share the same column structure, which is exactly the goal of combining two sheets with identical columns but different row data. Append Queries as New (E) is also correct because it performs the same row-stacking operation but outputs the result to a new query instead of modifying an existing one, preserving the original queries. Merge Queries (A) is incorrect because it joins tables horizontally by matching key columns, adding columns rather than rows.

Group By (B) is incorrect because it aggregates rows into summary values, not concatenates datasets. Pivot Column (D) is incorrect because it reshapes data by turning unique row values into new columns, which does not combine row data from two sheets.

Exam trap

The trap here is that candidates confuse 'Merge' (horizontal join) with 'Append' (vertical union), or think only one of the Append options is valid, but both 'Append Queries' and 'Append Queries as New' are correct operations for combining rows.

Practice this question →

134

MCQeasy

You have a Power BI data model with a 'Sales' fact table and a 'Date' dimension. You need to create a calculated column in the 'Sales' table that shows the fiscal year based on a 'Date' column. The fiscal year starts on July 1. Which DAX expression should you use?

A.SWITCH(TRUE(), MONTH(Sales[Date]) >= 7, YEAR(Sales[Date]), YEAR(Sales[Date])-1)

B.YEAR(Sales[Date])

C.YEAR(Sales[Date]) + 1

D.FORMAT(Sales[Date], "YYYY")

AnswerA

SWITCH(TRUE(), MONTH(Sales[Date]) >= 7, YEAR(Sales[Date]), YEAR(Sales[Date]) - 1) evaluates boolean conditions sequentially; for any date with month 7 or later it returns the current calendar year, which equals the fiscal year for a July-start fiscal year. For months January through June, it subtracts one, correctly assigning those dates to the prior fiscal year. This expression returns an integer, preserving numeric sorting and supporting direct use in relationships or calculated columns.

Why this answer

It uses SWITCH with TRUE() to evaluate a logical condition: if the month of the date is July or later (MONTH >= 7), it returns the current year; otherwise, it returns the previous year. This correctly implements a fiscal year starting on July 1, as required.

Exam trap

The trap here is that candidates often assume YEAR() alone is sufficient for fiscal year calculations, overlooking the need to adjust for the fiscal year start month, or they incorrectly add 1 to all years instead of conditionally shifting only the first half of the calendar year.

How to eliminate wrong answers

Option B is wrong because YEAR(Sales[Date]) returns the calendar year, not the fiscal year, so dates from January to June would be assigned to the wrong fiscal year. Option C is wrong because YEAR(Sales[Date]) + 1 always adds one year, which would incorrectly shift all dates forward by one year, not handle the July 1 start. Option D is wrong because FORMAT(Sales[Date], 'YYYY') simply returns the calendar year as a text string, with no fiscal year logic applied.

Practice this question →

135

MCQhard

You are building a Power BI semantic model that combines data from an on-premises SQL Server database and a SharePoint Online list. The SQL Server table contains 10 million rows and updates hourly. The SharePoint list contains 500 rows and updates daily. You need to minimize the data load time and ensure the model refreshes within the scheduled 30-minute window. What should you do?

A.Use DirectQuery for the SQL Server table and Import mode for the SharePoint list.

B.Set both tables to DirectQuery mode.

C.Set the SQL Server table to Dual mode and the SharePoint list to Import mode.

D.Import both tables into the model and disable incremental refresh.

AnswerA

DirectQuery for the SQL Server table is correct because it keeps the 10-million-row table out of the model's memory, avoiding a long and costly import/refresh; queries are pushed to SQL Server at report time. The SharePoint list, which is small, is well-suited to Import mode because SharePoint Online does not support DirectQuery, and importing it enables fast, in-memory performance and full modeling capabilities like calculated columns and relationships.

Why this answer

Using DirectQuery for the large SQL Server table (10M rows, hourly updates) avoids importing all rows into the model, significantly reducing data load time and memory usage. Import mode for the small SharePoint list (500 rows, daily updates) is appropriate since it loads quickly and supports full DAX functionality, while the combination keeps the total refresh within the 30-minute window.

Exam trap

The trap here is that candidates often assume Import mode is always best for performance, but for very large tables with frequent updates, DirectQuery avoids the bottleneck of importing millions of rows, while small tables are better imported to avoid live query overhead.

How to eliminate wrong answers

Option B is wrong because setting both tables to DirectQuery mode would force the SharePoint list to be queried live, which can introduce latency for each report interaction and may not support all DAX functions, plus it doesn't leverage the small size of the SharePoint data for fast import. Option C is wrong because Dual mode is designed for tables that need to serve both as a dimension table in Import mode and as a DirectQuery source, but it doesn't solve the load-time issue for the large SQL Server table—it still requires importing the data, which would exceed the 30-minute window. Option D is wrong because importing both tables, even with incremental refresh disabled, would require loading the full 10M rows from SQL Server on each refresh, which is likely to exceed the 30-minute window and consume excessive memory.

Practice this question →

136

MCQmedium

You have a table with a column 'Date' in text format (e.g., '2024-01-15'). You need to convert it to a date type. In Power Query, what is the best approach?

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

B.Use the Excel Power Query add-in.

C.Create a calculated column in DAX using DATEVALUE.

D.Change the column data type to Date in Power Query Editor.

AnswerD

Changing the column data type to Date in the Power Query Editor is the correct and most efficient approach because it leverages Power Query's built-in type conversion system, which parses the text representation into a true date value using the designated locale and format. To do this, you select the column, go to the Transform tab (or Home tab), and choose Data Type > Date; Power Query automatically inserts a 'Changed Type' step that records this transformation. This method is straightforward, requires no custom code, and ensures the data is correctly typed for all downstream operations like modeling, DAX calculations, and visual date hierarchies.

Why this answer

Option D is correct because Power Query's Change Type > Date (or the Date.From/Table.TransformColumnTypes operation) natively parses ISO-formatted text like '2024-01-15' into a true date type using the locale's date-parsing rules, which is the intended, one-step approach for this scenario. Splitting into year, month, and day (A) is unnecessary and error-prone when the text is already in a recognizable date format. Using the Excel Power Query add-in (B) is irrelevant since Power Query is already the tool in use, and creating a DAX calculated column with DATEVALUE (C) works in the data model rather than transforming the column at query time, which is less efficient and doesn't change the underlying column type.

Practice this question →

137

MCQhard

During data refresh in Power BI, an error occurs: 'The column 'OrderID' of the table 'Orders' contains a duplicate value and this column is part of a primary key.' The table 'Orders' is imported from an Azure SQL database. What is the most likely cause of this error?

A.The 'Orders' table was reordered in Power Query.

B.Data type mismatch between the source and Power BI.

C.A calculated column is referencing the 'Orders' table.

D.The source table has duplicate 'OrderID' values.

AnswerD

The 'Orders' table has a column designated as a key (likely OrderID) that must contain unique values for the model's relationships to work. When the refresh tries to load data, the VertiPaq engine checks uniqueness on that key; duplicate OrderID values in the source violate that constraint, causing the refresh to fail with a duplicate key error. This often happens when the source view or query returns repeated rows due to joins or missing DISTINCT, and it is the direct and primary cause of the reported error.

Why this answer

The error message explicitly states that the 'OrderID' column contains a duplicate value and is part of a primary key. In Power BI, when importing from a source like Azure SQL Database, the data model enforces uniqueness on primary key columns. If the source table has duplicate 'OrderID' values, the refresh fails because Power BI cannot maintain the required unique constraint.

Exam trap

The trap here is that candidates may confuse a primary key violation with other common refresh errors like data type mismatches or query folding issues, but the error message's explicit reference to 'duplicate value' and 'primary key' directly points to source data duplication.

How to eliminate wrong answers

Option A is wrong because reordering columns in Power Query does not affect data integrity or primary key uniqueness; it only changes the column sequence in the dataset. Option B is wrong because a data type mismatch would cause a conversion error, not a duplicate value error on a primary key column. Option C is wrong because a calculated column referencing the 'Orders' table does not introduce duplicate values; it computes values based on existing rows and does not alter the source data's uniqueness.

Practice this question →

138

MCQhard

You are a data analyst for a financial services company. You have a Power BI dataset that combines data from two sources: a CSV file in SharePoint Online and an on-premises SQL Server database. The CSV file contains exchange rates that are updated daily. The SQL Server database contains transaction data. You need to ensure that the dataset can be refreshed automatically in the Power BI service. The CSV file is updated at 6:00 AM daily, and the SQL Server database is updated continuously. You have already published the report. What should you do to enable automated refresh?

A.Use Power Automate to refresh the dataset after the CSV is updated.

B.Enable incremental refresh for the SQL Server table to reduce refresh time.

C.Install and configure an on-premises data gateway, then set up a scheduled refresh.

D.Configure a scheduled refresh in the dataset settings. The gateway is not required because the CSV file is in SharePoint Online.

AnswerC

The on-premises SQL Server is a data source that lives behind your corporate firewall, so the Power BI service uses an on-premises data gateway in standard mode to connect securely. After installing the gateway and adding the data source with the appropriate credentials, you assign it to the dataset and then configure a scheduled refresh in the dataset settings. This enables Power BI to query the SQL Server and the SharePoint CSV on a recurring basis.

Why this answer

The on-premises SQL Server database requires an on-premises data gateway to bridge the Power BI service with the local network. Even though the CSV file is in SharePoint Online, the dataset combines both sources; the gateway is mandatory for the SQL Server component. Without it, scheduled refresh cannot access the on-premises data, and the dataset will fail to refresh automatically.

Exam trap

The trap here is that candidates assume a gateway is unnecessary because one data source (SharePoint Online) is cloud-based, forgetting that the on-premises SQL Server requires a gateway for any automated refresh in the Power BI service.

How to eliminate wrong answers

Option A is wrong because Power Automate can trigger a refresh but does not solve the underlying connectivity issue for the on-premises SQL Server; the gateway is still required. Option B is wrong because incremental refresh reduces refresh time and data volume but does not enable connectivity to an on-premises data source; it is a performance optimization, not a connectivity solution. Option D is wrong because while the CSV file is in SharePoint Online and does not need a gateway, the on-premises SQL Server database absolutely requires an on-premises data gateway for the Power BI service to reach it; omitting the gateway will cause the scheduled refresh to fail.

Practice this question →

139

MCQhard

You are a data analyst for a multinational corporation. You are building a Power BI report that uses a large fact table (100 million rows) and several dimension tables. The data source is a SQL Server data warehouse. Users need to see near real-time data with a maximum latency of 15 minutes. The current import mode takes too long to refresh. You decide to use DirectQuery mode. However, queries are slow. You need to improve query performance. You consider creating aggregations in the data source. Which approach should you take in Power BI to leverage these aggregations?

A.Create a composite model with an imported aggregated table.

B.Define aggregations in Power BI on the DirectQuery tables.

C.Change the storage mode to Import for the fact table.

D.Create a SQL Server view that aggregates data and use it as the source.

AnswerB

Defining aggregations in Power BI on the DirectQuery tables is correct because Power BI can automatically redirect queries to a cached aggregation table when the query grain matches, drastically reducing load on the source and improving response times. This approach maintains the DirectQuery storage mode for detail-level queries, so users still see fresh data without a full import, and it leverages Power BI's optimization engine rather than requiring source-side changes.

Why this answer

Power BI allows you to define aggregations on DirectQuery tables, which enables the engine to route queries to pre-aggregated data at the source (e.g., SQL Server indexed views or materialized views) when possible, significantly reducing query latency. This approach leverages the existing aggregations in the data source without changing the storage mode or importing data, maintaining near real-time freshness with a 15-minute latency requirement.

Exam trap

The trap here is that candidates often think creating a SQL Server view (Option D) is the correct Power BI approach, but Power BI cannot automatically leverage such views as aggregations unless they are explicitly defined in the model; the exam tests whether you know that aggregations must be defined within Power BI on DirectQuery tables to enable query rewriting.

How to eliminate wrong answers

Option A is wrong because creating a composite model with an imported aggregated table would reintroduce import mode for that table, breaking the near real-time requirement (import mode refreshes are too slow for 15-minute latency) and adding complexity without leveraging the source aggregations directly. Option C is wrong because changing the storage mode to Import for the fact table would revert to the original slow refresh issue, as importing 100 million rows takes longer than 15 minutes, and it does not use the aggregations defined in the data source. Option D is wrong because creating a SQL Server view that aggregates data and using it as the source is a data-source-side change, not a Power BI approach to leverage aggregations; Power BI would treat the view as a regular table and still require DirectQuery or import, missing the optimization of Power BI's aggregation management.

Practice this question →

140

MCQhard

Your Power BI dataset uses DirectQuery to a SQL Server data warehouse. Users report that reports are slow. You need to improve performance without changing the data source. What should you do?

A.Switch the dataset to Import mode.

B.Disable the 'Enable query reduction' option in Power BI Desktop.

C.Create aggregated tables in Power BI using the Aggregations feature.

D.Increase the memory limit of the on-premises data gateway.

AnswerC

Aggregations create a cached, in-memory summary table that DirectQuery queries hit first, so most report visuals avoid round trips to SQL Server. This satisfies the constraint of improving performance without altering the data source, since the detail tables and DirectQuery connection remain unchanged.

Why this answer

Creating aggregated tables in Power BI using the Aggregations feature allows you to pre-summarize data at a higher granularity while still using DirectQuery. This reduces the amount of data queried from the SQL Server data warehouse, improving report performance without changing the underlying data source or switching to Import mode.

Exam trap

The trap here is that candidates often assume performance improvements must come from switching to Import mode or tuning the gateway, but the Aggregations feature is specifically designed to optimize DirectQuery performance without altering the source system.

How to eliminate wrong answers

Option A is wrong because switching to Import mode would change the data source behavior by caching data locally, which violates the constraint of not changing the data source and may not be feasible for large datasets due to memory limits. Option B is wrong because disabling 'Enable query reduction' would actually increase the number of queries sent to the data source, making performance worse, not better. Option D is wrong because increasing the memory limit of the on-premises data gateway does not improve query performance for DirectQuery; it only helps with data throughput for gateway operations, not the speed of queries against the SQL Server.

Practice this question →

141

MCQhard

A Power BI report contains a table with a column 'Date' of type date. The report users need to filter data by fiscal year, which starts on April 1. What is the best practice to support this requirement during data preparation?

A.Create a separate date table in Power Query with a fiscal year column.

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

C.Use a DAX calculated table to generate fiscal year dates.

D.Add a calculated column in the existing table using DAX.

AnswerA

A separate date table created in Power Query is the recommended way to handle fiscal-year reporting because it provides a clean, continuously dated calendar dimension that can be marked as the date table in your model. You can add a fiscal year column with a simple conditional formula that adjusts the calendar year based on your fiscal year start month. This supports DAX time intelligence functions like TOTALYTD and DATEADD, and keeps the date dimension independent from fact tables.

Why this answer

Creating a separate date table in Power Query with a fiscal year column is the best practice for handling fiscal year filtering. This approach ensures the date dimension is independent of fact tables, supports star schema design, and allows you to define fiscal year logic (starting April 1) directly in M code during data preparation, which is more efficient and maintainable than using DAX calculated columns or tables.

Exam trap

The trap here is that candidates often think a DAX calculated column or table is acceptable for fiscal year logic, but the exam emphasizes that data preparation (Power Query) is the correct phase for such transformations to maintain performance and star schema design.

How to eliminate wrong answers

Option B is wrong because splitting the date column into year, month, and day columns does not inherently create a fiscal year hierarchy; it only breaks the date into parts, requiring additional logic to map months to fiscal years, which is inefficient and does not provide a proper date dimension for filtering. Option C is wrong because using a DAX calculated table to generate fiscal year dates is less performant than doing so in Power Query; DAX calculated tables are computed after data load and can increase model size and refresh time, whereas Power Query transformations are applied during data preparation and are more efficient. Option D is wrong because adding a calculated column in the existing table using DAX introduces redundancy and violates star schema best practices; it also computes the fiscal year at query time rather than during data preparation, leading to potential performance issues and lack of reusability across multiple fact tables.

Practice this question →

142

MCQmedium

You have a Power BI dataset that combines sales data from two Excel files: Sales2023.xlsx and Sales2024.xlsx. Both files have the same schema. You need to combine them into a single table without duplicating rows. What is the best approach in Power Query?

A.Use Union in DAX.

B.Use Group By to summarize data.

C.Use Append Queries.

D.Use Merge Queries as a new query.

AnswerC

Append Queries in Power Query is specifically designed to combine two or more tables by stacking their rows one after another, aligning columns by name. When you have sales data from two sources with the same structure, appending creates a single table containing all records from both sources, which is exactly what the scenario requires. It runs at data refresh time in Power Query, ensuring the combined data is loaded efficiently into the Power BI data model.

Why this answer

Append Queries in Power Query is specifically designed to combine rows from two or more tables with the same schema into a single table, stacking them vertically without duplicating rows. This operation is performed in the Power Query Editor (M language) and is the standard approach for unioning data from multiple sources during the data preparation phase, before loading into the Power BI data model.

Exam trap

The trap here is that candidates often confuse Append Queries (vertical stacking) with Merge Queries (horizontal joining), or mistakenly think DAX Union is appropriate for data preparation, when Power Query is the correct tool for this task.

How to eliminate wrong answers

Option A is wrong because Union in DAX is a function used within calculated tables or measures in the data model, not in Power Query; it operates on tables already loaded into the model and can cause performance issues and duplicate rows if not handled carefully, whereas the requirement is to combine data during the preparation phase. Option B is wrong because Group By is used to aggregate data (e.g., sum, count) by grouping rows based on columns, not to combine two separate tables into one; it would summarize the data rather than preserving all rows. Option D is wrong because Merge Queries is used to join tables horizontally (like SQL JOIN) based on matching keys, adding columns from one table to another, not to stack rows vertically; it would create a wider table, not a longer one, and could introduce duplicates if not configured correctly.

Practice this question →

143

MCQmedium

You are a Power BI developer for a healthcare organization. You are building a dataset that includes patient data from an on-premises SQL Server database. The database contains a table 'PatientVisits' with columns: PatientID, VisitDate, DiagnosisCode, and Cost. The database also has a table 'DiagnosisLookup' with DiagnosisCode and Description. You need to create a star schema in Power BI. The requirements are: - The dataset must include a date dimension table that covers all dates from 2010 to 2030. - The 'PatientVisits' table should be the fact table. - Diagnosis descriptions should be in a dimension table. - You must use Power Query to create the date dimension table using M code. - The data refresh must be scheduled daily via the on-premises data gateway. You have already loaded the 'PatientVisits' and 'DiagnosisLookup' tables. What should you do next to complete the star schema?

A.Use DAX to create a date table using CALENDAR function in Power BI Desktop, then mark it as a date table.

B.Enable the 'Auto date/time' option in Power BI Desktop and hide the generated date hierarchy.

C.In the model view, create a relationship between PatientVisits[VisitDate] and DiagnosisLookup[DiagnosisCode].

D.In Power Query, create a blank query that generates a date table using the List.Dates function with a custom column for Year, Month, etc. Load it into the model and mark it as a date table.

AnswerD

A Power Query blank query using List.Dates generates a contiguous calendar covering 2010 to 2030, and adding Year and Month columns supports the star schema. Marking it as the date table enables time intelligence, and it refreshes through the existing on-premises gateway.

Why this answer

The requirement explicitly states that the date dimension table must be created using M code in Power Query, and the List.Dates function is the appropriate M function to generate a continuous range of dates from 2010 to 2030. After creating the table with additional columns like Year and Month, you must load it into the model and mark it as a date table to enable time intelligence functions. This approach satisfies the need for a custom date dimension that is not dependent on DAX or auto-generated hierarchies.

Exam trap

The trap here is that candidates often default to using DAX's CALENDAR function (Option A) because it is simpler, but the question explicitly requires M code in Power Query, making DAX-based solutions incorrect even if functionally equivalent.

How to eliminate wrong answers

Option A is wrong because using DAX with the CALENDAR function violates the explicit requirement to create the date dimension table using M code in Power Query. Option B is wrong because enabling 'Auto date/time' generates hidden date hierarchies automatically, which does not create a dedicated date dimension table in Power Query and does not meet the requirement for a custom M-based date table covering 2010 to 2030. Option C is wrong because creating a relationship between PatientVisits[VisitDate] and DiagnosisLookup[DiagnosisCode] is semantically incorrect; VisitDate is a date field and DiagnosisCode is a code field, and the correct relationship should be between PatientVisits[DiagnosisCode] and DiagnosisLookup[DiagnosisCode] to link the fact table to the diagnosis dimension.

Practice this question →

144

Multi-Selectmedium

You are a Power BI developer at a healthcare organization. You are building a report that must comply with HIPAA regulations. You need to ensure that patient data is not exposed to unauthorized users. You plan to use Row-Level Security (RLS) with roles defined in Power BI Desktop. However, you also need to limit the data imported into the model to only necessary columns. The source is an Azure SQL Database with a table 'Patients' containing columns: PatientID, Name, SSN, Diagnosis, AdmissionDate, DischargeDate. Which two actions should you take? (Choose TWO)

Select 2 answers

A.Create RLS roles to restrict access by Diagnosis.

B.Store data source credentials in the Power BI service.

C.Disable query caching for the dataset.

D.Use encrypted connection to the database.

E.Remove the SSN and Name columns in Power Query before loading.

AnswersA, E

Creating row-level security (RLS) roles with a DAX filter on the Diagnosis field dynamically restricts which rows each user or role can view. This ensures that a user assigned to a specific role only sees patient records matching authorized diagnoses, directly preventing unauthorized data exposure at the row level. RLS is evaluated at query time in the Power BI service, making it a robust access-control mechanism that works regardless of how the report is accessed.

Why this answer

Option A is correct because defining RLS roles in Power BI Desktop and mapping them to users in the Power BI service enforces row-level filtering so that each user only sees the patient rows they are authorized to view, which is a core HIPAA minimum-necessary access control. Option E is correct because removing SSN and Name in Power Query before loading implements data minimization, ensuring sensitive identifiers are never imported into the semantic model where they could be exposed. Option B is not correct because storing credentials in the Power BI service is a connectivity/authentication practice, not a data-exposure control for this scenario.

Option C is not correct because disabling query caching does not restrict which data is imported or who can see it. Option D is not correct because an encrypted connection protects data in transit but does not limit imported columns or enforce row-level access for report consumers.

Exam trap

The trap here is that candidates may confuse data security measures (like encrypted connections or credential storage) with data minimization and access control, leading them to select options that protect data in transit or enable refresh but do not directly limit imported columns or enforce row-level filtering.

Practice this question →

145

MCQeasy

You are importing data from a folder containing multiple CSV files with the same structure. You want to combine all files into a single table, but only include files that have been modified in the last 7 days. What Power Query transformation should you use?

A.Use 'Sample File' and manually filter

B.Filter the folder contents by 'Date Modified' before combining

C.Use 'Merge Queries' with the folder

D.Combine files using 'Combine & Load', then filter by date modified

AnswerB

Filtering the folder contents by 'Date Modified' before combining is correct because the folder query in Power Query contains metadata (including 'Date Modified') for each file before any binary content is read. By filtering this file-list table, you prune the set of files that the 'Combine Files' transformation will process, so only recent files are expanded and loaded. This is an efficient streaming-like approach that minimizes data extraction and refresh time.

Why this answer

Option B is correct because Power Query's Folder data source exposes file metadata columns such as Date modified, and applying a filter on that column before invoking the Combine Files transformation restricts the combined table to only files modified within the last 7 days. This keeps the import efficient and ensures the resulting single table contains only the desired recent files. Option A is wrong because 'Sample File' merely defines the file structure and does not filter by modification date.

Option C is wrong because 'Merge Queries' joins two existing queries on matching keys, not folder file metadata. Option D is wrong because filtering after combining would still load all files first, and the Date modified column is not retained in the combined result.

Practice this question →

146

MCQmedium

Refer to the exhibit. You are reviewing a DAX measure in Power BI. The measure is intended to calculate total sales for the year 2024. However, when used in a visual with a slicer on 'Sales[Date]', the measure does not respect the slicer selection. What is the most likely reason?

A.The FILTER function overrides the slicer filter context.

B.The CALCULATE function removes all filters by default.

C.The measure should use ALL(Sales[Date]) to respect slicers.

D.The DATE function syntax is incorrect.

AnswerA

In DAX, CALCULATE modifies the filter context by applying its filter arguments. When FILTER is used as a filter argument, it creates a new filter on the Sales table that replaces any existing filters on that table, including those from a slicer. This replacement behavior is by design: filter arguments in CALCULATE override existing filters on the same columns or tables. Therefore, the slicer's context is ignored, and the measure returns values based solely on the FILTER condition.

Why this answer

The FILTER function in the measure creates a new filter context that overrides the existing slicer filter context on 'Sales[Date]'. When CALCULATE evaluates the expression, it applies the FILTER as a table modifier, which replaces any external filters on the Date column, causing the slicer to be ignored. This is a common DAX behavior where explicit filter arguments in CALCULATE take precedence over existing filter contexts.

Exam trap

The trap here is that candidates often assume CALCULATE always respects slicers, but they miss that explicit filter arguments (like FILTER) override external filters on the same columns, leading to the slicer being ignored.

How to eliminate wrong answers

Option B is wrong because CALCULATE does not remove all filters by default; it only modifies the filter context based on its filter arguments, and without a REMOVEFILTERS or ALL function, it preserves existing filters. Option C is wrong because using ALL(Sales[Date]) would remove the slicer filter entirely, making the measure ignore the slicer even more, not respect it; to respect slicers, you should not use ALL or should use KEEPFILTERS. Option D is wrong because the DATE function syntax (DATE(2024,1,1) and DATE(2024,12,31)) is correct and would not cause the measure to ignore slicer selections.

Practice this question →

147

MCQmedium

Your organization uses Power BI to analyze sales data stored in Azure SQL Database. The data model includes a fact table with millions of rows. To improve performance, you need to reduce the amount of data loaded into the model. Which action should you take?

A.Apply row-level filters in Power Query to import only relevant rows

B.Use calculated tables in DAX to summarize data

C.Disable the Auto Date/Time feature

D.Configure incremental refresh with a date filter

AnswerA

Row-level filtering in Power Query (M) is applied before data enters the VertiPaq engine, enabling you to import only the rows that are relevant to your analysis. This reduces the number of rows materialized in the model, which directly shrinks the overall memory footprint and improves refresh time, query performance, and columnar compression ratios. Because only the necessary data is loaded, all downstream measures and reports run over a targeted dataset, making this the most effective way to reduce the initial data load size.

Why this answer

Applying row-level filters in Power Query reduces the volume of data imported into the Power BI model by only loading rows that meet specific criteria. This directly minimizes the data footprint in memory, improving query and refresh performance, especially for fact tables with millions of rows stored in Azure SQL Database.

Exam trap

The trap here is that candidates often confuse incremental refresh with reducing the initial data load, but incremental refresh only optimizes refresh cycles over time and does not limit the first full load unless combined with a date filter in Power Query.

How to eliminate wrong answers

Option B is wrong because calculated tables in DAX are created after data is loaded into the model, so they do not reduce the amount of data imported; they can even increase memory usage by duplicating or aggregating data. Option C is wrong because disabling the Auto Date/Time feature reduces the number of hidden date tables generated by Power BI, which improves model size and performance, but it does not reduce the amount of data loaded from the source. Option D is wrong because incremental refresh with a date filter partitions data for refresh scheduling and reduces the volume of data refreshed each time, but it still requires the full historical data to be loaded initially unless combined with a filter that limits the initial load; the question asks specifically about reducing the amount of data loaded into the model, and incremental refresh alone does not achieve that without additional filtering.

Practice this question →

148

MCQeasy

You are preparing data for a Power BI report. You have a query that connects to a REST API that returns JSON data. The JSON response contains a nested array of order details under an 'Orders' field. You need to expand the nested array so that each order becomes a row in the table. What should you do in Power Query?

A.Use the 'Group By' transformation on the 'Orders' column.

B.Use the 'Split Column' transformation by a delimiter.

C.Use the 'Expand' button on the 'Orders' column and select all fields.

D.Use the 'Unpivot Columns' transformation on the 'Orders' column.

AnswerC

The 'Expand' button on a column containing structured values (like a nested array) allows you to expand the array elements into new rows, effectively flattening the nested data. This is the correct way to turn each order into a row. Selecting all fields ensures all order details are included. This transformation is standard when working with JSON or XML data in Power Query.

Why this answer

Expanding the nested array column is the correct method to flatten JSON data in Power Query. The 'Expand' button appears when a column contains structured values such as lists or records. Expanding a list column creates a new row for each element, which is exactly what is needed to turn each order into a row.

Other transformations like unpivot, split, or group by do not handle nested structures and would produce incorrect results.

Exam trap

The trap here is confusing the Expand operation with Unpivot, as both can create additional rows but serve different purposes.

Practice this question →

149

Multi-Selectmedium

Which TWO actions can help reduce the size of a Power BI dataset when preparing data?

Select 2 answers

A.Include all historical data

B.Aggregate transaction data to daily level

C.Add calculated columns

D.Remove columns that are not used in reports

E.Use DirectQuery mode

AnswersB, D

Aggregating transaction data to a daily level reduces the number of rows imported into the model, which directly shrinks the compressed columnar storage. For example, millions of individual sales rows become a few thousand daily totals, and lower cardinality in date and measure columns yields better compression. This is an effective size-reduction technique when daily granularity satisfies your reporting needs.

Why this answer

Option B is correct because aggregating transaction-level rows into daily summaries collapses many individual records into far fewer rows, directly shrinking the dataset's row count and memory footprint in the Power BI model. Option D is correct because removing unused columns reduces the model's columnar cardinality and compression overhead, since Power BI's VertiPaq engine stores and compresses each column separately, so eliminating unnecessary columns lowers overall model size. Option A is incorrect because retaining all historical data increases row volume and model size rather than reducing it.

Option C is incorrect because calculated columns are materialized and stored in the model, adding to its size instead of shrinking it. Option E is incorrect because DirectQuery does not reduce dataset size—it leaves data in the source and queries it on demand, and it is a connectivity mode rather than a data-reduction technique.

Exam trap

Microsoft often tests the misconception that adding calculated columns is a harmless transformation, but in reality, they increase dataset size because they are stored as new columns in the VertiPaq engine.

Practice this question →

150

MCQhard

You are merging two queries in Power Query: 'Orders' and 'Customers'. The 'Orders' table has a 'CustomerID' column, and 'Customers' has 'CustomerID' and 'Name'. You need to bring the 'Name' into 'Orders' but only for matching CustomerIDs; unmatched rows should be removed. Which join kind should you use?

A.Right Anti

B.Full Outer

C.Inner

D.Left Outer

AnswerC

Inner join retains only rows where a matching key exists in both tables—so an order is included only if it has a corresponding customer, and customers appear only if they have at least one order. This produces exactly the intersection of the two queries, which is the standard approach for combining transactional and dimensional data to analyze completed order-customer pairs.

Why this answer

The Inner join kind in Power Query returns only rows where there is a match in both tables based on the key columns. Since the requirement is to bring the 'Name' into 'Orders' only for matching CustomerIDs and to remove unmatched rows, the Inner join is the correct choice. It ensures that only orders with a corresponding customer in the 'Customers' table are retained, and the 'Name' column is added to those matching rows.

Exam trap

The trap here is that candidates often confuse Left Outer join with Inner join, thinking that 'bringing in data only for matches' means keeping all left rows, but Left Outer retains unmatched left rows with nulls, while Inner removes them entirely.

How to eliminate wrong answers

Option A is wrong because Right Anti join returns only rows from the right table that have no match in the left table, which would exclude all matching rows and is the opposite of what is needed. Option B is wrong because Full Outer join returns all rows from both tables, including unmatched rows from each side, which would keep orders without a matching customer and introduce nulls, violating the requirement to remove unmatched rows. Option D is wrong because Left Outer join returns all rows from the left table (Orders) and only matching rows from the right table (Customers), which would keep orders without a matching customer (with null in Name), not removing unmatched rows as required.

Practice this question →