Courseiva

CCNA Pl300 Prepare Data Questions

75 of 96 questions · Page 1/2 · Pl300 Prepare Data topic · Answers revealed

Practice these questionsExam hubAll questions
1
MCQeasy

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
2
Multi-Selectmedium

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

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

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

Why this answer

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

Exam trap

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

Practice this question →
3
MCQeasy

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
4
Multi-Selectmedium

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

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

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

Why this answer

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

Exam trap

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

Practice this question →
5
MCQmedium

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
6
MCQeasy

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
7
MCQhard

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
8
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

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 →
9
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

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 →
10
MCQeasy

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
11
MCQeasy

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

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

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

Why this answer

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

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
12
MCQmedium

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

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

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

Why this answer

The 'Split Column by Delimiter' transformation in Power Query is specifically designed to divide a single text column into multiple columns based on a specified delimiter, such as a comma. In this case, the 'FullName' column contains names in the 'Last, First' format, so splitting by a comma delimiter will correctly separate the last name and first name into two distinct columns.

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
13
MCQeasy

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
14
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

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 →
15
MCQhard

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

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

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

Why this answer

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

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
16
MCQmedium

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
17
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 →
18
MCQeasy

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
19
MCQeasy

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

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

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

Why this answer

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

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
20
MCQhard

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

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

Aggregations reduce the amount of data queried from the source.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
21
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

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 →
22
MCQhard

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
23
Multi-Selecthard

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

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

Reducing data volume improves load time.

Why this answer

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

Exam trap

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

Practice this question →
24
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

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 →
25
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

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 →
26
MCQmedium

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
27
MCQmedium

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
28
MCQmedium

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

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

Query Folding pushes the filter to the database.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
29
MCQhard

You are preparing data from a CSV file that has inconsistent date formats. Some rows use 'MM/dd/yyyy' and others use 'dd/MM/yyyy'. You need to parse all dates correctly. What is the best approach in Power Query?

A.Use the 'Replace Values' to standardize the date format, then change data type.
B.Use the 'Parse' -> 'Date' transformation with a specific culture.
C.Use the 'Split Column' by delimiter to separate date parts.
D.Use the 'Detect Data Type' feature to automatically identify the format.
AnswerA

This approach can standardize formats before type conversion.

Why this answer

'Replace Values' allows you to standardize the inconsistent date strings (e.g., swapping day and month parts) before Power Query attempts to parse them as dates. After replacing the delimiters or reordering parts, you can change the column type to 'Date' using a consistent culture (e.g., 'en-US' for MM/dd/yyyy), ensuring all rows parse correctly regardless of original format.

Exam trap

The trap here is that candidates assume 'Parse' with a culture or 'Detect Data Type' can handle mixed formats, but Power Query requires explicit standardization before parsing when formats are inconsistent within a single column.

How to eliminate wrong answers

Option B is wrong because the 'Parse' -> 'Date' transformation with a specific culture assumes all dates in the column follow that single culture's format; it cannot handle mixed formats like MM/dd/yyyy and dd/MM/yyyy in the same column. Option C is wrong because 'Split Column' by delimiter separates date parts into individual columns (e.g., day, month, year), but it does not resolve which part is day vs. month when the order is inconsistent, requiring additional logic to recombine correctly. Option D is wrong because 'Detect Data Type' only identifies the overall data type (e.g., text or date) and cannot distinguish between multiple date formats within the same column; it would likely fail or produce errors for rows not matching the dominant format.

Practice this question →
30
Multi-Selectmedium

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

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

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

Why this answer

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

Exam trap

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

Practice this question →
31
MCQmedium

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
32
MCQeasy

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
33
Multi-Selectmedium

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

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

Relationships require columns, not measures.

Why this answer

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

Exam trap

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

Practice this question →
34
MCQeasy

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

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

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

Why this answer

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

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
35
MCQmedium

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

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

Append Queries adds rows from one table to another.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
36
MCQmedium

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
37
MCQmedium

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
38
MCQeasy

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

A.Use 'Replace Errors' to replace error values with null.
B.Remove rows with errors using 'Remove Rows' > 'Remove Errors'.
C.Change the data type to 'Date' and ignore errors.
D.Filter the column to exclude rows where the date is invalid after type conversion.
AnswerA

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
39
MCQmedium

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

A.The query will fail because the SQL query uses '>' with a string.
B.The SQL query should use a parameter for the date instead of a hardcoded value.
C.The filter on OrderQty > 10 should be included in the SQL query to reduce the amount of data transferred.
D.The grouping should be done in SQL to reduce data volume.
AnswerC

Filtering in SQL reduces data load; currently, all rows after the date are loaded.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
40
MCQmedium

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

A.Use 'Split Column by Number of Characters' with a fixed position.
B.Use 'Split Column by Delimiter' and choose 'Right-most delimiter'.
C.Use 'Replace Values' to replace space with a comma.
D.Use 'Extract Text After Delimiter' with a space.
AnswerB

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
41
Multi-Selectmedium

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

Select 2 answers
A.Snowflake
B.Excel workbook
C.Azure Synapse Analytics
D.SharePoint Online List
E.JSON file
AnswersA, C

Snowflake supports DirectQuery.

Why this answer

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

Exam trap

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

Practice this question →
42
MCQmedium

You are designing a data model in Power BI. You have a Sales table and a Date table. The Date table should contain all dates from 2020 to 2025. What is the best practice for creating the Date table?

A.Create a calculated table using VALUES from the Sales table's date column.
B.Use the Sales table's date column directly as the date dimension.
C.Enable the auto date/time option in Power BI settings.
D.Create a separate Date table using CALENDAR function in DAX.
AnswerD

Ensures a continuous date range for time intelligence.

Why this answer

Using the CALENDAR function to create a separate Date table ensures a complete, contiguous date range from 2020 to 2025, independent of the Sales table. This is a best practice in Power BI for time intelligence, as it guarantees all dates are present for accurate year-over-year calculations and avoids gaps or missing dates that could occur if relying solely on transaction data.

Exam trap

The trap here is that candidates often think using the Sales table's date column directly (Option B) is simpler and sufficient, but this violates the requirement for a complete date dimension and fails when no sales occur on certain dates, which is a common pitfall tested in PL-300.

How to eliminate wrong answers

Option A is wrong because creating a calculated table using VALUES from the Sales table's date column only includes dates where sales occurred, which may omit dates with no transactions, breaking time intelligence functions like TOTALYTD or SAMEPERIODLASTYEAR. Option B is wrong because using the Sales table's date column directly as the date dimension violates the star schema principle of having a separate, dedicated date table, leading to poor performance and inability to mark it as a date table for built-in time intelligence. Option C is wrong because enabling the auto date/time option in Power BI settings creates hidden date tables automatically, but these are not user-controllable, cannot be extended to a custom range like 2020–2025, and can cause confusion in model relationships.

Practice this question →
43
MCQmedium

You are preparing data from an Excel workbook that contains multiple sheets. Each sheet has a similar structure but different data. You need to combine all sheets into a single table in Power Query. What is the most efficient approach?

A.Load each sheet as a separate query and then use 'Append Queries' to combine them.
B.Use 'Merge Queries' to join the sheets based on a common column.
C.Use the 'Combine Files' approach with the workbook as a folder, then select all sheets.
D.Create a new query that references each sheet query and then merges them.
AnswerC

Power Query can treat a single workbook as a folder of sheets and combine them automatically.

Why this answer

The 'Combine Files' approach in Power Query treats the workbook as a folder, allowing you to select all sheets and automatically combine them into a single table. This is the most efficient method when multiple sheets have a similar structure, as it uses a single transformation step and handles dynamic sheet names without manual query creation.

Exam trap

The trap here is that candidates often confuse 'Append Queries' (which stacks rows) with 'Merge Queries' (which joins columns), and overlook the 'Combine Files' approach because they think it only applies to multiple files, not multiple sheets within a single workbook.

How to eliminate wrong answers

Option A is wrong because loading each sheet as a separate query and then using 'Append Queries' requires manual effort for each sheet, which is inefficient and not scalable for many sheets. Option B is wrong because 'Merge Queries' is designed for joining tables based on a common column (like a SQL JOIN), not for stacking rows from multiple sheets; it would produce a wide table instead of a long one. Option D is wrong because creating a new query that references each sheet query and then merging them still involves manual referencing and is less efficient than the built-in 'Combine Files' functionality, which automates the process.

Practice this question →
44
MCQmedium

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

A.Inner
B.Left Outer
C.Right Outer
D.Full Outer
AnswerB

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
45
MCQeasy

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

A.Split Column by Delimiter.
B.Merge Columns.
C.Extract Text.
D.Replace Values.
AnswerA

Splits a column into multiple columns based on a delimiter.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
46
MCQhard

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

A.The transformation will result in errors for rows containing commas.
B.The column will be converted to text automatically.
C.The column will be successfully converted to numbers.
D.The transformation will ignore the comma and convert the number correctly.
AnswerA

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
47
Multi-Selectmedium

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

Select 2 answers
A.Merge all queries into a single query.
B.Add calculated columns in Power Query instead of DAX.
C.Disable load for intermediate queries used only for reference.
D.Filter rows at the source to reduce data volume.
E.Keep all columns from the source data to avoid re-importing.
AnswersC, D

Prevents unnecessary data loading.

Why this answer

Disabling load for intermediate queries used only as reference steps prevents Power BI from materializing those tables in the data model. This reduces memory consumption and refresh time, as the engine skips loading data that isn't needed for reports or further transformations.

Exam trap

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

Practice this question →
48
MCQmedium

You need to create a Power BI data model that includes a date dimension. The source data contains a table with a Date column covering 2015-2025. You want to ensure that all dates in the model have a contiguous range for time intelligence. What should you do?

A.Hide the existing Date column and rely on auto date/time.
B.Add a calculated column for year and month from the existing Date column.
C.Create a calculated table using CALENDAR to generate a continuous date range and mark it as a date table.
D.Use the existing Date column as the date table and mark it as a date table.
AnswerC

Correct. Creating a calculated table using CALENDAR generates a contiguous range of dates, ensuring no gaps for time intelligence. (Although the option text says 'calculated column', the intended action is to create a calculated table.)

Why this answer

The CALENDAR function creates a calculated table (not a column) that generates a contiguous range of dates, which is essential for accurate time intelligence calculations such as YTD or QoQ. After creating this calculated table, you can mark it as a date table to enable DAX time intelligence functions. Although the option wording mistakenly says 'calculated column', the intent is to create a separate date table using CALENDAR, which is the proper method to ensure no gaps in the date dimension.

Exam trap

The trap is that candidates often think marking an existing date column as a date table (Option D) is sufficient, but they overlook the requirement for a contiguous range, which is critical for time intelligence to work correctly. Additionally, some may confuse CALENDAR as a calculated column function when it actually returns a table.

How to eliminate wrong answers

Option A is wrong because relying on auto date/time creates hidden date tables that are not user-defined, cannot be marked as a date table, and may not cover the full contiguous range needed for custom time intelligence. Option B is wrong because adding calculated columns for year and month does not address gaps in the date range; it only extracts parts from existing dates, leaving missing dates unhandled. Option D is wrong because using the existing Date column as the date table without ensuring contiguity can lead to gaps in the date dimension, causing time intelligence functions to return incorrect results or errors.

Practice this question →
49
MCQmedium

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

A.Use 'Replace Values' to replace null with 0
B.Use 'Replace Errors' with value 0
C.Use 'Fill Down' to propagate previous values
D.Add a custom column with an if statement
AnswerA

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
50
MCQmedium

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

A.Inner Join
B.Right Outer Join
C.Full Outer Join
D.Left Outer Join
AnswerD

Left outer join returns all rows from the first table and matching rows from the second.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
51
Multi-Selecthard

Which TWO of the following are valid ways to create a date table in Power BI?

Select 2 answers
A.Using the time intelligence functions like TOTALYTD.
B.Marking an existing table as a date table.
C.Enabling the auto date/time feature.
D.Using DAX function CALENDAR or CALENDARAUTO.
E.Using Power Query with List.Dates.
AnswersD, E

Creates a date table with a range of dates.

Why this answer

The DAX functions CALENDAR and CALENDARAUTO are specifically designed to generate a single-column table of dates, which can then be used as a date table. CALENDAR requires explicit start and end dates, while CALENDARAUTO automatically scans the model to determine the date range. Both are standard methods for creating a date table in Power BI.

Exam trap

The trap here is that candidates confuse time intelligence functions (like TOTALYTD) with table creation functions, or they assume that marking a table as a date table or enabling auto date/time actually creates a new date table, when in fact those options only configure existing data.

Practice this question →
52
MCQeasy

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

A.Change the system region settings of the Power BI service to US
B.Use the 'Using Locale' option in the Change Type step to select the appropriate locale (e.g., English (United States))
C.Change the column data type to Text and then manually replace separators
D.Split the column into day, month, and year, then combine them in the correct order
AnswerB

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
53
Multi-Selecteasy

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

Select 2 answers
A.Pivot Column
B.Append Queries
C.Unpivot Columns
D.Merge Queries
E.Group By
AnswersB, D

Appends rows from one query to another.

Why this answer

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

Exam trap

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

Practice this question →
54
MCQhard

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

A.Change Type to Decimal, then Replace Values (',' with ''), then Replace Values ('(' with '-')
B.Replace Values (',' with ''), then Replace Values ('(' with '-'), then Replace Values (')' with ''), then Change Type to Decimal
C.Replace Values (',' with ''), Change Type to Decimal, then Replace Values ('(' with '-')
D.Replace Values ('(' with '-'), Replace Values (')' with ''), Replace Values (',' with ''), then Change Type to Decimal
AnswerD

Correct order: handle negative sign first, then remove comma, then type conversion.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
55
MCQhard

You are configuring a Power BI dataset with incremental refresh. The above JSON shows part of the M script parameters. The dataset uses a single SQL Server data source. You need to ensure that incremental refresh works correctly. What must you do?

A.Change the connection string to use a SQL account instead of integrated security.
B.Rename the parameter 'StartDate' to 'RangeStart' and add a 'RangeEnd' parameter.
C.Enable query folding on the SQL Server source.
D.Define the parameters in the Power Query editor instead of the JSON file.
AnswerB

Incremental refresh uses two reserved parameters named RangeStart and RangeEnd to filter the data. The current parameter name is incorrect.

Why this answer

Incremental refresh in Power BI requires two special date/time parameters named 'RangeStart' and 'RangeEnd' (case-sensitive). The JSON snippet shows a parameter named 'StartDate', which is not recognized by the incremental refresh engine. Renaming it to 'RangeStart' and adding a 'RangeEnd' parameter allows Power BI to filter the data source query dynamically during refresh, ensuring only the changed or new rows are loaded.

Exam trap

The trap here is that candidates assume any date parameter name will work for incremental refresh, but Power BI strictly requires the exact names 'RangeStart' and 'RangeEnd' (case-sensitive) to enable the partitioning logic.

How to eliminate wrong answers

Option A is wrong because changing the connection string to use a SQL account instead of integrated security does not affect the incremental refresh mechanism; authentication method is unrelated to the parameter naming convention required by Power BI. Option C is wrong because query folding is automatically enabled for SQL Server sources when using native queries or direct table references; it is not a manual step that needs to be performed separately for incremental refresh to work. Option D is wrong because defining parameters in the Power Query editor is the standard method, but the JSON file shown is the correct way to store them; the issue is the parameter names, not where they are defined.

Practice this question →
56
MCQmedium

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

A.Use a fixed start and end date in the CALENDAR function
B.Create a disconnected date table
C.Create the date table using CALENDAR(MIN('Fact'[Date]), MAX('Fact'[Date]))
D.Enable Auto Date/Time in the model
AnswerC

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
57
MCQmedium

You are creating a Power BI dataset from a SQL Server data warehouse. The warehouse contains a fact table with 500 million rows and dimension tables. You need to minimize the data refresh time while ensuring that the dataset meets the reporting requirements. Which approach should you recommend?

A.Create a composite model using DirectQuery for the fact table and Import for dimensions.
B.Use DirectQuery mode for the dataset.
C.Configure incremental refresh on the fact table.
D.Use Import mode but filter rows and reduce columns in Power Query to only those needed.
AnswerD

Importing only necessary data reduces the amount of data loaded, directly decreasing refresh time while still supporting fast query performance.

Why this answer

Importing only the necessary columns and rows reduces the data volume, which directly minimizes refresh time. With 500 million rows, Import mode is generally faster than DirectQuery for large fact tables in Power BI, as it avoids querying the source on every interaction. Filtering and column reduction in Power Query ensures the dataset remains lean while meeting reporting requirements.

Exam trap

The trap here is that candidates often choose incremental refresh (Option C) thinking it always reduces refresh time, but it does not address the initial full load or the need to minimize data volume; the key is to reduce the data imported, not just partition it.

How to eliminate wrong answers

Option A is wrong because a composite model with DirectQuery on the fact table would still require live queries against 500 million rows, leading to slow report performance and potential timeouts, not minimizing refresh time. Option B is wrong because DirectQuery mode does not have a refresh process; it queries the source on demand, which would be extremely slow for a 500-million-row fact table and does not reduce data transfer. Option C is wrong because incremental refresh only partitions the fact table for scheduled refreshes, but it still requires importing all historical data initially and does not minimize the initial or ongoing refresh time as effectively as reducing the data volume.

Practice this question →
58
MCQmedium

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

A.Increase the scheduled refresh frequency to every 15 minutes.
B.Enable Query Folding on all steps in Power Query.
C.Change the storage mode to DirectQuery.
D.Remove unused columns from the table in Power Query.
AnswerD

Reduces data volume and improves refresh speed.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
59
MCQmedium

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

A.Use the 'Column from Examples' feature to create a new column with numeric values.
B.In Power Query, split the Response column by delimiter and then use the first part.
C.Use a DAX calculated column to convert text to number.
D.Change the data type of Response to whole number in Power Query, then filter out or replace null values.
AnswerD

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
60
MCQhard

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

A.Use the CALENDAR function in DAX to generate the date range
B.Mark the date column from the fact table as a date table and disable Auto Date/Time
C.Create a date dimension by using DISTINCT on the fact table's date column
D.Create a date table in Power Query by generating a list of dates from 1/1/2010 to 12/31/2030 and then add columns for attributes
AnswerD

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
61
Multi-Selecthard

Which TWO are best practices for optimizing Power Query performance? (Choose two.)

Select 2 answers
A.Disable the 'Enable background refresh' option in the query settings.
B.Merge tables as early as possible in the query to combine data.
C.Keep all columns in the table to avoid missing data.
D.Filter data as early as possible in the query to reduce row counts.
E.Split columns by delimiter to normalize data.
AnswersA, D

Disabling background refresh can improve performance by preventing simultaneous refreshes.

Why this answer

Disabling 'Enable background refresh' prevents Power Query from running queries in the background while you continue working in Power BI Desktop. This ensures that query execution is synchronous, which can improve performance by avoiding resource contention and allowing you to monitor progress directly. Background refresh can cause delays and unexpected behavior when multiple queries run simultaneously, especially with large data sources.

Exam trap

The trap here is that candidates often confuse data transformation best practices (like merging or splitting columns) with performance optimization techniques, leading them to select options B or E instead of focusing on reducing data volume and controlling query execution.

Practice this question →
62
MCQhard

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

A.Use the 'Run KQL command' option in Power Query to pass the query directly.
B.Add additional transformations in Power Query to reduce rows.
C.Enable query folding to push the query to the Kusto source.
D.Disable query folding to improve performance.
AnswerA

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
63
Multi-Selectmedium

Which TWO actions can you take in Power Query Editor to improve data quality and reduce load time? (Choose two.)

Select 1 answer
A.Split columns by delimiter to create more columns.
B.Filter out empty rows.
C.Sort the data by a key column.
D.Promote headers to use the first row as column names.
E.Merge queries to combine data from multiple sources.
AnswersB

Correct. Filtering out empty rows removes unnecessary data, reducing load time and improving data quality by eliminating incomplete records.

Why this answer

Filtering out empty rows in Power Query Editor directly reduces the number of rows loaded into the data model, which decreases memory usage and improves refresh performance. This action also enhances data quality by removing incomplete records that could cause errors in measures or relationships. The other options either do not reduce load time or may increase it.

Exam trap

The trap here is that candidates often confuse actions that improve data quality (like filtering or promoting headers) with actions that merely reorganize data (like sorting or splitting), leading them to select options that do not actually reduce load time or enhance data integrity.

Practice this question →
64
MCQmedium

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

A.Use an inactive relationship between Sales and Marketing and activate it with USERELATIONSHIP in measures.
B.Create a bridge table containing unique combinations of OrderID and CampaignID.
C.Create a separate table for each campaign and relate to Sales.
D.Merge the Marketing table into the Sales table using a left outer join.
AnswerB

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
65
MCQeasy

You are importing data from a CSV file into Power BI. The file contains a column 'SalesAmount' with values like '$1,234.56'. When you load the data, the column is detected as text. What is the most efficient way to convert this column to a numeric type in Power Query?

A.Use 'Replace Values' to remove the dollar sign and comma, then change the column type to decimal.
B.Use 'Detect Data Type' and hope it automatically converts.
C.Change the column type to decimal directly and ignore errors.
D.Split the column by delimiter ',' and then convert the first part.
AnswerA

This directly cleans the text and converts to number.

Why this answer

It directly addresses the root cause: the dollar sign and comma are non-numeric characters that prevent automatic type conversion. By using 'Replace Values' to remove these characters first, you clean the data so that Power Query can then safely change the column type to decimal. This is the most efficient approach because it avoids error rows and preserves data integrity.

Exam trap

The trap here is that candidates assume Power BI's automatic data type detection or direct type conversion can handle formatted currency values, but Power Query requires explicit cleaning of non-numeric characters before conversion.

How to eliminate wrong answers

Option B is wrong because 'Detect Data Type' relies on the current column values, which still contain non-numeric characters, so it will continue to detect the column as text and not perform any conversion. Option C is wrong because changing the column type to decimal directly without cleaning will cause errors for every row containing '$' or ',', resulting in null values or query failures. Option D is wrong because splitting by comma is unnecessary and destructive; it would separate the thousands separator from the number, losing the decimal precision and requiring additional steps to recombine values.

Practice this question →
66
MCQhard

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

A.Use DirectQuery for StockPrices (CSV) and import the other tables.
B.Import only StockPrices and Company, and use the auto date/time feature; ignore Earnings data.
C.Import all tables, create a date table with CALENDAR, and establish relationships: StockPrices[Date] -> DateTable[Date], StockPrices[Ticker] -> Company[Ticker], Earnings[Ticker] -> Company[Ticker], and create a many-to-many relationship between Earnings and DateTable using a bridge table.
D.Import all tables, then in Power Query merge StockPrices with Company and Earnings into a single flat table using left outer joins.
AnswerC

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
67
MCQeasy

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

A.The column was imported as text because the data source is a CSV file
B.Power Query detected that the column contains text values in some rows, so it set the data type to text
C.The 'Detect data type' option was disabled in Power Query settings
D.The source data is stored as text in the database
AnswerB

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
68
Multi-Selecthard

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

Select 2 answers
A.Configure dynamic data masking on the Azure SQL Database.
B.Use row-level security (RLS) in Power BI to hide sensitive columns.
C.Transform the data in Power Query by replacing sensitive values with a placeholder.
D.Use Microsoft Purview sensitivity labels to mask data.
E.Apply column-level security in Power BI Desktop.
AnswersA, C

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

Why this answer

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

Exam trap

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

Practice this question →
69
MCQmedium

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

A.Import all tables as they are and create a single flat table by merging Sales, Stores, and Products in Power Query.
B.Import Sales, Stores, and Products tables, create a separate date table using CALENDAR, and establish relationships between Sales and dimension tables.
C.Import Sales table only and create calculated columns for StoreName and ProductName using RELATED.
D.Import Sales table and use the auto date/time feature for time intelligence.
AnswerB

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
70
MCQmedium

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

A.Enable query folding in Power Query to push filters to the source.
B.Configure incremental refresh on the table using the LastModifiedDate column.
C.Schedule a full refresh every hour.
D.Create a Power BI dataflow that performs a full load and then use that dataflow as a source.
AnswerB

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
71
Multi-Selectmedium

You are preparing data for a Power BI report that requires a date table with continuous dates from 2020 to 2025. Which TWO methods can you use to create this date table in Power Query?

Select 2 answers
A.Use the 'Enter Data' feature and manually type dates.
B.Use the CALENDAR DAX function in a calculated table.
C.Reference another query that already has dates.
D.Use the List.Dates function to generate a list of dates.
E.Create a blank query and use #date and List.Transform to generate dates.
AnswersD, E

List.Dates generates a date list that can be converted to a table.

Why this answer

The List.Dates function in Power Query M generates a continuous list of dates by specifying a start date, a count of dates, and a step duration. This list can then be converted into a table, making it ideal for creating a date table directly in Power Query without leaving the data transformation environment.

Exam trap

The trap here is that candidates confuse DAX functions (like CALENDAR) with Power Query M functions (like List.Dates), leading them to select Option B even though the question explicitly restricts the scope to Power Query.

Practice this question →
72
Multi-Selectmedium

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

Select 2 answers
A.Use the 'Fill Down' or 'Fill Up' option to propagate non-null values into null cells.
B.Remove rows that contain null values using the 'Remove Rows' > 'Remove Blank Rows' option.
C.Replace null values with a default value using the 'Replace Values' transform.
D.Merge the table with another table that has no nulls.
E.Change the data type of the column to a non-nullable type.
AnswersA, C

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

Why this answer

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

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

Exam trap

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

Practice this question →
73
Multi-Selecteasy

Which TWO data source types can be used with Power BI dataflows?

Select 3 answers
A.Exchange Online mailbox
B.PDF file
C.Power BI dataset
D.SharePoint Online list
E.Azure SQL Database
AnswersC, D, E

Power BI datasets are supported as sources in dataflows, enabling linked or computed dataflows.

Why this answer

Power BI dataflows support a wide variety of data sources, including Power BI datasets (Option C), SharePoint Online lists (Option D), and Azure SQL Database (Option E). Therefore, all three options are valid data source types for dataflows. Option A (Exchange Online mailbox) and Option B (PDF file) are not supported as direct source types for dataflows.

Exam trap

Candidates often overlook that Azure SQL Database is a valid data source for Power BI dataflows, mistakenly thinking it is not supported. Be aware that dataflows can connect to many Azure and online services, including SharePoint Online lists, Power BI datasets, and Azure SQL Database.

Practice this question →
74
MCQmedium

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

A.In the Power BI service, go to the dataset settings and configure the scheduled refresh.
B.In Power Query Editor, apply the rangeStart and rangeEnd filters to the data and then close and apply.
C.In the Power BI service, create a new refresh schedule and set the incremental refresh period.
D.In Power BI Desktop, on the model view, select the table and set the incremental refresh policy.
AnswerD

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
75
MCQhard

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

A.Use DirectQuery for the SQL Server table and Import mode for the SharePoint list.
B.Set both tables to DirectQuery mode.
C.Set the SQL Server table to Dual mode and the SharePoint list to Import mode.
D.Import both tables into the model and disable incremental refresh.
AnswerA

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
Page 1 of 2 · 96 questions totalNext →

Ready to test yourself?

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

Start 20-question session