Knowledge + Practice

CCNA Pl300 Prepare Data Questions

75 of 264 questions · Page 1/4 · Pl300 Prepare Data topic · Answers revealed

Practice these questions Exam hub All 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

A time dimension allows consistent time analysis.

Why this answer

Option D is correct because 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 partitions the table and only refreshes partitions that have changed, based on a date/time column.

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

This will split the date into three columns: Year, Month, Day.

Why this answer

Option B is correct because splitting the 'Date' column by the '-' delimiter in Power Query separates the text into individual components (Year, Month, Day) in one step. This is a direct transformation for extracting parts from a date string in a standard format like 'YYYY-MM-DD'.

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

Login failed indicates the credentials are incorrect or insufficient.

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

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

Reduces data size.

Why this answer

Option C is correct because removing unused columns reduces the amount of data loaded into the Power Query engine and the data model, directly decreasing memory usage and refresh time. Power Query processes all columns in a query step, so eliminating unnecessary columns early minimizes the data footprint for subsequent transformations and the final load.

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 →

6

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

Auto-date/time creates hidden date tables for each date column, increasing model size. Disabling it reduces overhead.

Why this answer

Option A is correct because disabling the auto-date/time feature prevents Power BI from automatically creating hidden date tables for each date column, which can significantly increase model size and processing time. This is especially important when working with large fact tables from Azure Synapse Analytics, as it reduces memory consumption and improves query performance by eliminating unnecessary overhead.

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 →

7

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

Essential for time intelligence.

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 →

8

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

Even though the gateway is running, the credentials for the SharePoint Online data source must be configured in the gateway settings.

Why this answer

Option D is correct because 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 →

9

MCQhard

Refer to the exhibit. The JSON policy shows configuration for an incremental refresh dataflow. You notice that the dataflow refresh takes longer than expected. Which setting should you adjust to potentially improve performance?

A.Change jobType to 'FullRefresh' only

B.Increase maxParallelism to 20

C.Reduce the pollingInterval to 15

D.Increase retryCount to 5

AnswerB

More parallelism can process partitions faster.

Why this answer

Increasing maxParallelism to 20 allows the dataflow engine to process more partitions concurrently during an incremental refresh, reducing overall execution time. The default parallelism is often too low for large datasets, and raising this value can significantly improve throughput by utilizing available compute resources more efficiently.

Exam trap

The trap here is that candidates confuse pollingInterval or retryCount with performance-tuning settings, when in fact they are related to monitoring and reliability, not throughput.

How to eliminate wrong answers

Option A is wrong because changing jobType to 'FullRefresh' would refresh the entire dataset instead of only new or changed rows, which would likely increase refresh time and negate the benefits of incremental refresh. Option C is wrong because reducing pollingInterval to 15 affects how often the system checks for refresh status, not the actual data processing speed, and has no impact on refresh duration. Option D is wrong because increasing retryCount to 5 only affects fault tolerance by retrying failed operations, not the performance of a successful refresh.

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

Parameters allow dynamic filtering.

Why this answer

Option B is correct because 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

Fewer visuals and filters reduce the number of queries sent to the source.

Why this answer

Option B is correct because 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

Power Query can push the top N query to the source, reducing data transfer.

Why this answer

Option B is correct because using the 'Top N' filter in Power Query pushes the filter to the source if supported, reducing data transfer. Option A is wrong because it loads all rows. Option C is wrong because DirectQuery does not reduce the load; it queries the source each time.

Option D is wrong because native SQL query may still load all rows unless filtered.

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

Incremental refresh loads only new and updated rows, reducing the amount of data loaded each refresh.

Why this answer

Option C is correct because 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

MCQhard

You have a Power BI dataset that uses DirectQuery to an Azure SQL Database. Users complain that reports take too long to load. You suspect that the database is overwhelmed by queries. What should you do to improve performance while keeping DirectQuery?

A.Reduce the number of visuals per page and apply slicers to limit data.

B.Increase the Power BI Premium capacity size.

C.Create aggregations in the dataset.

D.Change the storage mode to Import.

AnswerA

Fewer visuals and slicers reduce the query load.

Why this answer

Option A is correct because reducing the number of visuals per page and applying slicers to limit data reduces the number of DAX queries sent to the Azure SQL Database via DirectQuery. Each visual generates at least one query, so fewer visuals mean fewer concurrent queries, and slicers add WHERE clauses that reduce the result set size, lowering the load on the database.

Exam trap

The trap here is that candidates often assume scaling up the Power BI service (Option B) will fix database performance issues, but DirectQuery performance is fundamentally limited by the source database's ability to handle queries, not by Power BI's compute capacity.

How to eliminate wrong answers

Option B is wrong because increasing Power BI Premium capacity size only provides more memory and CPU for the Power BI service, but does not reduce the number or complexity of queries sent to the Azure SQL Database; the database remains the bottleneck. Option C is wrong because aggregations in DirectQuery mode are not supported; aggregations require Import or Dual storage modes to pre-aggregate data locally. Option D is wrong because changing the storage mode to Import would break the requirement to keep DirectQuery; Import mode loads data into the Power BI engine, which changes the data freshness and storage model entirely.

Practice this question →

15

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 can process large data.

Why this answer

Option A is correct because dataflows in Power BI allow you to ingest and transform large volumes of data using the Power Query engine running in the cloud, which can handle datasets that exceed the 1 GB per dataset limit in shared capacity or the 10 GB limit in Premium capacities. By storing the transformed data in Azure Data Lake Storage Gen2, dataflows enable you to work with larger data volumes without being constrained by dataset size limits.

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 →

16

MCQmedium

You are using Power Query to combine data from multiple Excel files stored in a SharePoint Online document library. Each file has the same structure. You need to ensure that the query automatically includes new files added to the library without manual updates. Which approach should you use?

A.Use 'Get Data from Excel' and specify each file path manually.

B.Use 'Get Data from SQL Server' and write a query to read files.

C.Use 'Get Data from SharePoint Online Folder' and then combine files using 'Combine & Transform Data'.

D.Use 'Get Data from SharePoint Online List' and then expand the file content.

AnswerC

This approach dynamically lists all files and can be refreshed to include new files.

Why this answer

Option C is correct because the 'Get Data from SharePoint Online Folder' connector in Power Query retrieves metadata for all files in the folder, and the 'Combine & Transform Data' action automatically applies a sample file transformation to all files. When new files are added to the library, refreshing the query will include them without manual intervention, as the connector dynamically reads the folder contents.

Exam trap

The trap here is that candidates confuse 'SharePoint Online Folder' with 'SharePoint Online List', thinking that a list can also combine files, but lists store metadata and require additional expansion steps that do not automatically handle new files with the same structure.

How to eliminate wrong answers

Option A is wrong because manually specifying each file path requires updating the query whenever a new file is added, which violates the requirement for automatic inclusion. Option B is wrong because SQL Server is a relational database, not a file storage system; it cannot directly read Excel files from SharePoint Online, and writing a query to read files is not a supported approach. Option D is wrong because 'Get Data from SharePoint Online List' retrieves list items (metadata), not the actual file content; expanding file content from a list requires additional steps and does not natively support combining multiple Excel files with the same structure.

Practice this question →

17

MCQhard

You are connecting Power BI to an Azure SQL Database. The database contains a table 'Orders' with 10 million rows. You need to minimize the data load time and ensure that only the most recent 30 days of data are imported. Which approach should you use?

A.Import the entire table and apply a date filter in Power Query Editor.

B.Enable incremental refresh policy with a filter on the date column.

C.Use DirectQuery mode and apply a date filter in the report.

D.Use a Native Query that selects only the last 30 days of data.

AnswerD

This pushes the filter to the source, reducing data volume.

Why this answer

Option D is correct because using a Native Query that selects only the last 30 days of data pushes the filtering logic to the Azure SQL Database, reducing the amount of data transferred over the network and imported into Power BI. This minimizes load time by avoiding the import of all 10 million rows, while still ensuring only the required 30-day window is brought into the data model.

Exam trap

The trap here is that candidates often confuse incremental refresh (Option B) with a one-time filter, not realizing that incremental refresh requires a pre-configured policy and is meant for ongoing refreshes, not for reducing initial import size.

How to eliminate wrong answers

Option A is wrong because importing the entire 10 million rows and then filtering in Power Query Editor still requires the full data transfer and processing overhead, defeating the goal of minimizing load time. Option B is wrong because incremental refresh policy is designed for scheduled refreshes over time, not for a one-time import of the most recent 30 days; it requires a pre-existing date-range partition and does not reduce the initial load. Option C is wrong because DirectQuery mode does not import data but queries the source live, which can cause performance issues with large tables and does not meet the requirement to import data (the question specifies 'imported').

Practice this question →

18

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 refreshes during maintenance windows can avoid conflicts.

Why this answer

Option C is correct because incremental refresh policies must be aligned with the data warehouse's maintenance windows to avoid conflicts, such as attempting to refresh data while the source is being updated or backed up. This ensures data consistency and prevents refresh failures due to locked tables or incomplete data loads. Power BI's incremental refresh relies on the source being available and stable during the refresh window.

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 →

19

MCQhard

You are reviewing a Power BI data source configuration in the data source settings. The exhibit shows the JSON representation of a data source. Which issue might arise from this configuration?

A.The server name contains a hyphen, which is invalid in SQL Server connection strings.

B.The CommandTimeout value is too low and may cause queries to time out.

C.The option 'CreateNavigationProperties' is set to false, which may prevent relationships from being created.

D.The authentication kind 'Key' is not supported for Azure SQL Database, causing connection failure.

AnswerD

'Key' is not a valid authentication method for SQL Server; it should be 'UsernamePassword' or 'ServicePrincipal'.

Why this answer

Option D is correct because Azure SQL Database does not support the 'Key' authentication kind in Power BI data source settings. Azure SQL Database requires either Windows authentication, database credentials (Username/Password), or Azure AD-based authentication (such as OAuth2 or Service Principal). The 'Key' authentication kind is typically used for Azure Storage or Cosmos DB, not for Azure SQL Database, so this configuration will cause a connection failure.

Exam trap

The trap here is that candidates may assume 'Key' authentication is valid for any Azure service, but Microsoft restricts authentication methods per data source type, and Azure SQL Database explicitly does not support key-based authentication.

How to eliminate wrong answers

Option A is wrong because hyphens are perfectly valid in SQL Server connection strings; the server name can contain hyphens without any issue. Option B is wrong because the CommandTimeout value shown in the exhibit is not specified as too low; the default is 10 minutes, and the exhibit does not indicate an unusually low value that would cause timeouts. Option C is wrong because 'CreateNavigationProperties' set to false only affects whether Power BI automatically creates relationships in the data model based on foreign keys; it does not prevent relationships from being created manually, and it does not cause a connection failure.

Practice this question →

20

Multi-Selectmedium

You are preparing data for a star schema. Which THREE types of tables are typically included in a star schema?

Select 3 answers

A.Staging tables

B.Bridge tables

C.Dimension tables

D.Date tables

E.Fact tables

AnswersC, D, E

Store descriptive attributes.

Why this answer

In a star schema, dimension tables (C) provide descriptive attributes for the measures stored in fact tables. They are denormalized to optimize query performance in tools like Power BI, enabling fast slicing and dicing of data without complex joins.

Exam trap

The trap here is that candidates may confuse staging tables or bridge tables as core star schema components, but Microsoft explicitly tests that only fact, dimension, and date tables are the three standard table types in a star schema.

Practice this question →

21

MCQhard

Refer to the exhibit. The Power Query M code connects to a SQL database and groups sales data. You notice the query is slow. Which change would most likely improve performance?

A.Remove the GroupedRows step to simplify

B.Push the Year filter into the SQL query using a native query

C.Remove the filter on Year to process all data

D.Perform grouping in SQL instead of Power Query

AnswerB

Filtering at source reduces data transferred.

Why this answer

Option B is correct because pushing the Year filter into the SQL query via a native query reduces the amount of data transferred from SQL Server to Power Query. By filtering at the source, Power Query only loads the relevant rows, which minimizes memory usage and processing time. This leverages query folding, which is the most efficient way to handle large datasets in Power Query.

Exam trap

The trap here is that candidates often assume removing steps or performing grouping in Power Query is always faster, but they overlook the critical concept of query folding and the performance gain from pushing filters to the source database.

How to eliminate wrong answers

Option A is wrong because removing the GroupedRows step would eliminate the aggregation, but the query would still load all unfiltered data from SQL Server, which does not address the root cause of slow performance (data volume). Option C is wrong because removing the filter on Year would load even more data, making the query slower, not faster. Option D is wrong because performing grouping in SQL instead of Power Query is essentially the same as pushing the filter (Option B), but the question specifically asks for the change that would 'most likely improve performance' given the existing M code; Option D is a valid alternative but not the best answer because the exhibit shows a filter step already present, and pushing that filter is the most direct improvement, whereas grouping in SQL would require rewriting the query and may not be necessary if the bottleneck is the filter.

Practice this question →

22

MCQhard

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

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

B.Configure Incremental refresh with Detect data changes on the date column.

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

D.Create a calculated table with only the required columns.

AnswerB

This loads only the changed/new partitions, reducing refresh load while keeping full history.

Why this answer

Option B is correct because Incremental refresh allows you to partition the fact table by date, loading only recent data into the model while keeping the ability to query historical data on demand. By enabling 'Detect data changes', Power BI can efficiently identify and refresh only the partitions that have changed, minimizing data load and refresh time. This approach maintains query performance for any date range because historical partitions remain available in the model without being re-imported.

Exam trap

The trap here is that candidates often choose Import mode with a static filter (Option C) thinking it reduces data load, but they overlook the requirement to query any date range later, which Incremental refresh uniquely satisfies by partitioning data dynamically.

How to eliminate wrong answers

Option A is wrong because DirectQuery mode does not minimize data loaded into the model; it sends queries to the source database, which can be slow for large fact tables and does not reduce the in-memory footprint. Option C is wrong because applying a date filter in Power Query to limit rows would permanently restrict the data loaded, preventing the ability to query any date range later. Option D is wrong because creating a calculated table with only required columns does not address the row-level filtering by date and still requires importing the entire fact table, failing to minimize data load.

Practice this question →

23

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

Navigator allows you to choose specific sheets or tables to load.

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 →

24

Multi-Selectmedium

Which TWO transformations in Power Query are most likely to cause Query Folding to stop? (Select two.)

Select 2 answers

A.Pivot columns.

B.Filter rows based on a date column.

C.Remove columns.

D.Rename a column.

E.Add a custom column with a complex M formula.

AnswersA, E

Pivoting typically cannot be folded.

Why this answer

Option A is correct because Pivot columns in Power Query often require a transformation that cannot be translated into a native SQL query, causing Query Folding to break. When you pivot a column, the engine must restructure data from rows to columns, which typically involves operations like grouping and aggregation that many source systems (e.g., SQL Server) cannot fold back to a single SELECT statement. This forces Power Query to pull all data locally before applying the pivot, stopping folding.

Exam trap

The trap here is that candidates often assume any transformation that changes data structure (like pivot) or uses custom logic will break folding, but they may overlook that simple operations like filtering, removing columns, or renaming are fully foldable, while only complex or non-native operations cause folding to stop.

Practice this question →

25

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

Power Query shows icons (e.g., a folded paper) to indicate that a step is folded to the source.

Why this answer

Option C is correct because 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.

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 →

26

MCQmedium

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

A.Use Import mode and create aggregations

B.Use DirectQuery mode

C.Use Dual mode

D.Use Import mode with Incremental refresh

AnswerB

DirectQuery queries the source on demand, eliminating initial load.

Why this answer

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

Exam trap

The trap here is that candidates often assume Import mode is always faster for initial load due to caching, but for very large datasets, the import overhead makes DirectQuery the better choice for minimizing initial load time.

How to eliminate wrong answers

Option A is wrong because Import mode with aggregations still requires importing the base data into the Power BI model, which incurs the full initial load time for 10 million rows; aggregations only optimize query performance after the data is loaded. Option C is wrong because Dual mode is a hybrid that caches data in memory for some tables while using DirectQuery for others, but it still requires importing data for the cached portion, which does not minimize initial load time for a large table. Option D is wrong because Import mode with Incremental refresh still requires an initial full import of the 10 million rows, and incremental refresh only reduces subsequent refresh times, not the initial load time.

Practice this question →

27

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

COALESCE replaces Null with 0.

Why this answer

Option B is correct because 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 →

28

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

This splits the column into two columns.

Why this answer

Option B is correct because 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 →

29

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

Out of range errors often occur due to invalid date values.

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 →

30

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 stacks rows; renaming standardizes column names.

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 →

31

Multi-Selecthard

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

Select 3 answers

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

B.Create a connection to the SharePoint folder.

C.Filter the folder query to include only the relevant Excel files.

D.Use the 'Combine Files' transform with the first file as a sample.

E.Individually import each file and append them manually.

AnswersB, C, D

Necessary to access the files.

Why this answer

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

Exam trap

The trap here is that candidates often confuse 'Merge Queries' (which joins tables horizontally) with 'Append Queries' (which stacks rows vertically), and they may overlook that the 'Combine Files' transform is the automated way to append multiple files from a folder, not manual import and append.

Practice this question →

32

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

SharePoint Online is a cloud data source; Power BI can refresh directly.

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 →

33

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 enables numerical aggregations.

Why this answer

Option A is correct because you need to change the data type to a numeric type in Power Query Editor to perform aggregations. Option B is wrong because splitting the column does not change the data type. Option C is wrong because creating a calculated column in DAX is less efficient and may still require type conversion.

Option D is wrong because the relationship is not the issue.

Practice this question →

34

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

Standard and efficient for filtering.

Why this answer

Option B is correct because 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 →

35

MCQmedium

You are a data analyst for an e-commerce company. You have a Power BI dataset that imports data from a Salesforce source using the Salesforce connector. The dataset is refreshed daily. Recently, the refresh started failing with the error 'Unable to connect to the data source'. You have verified that the Salesforce credentials are correct and that the Salesforce service is healthy. What is the most likely cause?

A.The OAuth redirect URI for the Salesforce app is not configured correctly.

B.The on-premises data gateway is not installed.

C.The privacy levels for the data source are set to 'Private'.

D.The Power BI service is not available in the same region as Salesforce.

AnswerA

Correct. The redirect URI must match the Power BI service URL.

Why this answer

The Salesforce connector in Power BI uses OAuth 2.0 for authentication. Even if credentials are correct and the Salesforce service is healthy, a misconfigured OAuth redirect URI in the Salesforce connected app prevents Power BI from completing the authorization handshake, causing the 'Unable to connect to the data source' error during refresh. This is a common issue when the redirect URI does not match the Power BI service's callback endpoint.

Exam trap

The trap here is that candidates assume credential validity and service health guarantee connectivity, overlooking the OAuth redirect URI configuration which is a frequent misconfiguration in cloud-to-cloud integrations.

How to eliminate wrong answers

Option B is wrong because the on-premises data gateway is only required when connecting to on-premises data sources (e.g., SQL Server on a local network), not to cloud-based Salesforce. Option C is wrong because privacy levels (e.g., 'Private') control data combination behavior across sources during query execution, not the ability to connect or authenticate to a single source. Option D is wrong because Power BI service region availability does not affect connectivity to Salesforce; the Salesforce connector connects over the public internet regardless of region.

Practice this question →

36

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 groups by Category and sums Amount.

Why this answer

Option A is correct because 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 →

37

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 with Combine Files automatically appends all CSV files.

Why this answer

Option D is correct because 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 →

38

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.

AnswerB

Privacy level errors can occur even with a single source if the data source privacy setting is misconfigured or if the global privacy level is set to 'None' while the source is 'Private'.

Why this answer

The privacy level error in Power Query occurs when data from sources with incompatible privacy levels is combined. Option B is correct because the most likely cause is that the SQL Server data source has its privacy level set inconsistently across the environment (e.g., one source set to 'Private' and another to 'Public'), which prevents the query from merging or appending data due to Power BI's data privacy firewall. This firewall enforces isolation between sources based on their configured privacy levels, and an inconsistency triggers the error.

Exam trap

The trap here is that candidates often assume a privacy level error always involves combining two different data sources (like SQL Server and a CSV), but the error can also occur when the same data source has inconsistent privacy level settings across the environment, such as between Power BI Desktop and the Power BI Service.

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 →

39

Multi-Selecteasy

You are importing data from a web page that contains an HTML table. Power Query detects the table, but you notice that some columns contain HTML tags. Which TWO methods can you use to remove the HTML tags from the column values?

Select 2 answers

A.Split the column by delimiter '<' and then extract the first part.

B.Use 'Replace Values' to remove specific tags like <br> and </p>.

C.Remove the columns containing HTML tags.

D.Use the 'Clean' function to remove all HTML tags.

E.Use a custom column with the 'Html.Table' function to extract text.

AnswersB, E

For known tags, replace works.

Why this answer

Option B is correct because 'Replace Values' allows you to manually remove specific HTML tags (e.g., <br>, </p>) by replacing them with an empty string, which is a straightforward method for cleaning known tags. Option E is correct because the 'Html.Table' function in Power Query can parse HTML content and extract the text from a table, effectively stripping tags and returning clean text values.

Exam trap

The trap here is that candidates confuse the 'Clean' function (which removes non-printable characters) with a hypothetical HTML-cleaning function, leading them to select option D despite it being irrelevant to HTML tags.

Practice this question →

40

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

List.Dates generates a list of dates given start, count, and step.

Why this answer

Option B is correct because 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 →

41

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 →

42

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

The gateway stores credentials securely and users do not need access to the database.

Why this answer

Option A is correct because 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 here is that candidates often choose DirectQuery with SSO (Option B) thinking it avoids credential sharing, but SSO requires granting users direct database access, which violates the 'credentials not shared with users' requirement and does not work with a SharePoint Online list in a combined model.

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 →

43

MCQmedium

You have a Power BI dataset that uses a SQL Server view as the data source. The view returns data for all customers, but you only need data for customers in the 'West' region. The view does not accept parameters. How should you filter the data?

A.Use a native SQL query in Power Query with a WHERE clause to ensure query folding.

B.In Power Query, apply a filter on the Region column after loading the view.

C.Ask the database administrator to modify the view to include a WHERE clause.

D.Load the entire view into the model and use a report-level filter.

AnswerB

This filters the data after import.

Why this answer

Option B is correct because applying a filter in Power Query after loading the view is the standard approach when the source view does not accept parameters. This method leverages Power Query's query folding capabilities, which push the filter down to the SQL Server, reducing data transfer and improving performance. It avoids loading unnecessary data into the model while maintaining the ability to refresh efficiently.

Exam trap

The trap here is that candidates often assume a native SQL query is always more efficient, but Power Query's query folding can achieve the same result without breaking the connection to the view, and modifying the source view is not always an option.

How to eliminate wrong answers

Option A is wrong because using a native SQL query with a WHERE clause would bypass query folding and break the dependency on the view, potentially causing issues with refresh and governance. Option C is wrong because asking the DBA to modify the view is unnecessary and may not be feasible; Power Query can handle the filter without altering the source. Option D is wrong because loading the entire view into the model and using a report-level filter would import all customer data, wasting memory and degrading performance, and it does not reduce the data at the source.

Practice this question →

44

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

Filtering in Power Query reduces the amount of data loaded into the model.

Why this answer

Option C is correct because 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 →

45

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

Profiling can show data type errors.

Why this answer

The Column Profile feature in Power Query Editor provides a statistical summary of the data in a selected column, including value distribution, distinct counts, and data type detection. Option B is correct because the profile automatically highlights data type inconsistencies (e.g., numbers stored as text) by showing the 'Data Type' indicator and flagging errors, enabling you to identify and fix type issues before loading data.

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 →

46

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 format that is more efficient for large data volumes.

Why this answer

Options A, C, and E are correct because splitting the file, filtering rows early, and using an efficient file format improve performance. Option B is incorrect because the Power BI service can handle large files, but the gateway is not needed for cloud storage. Option D is incorrect because importing all columns reduces performance; you should select only needed columns.

Practice this question →

47

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

Query folding pushes filter to source.

Why this answer

Option B is correct because 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 →

48

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

Option C is correct because it explicitly removes both the dollar sign and the comma before changing the data type, ensuring that Power Query can interpret the cleaned text as a decimal number without errors. Directly changing the data type (Option B) would fail because Power Query cannot automatically parse currency symbols and thousands separators from text. Option A is inefficient because splitting the column is unnecessary when simple replacements suffice.

Option D is identical to C but omits the comma removal, which would leave the thousands separator and cause conversion failures.

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 →

49

Multi-Selecthard

You need to combine two tables in Power Query: 'Sales2023' (columns: Date, ProductID, Amount) and 'Sales2024' (columns: Date, ProductID, Amount). Which TWO transformations can be used to append rows from Sales2024 to Sales2023?

Select 2 answers

A.Combine Files

B.Use UNION function in DAX

C.Merge Queries as New with Full Outer join

D.Append Queries

E.Group By

AnswersC, D

Can combine all rows from both tables.

Why this answer

Option C is correct because a Merge with a Full Outer join in Power Query will combine all rows from both tables, effectively appending rows from Sales2024 to Sales2023 when the join key (e.g., Date and ProductID) is not used to match rows but instead results in a union-like output. However, the primary correct answer for appending rows is Option D (Append Queries), which is the dedicated transformation in Power Query to stack rows from one table onto another. Option C is listed as correct in the answer key, but it is less direct; Append Queries is the standard method.

Exam trap

The trap here is that candidates may confuse Merge Queries (which joins columns) with Append Queries (which stacks rows), and the Full Outer join option appears to work but is a misdirection; the correct Power Query transformation for appending rows is Append Queries, not Merge.

Practice this question →

50

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 (integer) is optimal for key columns.

Why this answer

Option C is correct because 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 →

51

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

This is a common credential issue.

Why this answer

Option C is correct because 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 →

52

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

Option A is correct because 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 →

53

Multi-Selectmedium

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

Select 2 answers

A.Extract - Duration

B.Extract - Year

C.Merge Columns

D.Format - Trim

E.Split Column by Delimiter (space)

AnswersA, E

Extracts time as duration from midnight.

Why this answer

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

Exam trap

The trap here is that candidates may think 'Extract - Duration' is only for calculating time differences, not for isolating the time portion, or they may overlook that splitting by a space delimiter is a valid alternative to more complex date/time functions.

Practice this question →

54

Matchingmedium

Match each DAX function to its description.

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

Concepts

Matches

Modifies the filter context

Evaluates an expression for each row and sums the results

Returns a table that represents a subset of another table

Clears all filters from a table or column

Returns a related value from another table

Why these pairings

These are common DAX functions used in Power BI.

Practice this question →

55

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

Generates a table of dates between start and end.

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 →

56

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

Appends rows from second table to first.

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 →

57

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

Proper data types improve storage and performance.

Why this answer

Setting correct data types for all columns (Option C) is a best practice because it ensures that Power BI uses the most efficient storage and processing methods. Incorrect data types can lead to increased memory usage, slower query performance, and inaccurate calculations, as Power BI must perform implicit conversions at query time.

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 →

58

MCQeasy

You are designing a Power BI semantic model for a retail company. You have a 'Products' table with columns: ProductID, ProductName, Category, UnitPrice. You need to ensure that when users filter by 'Category', they see only products belonging to that category. What should you set the 'Category' column's 'Data Category' property to?

A.Geography

B.Image URL

C.Category

D.Place

AnswerC

Category is the appropriate Data Category for product category fields.

Why this answer

The 'Data Category' property in Power BI is used to assign a semantic meaning to a column, such as 'Category', 'Geography', or 'Image URL', which helps Power BI apply appropriate default behaviors like grouping or filtering. Setting the 'Category' column's 'Data Category' to 'Category' ensures that when users filter by that column, Power BI treats it as a categorical field, displaying only products belonging to the selected category. This property does not affect the underlying data but informs the visualization layer how to interpret the column for better user experience.

Exam trap

The trap here is that candidates confuse the 'Data Category' property (which sets semantic metadata for visuals) with the 'Sort by Column' or 'Group' properties, mistakenly thinking it directly controls filtering logic rather than influencing how Power BI interprets the column for default visual behaviors.

How to eliminate wrong answers

Option A is wrong because 'Geography' is a data category intended for location-based columns (e.g., City, Country) and would not affect filtering behavior for product categories. Option B is wrong because 'Image URL' is used to display images from URLs in visuals, not to control filtering or grouping of categorical data. Option D is wrong because 'Place' is a specific data category for geographic places (e.g., addresses) and has no relevance to product category filtering.

Practice this question →

59

Multi-Selecthard

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

Select 2 answers

A.Convert Churn column to a numeric 0/1 column.

B.Create a calculated column for AverageCharges per customer.

C.Remove TotalCharges column if it can be calculated from MonthlyCharges and TenureMonths.

D.Set CustomerID as a unique key and mark it as the primary column.

E.Create a separate table for subscription types.

AnswersC, D

Reduces model size and redundancy.

Why this answer

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

Exam trap

Microsoft often tests the distinction between calculated columns and measures, leading candidates to choose Option B (creating a calculated column for an aggregation) instead of recognizing that such aggregations belong in measures to avoid bloating the model and breaking filter context.

Practice this question →

60

Multi-Selectmedium

You are connecting Power BI to an Azure SQL Database. You need to ensure that the connection uses the most secure authentication method available. Which THREE options should you consider? (Choose three.)

Select 3 answers

A.Use Windows authentication.

B.Use SQL Server authentication with a strong password.

C.Use a service principal with Microsoft Entra ID.

D.Use Microsoft Entra ID (formerly Azure AD) authentication.

E.Use a managed identity for the Power BI service.

AnswersC, D, E

Service principals are used for automated scenarios and are secure.

Why this answer

Option C is correct because using a service principal with Microsoft Entra ID (formerly Azure AD) enables token-based authentication for Power BI to Azure SQL Database, which is more secure than shared secrets or passwords. Service principals are non-interactive identities that can be granted fine-grained permissions, eliminating the need to store credentials and reducing the risk of credential exposure.

Exam trap

The trap here is that candidates often assume Windows authentication is the most secure option for Azure SQL Database, but it is not supported in Azure; the correct secure methods rely on Microsoft Entra ID tokens, service principals, or managed identities.

Practice this question →

61

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

Combines tables by stacking rows.

Why this answer

Option B is correct because the Append Queries operation in Power Query combines rows from two or more tables by stacking them vertically, which is a standard way to consolidate data from multiple sources when the columns are aligned. This is distinct from merging, which combines columns based on matching keys.

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 →

62

MCQhard

You are reviewing the M query snippet above. The query imports an Excel file and filters rows where Date is on or after January 1, 2024. However, the query fails with an error. What is the most likely cause?

A.The data type conversion cannot handle null values.

B.The Excel.Workbook function cannot read .xlsx files.

C.The date filter syntax is incorrect.

D.The file path is local and may not be accessible after deployment.

AnswerD

Hardcoded local paths cause failures when the file is not available.

Why this answer

Option B is correct because the file path is hardcoded to a local drive, which may not exist on the Power BI Service or another machine. Option A is wrong because the date filter syntax is correct. Option C is wrong because the Excel.Workbook function handles .xlsx files.

Option D is wrong because the data type transformations are correct.

Practice this question →

63

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

This allows you to see the view's output independently.

Why this answer

Option B is correct because 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 →

64

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

Option A is correct because 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 →

65

Matchingmedium

Match each Power BI concept to its definition.

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

Concepts

Matches

Fact and dimension tables in a denormalized structure

Uniqueness of values in a column

Link between tables based on common columns

Dynamic calculation using DAX

Static column added to a table using DAX

Why these pairings

These are fundamental concepts in data modeling.

Practice this question →

66

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

This removes the formatting and converts to number.

Why this answer

Option C is correct because 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 →

67

MCQeasy

You are importing data from a CSV file that contains a column 'Date' with values in different formats (e.g., '01/15/2024', '2024-01-15'). You need to ensure all dates are recognized correctly. What should you do in Power Query?

A.In Power Query, set the data type to 'Date' and use 'Using Locale' to specify the correct locale for each file.

B.Change the data source to a SQL database.

C.Split the column into multiple columns and merge them back.

D.Use 'Replace Values' to standardize the date formats.

AnswerA

Locale settings handle different date formats.

Why this answer

Option A is correct because Power Query's 'Using Locale' feature allows you to specify the cultural context (e.g., 'en-US' for MM/DD/YYYY or 'de-DE' for DD.MM.YYYY) when setting a column's data type to 'Date'. This ensures that dates in various formats, such as '01/15/2024' (US) and '2024-01-15' (ISO 8601), are interpreted correctly during import, as the locale dictates the expected date order and separator.

Exam trap

The trap here is that candidates often assume 'Replace Values' or manual splitting can handle date format inconsistencies, but Power Query's locale-aware type detection is the only built-in method that correctly interprets ambiguous date strings like '01/15/2024' versus '15/01/2024' without data loss.

How to eliminate wrong answers

Option B is wrong because changing the data source to a SQL database does not solve the problem of inconsistent date formats in the CSV; it merely shifts the data ingestion point without addressing the parsing logic. Option C is wrong because splitting and merging columns is a manual, error-prone workaround that does not leverage Power Query's built-in locale-aware date parsing, and it would require complex transformations for each format variant. Option D is wrong because 'Replace Values' is designed for static text replacement, not for interpreting and standardizing date formats; it cannot handle the semantic conversion of '01/15/2024' to a date value.

Practice this question →

68

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 sets privacyLevel to Private.

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 →

69

MCQhard

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

A.Use Table.Combine with the 'Table1' parameter

B.Use Remove Other Columns on the first file's columns

C.Use Table.ColumnsOfType to filter common columns

D.Use Merge Queries as New

AnswerC

Table.ColumnsOfType returns columns that exist in all tables.

Why this answer

Option D is correct because Table.ColumnsOfType allows selecting only columns that appear in all tables. Option A is wrong because Table.Combine appends all columns, creating nulls for missing columns. Option B is wrong because Merge queries are for joining, not combining.

Option C is wrong because Remove Other Columns removes columns not selected; you need to identify common ones first.

Practice this question →

70

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

Import is faster for interactive reports; DirectQuery may be slower.

Why this answer

Option B is correct because Import mode pre-aggregates data into an in-memory columnstore, enabling sub-second DAX query responses, while DirectQuery mode sends queries to the source database in real time, which can introduce latency. The choice between them directly impacts report interactivity and performance, as Import mode is optimized for fast, interactive slicing and dicing, whereas DirectQuery may suffer from slower response times on complex measures or large result sets.

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 →

71

Multi-Selecthard

You are importing data from a folder containing multiple Excel files with the same structure. You use Power Query's 'Combine Files' feature. Which TWO statements about this process are correct?

Select 2 answers

A.It automatically removes duplicate rows across files.

B.You can change the transformation order after combining.

C.It uses the first file as a template for transformation.

D.It automatically creates relationships between files.

E.It generates a sample file query to define transformations.

AnswersC, E

The first file's transformations are applied to others.

Why this answer

Option C is correct because when you use Power Query's 'Combine Files' feature, it uses the first file as a template to infer the schema and transformations. This sample file query defines how each subsequent file is processed, ensuring consistent column types and transformations across all files.

Exam trap

The trap here is that candidates often assume the 'Combine Files' feature automatically handles deduplication or relationship creation, when in fact it only standardizes transformations across files based on the first file's structure.

Practice this question →

72

Multi-Selecteasy

Which TWO of the following are valid 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, B

Power BI supports Azure SQL Database as a data source.

Why this answer

Azure SQL Database is a supported data source in Power BI, allowing direct import or DirectQuery connectivity via the SQL Server connector. This enables real-time or scheduled data refresh from a cloud-based relational database, leveraging T-SQL queries for data transformation.

Exam trap

The trap here is that candidates may confuse 'data source types' with 'data formats' or 'connectors,' leading them to select JSON file (a format) or MongoDB (a non-native connector) instead of recognizing that only officially listed connectors like Azure SQL Database and SharePoint Online List qualify as valid data source types in the Power BI interface.

Practice this question →

73

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

The error 'key didn't match any rows' means the merge operation found no matching rows for some keys.

Why this answer

Option D is correct because 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 →

74

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

Time intelligence functions require a continuous date table marked as a date table to work correctly.

Why this answer

Option D is correct because 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 →

75

MCQeasy

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

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

B.Use the 'Using Locale' transformation

C.Set the data type to Date using the 'Change Type' step in Power Query

D.Change the regional settings of the Power Query editor

AnswerC

Explicit type conversion will handle different formats.

Why this answer

Option A is correct because setting the data type explicitly in Power Query forces conversion regardless of source format. Option B is wrong because changing source locale doesn't fix format changes. Option C is wrong because using sample file doesn't enforce type.

Option D is wrong because split column is unnecessary.

Practice this question →

Page 1 of 4 · 264 questions totalNext →

Ready to test yourself?

Try a timed practice session using only Pl300 Prepare Data questions.

Start 20-question session