Courseiva

CCNA Prepare the data Questions

75 of 171 questions · Page 1/3 · Prepare the data · Answers revealed

Practice these questionsDomain overviewAll questions
1
MCQhard

You are designing a Power BI solution for a retail company. The data includes point-of-sale transactions with columns: TransactionID, StoreID, ProductID, Quantity, SalesAmount, TransactionDate. The company wants to analyze sales by hour of day. What is the best way to prepare the time dimension?

A.Add a calculated column extracting hour from TransactionDate in the fact table
B.Create a date dimension only and ignore time
C.Use DAX measures to extract hour on the fly
D.Create a separate Time dimension table with a one-to-many relationship to the fact table
AnswerD

Creating a separate Time dimension table with a TimeKey (for example, an integer 0–86399 representing seconds since midnight) and attributes such as hour, half-hour slot, and time band, then relating it to the fact table via a one-to-many relationship, gives every measure a consistent, reusable way to slice and drill by time of day. This follows star-schema best practice: the dimension stores the descriptive time attributes, the fact only stores the key, and users can navigate from hour to minute or across time periods without custom DAX. It fully supports the retail requirement of analyzing sales by hour while integrating cleanly with the date dimension.

Why this answer

Creating a separate Time dimension table with a one-to-many relationship to the fact table follows the star schema best practice for Power BI. This approach allows efficient filtering and grouping by hour without bloating the fact table with calculated columns, and it supports time-based analysis across multiple fact tables if needed. The Time dimension can include columns like Hour, HourBucket, or PeriodOfDay, enabling intuitive slicers and drill-downs.

Exam trap

The trap here is that candidates often think extracting the hour via a calculated column (Option A) is simpler and sufficient, but they overlook the performance and scalability benefits of a proper star schema with a separate Time dimension, which is a core concept tested in PL-300.

How to eliminate wrong answers

Option A is wrong because adding a calculated column to the fact table to extract hour increases storage and processing overhead, and it violates star schema principles by mixing dimensions into the fact table. Option B is wrong because ignoring time entirely fails to meet the requirement to analyze sales by hour of day, as a date-only dimension cannot support hourly granularity. Option C is wrong because using DAX measures to extract hour on the fly is inefficient for repeated calculations in visuals, can degrade performance with large datasets, and prevents the use of time-based hierarchies or slicers.

Practice this question →
2
MCQeasy

You have a Power BI dataset that uses Import mode and refreshes daily. The source data includes a column 'LastModifiedDate'. You want to reduce the amount of data loaded during each refresh by only loading rows that have changed since the last refresh. Which feature should you configure?

A.Enable query folding in Power Query.
B.Use the 'Reduce data' option in Power Query Editor.
C.Change the storage mode to DirectQuery.
D.Configure incremental refresh on the table using the 'LastModifiedDate' column.
AnswerD

Incremental refresh creates named date/time range partitions on a table and, during refresh, loads only new or modified partitions (e.g., the last N days) rather than the entire table. By using a 'LastModifiedDate' column, the refresh engine can detect which rows have changed and only refresh those historical partitions, dramatically reducing refresh time and data transfer. This requires using a Refresh Policy in Power Query (e.g., RangeStart and RangeEnd parameters) and the table must have a date/time column that records when each row was last modified.

Why this answer

Incremental refresh in Power BI allows you to filter data by a date/time column (such as 'LastModifiedDate') so that only rows that have changed since the last refresh are loaded. This reduces refresh time and data volume while still using Import mode. The feature requires a date/time column and a properly configured policy in the Power Query Editor.

Exam trap

The trap here is that candidates often confuse incremental refresh with query folding or 'Reduce data' options, not realizing that incremental refresh is the only feature designed to load only changed rows in Import mode while keeping the dataset in Import mode.

How to eliminate wrong answers

Option A is wrong because query folding pushes transformations back to the source database, but it does not selectively load only changed rows; it still processes the entire dataset. Option B is wrong because 'Reduce data' is not a built-in Power Query Editor feature; the correct option for reducing loaded data is incremental refresh. Option C is wrong because changing storage mode to DirectQuery avoids importing data entirely, but the question specifies Import mode and wants to reduce data loaded during refresh, not switch to a live query mode.

Practice this question →
3
Multi-Selectmedium

You are transforming a table that contains a 'Date' column in text format (e.g., '2026-01-15'). You need to create separate columns for Year, Month, and Day. Which THREE Power Query transformations can you use? (Choose three.)

Select 3 answers
A.Unpivot the Date column.
B.Split Column by Delimiter using '-' as the delimiter.
C.Use the Date.Year, Date.Month, Date.Day functions in a custom column.
D.Merge the Date column with itself.
E.Use the Extract feature to extract first 4 characters for Year, then subsequent characters.
AnswersB, C, E

Using Split Column by Delimiter with '-' assumes the Date column contains text values such as '2025-04-09'. In the Power Query editor, select the column, choose Split Column > By Delimiter, specify '-' as the custom delimiter, and split 'Each occurrence of the delimiter' to create three separate columns. After splitting, you should rename those columns to Year, Month, and Day and change their data types to Whole Number or Text as appropriate. This is a direct and effective method when the format is consistently ISO-like.

Why this answer

Option B is correct because the text dates use a consistent '-' delimiter, so Split Column by Delimiter with '-' produces three separate columns that can be renamed Year, Month, and Day. Option C is correct because a custom column can call Date.Year, Date.Month, and Date.Day (after converting the text to a date type with Date.FromText or a type change) to derive each component reliably. Option E is correct because the Extract feature can pull fixed character ranges from the text, e.g., the first 4 characters for Year and subsequent characters for Month and Day, which works for the ISO-style 'yyyy-MM-dd' format.

Option A is not appropriate because Unpivot rotates columns into attribute-value rows rather than creating separate Year, Month, and Day columns. Option D is not appropriate because merging a column with itself is a join operation that does not split or extract date parts.

Exam trap

Microsoft often tests the distinction between splitting a column by delimiter versus using date functions, where candidates may incorrectly choose Unpivot (Option A) thinking it 'unpacks' data, but it actually normalizes columns into rows.

Practice this question →
4
MCQhard

Refer to the exhibit. You are reviewing a Power Query script. The script fails with a 'DataSource.Error: Microsoft SQL: Login failed for user' error. Which step should you check first?

A.The column name in #"Removed Columns".
B.The data type transformation in #"Changed Type".
C.The filter condition in #"Filtered Rows".
D.The credentials used in the Source step.
AnswerD

The credentials used in the Source step are the root cause: the 'Login failed' error indicates the authentication attempt to the underlying data source was rejected. In Power Query, each data source stores its own credentials, and during refresh the engine retrieves them and passes them to the provider; if they are wrong, expired, or lack the required permissions, the connection fails immediately. This error surfaces before any subsequent query steps execute, so correcting or re-entering the credentials in the data source settings is the appropriate resolution.

Why this answer

The error 'DataSource.Error: Microsoft SQL: Login failed for user' indicates an authentication failure when connecting to the SQL Server database. This occurs at the Source step, where Power Query first attempts to establish a connection using the provided credentials. Checking and correcting the credentials in the Source step is the logical first step because no subsequent data transformations (like removing columns, changing types, or filtering rows) can execute if the initial data source connection fails.

Exam trap

The trap here is that candidates may focus on data transformation steps (like removing columns or changing types) because they appear later in the query, but the error originates at the very first step—the Source step—where authentication is validated before any data is retrieved.

How to eliminate wrong answers

Option A is wrong because the 'Removed Columns' step operates on columns that already exist in the data; a login failure prevents any data from being loaded, so column removal is irrelevant. Option B is wrong because 'Changed Type' transforms data types after data is successfully loaded; it cannot cause a connection-level authentication error. Option C is wrong because 'Filtered Rows' applies row-level filtering on existing data; it has no effect on the database connection or authentication process.

Practice this question →
5
MCQmedium

You are a data analyst for a retail company. You have a Power BI semantic model that imports data from an on-premises SQL Server database using an on-premises data gateway. The dataset contains a table named Products with a column named Category. You need to ensure that when users open a report, they only see data for the categories they are authorized to view. You have a table named UserCategories that maps user principal names (UPNs) to categories. What should you do?

A.Create a role in the semantic model, add a DAX filter on the Products table using LOOKUPVALUE to match the Category to the UserCategories table based on USERPRINCIPALNAME().
B.Use Power BI Desktop to create a calculated table that filters Products based on the current user, and then hide the original Products table.
C.Implement row-level security in the on-premises SQL Server database by creating a security policy and mapping users to categories.
D.Create a role in the semantic model and add a static DAX filter on the Products table for each category, such as [Category] = "Electronics".
AnswerA

Creating a role with a DAX filter that uses USERPRINCIPALNAME() and LOOKUPVALUE allows dynamic row-level security. The filter compares the category of each row to the categories assigned to the current user in the UserCategories table, ensuring users only see authorized data.

Why this answer

Row-level security in Power BI is implemented by creating roles and defining DAX filter expressions on tables. To dynamically restrict data per user, the filter should use USERPRINCIPALNAME() to identify the current user and LOOKUPVALUE to retrieve the categories assigned to that user from the UserCategories table. This ensures each user sees only their authorized categories.

Exam trap

The trap here is confusing static role filters with dynamic row-level security, or attempting to enforce security at the data source instead of within the Power BI semantic model.

Practice this question →
6
Multi-Selecthard

Which THREE actions in Power Query Editor can improve the performance of data refresh? (Select three.)

Select 3 answers
A.Sort data in ascending order to improve compression.
B.Merge queries before filtering.
C.Remove columns that are not used in the report.
D.Disable the 'Enable load' option for intermediate tables that are not needed in the model.
E.Filter rows as early as possible in the query.
AnswersC, D, E

Removing columns that are not used in the report reduces the data volume that must be evaluated, compressed, and stored in the VertiPaq engine, directly shrinking the model size and refresh duration. Every column, even an unused one, requires space for its dictionary and encoded segments, so eliminating irrelevant columns cuts memory overhead and can speed up query performance by reducing the number of column segments to scan. This practice also simplifies the query editor view and prevents accidental inclusion of sensitive or duplicate data in the model.

Why this answer

Option C is correct because removing unused columns reduces the volume of data that Power Query must read, transform, and load into the model, lowering memory and refresh cost. Option D is correct because disabling 'Enable load' on intermediate queries prevents those staging tables from being materialized into the data model, so refresh avoids loading data that no report consumes. Option E is correct because applying filters as early as possible (ideally at the source or in the first steps) reduces the number of rows flowing through subsequent transformation steps, which is the most effective way to speed up refresh.

Option A is not correct because sorting rows does not improve refresh performance in Power Query; compression benefits in the VertiPaq engine come from column cardinality and encoding, not from row order. Option B is not correct because merging queries before filtering typically increases the work done on larger, unfiltered datasets; filtering first reduces the rows that must be joined.

Exam trap

The trap here is that candidates often think sorting improves compression (a common misconception from database indexing), but in Power BI, compression is handled by the VertiPaq engine and is not influenced by the order of data in Power Query.

Practice this question →
7
Multi-Selecthard

You are developing a Power BI semantic model that uses a large fact table from Azure Synapse Analytics. You need to optimize the model for performance. Which THREE actions should you take?

Select 3 answers
A.Disable auto-date/time feature for the model.
B.Use integer surrogate keys instead of string keys for dimensions.
C.Combine the fact table with dimension tables into a single wide table.
D.Create calculated columns in the fact table instead of in Power Query.
E.Remove unnecessary columns from the fact table.
AnswersA, B, E

Disabling the auto-date/time feature prevents Power BI from generating hidden date tables for every date column in the model. These hidden tables add significant memory and storage overhead, especially when you have many date columns. By turning the feature off, you reduce the model's size and refresh time, and you can instead create a single explicit date table if you need date logic.

Why this answer

Option A is correct because disabling the Auto date/time feature prevents Power BI from creating hidden date tables for every date column, which reduces model size and memory consumption and speeds up processing and refresh. Option B is correct because integer surrogate keys compress far better in the VertiPaq engine than string keys, lowering memory usage and improving join and relationship performance. Option E is correct because removing unnecessary columns from the fact table reduces the model's memory footprint and improves compression and query performance.

Option C is not appropriate because combining the fact table with dimensions into a single wide table denormalizes the star schema, increases storage and redundancy, and typically degrades performance rather than optimizing it. Option D is not appropriate because calculated columns are computed at refresh time and consume memory, whereas transformations in Power Query are performed during load and are generally preferred for performance.

Exam trap

The trap here is that candidates often think combining tables into a wide table simplifies the model, but this actually degrades performance by breaking star schema design principles, which are critical for efficient query processing in Power BI.

Practice this question →
8
Multi-Selectmedium

You are creating a Power BI report from a SQL Server database that contains a table Orders with columns: OrderDate, CustomerID, ProductID, Quantity, UnitPrice. You need to build a star schema. Which THREE tables should you create? (Choose three.)

Select 3 answers
A.OrderDetails table with line items.
B.Date dimension table with date attributes.
C.Product dimension table with product attributes.
D.Customer dimension table with customer attributes.
E.Sales fact table with measures.
AnswersB, C, D

A Date dimension table is essential for time intelligence in Power BI, as it provides a contiguous set of dates with attributes like year, quarter, month, and week. By marking it as the date table, DAX functions such as TOTALYTD and SAMEPERIODLASTYEAR can perform time-based calculations correctly. Without a dedicated Date dimension, filtering by fiscal periods or comparing periods across years becomes unreliable, especially if the fact table has gaps in dates.

Why this answer

In a star schema, dimension tables contain descriptive attributes (e.g., dates, products, customers) and are connected to a central fact table. For the Orders table, a Date dimension (B) is essential for time-based analysis, a Product dimension (C) provides product details, and a Customer dimension (D) stores customer attributes. These three dimensions normalize the data and enable efficient slicing and dicing in Power BI.

Exam trap

The trap here is that candidates often confuse dimension tables with fact tables or think that line-item details (Option A) should be a separate dimension, when in fact they belong in the fact table to maintain a star schema's simplicity and performance.

Practice this question →
9
MCQeasy

You have a Power BI dataset that uses data from Microsoft Excel files stored in SharePoint Online. Users report that the data is not refreshing as scheduled. You verify that the gateway is installed and running. What is the most likely cause of the refresh failure?

A.The gateway is not running.
B.The gateway does not support SharePoint Online data sources.
C.The gateway is not configured to use the on-premises data source type.
D.The data source credentials are not provided in the gateway.
AnswerD

This is the correct diagnosis: the gateway is running, but the SharePoint Online data source lacks stored credentials in the gateway's data source management settings. Without those credentials, the gateway cannot authenticate to SharePoint Online during a scheduled refresh, resulting in a failure. You need to add the data source and fill in the appropriate authentication method (e.g., OAuth2) to resolve it.

Why this answer

Even when the gateway is installed and running, it must have valid data source credentials configured for the SharePoint Online Excel files. Without these credentials, the gateway cannot authenticate to SharePoint Online to retrieve the data, causing the scheduled refresh to fail. The gateway uses the stored credentials to connect to the data source during each refresh cycle.

Exam trap

The trap here is that candidates assume a running gateway automatically handles all data sources, but the gateway requires explicit credential configuration for each data source, including cloud-based ones like SharePoint Online.

How to eliminate wrong answers

Option A is wrong because the question explicitly states that the gateway is installed and running, so this cannot be the cause. Option B is wrong because the on-premises data gateway fully supports SharePoint Online as a data source when configured correctly, as it can connect to cloud services via the gateway's cloud-to-on-premises bridging. Option C is wrong because SharePoint Online is a cloud-based data source, not an on-premises data source type; the gateway handles cloud data sources like SharePoint Online through its standard cloud connector configuration, not through an on-premises data source type.

Practice this question →
10
Multi-Selectmedium

You are importing data from a SQL Server database. The source table has a column 'ModifiedDate' of type datetime2. In Power Query, you want to ensure that only rows modified within the last 7 days are loaded. Which THREE steps should you take?

Select 3 answers
A.Load all rows and then use a DAX filter in the data model.
B.Use a parameter for the date range and reference it in the filter.
C.Split the column into date and time and then filter on the date part.
D.In Power Query, add a filter step using a custom column or the filter row feature.
E.Use a native SQL query with a WHERE clause to filter at the source.
AnswersB, D, E

A Power Query parameter, such as a date range or a scalar date value, can be referenced directly in the filter row step of a query. This makes the filter dynamic and easy to update without editing M code, and when the source is a SQL database, the filter step often folds into the generated SQL statement, reducing the amount of data imported. Parameterizing the date also supports scheduled refresh scenarios, where the filter value can be changed via the API or in the service, and it avoids hard-coded values scattered across steps.

Why this answer

Using a parameter for the date range and referencing it in the filter allows for dynamic, maintainable filtering in Power Query. This approach leverages Power Query's M language to apply a filter step that can be easily updated without modifying the query logic, ensuring only rows from the last 7 days are loaded during data refresh.

Exam trap

The trap here is that candidates often think splitting a datetime column is necessary for date-based filtering, but Power Query's native filter on datetime2 works correctly and is more efficient, while loading all rows and using DAX is a common anti-pattern that wastes resources.

Practice this question →
11
MCQmedium

You have a Power BI semantic model that uses DirectQuery to an Azure Synapse Analytics dedicated SQL pool. The model is used by a real-time dashboard. Users report that the dashboard is slow. You need to improve query performance without changing the source system. Which action should you take?

A.Create aggregations on the fact table
B.Reduce the number of visuals on the dashboard and apply page-level filters
C.Enable dual storage mode for all tables
D.Disable the 'Reduce queries' option in Power BI Desktop
AnswerB

Reducing the number of visuals on a DirectQuery dashboard directly reduces the number of separate DAX queries that Power BI sends to the underlying data source, because each visual in DirectQuery mode issues its own live query when rendered. Applying page-level filters constrains the rowset returned for all visuals on that page, which lowers the data volume and speeds up each query. Together these actions are a simple, immediate way to lower query load without changing the data model.

Why this answer

Reducing the number of visuals and applying page-level filters directly reduces the number of queries sent to the Azure Synapse Analytics dedicated SQL pool via DirectQuery. Since the source system cannot be changed, the only way to improve performance is to minimize the query load from the dashboard. Page-level filters ensure that only relevant data is queried, and fewer visuals mean fewer separate queries, which collectively reduces latency.

Exam trap

The trap here is that candidates often assume performance improvements must come from data modeling changes (like aggregations or storage modes), but the question explicitly forbids changing the source system, so the only viable approach is to reduce the query load from the client side.

How to eliminate wrong answers

Option A is wrong because creating aggregations on the fact table would require modifying the source system (the Azure Synapse Analytics dedicated SQL pool), which is explicitly prohibited by the question. Option C is wrong because enabling dual storage mode for all tables would force some tables to import data into memory, which changes the storage mode and violates the constraint of not changing the source system; moreover, dual storage mode can increase complexity and may not improve performance for DirectQuery models. Option D is wrong because disabling the 'Reduce queries' option in Power BI Desktop would actually increase the number of queries sent to the source, worsening performance; this option is designed to reduce query redundancy, so disabling it is counterproductive.

Practice this question →
12
MCQeasy

You are connecting to an Azure SQL Database from Power BI Desktop. The database contains a view that returns thousands of rows. You only need the last 100 rows for analysis. What is the most efficient way to reduce the data loaded?

A.Write a native SQL query with a WHERE clause to limit rows
B.Use DirectQuery mode and add a filter in the report
C.Import all rows and then remove rows in Power Query
D.Use the 'Keep Top Rows' transformation in Power Query after applying a sort
AnswerD

Applying a sort step followed by Keep Top Rows in Power Query is the optimal approach because the M engine can fold these transformations into a single SELECT TOP (N) ORDER BY statement on Azure SQL. This ensures that only the top N rows are fetched from the database, drastically reducing data transfer and load time. Additionally, the transformation remains declarative and reusable, and it aligns with best practices for source-side filtering in Power BI.

Why this answer

It uses Power Query's 'Keep Top Rows' transformation after sorting the view by the desired order (e.g., descending on a date column). This approach pushes the sort and row-limiting logic to the source database via query folding, ensuring only the last 100 rows are transferred over the network, which is the most efficient method for reducing data loaded.

Exam trap

The trap here is that candidates may think a native SQL query (Option A) is always the most efficient, but they overlook that Power Query's query folding can achieve the same result with better integration and maintainability, while a poorly written SQL query without proper sorting would not correctly retrieve the 'last' rows.

How to eliminate wrong answers

Option A is wrong because writing a native SQL query with a WHERE clause to limit rows does not guarantee you get the 'last' 100 rows unless you also specify an ORDER BY clause; a WHERE clause alone cannot select the last rows without a deterministic sort order, and it may require complex subqueries that are less efficient than query folding. Option B is wrong because DirectQuery mode does not reduce the data loaded into Power BI; it sends queries to the database on demand, but the filter is applied at query time, not during data loading, and it does not minimize the initial data transfer for the view—it still requires the database to process the full view before applying the filter. Option C is wrong because importing all rows and then removing rows in Power Query is inefficient; it transfers the entire dataset (thousands of rows) over the network and into memory, defeating the purpose of reducing data loaded.

Practice this question →
13
MCQhard

You have a Power BI semantic model that uses Import mode with a SQL Server data source. The refresh takes over two hours. You need to reduce the refresh time while keeping data up-to-date. What is the best strategy?

A.Switch the data source to DirectQuery mode.
B.Remove unnecessary columns and rows from the query.
C.Configure incremental refresh policy on the fact table.
D.Reduce the scheduled refresh frequency to once a day.
AnswerC

Configuring incremental refresh on the fact table is the correct approach because it partitions data by date using RangeStart and RangeEnd parameters, so only new and updated rows are loaded during each scheduled refresh. This dramatically cuts the data processed per refresh cycle, especially for a fact table accumulating transactional data over time. It requires at least one date column and enables the 'refresh policy' capability in Power BI to manage historical and current partitions separately.

Why this answer

Incremental refresh policy allows you to refresh only the most recent data (e.g., last 5 days) while keeping historical partitions unchanged, drastically reducing the amount of data loaded during each refresh. This is the most effective way to reduce refresh time in Import mode while maintaining data freshness, as it avoids re-querying the entire fact table from SQL Server.

Exam trap

The trap here is that candidates often confuse reducing refresh frequency (Option D) with reducing refresh time, or they think DirectQuery (Option A) is a universal performance fix, when in fact it shifts the performance burden to query time and sacrifices Import mode capabilities.

How to eliminate wrong answers

Option A is wrong because switching to DirectQuery mode would eliminate the import process but would introduce query-time performance issues and remove the ability to use many Power BI features (e.g., time intelligence, calculated tables); it does not reduce refresh time but changes the data access model entirely. Option B is wrong because removing unnecessary columns and rows is a general optimization that can help, but it does not address the core issue of a large fact table that takes over two hours to refresh; the primary bottleneck is the volume of historical data, not just extraneous fields. Option D is wrong because reducing scheduled refresh frequency to once a day would not reduce the refresh time itself; it only makes the data less current, which contradicts the requirement to keep data up-to-date.

Practice this question →
14
Multi-Selecthard

Which TWO are valid reasons to use a dataflow in Power BI when preparing data?

Select 2 answers
A.To handle large data volumes that exceed dataset limits
B.To reuse the same transformed data across multiple datasets
C.To avoid using an on-premises data gateway
D.To achieve real-time data refresh
E.To automatically enforce data lineage
AnswersA, B

Dataflows are a valid choice for large data volumes because they perform transformations in the cloud using Power Query and can store processed output in Azure Data Lake, allowing you to reduce raw data size before it ever reaches a dataset. For example, you can filter, aggregate, or remove unwanted columns so the final dataset remains within Power BI's 1-GB Pro or larger Premium capacity limits. Dataflows also support enhanced compute and incremental refresh, which help process billions of rows without hitting the same bottlenecks as direct import.

Why this answer

Option A is correct because dataflows store transformed data in Common Data Model (CDM) entities in Azure Data Lake Storage Gen2, so they can process and persist large data volumes that would otherwise exceed a single dataset's size or memory limits. Option B is correct because a dataflow's CDM entities can be consumed by multiple Power BI datasets, letting you define the transformation logic once and reuse the same cleansed data across many datasets instead of duplicating ETL work. Option C is not a valid reason, since dataflows that connect to on-premises sources still require an on-premises data gateway; dataflows do not eliminate that need.

Option D is not correct because dataflows are refreshed on a scheduled (batch) basis and are not a mechanism for real-time streaming, which is handled by streaming datasets or push datasets. Option E is not correct because data lineage is a metadata/governance feature surfaced automatically in the workspace, not something a dataflow is used to enforce.

Exam trap

The trap here is that candidates often confuse dataflows with streaming datasets or assume that cloud-based dataflows bypass all gateway requirements, but in reality, on-premises data sources still need a gateway, and dataflows do not support real-time refresh.

Practice this question →
15
MCQhard

You are a Power BI data analyst at a healthcare organization. You import patient encounter data from an Azure Synapse Analytics dedicated SQL pool. The data contains a column named EncounterDate of type datetime2. You need to create a Power Query transformation that adds a column showing the fiscal year, which starts on July 1. The fiscal year should be labeled as FY2024 for dates from July 1, 2023, through June 30, 2024. Which transformation should you use?

A.Add a custom column with the formula Date.Year([EncounterDate]) and concatenate 'FY' with the result.
B.Add a custom column with the formula Date.Year(Date.AddMonths([EncounterDate], 6)) and concatenate 'FY' with the result.
C.Use the Date.StartOfYear function with a parameter of 7 to get the fiscal year start date, then extract the year.
D.Add a custom column with the formula Date.AddMonths([EncounterDate], -6) and then extract the year.
AnswerB

Adding six months shifts July 1 to January 1 of the next calendar year, so Date.Year returns the fiscal year number. For example, July 1, 2023 becomes January 1, 2024, and Date.Year yields 2024, producing FY2024. This correctly labels the fiscal year starting July 1.

Why this answer

To calculate a fiscal year that starts on July 1, you must shift the date by six months before extracting the year. The transformation Date.Year(Date.AddMonths([EncounterDate], 6)) correctly maps July 1 through June 30 to the appropriate fiscal year number, which can then be prefixed with 'FY' to match the required label format.

Exam trap

The trap here is using the calendar year or subtracting months instead of adding months, which misclassifies dates that fall in the second half of the calendar year but belong to the next fiscal year.

Practice this question →
16
Multi-Selecthard

Which THREE factors should you consider when designing an incremental refresh policy for a large fact table in Power BI?

Select 3 answers
A.The table must be configured for DirectQuery mode
B.The source data must be stored in a cloud database
C.The refresh policy must consider the data warehouse's maintenance windows
D.The table must be partitioned in the Power BI model
E.The source table must include a date or datetime column for filtering
AnswersC, D, E

Scheduling the refresh policy must align with the data warehouse's maintenance windows to avoid failures. During maintenance, the source database may be unavailable, have locked tables, or experience degraded performance, which can cause the incremental refresh to time out or return incomplete data. By scheduling refreshes outside these windows, you ensure source connectivity and consistent data loads, making this a valid design consideration.

Why this answer

Option C is correct because an incremental refresh policy must be scheduled so that its refresh windows do not collide with the data warehouse's maintenance windows, since the source must be available and consistent when Power BI queries it for new or changed partitions. Option D is correct because incremental refresh works by partitioning the table in the Power BI model (typically by RangeStart/RangeEnd parameters on a date column), so only the newest partitions are refreshed while historical partitions are retained. Option E is correct because incremental refresh requires a date or datetime column in the source table to filter and define the partition boundaries; without it, Power BI cannot determine which rows are new or changed.

Option A is not required, since incremental refresh is supported for Import mode (and DirectQuery is not a prerequisite). Option B is not required, as the source can be an on-premises database accessed via a data gateway, not only a cloud database.

Exam trap

The trap here is that candidates often assume incremental refresh requires a cloud source or DirectQuery mode, but Power BI's incremental refresh is designed for Import mode and works with any supported data source that provides a date/time column for filtering.

Practice this question →
17
MCQeasy

You are importing data from an Excel workbook that contains multiple sheets. You only need data from the 'Sales' sheet. In Power Query Editor, what should you do to load only that sheet?

A.Load all sheets, then delete the queries for sheets you don't need.
B.In the Navigator dialog, select the 'Sales' sheet and click 'Transform Data'.
C.Use a filter transform to exclude rows from other sheets.
D.Select the entire workbook and then filter out other sheets in Power Query.
AnswerB

In the Navigator dialog, each worksheet and named table appears as a previewable source; choosing the 'Sales' sheet and clicking 'Transform Data' sends only that sheet's data into Power Query Editor as the single source for a new query. This lets you filter rows, change data types, or add custom columns before loading, while leaving every other sheet unread and out of the data model. This is the efficient, supported workflow for importing a specific worksheet.

Why this answer

In Power Query Editor, the Navigator dialog allows you to preview and select specific tables or sheets from a data source before loading. By selecting the 'Sales' sheet and clicking 'Transform Data', you load only that sheet into Power Query Editor for transformation, avoiding unnecessary data. This is the correct and efficient method to import a single sheet from a multi-sheet Excel workbook.

Exam trap

The trap here is that candidates may think they can use a filter or query folding to exclude entire sheets after loading, but Power Query treats each sheet as a separate table, not as rows within a single table, so filtering cannot remove sheets.

How to eliminate wrong answers

Option A is wrong because loading all sheets and then deleting unwanted queries is inefficient and violates the principle of early data reduction; it also consumes memory and processing time for data that will be discarded. Option C is wrong because filter transforms operate on rows within a single table, not on sheets or tables; you cannot use a row filter to exclude entire sheets from a workbook. Option D is wrong because selecting the entire workbook in the Navigator dialog loads all sheets as separate queries, and Power Query does not support filtering out other sheets after loading; you would need to manually remove or disable the unwanted queries.

Practice this question →
18
MCQeasy

You are reviewing a Power Query query that loads data from a SQL Server database. The query includes multiple steps that perform data transformation. You want to ensure that the query is optimized by pushing as many transformations as possible to the SQL Server. What should you look for?

A.View the native SQL query generated by Power Query.
B.Use the Performance Analyzer in Power BI Desktop.
C.Check for the 'Query Folding' indicators in the query editor.
D.Review the data preview for each step.
AnswerC

Correct. Query folding indicators in Power Query Editor (a table icon with a down arrow or a fold icon) show whether a step is translated into native SQL and executed on the server. This directly tells you if transformations are being pushed to SQL Server.

Why this answer

Query folding indicators in Power Query Editor show whether transformations are being pushed to the SQL Server source. When a step shows a 'folded' icon (a table with a down arrow), it means the transformation is translated into native SQL and executed on the server, reducing data transfer and improving performance. Checking these indicators directly confirms which steps are folded and which are not, allowing you to optimize the query by reordering or rewriting steps to maximize folding.

Option A is incorrect because viewing the native SQL query only shows the final aggregated SQL, not the folding status of each individual step. Option B is also incorrect as the Performance Analyzer helps measure overall report performance but does not indicate folding for each step. Option D is irrelevant because data previews do not show folding status.

Exam trap

The trap here is that candidates often confuse viewing the native SQL query (Option A) with checking query folding indicators, but the native query only shows the final aggregated SQL, not the folding status of each individual step, which is what the question specifically asks for.

How to eliminate wrong answers

Option A is wrong because viewing the native SQL query generated by Power Query only shows the final folded query, not the folding status of individual steps; it does not help identify which specific transformations are being pushed. Option B is wrong because the Performance Analyzer in Power BI Desktop measures report and visual rendering performance, not query folding or source-side optimization of Power Query transformations. Option D is wrong because reviewing the data preview for each step shows the output of transformations but gives no indication of whether those transformations are being executed on the SQL Server or locally in Power Query.

Practice this question →
19
MCQeasy

Refer to the exhibit. You load the Sales table into Power BI. You need to calculate the total net sales after discount (SalesAmount * (1 - Discount)). However, some rows have Null in the Discount column. What is the correct DAX measure?

A.Net Sales = SUM(Sales[SalesAmount]) * (1 - SUM(Sales[Discount]))
B.Net Sales = SUMX(Sales, Sales[SalesAmount] * (1 - COALESCE(Sales[Discount], 0)))
C.Net Sales = SUMX(Sales, Sales[SalesAmount] * (1 - Sales[Discount]))
D.Net Sales = SUMX(Sales, DIVIDE(Sales[SalesAmount], 1 - Sales[Discount]))
AnswerB

SUMX iterates row by row over the Sales table, so Sales[SalesAmount] and Sales[Discount] are evaluated in the same row. The COALESCE function replaces any null Discount value with 0, preventing null propagation and treating missing discounts as no discount. This correctly computes net revenue per row as SalesAmount * (1 - Discount), then sums across all rows, which matches the intended business definition of net sales.

Why this answer

It uses SUMX to iterate over each row of the Sales table, applying the calculation row-by-row, and uses COALESCE to replace any NULL in the Discount column with 0, ensuring the discount is correctly treated as zero for rows without a discount. This avoids the aggregation errors that occur when using SUM on the Discount column directly.

Exam trap

The trap here is that candidates often choose Option C, thinking that DAX will automatically ignore NULLs in multiplication, when in fact any NULL operand produces a NULL result, leading to incorrect totals.

How to eliminate wrong answers

Option A is wrong because it aggregates SalesAmount and Discount separately with SUM, then multiplies the totals, which incorrectly applies a single aggregated discount rate to the total sales amount rather than calculating row-by-row. Option C is wrong because it does not handle NULL values in the Discount column; any row with a NULL discount will cause the entire multiplication to result in NULL, producing incorrect totals. Option D is wrong because it uses DIVIDE with (1 - Sales[Discount]) as the denominator, which is mathematically incorrect for calculating net sales after discount and also fails to handle NULLs.

Practice this question →
20
MCQmedium

You have a table with a column 'FullName' that contains names in the format 'Last, First'. You need to split this column into 'LastName' and 'FirstName' columns. Which Power Query transformation should you use?

A.Pivot the FullName column.
B.Split Column by Delimiter using comma.
C.Group By the FullName column and aggregate.
D.Extract first characters using 'Extract' transformation.
AnswerB

In Power Query, the Split Column feature by a delimiter divides text into separate columns at each occurrence of a specified delimiter. For a FullName column containing comma-separated names, choosing comma as the delimiter splits it into two columns, with options to control the number of splits and how to handle extra delimiters. This directly achieves the goal of separating the name into distinct parts.

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 comma. In this case, the 'FullName' column contains names in the 'Last, First' format, so splitting by a comma delimiter will correctly separate the last name and first name into two distinct columns.

Exam trap

The trap here is that candidates might confuse 'Split Column by Delimiter' with 'Extract' or 'Pivot', thinking that extracting the first few characters or pivoting the column could achieve the same result, but only the delimiter-based split correctly handles the variable-length 'Last, First' format.

How to eliminate wrong answers

Option A is wrong because Pivot transforms unique values from a column into new columns and aggregates associated data, which is not applicable for splitting a single text column. Option C is wrong because Group By aggregates rows based on a column and computes summary statistics, not splitting text values within a cell. Option D is wrong because the 'Extract' transformation (e.g., Extract First Characters) only retrieves a fixed number of characters from the start of a string, which cannot handle variable-length names separated by a delimiter.

Practice this question →
21
MCQhard

You are analyzing a Power BI dataset definition. The dataset refreshes but recently started failing with the error 'The 'OrderDate' column of the table 'Orders' has a date value that is out of range.' You need to diagnose the issue. What is the most likely cause?

A.The source table contains a date value that is not recognized as a valid DateTime by Power BI.
B.The columns in the dataset definition do not match the source table.
C.The connection string is invalid.
D.The M expression has a syntax error.
AnswerA

The correct explanation is that this error is triggered during Power Query's type conversion when a value in the source table cannot be coerced to the expected DateTime type because it falls outside the valid date range (e.g., a year before 1753 or a calendar date like February 30). When the dataset definition expects a DateTime column and the underlying source contains an invalid date literal, the M engine raises an out-of-range or conversion error even though the source itself is accessible. This is distinct from a structural, connection, or syntax problem because the failure occurs during data evaluation/type handling, not during connection or parsing.

Why this answer

The error message indicates that a date value in the 'OrderDate' column of the 'Orders' table is out of the range supported by Power BI's DateTime type (1/1/1900 00:00:00 to 12/31/9999 23:59:59.999). This typically occurs when the source contains a date like '0001-01-01' or a future date beyond the upper bound, which Power BI cannot convert to a valid DateTime. Option A is correct because the source table has a date value that is not recognized as a valid DateTime by Power BI, causing the refresh to fail.

Exam trap

The trap here is that candidates often confuse a date-out-of-range error with a data type mismatch error (e.g., text vs. date), but the specific wording 'out of range' points to a valid date value that falls outside Power BI's supported DateTime range, not an invalid format.

How to eliminate wrong answers

Option B is wrong because a mismatch between dataset columns and source columns would produce a different error, such as 'The column 'X' of the table 'Y' was not found' or a schema mismatch, not a date-out-of-range error. Option C is wrong because an invalid connection string would cause a connection failure error (e.g., 'Cannot connect to the data source') before any data is loaded, not a date validation error during refresh. Option D is wrong because an M expression syntax error would be caught during query parsing and would produce a syntax error message, not a runtime data validation error about a specific column value being out of range.

Practice this question →
22
MCQeasy

You are importing data from an Excel workbook that contains a column 'Region' with values such as 'North', 'South', 'East', 'West', and some blank cells. You need to replace the blank cells with 'Unknown' to ensure consistent reporting. What is the most efficient way to achieve this in Power Query?

A.Use the 'Replace Values' feature to replace null with 'Unknown'.
B.Filter out the rows where 'Region' is blank.
C.Add a conditional column that checks if 'Region' is null and returns 'Unknown'.
D.Use the 'Fill Down' feature to fill blanks with the value from the row above.
AnswerA

The 'Replace Values' feature in Power Query can replace null values with a specified text. This is a straightforward and efficient method to handle blanks in a single column. It does not require complex coding and is a common data cleansing step.

Why this answer

Using 'Replace Values' to replace null with 'Unknown' directly modifies the column in place, ensuring all blanks become 'Unknown' without adding extra columns or removing rows. This is the most efficient and straightforward method for this data cleansing task.

Exam trap

The trap here is overcomplicating the solution by considering conditional columns or filtering, when a simple replace operation suffices.

Practice this question →
23
MCQmedium

You need to combine data from three different SharePoint lists into a single table for analysis. The lists have different column names but contain similar data. What is the best approach in Power Query?

A.Use 'Unpivot Columns' to transform the data.
B.Use 'Group By' to combine values.
C.Use 'Append Queries' and then rename columns to match.
D.Use 'Merge Queries' on a common key column.
AnswerC

Append Queries is the correct operation because it stacks rows from multiple queries vertically, which is exactly what you need when combining three SharePoint lists that contain similar record types. When appending, Power Query matches columns by name, so after the append you may need to rename columns in the individual queries to ensure a uniform schema; otherwise you can end up with mismatched columns or nulls. This method preserves all source rows and yields a single consolidated table that is ready for further transformation.

Why this answer

Append Queries is the correct approach because it stacks rows from multiple tables (SharePoint lists) into a single table, which is exactly what you need when combining data with similar structures but different column names. After appending, you can rename columns to unify the schema. This is the standard Power Query method for row-level concatenation of tables.

Exam trap

The trap here is that candidates confuse 'Append' (row stacking) with 'Merge' (column joining), often choosing Merge because they think they need a 'common key' to combine data, but the question explicitly states the lists have different column names, making a key-based join inappropriate.

How to eliminate wrong answers

Option A is wrong because Unpivot Columns transforms columns into rows, which is used for normalizing wide tables, not for combining separate tables. Option B is wrong because Group By aggregates data (e.g., sum, count) and would lose individual row details, not combine lists. Option D is wrong because Merge Queries joins tables horizontally based on a common key, which is for relational lookups, not for stacking rows from different sources.

Practice this question →
24
MCQhard

You are a Power BI data analyst at a university. You import a table named Enrollments from an on-premises SQL Server database. The table contains a column named StudentEmail that is stored as text and sometimes has leading or trailing spaces. You need to remove these spaces so that the column can be used to create a relationship with a Students table that also has a StudentEmail column. You must perform this cleanup in Power Query, and the transformation must apply to the entire column without creating a new column. Which transformation should you use?

A.On the Transform tab, select Format and then Trim.
B.On the Add Column tab, select Format and then Trim.
C.On the Transform tab, select Replace Values and replace a space with an empty value.
D.On the Transform tab, select Format and then Clean.
AnswerA

The Trim transformation removes leading and trailing whitespace from text values across the entire selected column. It modifies the existing column in place, which matches the requirement to avoid creating a new column. After trimming, the values can match the Students table and support a valid relationship. This is the standard data cleansing step for text keys.

Why this answer

Trim is the Power Query transformation that removes leading and trailing whitespace from text values in place. Applying it from the Transform tab modifies the existing StudentEmail column, so the cleaned values can match the Students table and support the relationship. The Add Column variant would create a separate column, and Clean or Replace Values do not correctly remove only the surrounding spaces.

Exam trap

The trap here is selecting Trim from the Add Column tab instead of the Transform tab, which creates a new column and leaves the original column with the unwanted spaces.

Practice this question →
25
MCQeasy

You are connecting to an Excel workbook stored in Microsoft SharePoint Online. You want to refresh the data in Power BI service without manual intervention. Which type of gateway is required?

A.On-premises data gateway (standard mode)
B.On-premises data gateway (personal mode)
C.No gateway is required
D.Virtual network (VNet) gateway
AnswerC

No gateway is required because SharePoint Online is a cloud data source that the Power BI service can connect to natively. During refresh, Power BI authenticates directly to SharePoint Online using the stored credentials (typically OAuth or an organizational account) and retrieves the Excel workbook through SharePoint's cloud APIs. The entire refresh operation runs in the cloud, eliminating the need for any on-premises gateway infrastructure or a local machine being online.

Why this answer

When connecting to an Excel workbook stored in Microsoft SharePoint Online, the data source resides entirely in the cloud. Power BI service can directly access SharePoint Online via its cloud-to-cloud connectivity using the same Microsoft Entra ID authentication context. Therefore, no on-premises data gateway is required for scheduled refresh because the data never traverses an on-premises network.

Exam trap

The trap here is that candidates mistakenly assume any Excel workbook refresh requires a gateway, but the critical distinction is whether the file is stored on-premises (e.g., a network share) versus in a cloud service like SharePoint Online or OneDrive for Business.

How to eliminate wrong answers

Option A is wrong because the on-premises data gateway (standard mode) is designed for connecting to data sources that reside on-premises or in a private network, not for cloud-native sources like SharePoint Online. Option B is wrong because the personal mode gateway is a single-user gateway intended for on-premises data sources and does not support cloud-to-cloud refresh scenarios. Option D is wrong because a Virtual Network (VNet) gateway is used to connect Azure virtual networks to on-premises networks or other VNets, not for accessing cloud SaaS data sources like SharePoint Online.

Practice this question →
26
MCQeasy

You are importing data from a CSV file that contains a column with mixed data types (numbers and text). Power BI automatically assigns the data type as Text. You need to perform numerical aggregations on this column. What should you do?

A.Split the column using a delimiter to separate numbers from text.
B.Create a relationship with a numeric table to enable aggregation.
C.Create a calculated column in DAX using VALUE() to convert the text to numbers.
D.In Power Query Editor, change the data type of the column to Whole Number or Decimal Number.
AnswerD

Changing the data type in Power Query Editor to Whole Number or Decimal Number is the proper and idiomatic remedy because it defines the column as numeric from the moment it enters the data model, immediately making it available for aggregations like SUM and AVERAGE. This transform is part of your ETL workflow, gets applied at each refresh, and is self-documenting in the query steps, ensuring downstream reports treat the column precisely as intended.

Why this answer

Changing the column's data type to Whole Number or Decimal Number in Power Query Editor will force Power BI to interpret the numeric values as numbers, enabling aggregations like SUM or AVERAGE. Power Query Editor provides a robust transformation environment where data type changes are applied during the load process, ensuring the column is treated as numeric for all downstream calculations. This approach is more efficient and reliable than using DAX conversions, as it avoids the overhead of calculated columns and leverages Power Query's native type detection and error handling.

Exam trap

The trap here is that candidates may think a DAX calculated column (Option C) is the correct approach for data type conversion, but the PL-300 exam emphasizes performing data transformations in Power Query Editor (the 'Prepare the data' domain) rather than in DAX, as Power Query is the proper tool for cleaning and shaping data before loading it into the model.

How to eliminate wrong answers

Option A is wrong because splitting the column using a delimiter does not address the mixed data types; it would separate values into multiple columns but still leave text entries that cannot be aggregated numerically. Option B is wrong because creating a relationship with a numeric table does not convert the existing text column to numbers; relationships are based on matching keys, not data type conversion, and aggregation requires numeric values in the same column. Option C is wrong because while a DAX calculated column using VALUE() can convert text to numbers, it is less efficient than changing the data type in Power Query Editor, as it adds a column to the model and may fail on non-numeric text entries, whereas Power Query can handle errors during load.

Practice this question →
27
MCQhard

You are designing a data model in Power BI. You have a 'Sales' table and a 'Date' table. The 'Sales' table has a 'SalesDate' column of type Date. You need to create a relationship between the tables, but the 'Date' table contains dates from 2010 to 2025, while the 'Sales' table only has data from 2020. Which type of relationship should you create to ensure optimal performance and correct filtering?

A.Many-to-one with bi-directional cross-filtering
B.Many-to-one with single cross-filter direction from Date to Sales
C.One-to-one (1:1)
D.Many-to-many (M:M)
AnswerB

This is the correct modeling pattern for a star schema: the Date table contains unique dates, while Sales has multiple rows for each date, so the relationship is many-to-one. With single cross-filter direction from Date to Sales, filtering by a date or a date hierarchy propagates to sales transactions, enabling efficient time-based aggregations. This unidirectional flow is unambiguous, standard, and performs well because it minimizes the filter context path.

Why this answer

A many-to-one relationship with single cross-filter direction from Date to Sales ensures that filters applied to the Date table propagate to the Sales table, which is the standard star schema design. This configuration optimizes query performance by avoiding unnecessary bi-directional filtering and correctly handles the date range mismatch, as the Date table's larger range does not affect filtering of Sales data.

Exam trap

The trap here is that candidates often assume bi-directional filtering is needed for correct filtering, but in a star schema, single-direction filtering from the dimension to the fact table is both sufficient and optimal for performance.

How to eliminate wrong answers

Option A is wrong because bi-directional cross-filtering would force Power BI to evaluate filter context in both directions, which can degrade performance and cause ambiguous filter propagation, especially when the Date table has a superset of dates. Option C is wrong because a one-to-one relationship requires both tables to have unique values in the key columns, which is not the case here (Sales table has multiple rows per date). Option D is wrong because a many-to-many relationship is unnecessary and introduces complexity; it would require a bridge table or special handling, and it does not reflect the actual cardinality where one date can have many sales.

Practice this question →
28
MCQeasy

You are reviewing a DAX query in DAX Studio. The exhibit shows a query that returns a table. What is the purpose of the SUMMARIZE function in this query?

A.To group sales by product category and compute total sales
B.To add a calculated column to the Sales table
C.To add a new row for total sales per category
D.To filter the Sales table by Product Category
AnswerA

SUMMARIZE evaluates a table expression over the Sales table, grouping rows by the Product[Category] column and computing the sum of Sales[Amount] for each distinct category. The result is a new two-column table (Category and Total Sales), with one row per category. This is the correct interpretation because SUMMARIZE is explicitly designed for grouped aggregation, not for altering the original table or its schema.

Why this answer

The SUMMARIZE function in DAX is used to group rows from a table based on one or more columns (here, Product Category) and then compute an aggregated value (Total Sales) using a SUM expression. The query returns a new table with one row per category and the corresponding total sales, which is the standard purpose of SUMMARIZE for grouping and aggregation.

Exam trap

The trap here is that candidates confuse SUMMARIZE with functions that add calculated columns (ADDCOLUMNS) or filter tables (FILTER), leading them to pick options B or D, when in fact SUMMARIZE is specifically for grouping and aggregation.

How to eliminate wrong answers

Option B is wrong because SUMMARIZE does not add a calculated column to an existing table; it returns a new table with grouped rows and aggregated columns, whereas calculated columns are added using CALCULATEDCOLUMN or ADDCOLUMNS. Option C is wrong because SUMMARIZE does not add a new row for total sales per category; it creates one row per unique combination of grouping columns, and a grand total row would require a separate function like ROLLUP or GROUPBY. Option D is wrong because SUMMARIZE does not filter the Sales table; filtering is done with functions like FILTER or CALCULATETABLE, while SUMMARIZE only groups and aggregates the existing rows.

Practice this question →
29
MCQeasy

You are using Power Query to combine data from multiple CSV files in a folder. Each file has the same structure. You want to append all rows into a single table. Which Power Query function should you use?

A.Group By.
B.Append Queries.
C.Merge Queries as new.
D.Combine Files from the folder connector.
AnswerD

The folder connector's Combine Files operation reads every CSV in the folder, applies the shared table structure as a template, and appends all rows into one table, directly satisfying the requirement to merge identically structured files without manual per-file queries.

Why this answer

The 'Combine Files' transformation in Power Query is specifically designed to import and append multiple CSV files from a folder into a single table. When you connect to a folder using the 'From Folder' connector, Power Query automatically generates a 'Combine Files' step that uses the 'Table.Combine' function under the hood, which appends all rows from identically structured files into one unified table.

Exam trap

The trap here is that candidates often confuse 'Append Queries' (which is a manual, query-level operation) with the automated 'Combine Files' feature that the folder connector provides, leading them to pick Option B instead of recognizing that the folder connector's built-in combine functionality is the correct and intended method for this scenario.

How to eliminate wrong answers

Option A is wrong because 'Group By' is an aggregation operation that groups rows based on column values and computes summaries (e.g., sum, count), not a method to append rows from multiple files. Option B is wrong because 'Append Queries' is a manual operation that combines two or more existing queries in the Power Query Editor, but it is not the direct function used when importing multiple files from a folder; the folder connector's 'Combine Files' is the automated approach. Option C is wrong because 'Merge Queries as new' performs a join (like SQL JOIN) based on matching columns, which combines columns from different tables, not appending rows.

Practice this question →
30
MCQhard

Refer to the exhibit. The Power Query M code connects to a SQL Server database and performs data transformation. However, the query is failing with a privacy level error. What is the most likely cause?

A.The SQL Server credentials are not correctly configured in the data source settings.
B.The privacy levels for the SQL Server data source are set inconsistently across the environment.
C.The query uses a CSV file from a local folder that has a privacy level set to 'Private'.
D.The query combines data from SQL Server and another data source with incompatible privacy levels.
AnswerD

This is correct because Power Query's privacy-level engine prevents data from being combined across sources that have incompatible privacy levels, such as a 'Private' CSV file from a local folder and a SQL Server source with a different level. When the query attempts to fold or merge data from these two sources, the engine raises a privacy-level error unless levels are adjusted to allow the combination or a suitable firewall level is configured. The error is specifically triggered by the act of combining the sources, not by the nature of any single source.

Why this answer

A privacy level error in Power Query occurs when combining data from multiple sources with incompatible privacy levels. The query connects to SQL Server and performs transformations, but if it also references another source (e.g., from a previous step or additional data) with a privacy level that conflicts with SQL Server's privacy level, the Power Query firewall blocks the query. In this scenario, the most likely cause is that the query combines SQL Server data with another data source (such as a CSV file or another database) that has a privacy level set to 'Private' while SQL Server is set to 'Organizational' or 'Public', causing the error.

Option D correctly identifies this. Options A and B are incorrect because a single data source cannot cause a privacy level error, and inconsistent privacy levels across environments do not trigger the firewall on a single source.

Exam trap

The trap is that candidates often think a privacy level error requires inconsistent settings on a single source or credentials issues, but the error always involves combining multiple sources. Even if only SQL Server is visible, the query may be implicitly referencing another source.

How to eliminate wrong answers

Option A is wrong because incorrect SQL Server credentials would produce a connection or authentication error (e.g., 'Cannot connect to database'), not a privacy level error. Option C is wrong because a CSV file from a local folder with a privacy level set to 'Private' alone does not cause a privacy level error; the error arises only when combining that CSV with another source that has an incompatible privacy level (e.g., 'Public'), and the question states the query connects to SQL Server, not a CSV. Option D is wrong because while combining data from SQL Server and another source with incompatible privacy levels can cause the error, the question specifically states the query 'connects to a SQL Server database and performs data transformation'—it does not mention combining with another source, so the most likely cause is the inconsistency within the SQL Server source's own privacy level settings across the environment, not a cross-source combination.

Practice this question →
31
MCQmedium

You are preparing a data model that uses a date table. You need to ensure that the date table includes all dates from January 1, 2020 to December 31, 2025. What is the most efficient way to create this date table in Power Query?

A.Import a date table from an Excel file.
B.Use the 'List.Dates' function to generate the date range.
C.Use the 'Calendar' function in Power Query M.
D.Use the 'CALENDAR' DAX function in Power Query.
AnswerB

Using the 'List.Dates' function in Power Query M is the correct approach because it dynamically generates a continuous list of dates from a specified start date, a count of dates, and a step increment, all defined as parameters. You then wrap that list with Table.FromList or convert it into a column, allowing you to create a date table that automatically adapts to your data range without manual maintenance or external file dependencies.

Why this answer

The 'List.Dates' function in Power Query M is the most efficient way to generate a contiguous range of dates directly within the query editor, without external dependencies. It creates a list of dates from a start date to an end date using a specified step (e.g., #duration(1,0,0,0) for one day), which can then be converted into a table. This approach is lightweight, fully self-contained, and avoids the overhead of importing external files or using DAX functions that are not native to Power Query.

Exam trap

The trap here is that candidates confuse DAX functions (like 'CALENDAR') with Power Query M functions, assuming they can be used interchangeably in the Power Query editor, when in fact DAX functions are only available in the data modeling layer (e.g., calculated tables) and not in Power Query.

How to eliminate wrong answers

Option A is wrong because importing a date table from an Excel file introduces an external dependency, requires manual maintenance, and is less efficient than generating the dates natively in Power Query. Option C is wrong because there is no built-in 'Calendar' function in Power Query M; the correct M function is 'List.Dates' or the 'Date' functions, and 'Calendar' is a DAX function, not an M function. Option D is wrong because 'CALENDAR' is a DAX function used in Power Pivot or Analysis Services, not in Power Query; Power Query uses M language, and DAX functions cannot be used directly in the Power Query editor.

Practice this question →
32
Multi-Selecthard

Which THREE of the following are best practices for data preparation in Power BI to improve performance and maintainability? (Select THREE.)

Select 3 answers
A.Filter out unnecessary rows as early as possible in the query
B.Avoid renaming columns in Power Query; use original names
C.Use query folding to push transformations back to the source
D.Split complex queries into multiple steps for clarity
E.Keep all columns from the source to avoid missing data
AnswersA, C, D

Early filtering reduces data volume and improves performance.

Why this answer

Filtering out unnecessary rows early in Power Query reduces the amount of data loaded into memory and processed in subsequent transformation steps. This practice, known as early filtering, minimizes the data footprint and improves both refresh performance and report responsiveness. By applying filters as the first transformation, you leverage query folding to push the filter logic to the source database, further enhancing efficiency.

Exam trap

The trap here is that candidates often confuse 'maintainability' with 'avoiding changes to source names,' but Power BI encourages renaming for clarity, and the real performance pitfall is keeping unnecessary columns, not renaming them.

Practice this question →
33
MCQhard

You are developing a Power BI semantic model that must combine data from an on-premises SQL Server database and a SharePoint Online list. The organization requires that credentials for the on-premises data source be stored securely and not shared with users. Which data connectivity approach should you use?

A.Use an on-premises data gateway and store the SQL Server credentials in the gateway data source settings
B.Use DirectQuery with single sign-on for the SQL Server database
C.Export the SQL Server data to an Excel file stored in SharePoint and then import from SharePoint
D.Import data from SQL Server using Windows authentication embedded in the data source settings
AnswerA

Using an on-premises data gateway is the recommended approach because it securely bridges Power BI service to your on-premises SQL Server. Storing the SQL Server credentials in the gateway's data source settings keeps them encrypted and accessible only to gateway administrators, enabling scheduled refreshes without requiring individual users to have database permissions. This also supports row-level security mapping when needed, making it a secure and scalable solution.

Why this answer

Using an on-premises data gateway with credentials stored in the gateway data source settings allows the Power BI service to securely connect to the on-premises SQL Server without exposing credentials to users. The gateway acts as a secure bridge, storing the SQL Server credentials encrypted in the gateway configuration, which satisfies the requirement that credentials not be shared with users.

Exam trap

The trap is that candidates often choose DirectQuery with SSO (Option B) thinking it avoids credential sharing, but SSO relies on each user's own credentials and requires granting users direct database access, which violates the requirement that credentials be stored securely and not shared with users. The correct approach is to use a gateway with centrally stored credentials.

How to eliminate wrong answers

Option B is wrong because DirectQuery with single sign-on (SSO) would pass the user's own identity to the SQL Server, which requires users to have direct database permissions and does not store credentials securely for the organization; it also does not work with SharePoint Online lists in a combined model without a gateway. Option C is wrong because exporting SQL Server data to an Excel file in SharePoint bypasses the requirement for secure credential storage and introduces data staleness, manual refresh, and security risks from file-based sharing. Option D is wrong because embedding Windows authentication credentials in the data source settings would store the credentials in the Power BI model metadata, which is not secure and would be exposed to users who have access to the dataset settings.

Practice this question →
34
MCQeasy

You have a Power Query query that loads data from an OData source. You need to reduce the amount of data loaded into the data model. What is the best practice?

A.Apply a filter in the data model using DAX.
B.Use 'Enable load' option to turn off loading for the query.
C.Apply a filter in Power Query before loading.
D.Load all data and then hide columns you don't need.
AnswerC

Applying a filter in Power Query before the data is loaded reduces the number of rows that are imported into the data model. For OData sources, the filter can often be folded into the native query sent to the server, so only matching rows traverse the network and are stored in VertiPaq. This lowers memory usage, improves refresh time, and shrinks the model footprint — the correct way to reduce data volume.

Why this answer

Applying filters in Power Query before loading data into the data model is the best practice for reducing data volume. Power Query pushes filters down to the OData source using OData query parameters (e.g., $filter), ensuring only the required rows are retrieved from the source. This minimizes network transfer and memory usage in the data model, aligning with the principle of early filtering in the ETL process.

Exam trap

The trap here is that candidates often confuse filtering in the data model (DAX) with filtering during data ingestion (Power Query), assuming both reduce data volume equally, but only Power Query filters reduce the actual data loaded into memory.

How to eliminate wrong answers

Option A is wrong because applying a filter in the data model using DAX does not reduce the amount of data loaded; it only restricts what is visible in reports, while the entire dataset remains in memory. Option B is wrong because disabling 'Enable load' for the query prevents the entire query from being loaded, which is not a method to reduce data volume for a query that is needed—it removes the query entirely from the model. Option D is wrong because loading all data and then hiding columns does not reduce the amount of data loaded; hidden columns still consume memory and storage in the data model.

Practice this question →
35
MCQmedium

You are working with a Power BI dataset that imports data from a web API. The API returns a JSON response containing a nested array of order details for each order. You need to flatten this nested array so that each order detail becomes a separate row in the table. Which Power Query transformation should you use?

A.Transpose the table.
B.Expand the column containing the array.
C.Use the Unpivot Columns transformation.
D.Use the Split Column transformation with a comma delimiter.
AnswerB

Expanding the column that holds the nested array (often a List or Record type) will create a new row for each element in the array, effectively flattening the data. This is the standard way to unnest arrays in Power Query. After expansion, related order information is repeated for each detail row, which may require further shaping.

Why this answer

Expanding the column that contains the nested array is the correct way to flatten JSON data in Power Query. This creates one row per array element, duplicating the parent order fields as needed. Other transformations do not handle nested structures and would fail to produce the required row-level detail.

Exam trap

The trap here is thinking that Unpivot or Split Column can handle nested arrays, when only the Expand operation is designed for this purpose.

Practice this question →
36
Multi-Selecteasy

You are profiling data in Power Query Editor. Which THREE tasks can you perform using the Column Profile feature?

Select 3 answers
A.Add a conditional column
B.Identify data type issues
C.Count distinct values
D.View the distribution of values
E.Replace values
AnswersB, C, D

The column profile pane in Power Query Editor displays a range of statistics and data type indicators, making mismatches visually apparent—for example, a numeric column containing text values will show a 'Text' data type badge or error entries. Recognizing these issues is a primary objective of profiling so you can convert or correct types before loading. This is a capability of profiling, not a step you execute on data.

Why this answer

The Column Profile feature in Power Query Editor is a read-only data profiling tool, so it can identify data type issues (B) by showing errors and mismatches in the column statistics, which helps you spot columns that need type correction. It also counts distinct values (C), displaying the number of unique entries in the column profile pane to assess cardinality. Additionally, it shows the distribution of values (D) through value frequency charts and statistics such as count, min, max, and unique counts, helping you understand data patterns.

The unmarked options do not belong because adding a conditional column (A) and replacing values (E) are data transformation operations performed via the Add Column and Transform ribbons, not tasks provided by the profiling feature.

Exam trap

The trap here is that candidates confuse the Column Profile feature (a read-only profiling tool) with data transformation actions like adding columns or replacing values, leading them to select options that are actually performed in other parts of Power Query Editor.

Practice this question →
37
Multi-Selecthard

You are connecting to a large CSV file (10 GB) stored in Azure Blob Storage. You need to load the data into Power BI with optimal performance. Which THREE practices should you follow? (Choose three.)

Select 3 answers
A.Use Parquet format instead of CSV if possible.
B.Import all columns and use Power BI to hide unused ones.
C.Split the large CSV file into multiple smaller files in the same folder.
D.Filter rows in Power Query to remove unnecessary data early in the transformation.
E.Use the on-premises data gateway to connect to Azure Blob Storage.
AnswersA, C, D

Parquet is a columnar storage format with built-in compression and predicate pushdown, meaning Power Query can read only the specific columns and row groups required instead of scanning the entire 10 GB text file. This dramatically reduces the amount of I/O and memory consumed during the Data Refresh, and also preserves the schema and data types natively, eliminating costly type-inference and parsing steps. By shifting to Parquet, you effectively move the heavy lifting to the storage engine, which is far more efficient for analytical workloads.

Why this answer

Option A is correct because Parquet is a columnar, compressed format that Power BI can read far more efficiently than CSV, reducing file size and scan time for a 10 GB dataset. Option C is correct because splitting the large CSV into multiple smaller files in the same folder enables Power BI's parallel loading and folder-combine pattern, improving throughput versus reading one monolithic file. Option D is correct because applying row filters in Power Query early reduces the volume of data pulled into the model, lowering memory and refresh cost.

Option B is wrong because importing all columns then hiding them still loads every column into the model, wasting memory and bandwidth. Option E is wrong because the on-premises data gateway is only needed for on-premises data sources, not for a native cloud source like Azure Blob Storage.

Exam trap

The trap here is that candidates may think importing all columns and hiding them is harmless, but Power BI still loads the full data into memory, wasting resources; the correct approach is to remove unnecessary columns and rows early in Power Query.

Practice this question →
38
MCQeasy

You are loading data from a SQL Server database into Power BI. You notice that the import takes a long time because the source table contains many rows. You only need a subset of rows based on a date filter. What should you do to improve performance?

A.Remove unnecessary columns in Power Query.
B.Use a SQL query with a WHERE clause in the Power Query Editor.
C.Load all data and then apply a filter in Power BI.
D.Enable incremental refresh on the dataset.
AnswerB

Writing a SQL query with a WHERE clause directly in Power Query Editor is the most efficient approach because it implements query folding—SQL Server executes the filter and returns only the rows that satisfy the predicate. This minimizes data transfer, avoids loading irrelevant rows into Power Query memory, and speeds up both the initial load and subsequent refreshes. When the source is a relational database, a native SQL query with a filtered result set is a best practice for row-level reduction at the source.

Why this answer

Using a SQL query with a WHERE clause in Power Query Editor pushes the date filter down to the SQL Server database, reducing the amount of data transferred over the network and imported into Power BI. This query folding technique leverages the database engine's indexing and processing power, which is far more efficient than filtering after import. By retrieving only the necessary rows upfront, you minimize both network latency and memory consumption in Power BI.

Exam trap

The trap here is that candidates often choose 'Remove unnecessary columns' (Option A) thinking it reduces data volume, but they overlook that row count reduction via query folding has a far greater impact on import performance than column reduction.

How to eliminate wrong answers

Option A is wrong because removing unnecessary columns in Power Query reduces the data width but does not address the root cause of slow import—the large number of rows being transferred from SQL Server. Option C is wrong because loading all data and then applying a filter in Power BI still requires the full dataset to be imported, which consumes network bandwidth and memory, defeating the purpose of performance improvement. Option D is wrong because incremental refresh is designed for scheduled data refreshes over time, not for optimizing the initial import of a single large table; it requires a date-range parameter and a supported data source, and does not reduce the initial load time.

Practice this question →
39
MCQeasy

You are preparing a Power BI dataset that will be used by report authors. The source data contains a column named 'CustomerName' with leading and trailing spaces. You need to remove these spaces in Power Query. Which transformation should you use?

A.Split Column
B.Replace Values
C.Clean
D.Trim
AnswerD

The Trim transformation removes all leading and trailing whitespace from text values. Applying it to CustomerName cleans the data without altering internal spaces, which is exactly what is needed. This transformation is available in the Power Query Editor under the Transform tab and is applied to the selected column.

Why this answer

The Trim transformation is specifically designed to remove leading and trailing whitespace from text values. It preserves internal spaces, ensuring names remain intact. Other transformations either target different issues or would alter the data incorrectly.

Using Trim is the standard and correct way to clean this column in Power Query.

Exam trap

The trap here is confusing Trim with Clean, where Clean removes non-printable characters but does not affect ordinary spaces.

Practice this question →
40
MCQeasy

You are transforming data in Power Query. A column named 'SalesAmount' contains values as text with a dollar sign and thousands separator, e.g., "$1,234.56". You need to convert this column to a decimal number for analysis. What is the most efficient sequence of transformations?

A.Split the column by delimiter and keep the numeric part, then change data type.
B.Change data type to Decimal Number directly; Power Query will automatically clean the values.
C.Use Replace Values to remove '$' and ',', then change data type to Decimal Number.
D.Use Replace Values to remove '$' and ',' then change data type to Decimal.
AnswerC

Removing specific characters is a direct and efficient method; however, a more robust approach is to use Text.Select to keep only digits and the decimal point, but Replace Values is simplest given the known characters.

Why this answer

It explicitly removes both the dollar sign and the comma using Replace Values before changing the data type to Decimal Number, ensuring proper conversion without errors. Option A is inefficient; splitting the column is unnecessary when simple replacements work. Option B would fail because Power Query cannot automatically parse currency symbols and thousands separators from text when changing data type directly.

Option D appears similar but specifies 'Decimal' instead of 'Decimal Number', which is not a valid data type in Power Query, leading to an error or incorrect result.

Exam trap

The trap here is that candidates assume Power Query's automatic type detection or direct data type change can handle currency symbols and separators, but in reality, it requires explicit cleaning steps to avoid errors or incorrect conversions.

How to eliminate wrong answers

Option A is wrong because splitting the column by delimiter is an overly complex approach that introduces unnecessary steps and potential data loss; it is not the most efficient sequence. Option B is wrong because Power Query cannot automatically clean currency symbols and thousands separators when changing data type directly; it will either error or leave the column as text. Option D is wrong because it only removes the dollar sign but not the comma, so the thousands separator remains, causing the data type conversion to fail or produce incorrect results.

Practice this question →
41
MCQeasy

You have a column 'ProductID' that contains integers. You need to ensure that this column is used as a key in relationships. What data type should the column have?

A.Decimal Number.
B.Text.
C.Whole Number.
D.Binary.
AnswerC

Whole Number, which uses the 64-bit integer data type, is the optimal choice for a product ID key column. Integers provide exact, unambiguous equality comparison, allow the most efficient indexing and hashing, and compress extremely well in columnar storage, resulting in faster joins, filters, and relationships. Since the ProductID contains only integer values, Whole Number perfectly represents the domain without unnecessary conversion or storage overhead.

Why this answer

In Power BI, relationship keys must be of a data type that supports exact matching and efficient indexing. Whole Number (integer) is the optimal type for primary keys in relationships, as it ensures unique, non-decimal values that Power BI can use for fast lookups and joins without precision issues.

Exam trap

The trap here is that candidates may think Text is always acceptable for keys (since it can hold any value), but the question specifically tests the optimal data type for integer-based keys in Power BI relationships, where Whole Number is the correct and most efficient choice.

How to eliminate wrong answers

Option A is wrong because Decimal Number introduces floating-point precision issues that can cause relationship failures or unexpected mismatches when exact key matching is required. Option B is wrong because Text can be used as a key, but it is less efficient than Whole Number and not the recommended type for integer-based IDs; the question specifies the column contains integers, so Text would require unnecessary conversion. Option D is wrong because Binary is not a valid data type for relationship keys in Power BI; it is used for storing binary data like images and cannot participate in relationships.

Practice this question →
42
MCQeasy

You have a Power BI dataset that refreshes daily from an on-premises SQL Server database. The refresh fails with an error 'The data source credentials cannot be used for the connection'. What is the most likely cause?

A.The on-premises gateway is not running.
B.The dataset is using DirectQuery and the connection string is incorrect.
C.The credentials stored in the gateway for the SQL Server have changed or expired.
D.The SQL Server is not accessible from the cloud.
AnswerC

The on-premises data gateway stores the SQL Server credentials that the Power BI service uses during refresh. If the SQL Server password was changed or the account expired, the gateway can no longer authenticate to the database, and the refresh fails with a 'The credentials stored in the gateway are invalid' error. Re-validating and updating the credentials in the gateway data source settings resolves this issue, making it the correct root cause.

Why this answer

The error 'The data source credentials cannot be used for the connection' specifically indicates that the credentials stored in the on-premises data gateway for the SQL Server data source have changed or expired. When a Power BI dataset refreshes from an on-premises SQL Server via a gateway, the gateway uses stored credentials to authenticate; if those credentials are no longer valid (e.g., password was rotated), the connection fails with this exact error.

Exam trap

The trap here is that candidates often confuse a credentials error with a connectivity error (like the gateway being offline or the server being unreachable), but the specific wording 'credentials cannot be used' points directly to authentication failure, not network or gateway availability.

How to eliminate wrong answers

Option A is wrong because if the on-premises gateway were not running, the error would typically be 'Unable to connect to the gateway' or 'Gateway not found', not a credentials-specific error. Option B is wrong because the dataset refreshes daily (implying Import mode, not DirectQuery), and even if it were DirectQuery, an incorrect connection string would produce a 'Cannot connect to server' or 'Login failed' error, not a credentials-specific error. Option D is wrong because if the SQL Server were not accessible from the cloud, the error would be a network-level timeout or 'Server not found', not a credentials-specific error; the gateway handles the cloud-to-on-premises connectivity, so the cloud does not directly access the SQL Server.

Practice this question →
43
MCQhard

You are connecting to an Azure SQL database using DirectQuery. The database has a large table with millions of rows. Users need to see aggregated data quickly. What should you implement to improve query performance?

A.Create aggregations in Power BI on the large table.
B.Increase the memory limit of the Power BI Desktop.
C.Use a composite model with a smaller imported table.
D.Add indexes to the database table.
AnswerA

Aggregations reduce the amount of data queried from the source.

Why this answer

Creating aggregations in Power BI on the large table allows the DirectQuery model to pre-aggregate data at the source or in Power BI, reducing the volume of data queried and improving response times for aggregated results. This is a key performance optimization for DirectQuery models with large tables, as it avoids scanning millions of rows for every query.

Exam trap

The trap here is that candidates often confuse database-side optimizations (like indexes) with Power BI-side optimizations (like aggregations), leading them to choose Option D, but the question explicitly asks what you should implement in Power BI, not in the database.

How to eliminate wrong answers

Option B is wrong because increasing the memory limit of Power BI Desktop does not improve query performance against an Azure SQL database via DirectQuery; memory limits affect local processing, not the database query execution. Option C is wrong because using a composite model with a smaller imported table would break the DirectQuery requirement and introduce data freshness issues, as the imported table would need to be refreshed separately and may not reflect real-time data. Option D is wrong because adding indexes to the database table is a database-side optimization that can improve query performance, but it is not a Power BI implementation; the question asks what you should implement in Power BI, and indexes are managed by the database administrator, not within Power BI.

Practice this question →
44
MCQeasy

You need to create a date table in Power BI using DAX. Which function should you use to generate a continuous list of dates?

A.CALCULATE.
B.CALENDAR.
C.TODAY.
D.DATEVALUE.
AnswerB

CALENDAR is the correct DAX function because it explicitly returns a single-column table containing a continuous series of all dates from a specified start date to an end date, inclusive. This generated table can be used directly as a date table or extended with calculated columns for year, month, quarter, and other date attributes. It requires two date arguments, typically defined as DATE literals or scalar expressions that resolve to dates.

Why this answer

The CALENDAR function in DAX generates a single-column table of dates that starts from a specified start date and ends at a specified end date, creating a continuous list of all dates in that range. This is the correct function to use when you need to create a date table for time intelligence calculations in Power BI.

Exam trap

The trap here is that candidates confuse CALCULATE (a context modifier) with CALENDAR (a table generator) because of the similar spelling, or they mistakenly think TODAY or DATEVALUE can produce a range of dates when they only return single values.

How to eliminate wrong answers

Option A is wrong because CALCULATE is a filter modifier function used to evaluate an expression in a modified filter context, not to generate a list of dates. Option C is wrong because TODAY returns only the current date as a scalar value, not a continuous list of dates. Option D is wrong because DATEVALUE converts a date string into a date/time value, but it does not generate a range of dates.

Practice this question →
45
MCQeasy

You need to combine data from two tables in Power Query that have the same columns but different row sets. Which operation should you use?

A.Union Queries
B.Group By
C.Append Queries
D.Merge Queries
AnswerC

Append Queries is the correct operation. It stacks the rows from the second table beneath the rows from the first table, effectively performing a union of the data when both tables have the same columns.

Why this answer

Append Queries is the correct operation in Power Query when you need to combine two tables with identical columns but different row sets. It stacks the rows from the second table beneath the rows from the first table, effectively performing a union of the data. This is distinct from Merge Queries, which joins columns based on a key, and Group By, which aggregates data.

Exam trap

The trap here is that candidates confuse 'Merge Queries' (which adds columns) with 'Append Queries' (which adds rows), especially since the term 'Union' is commonly used in SQL but is not the exact Power Query operation name.

How to eliminate wrong answers

Option A is wrong because 'Union Queries' is not a native Power Query operation; the correct term is Append Queries, and while the concept is similar to a SQL UNION, the Power Query interface uses 'Append' to combine rows. Option B is wrong because Group By is used to aggregate rows based on a column, not to combine separate tables with the same schema. Option D is wrong because Merge Queries performs a join (like SQL JOIN) that adds columns from one table to another based on matching keys, not stacking rows.

Practice this question →
46
Multi-Selectmedium

Which THREE of the following are best practices when preparing data in Power BI for optimal performance?

Select 3 answers
A.Merge all tables into a single table for simplicity.
B.Create calculated columns instead of measures when possible.
C.Set correct data types for all columns.
D.Remove unnecessary columns and rows during import.
E.Use query folding to push transformations to the data source.
AnswersC, D, E

Assigning correct data types (such as integer, decimal, and date) rather than generic text optimizes the compression algorithms in Power BI, leading to smaller model sizes and faster scan performance. It also ensures that DAX functions like SUM and DATE arithmetic behave predictably, and prevents implicit type conversions that can introduce errors or degrade performance.

Why this answer

Option C is correct because assigning the correct data types (for example, whole number, decimal, date/time, or text) to every column reduces memory usage in the VertiPaq engine, enables efficient compression, and prevents implicit conversions that slow down DAX calculations. Option D is correct because removing unnecessary columns and rows during import (ideally in Power Query before loading) shrinks the data model, lowers memory consumption, and speeds up refresh and query processing. Option E is correct because query folding translates Power Query transformations into native source queries (for example, SQL statements), letting the source database perform filtering, joins, and aggregations so less data is transferred and processed locally.

Option A is not a best practice because merging all tables into one wide table creates redundancy, bloats the model, and destroys the star-schema relationships that Power BI optimizes for. Option B is not a best practice because calculated columns are computed and stored during refresh, consuming memory and increasing model size, whereas measures are evaluated at query time and are generally preferred for aggregations.

Exam trap

The trap here is that candidates may think merging all tables simplifies the model (Option A) or that calculated columns are more efficient than measures (Option B), but Power BI's in-memory engine and query folding mechanics reward normalized star schemas and measure-based calculations for optimal performance.

Practice this question →
47
Multi-Selecteasy

Which TWO are valid ways to combine data from multiple sources in Power Query? (Choose two.)

Select 2 answers
A.Pivot Column.
B.Append Queries.
C.Create relationships in the data model.
D.Merge Queries.
E.Group By.
AnswersB, D

Append Queries combines multiple tables by vertically stacking all rows from each input into a single resultant table, which is essential when consolidating datasets that share a common schema—like monthly sales exports from different regions. It aligns columns by name and fills missing values with null for any mismatched columns, making it a true row-level combination method. This is one of the two valid ways to physically combine data in Power Query.

Why this answer

Append Queries (B) is correct because it stacks rows from two or more tables with matching or similar columns into a single table, which is the standard Power Query way to combine data vertically from multiple sources. Merge Queries (D) is correct because it joins two tables horizontally on one or more matching key columns, equivalent to a SQL JOIN, allowing data from multiple sources to be combined side by side. Pivot Column (A) is not a combining operation; it reshapes existing values in a single table from rows into columns.

Create relationships in the data model (C) links tables for analysis in Power Pivot/DAX but does not itself combine or transform data within Power Query. Group By (E) aggregates rows within a single table (e.g., sum, count) and does not combine data from multiple sources.

Exam trap

The trap here is that candidates often confuse 'combining data from multiple sources' with 'creating relationships in the data model,' which is a separate step performed after data loading, not a Power Query transformation.

Practice this question →
48
MCQhard

Your Power BI dataset uses a SQL view that joins multiple tables. You notice that some columns have null values where you expect data. You suspect the view definition has a bug. How can you verify the view's output in Power Query?

A.Check the 'Table Preview' in the data model
B.Create a new query that runs the view's SQL directly against the source
C.Use 'View Native Query' in Power Query
D.Use 'Data Profiling' in Power Query
AnswerB

By creating a new Power Query query that executes the view's SQL statement directly against the source database, you bypass any existing transformations and fetch the exact rows and columns the view returns. This gives you an independent, unfiltered look at the view's output, allowing you to compare it against what the dataset actually uses. This is the only method listed that reliably exposes the raw view result set.

Why this answer

Creating a new query that runs the view's SQL directly against the source in Power Query allows you to isolate and execute the exact SQL statement, bypassing any transformations or folding issues. This lets you compare the raw output from the source with the view's expected results, directly verifying if the view definition itself contains a bug. It is the most straightforward method to confirm whether the null values originate from the view or from subsequent Power Query steps.

Exam trap

The trap here is that candidates confuse 'View Native Query' (which shows the folded query after transformations) with the ability to run the original view SQL directly, leading them to choose option C instead of B.

How to eliminate wrong answers

Option A is wrong because the 'Table Preview' in the data model shows data after all Power Query transformations have been applied, not the raw output of the SQL view; it cannot isolate the view's definition from subsequent data shaping steps. Option C is wrong because 'View Native Query' in Power Query displays the query that Power Query sends to the source after folding, which may include transformations and not the original view SQL; it does not let you run the view's SQL independently to verify its output. Option D is wrong because 'Data Profiling' in Power Query provides statistics like column quality and distribution, but it does not show the raw SQL output or allow you to execute the view's SQL directly to identify bugs in the view definition.

Practice this question →
49
Multi-Selecthard

Which THREE of the following are best practices for optimizing data load performance in Power BI?

Select 3 answers
A.Remove unnecessary columns and rows during the import process.
B.Split a large fact table into multiple smaller fact tables.
C.Set data types correctly in Power Query to avoid type detection overhead.
D.Use DirectQuery mode instead of Import mode to reduce data load time.
E.Use query folding to push transformations to the source database.
AnswersA, C, E

Reducing data volume improves load time.

Why this answer

Removing unnecessary columns and rows during the import process reduces the amount of data loaded into the Power BI data model, which directly decreases memory usage and refresh time. By filtering out irrelevant data early in Power Query, you minimize the data volume that must be processed and stored, leading to faster load performance.

Exam trap

The trap here is that candidates may confuse 'splitting tables' (Option B) with star schema design best practices, but splitting a fact table unnecessarily violates dimensional modeling principles and harms performance, whereas proper star schema involves splitting dimensions from facts, not splitting facts themselves.

Practice this question →
50
MCQeasy

You are preparing data for a Power BI report. The source data contains a column with values like '1,234.56' formatted as text. You need to convert this to a numeric value for calculations. What is the best approach?

A.In Power Query Editor, split the column by comma and then use the second part.
B.In DAX, create a calculated column using VALUE() after removing commas.
C.In Power Query Editor, replace the comma with an empty string, then change the data type to Decimal Number.
D.In Power Query Editor, use the 'Clean' transform to remove non-numeric characters.
AnswerC

The comma is a thousands separator, not a decimal point, so removing it yields a clean numeric string that Power Query can cast to Decimal Number. Direct type conversion would fail or produce errors on the comma-formatted text.

Why this answer

Power Query Editor provides the most efficient and scalable method for cleaning and converting text-based numeric data. By replacing the comma with an empty string and then changing the column data type to Decimal Number, you perform the transformation directly in the data preparation layer (M language), which is optimized for performance and avoids the overhead of DAX calculated columns. This approach also ensures the data remains clean for all downstream calculations.

Exam trap

Microsoft often tests the misconception that the 'Clean' transform removes all non-numeric characters, but in reality it only removes non-printable control characters, not punctuation like commas or periods.

How to eliminate wrong answers

Option A is wrong because splitting the column by comma would separate the thousands separator from the number, leaving only the decimal part (e.g., '1' and '234.56'), which loses the integer portion and corrupts the value. Option B is wrong because using DAX with VALUE() after removing commas requires a calculated column that is evaluated row-by-row in the data model, which is less efficient than performing the transformation in Power Query and can lead to performance issues with large datasets. Option D is wrong because the 'Clean' transform in Power Query removes non-printable characters (like tabs and line breaks), not punctuation such as commas, so it would not remove the thousands separator and would leave the text value unchanged.

Practice this question →
51
MCQhard

You are reviewing a Power BI data source configuration JSON. The exhibit shows a data source definition. What is the privacy level setting for the data source 'SalesData'?

A.Organizational
B.Private
C.None
D.Public
AnswerB

The JSON explicitly declares "privacyLevel": "Private", so the correct answer is Private. This level tells Power Query that the source contains sensitive or confidential data, causing it to partition and rewrite queries so that no rows or columns from this source are mixed into other data sources without permission. It is the most restrictive privacy level and exactly matches the token present in the configuration file, making it the only factually defensible choice.

Why this answer

The privacy level setting for the data source 'SalesData' is 'Private' because the JSON snippet shows the property "privacyLevel":"Private". In Power BI, the Private privacy level restricts data from being combined with data from other sources, ensuring that sensitive data is not inadvertently shared across queries. This setting is explicitly defined in the data source configuration and is not Organizational, None, or Public.

Exam trap

The trap here is that candidates may confuse the 'Private' privacy level with the 'Organizational' level, thinking that data within the same organization is automatically safe to combine, but the JSON explicitly shows 'Private', which is the most restrictive setting.

How to eliminate wrong answers

Option A is wrong because 'Organizational' is a valid privacy level in Power BI that allows data to be combined with other Organizational data sources, but the JSON explicitly shows 'Private', not 'Organizational'. Option C is wrong because 'None' is not a valid privacy level in Power BI; the valid levels are Private, Organizational, and Public. Option D is wrong because 'Public' is a valid privacy level that allows data to be combined with any other data source, but the JSON explicitly shows 'Private', not 'Public'.

Practice this question →
52
Multi-Selecthard

Which THREE factors should you consider when choosing between Import and DirectQuery storage modes? (Choose three.)

Select 3 answers
A.User license type (Pro vs Premium).
B.Report interactivity and performance needs.
C.Report color scheme and branding.
D.Data volume and size limits.
E.Data refresh frequency and latency requirements.
AnswersB, D, E

Report interactivity and performance needs are critical because Import mode pre-loads data into memory via VertiPaq, enabling sub-second slicer and cross-filter responses. DirectQuery issues live queries to the source system, so performance depends on the source database's load, indexing, and connectivity speed, which can cause visible delays. For highly interactive dashboards, import or a composite model with aggregated import tables is usually superior.

Why this answer

Option B is correct because Import mode loads data into the in-memory VertiPaq engine and typically delivers the fastest query performance and richest interactivity, while DirectQuery pushes queries to the source and can introduce latency, so the report's interactivity and performance needs directly drive the choice. Option D is correct because Import is bounded by dataset size limits (for example, 1 GB per dataset on Shared/Pro capacity, up to 400 GB on Premium/Fabric capacities) and memory, whereas DirectQuery has no such dataset size limit since data stays at the source, making data volume a key factor. Option E is correct because Import requires scheduled refreshes (up to 8 per day on Pro, 48 on Premium) and only shows data as of the last refresh, while DirectQuery reflects source changes in near real time, so refresh frequency and latency requirements are decisive.

Option A is not a storage-mode factor because the Pro vs Premium license affects capacity features and refresh limits rather than the fundamental Import vs DirectQuery decision. Option C is irrelevant because color scheme and branding are purely visual design choices unrelated to how data is stored or queried.

Exam trap

The trap here is that candidates confuse licensing constraints (Pro vs Premium) with storage mode capabilities, when in fact both modes are available regardless of license, though Premium offers higher Import size limits and additional features like automatic page refresh for DirectQuery.

Practice this question →
53
Multi-Selecteasy

Which TWO of the following are valid database data source types for Power BI?

Select 2 answers
A.Azure SQL Database
B.SharePoint Online List
C.JSON file
D.MongoDB
E.Oracle database
AnswersA, E

Azure SQL Database is a first-party relational database service in Microsoft Azure with a native Power BI connector. It supports both Import mode and DirectQuery mode through the SQL Server or Azure SQL Database connector, and it authenticates with SQL credentials or Azure Active Directory. Because it is an officially maintained connector, it qualifies as a valid data source type in Power BI.

Why this answer

Azure SQL Database (A) is a valid Power BI data source because Power BI Desktop and the Power BI service include a native Azure SQL Database connector that uses the SQL protocol/TDS to query it directly. Oracle database (E) is also valid because Power BI ships with a dedicated Oracle connector (requiring the Oracle Data Provider for .NET, ODP.NET) to import or use DirectQuery against Oracle. SharePoint Online List (B) is not a database data source type—it is a cloud list service accessed via the SharePoint Online List connector, not a database.

JSON file (C) is a flat/semi-structured file source consumed through the JSON connector, not a database. MongoDB (D) is a NoSQL document store and is not one of Power BI's built-in database connectors (it would require an ODBC driver or third-party tool), so it is not a valid database data source type here.

Exam trap

Candidates may incorrectly assume that SharePoint Online List is a valid data source type due to its frequent use, but in the context of this question, only database types like Azure SQL and Oracle are considered. They also confuse file formats (JSON) and non-native connectors (MongoDB) with officially listed source types.

Practice this question →
54
MCQmedium

You receive a Power Query error: 'Expression.Error: The key didn't match any rows in the table.' This occurs when merging two queries. What is the most likely cause?

A.The join columns have different data types.
B.The second table is empty due to a permission issue.
C.The join columns contain duplicate values.
D.The join column in the first table contains values that do not exist in the second table.
AnswerD

This is the direct reason for the error: when a value in the first table's join column is not present in the second table's join column, the merge operation cannot find a matching row for that key. If the join kind requires a match or you are using a lookup-style operation, Power Query raises 'Expression.Error: The key didn't match any rows' instead of silently inserting nulls.

Why this answer

The error 'The key didn't match any rows in the table' occurs during a merge operation when Power Query attempts to find a matching value from the first table's join column in the second table's join column, but no match exists. This is a standard behavior for inner joins or left outer joins where the lookup fails, and it typically indicates that the first table contains values absent in the second table.

Exam trap

Microsoft often tests the misconception that this error is caused by data type mismatches or duplicate values, but the actual cause is a missing key in the lookup table, which is a fundamental concept in Power Query merge operations.

How to eliminate wrong answers

Option A is wrong because different data types in join columns would cause a type mismatch error (e.g., 'We cannot convert the value...'), not a key-matching error; Power Query automatically attempts type coercion during merge, but if it fails, it raises a different error. Option B is wrong because an empty second table due to permission issues would produce a different error, such as a data source access error or a 'Table is empty' warning, not a key-matching error; the merge operation would still attempt to match keys, but if the table is empty, no rows exist to match, leading to a different behavior (e.g., no rows returned) rather than this specific error. Option C is wrong because duplicate values in join columns are allowed in Power Query merges; they result in a many-to-many or one-to-many relationship, not a key-matching error, and the merge will still succeed by creating multiple matches.

Practice this question →
55
MCQmedium

You are designing a Power BI semantic model that uses a large fact table from Azure SQL Database. The table includes a date column. You need to ensure that the model supports time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR. What is the recommended approach?

A.Use the 'Add Calendar' function in Power Query and rely on auto-date/time.
B.Use DirectQuery mode and rely on the SQL Server date functions.
C.Use the built-in date hierarchy from the fact table's date column.
D.Create a separate date table and mark it as a date table in the model.
AnswerD

Creating a separate date table and marking it as a date table is the correct approach because it establishes a continuous, non-blank range of dates that the DAX engine explicitly recognizes for time intelligence. Marking the table via 'Mark as Date Table' sets the ‘Date’ column as the authoritative calendar reference, which enables functions like TOTALYTD, PREVIOUSYEAR, and PARALLELPERIOD to correctly compute period boundaries and offsets. This practice also supports fiscal calendars, holidays, and custom hierarchies, making it the recommended design pattern in Power BI for any model requiring robust date analysis.

Why this answer

Time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR require a separate date table with a contiguous date range marked as the date table in the model. This ensures that DAX can correctly calculate time-based aggregations across all dates, even if the fact table has gaps or missing dates. Without a marked date table, these functions may return incorrect or blank results.

Exam trap

The trap here is that candidates often think auto-date/time or the built-in date hierarchy is sufficient, but Microsoft explicitly recommends creating and marking a separate date table for reliable time intelligence, especially when using large fact tables with non-contiguous dates.

How to eliminate wrong answers

Option A is wrong because the 'Add Calendar' function in Power Query creates a date table but does not automatically mark it as a date table in the model; you must still manually mark it, and relying on auto-date/time disables the use of explicit date tables, which is required for robust time intelligence. Option B is wrong because DirectQuery mode does not support DAX time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR; these functions require a local date table in the model, not SQL Server date functions. Option C is wrong because using the built-in date hierarchy from the fact table's date column relies on auto-date/time, which creates hidden date tables but does not allow you to mark a custom date table, leading to potential issues with non-contiguous dates and incorrect time intelligence calculations.

Practice this question →
56
MCQeasy

You are preparing an Excel workbook for import into Power BI. The workbook contains one worksheet named 'SalesData' with a well-formed table named 'tblSales' that includes headers in the first row. You want the most reliable way to import only this table so that column names and types are detected correctly. What should you do?

A.Use Get Data > Folder, point to the directory containing the workbook, and combine the files.
B.Use Get Data > Excel workbook, select the workbook, and choose the 'tblSales' table in the Navigator.
C.Use Get Data > Text/CSV and point to the .xlsx file, then set the delimiter to Tab.
D.Use Get Data > Excel workbook, select the workbook, and choose the 'SalesData' worksheet in the Navigator.
AnswerB

Selecting the named table in Navigator imports the defined table range, so headers, column names, and data types are recognized reliably. Named tables are stable references, so adding rows or columns within the table updates the query without reconfiguration, making this the most dependable approach for a well-structured worksheet.

Why this answer

Choosing the named table in Navigator gives Power Query a precise, stable reference to the structured range. Headers and data types come through cleanly, and the query adapts as the table grows. Importing the whole worksheet or using connectors meant for other file types introduces detection problems and unnecessary complexity.

Exam trap

The trap here is selecting the worksheet instead of the named table, which imports extra cells and can confuse header and type detection.

Practice this question →
57
Multi-Selectmedium

Which TWO options are valid methods to combine multiple tables in Power Query?

Select 2 answers
A.Join
B.Concatenate
C.Merge
D.Union
E.Append
AnswersC, E

Merge is the correct Power Query transformation for combining tables horizontally. It matches rows between two tables based on one or more key columns, using a join kind such as Left Outer, Inner, or Full Outer. After merging, you expand the resulting nested column to bring in the desired fields from the second table. This aligns with the standard relational concept of a join, but within Power Query the precise name is 'Merge Queries'.

Why this answer

In Power Query, Merge is the correct operation for combining two tables horizontally by matching rows on one or more key columns, equivalent to a SQL JOIN, and it is performed via Home > Merge Queries. Append is the correct operation for stacking tables vertically, adding the rows of one table beneath another, equivalent to SQL UNION ALL, and it is performed via Home > Append Queries. The other options are not Power Query commands: Join and Union are SQL terms (Union also implies deduplication, which Append does not do), and Concatenate is a text function (Text.Combine / & operator) for joining string values, not tables.

Exam trap

The trap here is that candidates confuse SQL terminology (Join, Union) with Power Query's specific functions (Merge, Append), leading them to select the generic terms instead of the correct Power Query operations.

Practice this question →
58
MCQmedium

You connect to a large Azure SQL Database table with over 100 million rows. You need to create a report that shows sales by month for the current year only. Which data reduction technique should you use in Power Query to minimize data load?

A.Import all data and then remove columns that are not needed.
B.In Power Query, apply a date filter on the source query so only current year data is imported.
C.Load all data and filter using a visual-level filter in the report.
D.Use a calculated table in DAX to filter the data.
AnswerB

Filtering the source query by date restricts the import to current-year rows only, so Power Query loads a small subset rather than all 100 million rows. This reduces data volume at the source, which is the most efficient reduction technique.

Why this answer

Applying a date filter in Power Query at the source query level ensures that only rows from the current year are imported into the Power BI data model. This reduces the data volume from over 100 million rows to a fraction, minimizing memory usage and improving refresh performance. Power Query pushes the filter down to the Azure SQL Database using a WHERE clause in the SQL query, so only the filtered data is transferred over the network.

Exam trap

The trap here is that candidates often assume visual-level filters or DAX calculated tables are sufficient for performance, but they fail to realize that data reduction must occur at the data source or during import to minimize memory and refresh time.

How to eliminate wrong answers

Option A is wrong because importing all 100 million rows and then removing columns still loads the full row count into the data model, wasting memory and bandwidth; column removal does not reduce row volume. Option C is wrong because loading all data and applying a visual-level filter only hides rows in the report, but the entire dataset remains in the model, causing unnecessary memory consumption and slower performance. Option D is wrong because a calculated table in DAX still requires the full table to be loaded first before filtering, negating any data reduction at the import stage.

Practice this question →
59
Multi-Selectmedium

You are connecting to an on-premises Oracle database from Power BI Service. The gateway is installed and configured. However, the scheduled refresh fails with an error indicating that the data source credentials are invalid. Which TWO steps should you take to resolve the issue? (Choose two.)

Select 2 answers
A.Re-publish the Power BI report from Power BI Desktop.
B.Update the data source credentials in the gateway settings in Power BI Service.
C.Verify that the gateway machine can connect to the Oracle server and that the Oracle client is installed.
D.Edit the data source settings in Power BI Desktop and republish.
E.Reinstall the on-premises data gateway.
AnswersB, C

Updating the data source credentials in the gateway settings in Power BI Service is the correct resolution because the service uses those stored credentials every time it refreshes data through the on-premises data gateway. If the Oracle password changed, the account was locked, or the stored credentials simply expired, the refresh fails with authentication errors. Editing the data source in the service, re-entering the username and password, and re-testing the connection re-establishes a valid identity, allowing the refresh to succeed without altering the report or gateway installation.

Why this answer

The scheduled refresh failure indicates that the stored credentials for the on-premises Oracle data source in Power BI Service are invalid. You must update the data source credentials in the gateway settings under 'Manage gateways' in Power BI Service to provide a valid username and password that the gateway can use to authenticate against the Oracle database. Option C is correct because the gateway machine requires the Oracle client software (e.g., Oracle Data Access Components or ODP.NET) to be installed and configured, and the gateway must have network connectivity to the Oracle server; verifying these ensures the gateway can reach and authenticate with the database.

Exam trap

The trap here is that candidates assume re-publishing or editing the report in Power BI Desktop will propagate credential changes to the gateway, but Power BI Service stores credentials independently for scheduled refresh, and only updating them in the gateway settings resolves the issue.

Practice this question →
60
MCQhard

You are cleaning a column that contains numbers stored as text, with occasional leading/trailing spaces and currency symbols. You apply the function above to the column. However, some rows return null even though the original text appears to be a valid number, such as '$ 1,234.56'. What is the most likely cause?

A.The try...otherwise block is not catching parsing errors.
B.Text.Clean removes necessary decimal separators.
C.The function is applied to the wrong data type column.
D.The function does not remove commas and currency symbols before conversion.
AnswerD

The custom function uses Text.Trim and Text.Clean, which remove leading/trailing whitespace and non-printable characters only—they do not eliminate commas or currency symbols. When Number.From encounters a string like '$1,200', it raises an error because those characters are not part of a valid numeric literal in the default culture. The try-otherwise then returns null, so these unrecognized characters remain the true cause of the conversion failure. To fix it, you must strip commas and symbols (e.g., Text.Replace or Text.Remove) before invoking Number.From.

Why this answer

The correct answer is D because conversion functions like Number.FromText do not automatically strip currency symbols, commas, or spaces. The input '$ 1,234.56' must be cleaned (e.g., remove $, spaces, and commas) before conversion, or the conversion fails and returns null. The error handling (if any) only catches the error; it does not sanitize the input.

Exam trap

Candidates may assume that error handling will fix conversion failures, but error handling only catches the error after parsing fails. The input must be pre-processed to remove non-numeric characters before conversion.

How to eliminate wrong answers

Option A is wrong because the try...otherwise block does catch parsing errors; the issue is that the conversion itself fails, not that the error handling is faulty. Option B is wrong because Text.Clean removes non-printable characters (like line feeds), not decimal separators; decimal separators are preserved. Option C is wrong because the function is applied to a text column (as stated), and the data type mismatch is not the root cause—the problem is the uncleaned text content, not the column type.

Practice this question →
61
MCQmedium

You are preparing a Power BI report that uses data from Azure SQL Database. The data includes a date column that needs to be used in time intelligence calculations. You want to ensure that the date column is recognized as a date table in the data model. What should you do?

A.Set the data type of the date column to Date.
B.Use the DAX function DATEADD to create a date table.
C.In the model view, mark the table as a date table by selecting the date column.
D.Create a calculated column using DATEVALUE to convert the date.
AnswerC

This is the correct action because marking a table as a date table explicitly tells Power BI which column represents the primary date and defines the table as the source for time intelligence functions. When a table is marked as a date table, Power BI validates that the column contains unique, continuous dates and uses it to enable functions like TOTALYTD, SAMEPERIODLASTYEAR, and RELATEDDAX date filtering. This designation ensures that relationships and calculations are aligned with the reporting date range.

Why this answer

Marking a table as a date table in the model view explicitly tells Power BI that the table contains a complete set of dates for time intelligence calculations. This ensures that DAX functions like TOTALYTD, SAMEPERIODLASTYEAR, and DATEADD work correctly by using the marked date column as the primary date reference for the model, rather than relying on auto-generated date hierarchies.

Exam trap

The trap here is that candidates often confuse setting a column's data type to Date with marking the table as a date table, assuming the data type alone is sufficient for time intelligence, but Power BI requires explicit table marking to enable proper date filtering and DAX time functions.

How to eliminate wrong answers

Option A is wrong because setting the data type to Date only ensures the column is recognized as a date value, but it does not designate the table as a date table; time intelligence functions require a marked date table with a continuous range of dates. Option B is wrong because DATEADD is a time intelligence function used to shift dates, not a function to create a date table; creating a date table requires CALENDAR or CALENDARAUTO, not DATEADD. Option D is wrong because DATEVALUE converts a text string to a date, but it does not mark the table as a date table; the table must be explicitly marked in the model view for time intelligence to function properly.

Practice this question →
62
MCQeasy

You are importing data from a folder containing multiple CSV files with identical structure. You want to automatically combine all files into one table in Power Query. Which connector should you use?

A.Excel Workbook connector
B.CSV connector
C.Web connector
D.Folder connector
AnswerD

The Folder connector is the correct solution because it connects to a local directory, lists all files, and supports the 'Combine Files' feature in Power Query. With CSV files, it samples the first file to infer the schema, then automatically applies the same transformation to every file and appends the results into a single table. This is the standard approach for importing multiple CSV files with identical structures.

Why this answer

The Folder connector is the correct choice because it is specifically designed to connect to a folder containing multiple files, and when combined with the 'Combine Files' transformation in Power Query, it automatically merges all CSV files with identical structures into a single table. This connector handles the iterative process of reading each file and appending rows without manual scripting.

Exam trap

The trap here is that candidates often choose the CSV connector because they think it can handle multiple files, but it only processes a single file per connection, while the Folder connector is the correct tool for batch combining.

How to eliminate wrong answers

Option A is wrong because the Excel Workbook connector is used for importing data from a single Excel file, not for combining multiple CSV files from a folder. Option B is wrong because the CSV connector imports only one CSV file at a time; it does not support batch processing or automatic combination of multiple files from a directory. Option C is wrong because the Web connector is designed to import data from web URLs or APIs, not from local or network folders containing CSV files.

Practice this question →
63
MCQhard

You are building a Power BI data model with multiple fact tables and dimension tables. One of the dimension tables has a one-to-many relationship with two fact tables, but the relationships are inactive. You need to create measures that use both fact tables and the dimension table without relying on user interactions to activate relationships. What should you do?

A.Merge the fact tables into one table.
B.Create calculated columns in the dimension table to relate to each fact table.
C.Use the USERELATIONSHIP function in the measure definition.
D.Change both relationships to active.
AnswerC

USERELATIONSHIP is the DAX function designed to activate a specific inactive relationship inside a measure definition by taking two column arguments. It temporarily overrides the default active relationship for that measure only, leaving the model's active relationship intact for all other calculations. This is the standard pattern for handling multiple paths between the same tables, such as Order Date and Ship Date both referencing a Calendar table.

Why this answer

The USERELATIONSHIP function in DAX allows you to temporarily activate an inactive relationship within a measure definition. This enables you to use a dimension table with multiple fact tables without changing the model's default active relationships or relying on user interactions. By specifying the inactive relationship in the measure, you can perform accurate aggregations across both fact tables while maintaining the intended data model structure.

Exam trap

The trap here is that candidates often think they must make all relationships active or merge tables to use a dimension with multiple fact tables, not realizing that USERELATIONSHIP provides a clean, dynamic solution without compromising model integrity.

How to eliminate wrong answers

Option A is wrong because merging fact tables would create a single, denormalized table that violates star schema best practices, leads to data redundancy, and makes it harder to maintain separate business processes. Option B is wrong because calculated columns in the dimension table would create static values that do not dynamically respect filter context from the fact tables, and they cannot replace the functionality of active relationships in DAX measures. Option D is wrong because changing both relationships to active would create ambiguity in filter propagation, as Power BI cannot have two active one-to-many relationships from the same dimension table to different fact tables without causing cross-filtering issues.

Practice this question →
64
MCQhard

You are designing a Power BI data model for a sales analytics solution. The source data includes a 'Sales' fact table with millions of rows and dimension tables for 'Customer', 'Product', 'Date', and 'Salesperson'. You need to minimize the model size in Power BI. Which action should you take?

A.Remove any columns from the fact table that are not used in the model.
B.Set the 'Storage mode' of fact table to 'DirectQuery'.
C.Enable 'Include relationship columns' in the relationship settings.
D.Create a calculated column for row number.
AnswerA

Removing unused columns from the fact table directly reduces the amount of data imported into the model. Each column consumes memory for storage and compression dictionaries; eliminating unnecessary columns lowers the overall model footprint and speeds up refresh and query times. This is a foundational data modeling best practice for minimizing model size without changing query behavior or architecture.

Why this answer

Removing unused columns from the fact table reduces the amount of data imported into the Power BI model, which directly minimizes model size. Each column consumes memory for compression and storage, so eliminating unnecessary columns reduces the model footprint. DirectQuery (Option B) would also reduce the amount of data stored in the model, but it changes the storage mode to query the source directly, which is not the intended approach for minimizing the size of an imported data model.

Exam trap

The trap is that candidates might choose DirectQuery to avoid importing data, but the question asks about minimizing model size in an imported model context (implied by the need to minimize size). DirectQuery is a different architecture, not a means of optimizing the size of an imported model.

How to eliminate wrong answers

Option B is wrong because setting the fact table to DirectQuery avoids importing data into the in-memory model, but it does not minimize the model size—it shifts query execution to the source, which can degrade performance and is not a size-minimization technique for an imported model. Option C is wrong because enabling 'Include relationship columns' actually adds hidden columns to the fact table for relationship propagation, increasing model size rather than reducing it. Option D is wrong because creating a calculated column for row number adds a new column to the model, consuming additional memory and increasing the model size, which is the opposite of the goal.

Practice this question →
65
MCQeasy

You are preparing data from a CSV file that contains date values in the format 'MM/dd/yyyy'. When you load the file into Power BI Desktop, the dates appear as text. What should you do to ensure the dates are recognized as date data type?

A.Use 'Replace Values' to replace '/' with '-'
B.Change the column data type to Date in Power Query Editor
C.Split the column by delimiter '/'
D.Promote the first row as headers
AnswerB

In Power Query Editor, selecting the column and setting the Data Type to Date invokes the type conversion engine, which parses each string value using the current culture's date formats. For a CSV with dates like '12/31/2024', this transformation changes the column's metadata to DateTime/Date type, enabling date-specific transformations, filtering, and accurate loading into Power BI. Since the column is currently text, this is the necessary step to ensure Power BI treats the values as actual dates for time intelligence calculations.

Why this answer

Changing the column data type to Date in Power Query Editor forces Power BI to interpret the text values as dates using the current locale settings. Since the CSV file contains dates in 'MM/dd/yyyy' format, Power Query can parse this pattern when the data type is explicitly set to Date, converting the text strings into the Date data type for proper time intelligence and filtering.

Exam trap

The trap here is that candidates often think replacing delimiters or splitting columns will magically change the data type, but Power BI requires an explicit data type change in Power Query Editor to convert text to dates.

How to eliminate wrong answers

Option A is wrong because replacing '/' with '-' merely changes the delimiter character; the values remain as text strings and are not converted to the Date data type. Option C is wrong because splitting the column by delimiter '/' would separate the month, day, and year into three separate text columns, losing the original date structure and requiring additional steps to reassemble. Option D is wrong because promoting the first row as headers only affects column naming, not the data type of the values; it does not address the text-to-date conversion issue.

Practice this question →
66
MCQmedium

You have a Power BI report that uses a dataset with many columns. You want to reduce the dataset size by removing columns that are not used in any report visual. What is the best practice?

A.Remove the columns in Power Query Editor before loading
B.Use the Q&A feature to exclude columns
C.Apply report-level filters to exclude the columns
D.Hide the columns in the model view
AnswerA

Removing columns in Power Query Editor before loading is the only true data-reduction method among the options. This action prevents the column's data from ever being imported into the model, reducing the .pbix file size, memory footprint, and refresh time. It also improves report performance because fewer columns are processed during queries. This is the recommended approach when a column is not needed for any analysis.

Why this answer

Removing columns in Power Query Editor before they are loaded into the data model physically excludes them from the dataset, reducing its size and improving performance. This is the only method that prevents unused columns from consuming memory and storage in the in-memory VertiPaq engine.

Exam trap

The trap here is that candidates often confuse hiding columns (which only affects the user interface) with physically removing them, leading them to choose option D, but only removal before loading reduces dataset size.

How to eliminate wrong answers

Option B is wrong because the Q&A feature is a natural language query tool for exploring data, not a mechanism to exclude columns from the dataset. Option C is wrong because report-level filters only hide data at the visual or page level; the columns remain in the dataset and still consume memory. Option D is wrong because hiding columns in the model view only removes them from the field list in reports; they are still loaded into the data model and occupy space in the VertiPaq engine.

Practice this question →
67
Multi-Selectmedium

Which TWO are best practices when preparing data for Power BI?

Select 2 answers
A.Change data type of all columns after all transformations
B.Promote headers if the first row contains column names
C.Split columns that contain multiple values into separate rows
D.Avoid merging queries; always use lookups
E.Avoid unpivoting columns; keep data wide
AnswersB, C

Promoting headers is a best practice because it replaces the default generic column names (Column1, Column2) with the meaningful labels from the first row, which then become the actual column names. Once promoted, you can reference these columns by name in Power Query formulas, making expressions more readable and maintainable. It also improves clarity for end users in the data model, since they see intuitive field names instead of placeholders.

Why this answer

Option B is correct because promoting headers when the first row contains column names ensures Power BI treats that row as field names rather than data, giving properly named columns for modeling and visuals. Option C is correct because splitting columns that contain multiple values into separate rows normalizes the data into a proper tabular shape, which is required for accurate aggregations and relationships in Power BI. Option A is not a best practice because data types should generally be set as early as possible, not after all transformations, to avoid errors and unexpected coercion.

Option D is wrong because merging queries is a valid and often efficient technique; lookups are not always preferable. Option E is wrong because unpivoting is a recommended transformation to convert wide (crosstab) data into a tall, analysis-friendly structure.

Exam trap

The trap here is that candidates may think changing data types at the end is safer (Option A) or that merging queries should be avoided (Option D), but the exam tests understanding that data type changes should be applied early and that merging is a standard relational data preparation technique.

Practice this question →
68
MCQeasy

You are creating a Power BI report for a marketing team. The data is stored in a folder of CSV files on a network drive that is accessible from your computer. You need to combine all CSV files into a single table in Power BI. The files have the same structure. What should you do in Power Query?

A.Connect to each CSV file individually and use Append Queries.
B.Use the 'Combine Files' transform after connecting to the folder.
C.Use Merge Queries to join the files based on a common column.
D.Change the data source settings to treat the folder as a single data source.
AnswerB

The 'Combine Files' transform is the correct approach because Power Query connects to the folder, reads a sample file to infer the schema, and then automatically applies the same transformation steps to every CSV in that folder, stacking them into a single table. It handles an arbitrary number of files, is refreshable when files are added or removed, and is the native, scalable pattern for importing multiple CSV files with identical structures.

Why this answer

Power Query's 'Combine Files' transform is specifically designed to handle multiple CSV files with identical schemas stored in a folder. When you connect to the folder as a data source, Power Query automatically generates a sample file query and a transformation function that applies the same steps to all files, then combines the results into a single table. This is the most efficient and recommended approach for this scenario, avoiding manual appending or merging.

Exam trap

The trap here is that candidates often confuse 'Append Queries' (which stacks rows) with 'Combine Files' (which automates the same process for a folder), or mistakenly think 'Merge Queries' (which joins columns) is appropriate for combining files with identical structures.

How to eliminate wrong answers

Option A is wrong because connecting to each CSV file individually and using Append Queries is inefficient and error-prone; it requires manual setup for each file and does not scale well if new files are added to the folder. Option C is wrong because Merge Queries are used to join tables based on a common column (like a SQL JOIN), not to combine rows from multiple files with the same structure; this would incorrectly attempt to match rows across files rather than stacking them. Option D is wrong because changing data source settings to treat the folder as a single data source is not a valid Power Query operation; the folder connector already treats the folder as a container, but you must explicitly use the 'Combine Files' transform to merge the contents.

Practice this question →
69
Multi-Selectmedium

Which TWO of the following are valid reasons to use a calculated column instead of a measure in Power BI? (Select exactly two.)

Select 2 answers
A.You need to use the column in a relationship.
B.You need to create a hierarchy for drill-down.
C.You need to perform a dynamic aggregation that changes with filters.
D.You need to calculate a running total.
E.You need to use the value as a filter or slicer.
AnswersA, E

Relationships require columns, not measures.

Why this answer

Calculated columns are evaluated row by row and stored in the model, making them available for use in relationships. Measures, in contrast, are evaluated at query time and cannot be used to define relationships between tables in Power BI.

Exam trap

The trap here is that candidates often confuse the static nature of calculated columns with the dynamic behavior of measures, incorrectly assuming that calculated columns can perform dynamic aggregations or running totals that respond to slicer selections.

Practice this question →
70
MCQeasy

You are preparing data for a Power BI report. You have a table that contains a 'ProductID' column with some null values. You need to ensure that the 'ProductID' column does not contain any null values in the data model. Which Power Query transformation should you apply?

A.Group By
B.Remove Duplicates
C.Remove Rows -> Remove Blank Rows
D.Replace Values -> Replace null with a default value
AnswerD

Replace Values -> Replace null with a default value is the correct approach because it directly modifies the ProductID column, converting each null into a specified default such as 'Unknown' or '0'. This preserves all rows while guaranteeing that no empty ProductID values remain, which is exactly the requirement. Power Query's Replace Values operation supports replacing nulls specifically, making it a targeted and effective data-cleaning step.

Why this answer

Replacing null values with a default value directly ensures that the ProductID column has no nulls in the data model. This transformation can be applied to a specific column using 'Replace Values' in Power Query, where you replace null with a chosen default. Options A and B do not address null values.

Option C, 'Remove Blank Rows', only removes rows where all columns are blank, so rows with data in other columns but null ProductID remain, failing the requirement.

Exam trap

The trap is thinking that 'Remove Blank Rows' removes rows with any null in a column; actually it only removes rows where every cell in the row is null. For a specific column like ProductID, filtering rows where ProductID is null or replacing nulls are the correct approaches.

How to eliminate wrong answers

Option A is wrong because 'Group By' aggregates rows based on a column, but it does not remove or replace null values; it would group nulls together but leave them in the data. Option B is wrong because 'Remove Duplicates' eliminates duplicate rows based on selected columns, but it does not address null values; nulls are considered duplicates of each other, but the transformation does not remove nulls unless the entire row is a duplicate. Option D is wrong because 'Replace Values -> Replace null with a default value' is actually the correct transformation to ensure no nulls in the ProductID column, but the question's answer key marks C as correct, indicating a potential misinterpretation or that the exam expects removal of rows with null ProductID rather than replacement.

Practice this question →
71
MCQmedium

You are importing data from an Excel workbook that contains multiple sheets. Each sheet has similar structure but different data for different regions. You need to combine all sheets into a single table for analysis. What should you do?

A.In Power Query Editor, use Merge Queries to combine the sheets.
B.In Power Query Editor, use Append Queries to combine the sheets.
C.Copy and paste the data from each sheet into a master sheet in Excel.
D.In Power Query Editor, use Group By to consolidate the data.
AnswerB

Append Queries stacks tables with matching columns vertically, combining the identically structured regional sheets into one table. This satisfies the requirement to consolidate all sheets into a single table for analysis without altering the underlying column structure.

Why this answer

The Append Queries operation in Power Query Editor is designed to combine rows from multiple tables or queries with similar column structures into a single table. Since each sheet in the Excel workbook contains data for different regions with the same structure, appending them stacks the rows, creating a unified dataset for analysis.

Exam trap

The trap here is that candidates often confuse Merge Queries (which combines columns via joins) with Append Queries (which combines rows), leading them to select Option A when the requirement is to stack data from multiple sheets.

How to eliminate wrong answers

Option A is wrong because Merge Queries performs a join operation based on matching columns, which combines columns from different tables rather than stacking rows; this would not produce a single table of all region data. Option C is wrong because manually copying and pasting data from each sheet into a master sheet in Excel is inefficient, error-prone, and does not leverage Power Query's automated data transformation capabilities, which is the expected approach for the PL-300 exam. Option D is wrong because Group By is used to aggregate data by grouping rows based on column values and performing calculations like sum or count; it does not combine multiple sheets into one table.

Practice this question →
72
MCQhard

You are preparing a Power BI dataset that includes a table 'Products' imported from an Excel workbook. The table has a column 'ProductCode' that contains values like 'A100', 'B200', etc. You need to create a new column that extracts the numeric part of the code (e.g., 100, 200) and uses it as an integer. Which Power Query transformation should you use?

A.Use the Replace Values transformation to replace 'A' and 'B' with an empty string, then change the type.
B.Use the Extract transformation and select 'Text Before Delimiter'.
C.Use the Split Column transformation by delimiter 'A' and keep the second part.
D.Add a Custom Column with the formula Text.Select([ProductCode], {"0".."9"}) and change the type to Whole Number.
AnswerD

Text.Select extracts only the characters specified in the list, in this case digits 0 through 9. This yields the numeric portion as text, which can then be converted to Whole Number. This approach is flexible and handles varying letter prefixes. It is a robust way to isolate numbers from alphanumeric strings.

Why this answer

Using Text.Select with a list of digits extracts only the numeric characters from the code, regardless of the letter prefix. This creates a text column that can then be converted to an integer. Other methods are either too specific to certain prefixes or extract the wrong portion.

Text.Select is the most flexible and correct approach.

Exam trap

The trap here is assuming that a simple Replace Values or Split Column will work for all codes, when the prefixes can vary and are not known in advance.

Practice this question →
73
MCQmedium

You are connecting to a SharePoint folder that contains Excel workbooks. Each workbook has multiple sheets. You need to combine data from a specific sheet named 'Sales' across all workbooks. Which Power Query approach should you use?

A.Use the SharePoint Online List connector and select the document library.
B.Use the SharePoint folder connector, filter by .xlsx, then expand the Content column and filter by sheet name 'Sales'.
C.Use the Excel connector and specify the folder path.
D.Use the Web connector and provide the SharePoint site URL.
AnswerB

Using the SharePoint folder connector is the correct approach because it connects to a library folder and returns a table with one row per file, including a Content column that stores the raw binary data of each Excel workbook. After filtering that table to .xlsx files, you expand the Content column, which invokes Power Query's Excel parser to load each workbook and list all its worksheets. Applying a filter on the sheet name to 'Sales' ensures that exactly the sheet you need is combined across all matching workbooks, making this the native and reliable method for importing multiple Excel files from a SharePoint folder.

Why this answer

The SharePoint folder connector retrieves all files in the folder, including Excel workbooks. By filtering for .xlsx files and then expanding the Content column, you access the binary data of each workbook. You can then filter by the 'Sales' sheet name to combine data from that specific sheet across all workbooks, which is the only approach that directly handles multiple workbooks with multiple sheets.

Exam trap

The trap here is that candidates often confuse the SharePoint folder connector with the SharePoint Online List connector, mistakenly thinking the list connector can access document libraries, when it is strictly for list data.

How to eliminate wrong answers

Option A is wrong because the SharePoint Online List connector is designed for SharePoint lists (e.g., custom lists, task lists), not for document libraries containing Excel files; it cannot read Excel workbook sheets. Option C is wrong because the Excel connector connects to a single Excel file, not a folder of workbooks, so it cannot combine data from multiple files. Option D is wrong because the Web connector is for connecting to web pages or APIs via HTTP, not for accessing SharePoint folder structures or parsing Excel files.

Practice this question →
74
MCQeasy

You are merging two tables in Power Query: 'Orders' and 'Customers'. You want to include only rows from Orders that have a matching CustomerID in Customers. Which join kind should you use?

A.Inner join
B.Full Outer join
C.Right Outer join
D.Left Outer join
AnswerA

Inner join is the correct choice because it returns only rows where the join key exists in both the Orders and Customers tables. This effectively filters the data to matching records, which is typically the desired outcome when merging transactional data with dimension tables. In Power Query, inner join is the default and guarantees that no unmatched rows are included, ensuring referential integrity in the merged result.

Why this answer

An inner join in Power Query returns only rows from both tables where there is a match on the join key. Since you want to include only rows from Orders that have a matching CustomerID in Customers, the inner join is the correct choice. It filters out any Orders rows without a corresponding CustomerID in the Customers table.

Exam trap

The trap here is that candidates often confuse left outer join (which keeps all rows from the first table) with inner join, mistakenly thinking they need to preserve all Orders rows, but the question explicitly requires only matching rows.

How to eliminate wrong answers

Option B (Full Outer join) is wrong because it returns all rows from both tables, including rows without matches, which would include Orders rows with no matching CustomerID. Option C (Right Outer join) is wrong because it returns all rows from Customers and only matching rows from Orders, which would include Customers rows without matching Orders, not the desired behavior. Option D (Left Outer join) is wrong because it returns all rows from Orders and only matching rows from Customers, which would include Orders rows with no matching CustomerID, contrary to the requirement.

Practice this question →
75
MCQmedium

You are building a Power BI data model from an Azure SQL Database. The source table contains a column 'OrderDate' of type datetime. You want to create a date table in Power Query that includes all dates from the minimum to maximum OrderDate. Which M function should you use to generate the list of dates?

A.List.Dates
B.List.Generate
C.List.DateTimes
D.List.Range
AnswerA

List.Dates is the correct M function because it is specifically designed to generate a sequential list of date values. Its signature, List.Dates(start as date, count as number, step as duration), creates a contiguous series by incrementing the start date by the provided duration each time. For example, List.Dates(#date(2020,1,1), 366, #duration(1,0,0,0)) returns a list of all 366 days in 2020, which can then be converted directly into a date dimension table. This makes it the most direct, readable, and type-appropriate choice for this requirement.

Why this answer

`List.Dates` generates a list of sequential dates (type `date`) given a start date, a count of values, and a step duration. In Power Query, when you need a date table covering the range from the minimum to maximum `OrderDate`, you compute the count as `Duration.Days(MaxDate - MinDate) + 1` and use `List.Dates(MinDate, Count, #duration(1,0,0,0))`. This produces a clean list of dates without time components, ideal for a date dimension.

Exam trap

The trap here is that candidates confuse `List.DateTimes` (which includes time) with `List.Dates` (date only), or they overcomplicate the solution by choosing `List.Generate` when a simpler, purpose-built function exists.

How to eliminate wrong answers

Option B is wrong because `List.Generate` is a general-purpose generator that requires a custom loop function with initial, condition, next, and optional transform parameters; it is overly complex and not the direct function for generating a simple sequence of dates. Option C is wrong because `List.DateTimes` generates a list of datetime values (including time components), not just dates, which would introduce unnecessary time granularity and potential performance overhead for a date table. Option D is wrong because `List.Range` extracts a contiguous subset from an existing list; it does not generate a new list of dates from scratch.

Practice this question →
Page 1 of 3 · 171 questions totalNext →

Ready to test yourself?

Try a timed practice session using only Prepare the data questions.

Start 20-question session