CCNA Prepare the data Questions — Page 3 of 3

151

MCQeasy

Refer to the exhibit. You are configuring a scheduled refresh for a Power BI dataset. The exhibit shows the refresh schedule settings. The dataset is in a workspace in a Premium capacity. The scheduled refresh runs at 5:00 AM UTC daily. However, the refresh is failing consistently. What is the most likely cause?

A.The notify option should be set to 'OnFailureOrSuccess' to get alerts.

B.The data source requires a gateway to be configured for refresh.

C.The refresh time is outside the allowed window for Premium capacities.

D.The dataset is in a Premium capacity, which does not support scheduled refresh.

AnswerB

On-premises or private-network sources cannot be reached directly by the Power BI service, so a data gateway must bridge the connection. Without a configured gateway, the scheduled refresh cannot authenticate to the source, explaining the consistent failures despite Premium capacity.

Why this answer

The exhibit shows a scheduled refresh configured for a dataset in a Premium capacity workspace, but the refresh is failing consistently. The most likely cause is that the data source requires a gateway to be configured for refresh. Even in Premium capacities, if the data source is on-premises or within a private network (e.g., SQL Server, Oracle, or SharePoint on-premises), an on-premises data gateway must be installed and configured to enable the Power BI service to connect and refresh the data.

Without a gateway, the scheduled refresh will fail because the cloud service cannot directly access the local data source.

Exam trap

The trap here is that candidates often assume Premium capacities automatically resolve connectivity issues or that scheduled refresh is not supported in Premium, when in reality the gateway requirement is independent of capacity tier and depends solely on the data source location.

How to eliminate wrong answers

Option A is wrong because the notify option (set to 'OnFailureOrSuccess' or 'OnFailure') controls email notifications for refresh outcomes, but it does not affect whether the refresh succeeds or fails; it only determines if you receive alerts. Option C is wrong because Premium capacities have a larger refresh window (up to 48 refreshes per day) and 5:00 AM UTC is well within the allowed window; there is no restriction that would cause a failure at that time. Option D is wrong because Premium capacities fully support scheduled refresh; in fact, they offer more frequent refresh slots than shared capacities, so this is not a limitation.

Practice this question →

152

MCQhard

You are designing a Power BI solution that ingests data from multiple sources: Azure Blob Storage, Salesforce, and an on-premises Oracle database. The data must be combined into a single semantic model. The Oracle database contains sensitive customer information that must be masked before being loaded. Which approach should you use to prepare the data?

A.Create a Power BI dataflow that extracts, transforms, and masks data before loading into the semantic model

B.Use DirectQuery to connect to all sources and rely on database-level masking

C.Import all data into Power BI Desktop and apply transformations in the Power Query Editor

D.Stage the data in an Azure SQL Database and use SQL Server Analysis Services to mask data

AnswerA

Power BI dataflows are a native, cloud-based ETL service in the Power BI service that can connect to a wide range of sources, apply data cleansing and shaping logic through Power Query Online, and include masking steps such as hashing, replacing, or removing sensitive columns before the resulting entity is loaded into a semantic model. This centralized approach supports enterprise-scale data preparation, scheduled refresh, and reusable entities that can serve multiple reports and datasets, making it the right architecture for this scenario.

Why this answer

Power BI dataflows provide a cloud-based ETL solution that can connect to Azure Blob Storage, Salesforce, and on-premises Oracle (via an on-premises data gateway), perform transformations including data masking, and then load the prepared data into a shared semantic model. This approach centralizes data preparation, ensures sensitive data is masked before any downstream consumption, and supports scheduled refreshes without requiring additional infrastructure.

Exam trap

The trap here is that candidates often assume data masking must be done at the database level or via a separate service like SSAS, but Power BI dataflows can perform masking during the transformation phase, making them the most integrated and efficient solution for this multi-source scenario.

How to eliminate wrong answers

Option B is wrong because DirectQuery does not allow data masking within Power BI; it passes queries directly to the source, so sensitive data would remain unmasked unless the source itself applies masking, which is not guaranteed across heterogeneous sources. Option C is wrong because importing all data into Power BI Desktop and applying transformations in Power Query Editor only masks data locally in the .pbix file, not in a shared, scalable semantic model, and it does not support scheduled cloud-based refreshes for on-premises Oracle without additional gateway configuration. Option D is wrong because staging data in Azure SQL Database and using SQL Server Analysis Services (SSAS) to mask data introduces unnecessary complexity and cost, and SSAS is not required for masking; Power BI dataflows can perform masking natively without additional services.

Practice this question →

153

MCQeasy

You need to connect Power BI to an Excel file stored on a local network drive. The file is updated manually each morning. You want the Power BI report to always show the latest data when opened. Which data connectivity mode should you choose?

A.Live Connection

B.Dual

C.Import

D.DirectQuery

AnswerC

Import mode loads the contents of the Excel file into the VertiPaq in-memory engine, where data is compressed and stored for fast interactive analysis. After the initial load, you can refresh the dataset on a schedule to pick up changes in the workbook, though for files on a local machine an on-premises data gateway is needed for automated refresh. Because the data is cached, all Power BI features—including calculated columns, measures, and RLS—are fully supported, and visuals do not hit the source file on every interaction.

Why this answer

(Import) is correct because Power BI must load the Excel data into its internal VertiPaq engine to support the full range of transformations and visualizations. Since the file is on a local network drive and updated manually, Import mode allows you to refresh the dataset on demand or via a scheduled refresh, ensuring the report always shows the latest data when opened. DirectQuery and Live Connection are not applicable because they require a live queryable data source (like SQL Server or Analysis Services), not a flat file.

Exam trap

The trap here is that candidates often confuse DirectQuery with the ability to query any file-based source, but Microsoft explicitly restricts DirectQuery to SQL-based and OData sources, not Excel or CSV files.

How to eliminate wrong answers

Option A (Live Connection) is wrong because it is used only for connecting to an Analysis Services tabular model or a Power BI dataset, not to an Excel file stored on a network drive. Option B (Dual) is wrong because Dual mode is a storage mode that combines Import and DirectQuery for composite models, but it still requires a DirectQuery-capable source (like SQL Server) and cannot be used with Excel files. Option D (DirectQuery) is wrong because DirectQuery mode is designed for relational databases or other sources that support real-time querying; Excel files are not supported in DirectQuery mode in Power BI.

Practice this question →

154

MCQeasy

You are importing data from a CSV file that contains a column 'Date' with values like '2026-01-15'. After loading, Power Query detects the column as type 'text'. What is the recommended step to ensure the column is treated as a date?

A.In Power Query, select the column and change the data type to 'Date' using the 'Data Type' dropdown.

B.In Power BI Desktop, use the 'Format' pane to set the column as a date.

C.Use the 'Parse' -> 'Date' transformation in Power Query.

D.Use the 'Detect Data Type' button in Power Query to automatically detect all columns.

AnswerA

Power Query inferred text because the CSV values were read as strings. Explicitly setting the column's data type to Date via the Data Type dropdown applies locale-aware conversion, ensuring the values are stored and modelled as dates rather than text.

Why this answer

In Power Query, the recommended method to convert a text column containing date-formatted strings (like '2026-01-15') to a proper Date type is to select the column and change its data type using the 'Data Type' dropdown in the Transform tab. This ensures the column is treated as a date for downstream calculations and modeling. Power Query automatically parses the text into a date based on the locale and format of the data.

Exam trap

The trap here is that candidates confuse the 'Format' pane in the report view (which only changes display formatting) with the Power Query data type change (which alters the column's data type in the data model), leading them to select Option B.

How to eliminate wrong answers

Option B is wrong because the 'Format' pane in Power BI Desktop is used for visual formatting (e.g., display format of a date in a report), not for changing the underlying data type of a column in the data model. Option C is wrong because there is no 'Parse' -> 'Date' transformation in Power Query; the correct transformation is 'Change Type' -> 'Date' or using the 'Data Type' dropdown. Option D is wrong because the 'Detect Data Type' button in Power Query attempts to auto-detect types for all columns, but it may not reliably convert a text column to date if the data is ambiguous or if the detection logic fails; the recommended step is to explicitly set the data type.

Practice this question →

155

MCQmedium

You are importing data from a SQL Server view into Power BI. The view contains calculated columns that are expensive to compute. You want to minimize the load on the source database during refresh. What should you do?

A.Import the raw data and perform transformations in Power Query.

B.Enable query folding to push transformations to SQL Server.

C.Use a native SQL query in Power Query to perform calculations.

D.Create a materialized view in SQL Server with the calculations.

AnswerA

By importing raw data, you offload transformation work to Power BI's mashup engine, which leverages Power Query's in-memory and streaming capabilities. This avoids pushing compute-intensive operations to SQL Server, preserving database resources for other workloads. Additionally, Power Query's step-based transformations are highly maintainable and can be refreshed on a schedule.

Why this answer

Importing raw data and performing transformations in Power Query offloads the computational burden from the SQL Server source to Power BI's mashup engine. This minimizes load on the source database during refresh, as expensive calculated columns are not executed on SQL Server. Power Query can apply transformations after the data is extracted, reducing the need for server-side processing.

Exam trap

The trap here is that candidates often assume pushing transformations to the source (via query folding or native SQL) is always more efficient, but the question specifically asks to minimize load on the source database, making offloading to Power Query the correct choice.

How to eliminate wrong answers

Option B is wrong because enabling query folding pushes transformations back to SQL Server, which would increase the load on the source database by having it perform the expensive calculations, contradicting the goal of minimizing load. Option C is wrong because using a native SQL query in Power Query to perform calculations still executes those calculations on SQL Server, placing the computational burden on the source database. Option D is wrong because creating a materialized view in SQL Server with the calculations would require the source database to compute and store the results, increasing load during refresh rather than reducing it.

Practice this question →

156

Multi-Selectmedium

You are using Power Query to transform a column 'FullName' containing values like 'Smith, John'. You need to split this into 'LastName' and 'FirstName' columns. Which THREE steps are required?

Select 3 answers

A.Unpivot columns

B.Use 'Split Column' by delimiter

C.Trim leading/trailing spaces from new columns

D.Merge the split columns back

E.Rename the new columns to LastName and FirstName

AnswersB, C, E

Using 'Split Column' by delimiter is the core transformation that parses the fullname column into two columns based on the comma separator. In Power Query, this command (found on the Transform tab) scans each cell for the specified delimiter and divides the text at that point, producing two new columns containing the fragments. This is the only option here that directly creates the separate LastName and FirstName values from the original string, so it is the correct primary action.

Why this answer

Option B is correct because the 'FullName' values like 'Smith, John' must first be separated at the comma, which is exactly what Power Query's 'Split Column' by delimiter (comma) does, producing two new columns. Option C is correct because the delimiter is followed by a space, so the resulting 'John' value will have a leading space that must be removed with Trim to get clean FirstName values. Option E is correct because the split produces generically named columns (e.g., 'FullName.1' and 'FullName.2'), so they must be renamed to 'LastName' and 'FirstName' to match the required output.

Option A does not belong because Unpivot transforms columns into rows and is unrelated to splitting a single text column. Option D does not belong because merging the columns back would undo the split and recreate a single combined value, contradicting the goal of separate LastName and FirstName columns.

Practice this question →

157

MCQeasy

You need to combine two tables: Sales and Products, where Sales has a ProductID column and Products has a ProductKey column. The tables have a many-to-one relationship. Which Power Query transformation should you use?

A.Group By.

B.Append Queries.

C.Merge Queries.

D.Pivot Column.

AnswerC

Merge Queries performs a relational join by matching rows on a common key column, such as ProductID, with configurable join types (Inner, Left Outer, Full Outer, etc.). This enriches each sales record with the corresponding product attributes (name, price, category) from the Products table in a single output table. Because the requirement is to combine two tables column-wise based on a shared field, Merge Queries is the correct approach.

Why this answer

Merge Queries (C) is correct because it performs a join between two tables based on matching columns, which is exactly what is needed to combine Sales and Products using ProductID and ProductKey. This transformation supports many-to-one relationships and allows you to expand related columns from the Products table into the Sales table, enabling further data analysis.

Exam trap

The trap here is that candidates confuse Merge Queries with Append Queries, mistakenly thinking that combining tables always means stacking rows, rather than joining on a key relationship.

How to eliminate wrong answers

Option A is wrong because Group By aggregates data by grouping rows and computing summary statistics (e.g., sum, count), not for combining tables based on key columns. Option B is wrong because Append Queries stacks rows from two tables vertically, requiring identical column structures, and does not join on a key relationship. Option D is wrong because Pivot Column transforms unique values from a column into new columns, typically for reshaping data, not for merging related tables.

Practice this question →

158

MCQhard

You are designing a Power BI data model for a sales analysis. The source data has a table 'Orders' with columns: OrderID, CustomerID, ProductID, OrderDate, Quantity, UnitPrice. You also have a table 'Customers' with CustomerID, CustomerName, and 'Products' with ProductID, ProductName. You need to create a star schema. What should you do?

A.Split Orders into multiple fact tables by year

B.Create a snowflake schema by normalizing Customers and Products further

C.Keep Customers and Products as separate dimension tables, and Orders as the fact table

D.Merge Customers and Products into a single dimension table

AnswerC

Keeping Customers and Products as separate dimension tables and Orders as the fact table correctly implements a star schema: the fact table holds numeric measures and foreign keys, while each dimension provides descriptive attributes linked by one-to-many relationships. This design lets users filter sales by any customer or product attribute without duplication or fan-out, and it lets DAX aggregate order measures directly and efficiently.

Why this answer

In a star schema, a single fact table (Orders) stores quantitative measures (Quantity, UnitPrice) and foreign keys (CustomerID, ProductID) that link to dimension tables (Customers, Products) containing descriptive attributes. This design optimizes query performance by reducing joins and enabling efficient aggregation, which is a best practice for Power BI data modeling.

Exam trap

Microsoft often tests the misconception that splitting fact tables by time (e.g., year) is beneficial, but the correct approach is to keep a single fact table with a date dimension for time-based analysis.

How to eliminate wrong answers

Option A is wrong because splitting Orders into multiple fact tables by year would break the star schema principle of having a single fact table per business process, leading to complex cross-table queries and loss of historical trend analysis. Option B is wrong because creating a snowflake schema by normalizing Customers and Products further (e.g., splitting CustomerName into separate tables) would increase the number of joins and degrade query performance in Power BI, which prefers denormalized dimension tables. Option D is wrong because merging Customers and Products into a single dimension table would create a non-conformed dimension with mixed attributes, causing data redundancy and making it impossible to analyze customers and products independently.

Practice this question →

159

MCQhard

You are a data analyst at a utility company. You load a table of meter readings into Power BI Desktop from an Azure SQL Database. The table has columns MeterId, ReadingTimestamp, and Consumption. You discover duplicate rows caused by a known upstream issue where the same reading is sometimes inserted twice with identical values in all three columns. You need to remove these exact duplicate rows in Power Query while keeping one copy of each reading. What should you do?

A.Add an index column, then use Remove Rows > Remove Duplicates on the index column.

B.Group By MeterId, ReadingTimestamp, and Consumption with an All Rows operation, then expand the first row of each group.

C.Select the MeterId column only, then use Remove Rows > Remove Duplicates.

D.Select all three columns, then use Remove Rows > Remove Duplicates.

AnswerD

Remove Duplicates evaluates the selected columns as a composite key, so rows that match on MeterId, ReadingTimestamp, and Consumption are treated as duplicates. One copy of each distinct combination is retained. This precisely targets the exact duplicates described without risking removal of legitimate readings that differ in any column.

Why this answer

Remove Duplicates treats the set of selected columns as the key for comparison. Selecting MeterId, ReadingTimestamp, and Consumption means only rows identical across all three are collapsed, which matches the described upstream duplication. Narrower keys would delete valid readings, and an index column would prevent any matches.

Exam trap

The trap here is choosing a single column as the duplicate key, which feels efficient but actually removes valid readings that share the same meter identifier.

Practice this question →

160

MCQmedium

You are reviewing a Power BI dataset configuration in the service. The JSON shows a data source for an Azure SQL Database. Which statement about the configuration is correct?

A.The dataset uses key-based authentication and does not use single sign-on.

B.The dataset uses single sign-on with Azure AD.

C.The dataset is configured to use a cloud gateway for direct query.

D.The dataset uses cloud-only data sources and does not require a gateway.

AnswerD

The dataset points to Azure SQL Database, a fully managed cloud PaaS service, so Power BI can connect directly over the internet without any on-premises data gateway. Neither Import mode nor DirectQuery requires a gateway for cloud-only sources, because the data source is not behind a corporate firewall and is already reachable via standard cloud endpoints. This is why the configuration correctly shows no gateway association and the dataset functions normally.

Why this answer

Azure SQL Database is a cloud-native data source in Power BI, and cloud data sources do not require a gateway, whether using DirectQuery or Import. The configuration shown in the JSON is for an Azure SQL Database, which is inherently cloud-only, so no gateway is needed. Option A is incorrect because key-based authentication is not supported for Azure SQL Database; using 'Key' credential type would cause a connection failure.

Option B is incorrect because the JSON does not indicate single sign-on (no 'SingleSignOn' property set to true). Option C is incorrect because a cloud gateway is not required for Azure SQL Database; it is only needed for on-premises or virtual-network sources.

Exam trap

The trap here is that candidates often assume any Azure SQL Database connection automatically uses Azure AD SSO or requires a gateway, but the JSON's credential type explicitly reveals the authentication method, and cloud-native sources like Azure SQL Database do not inherently need a gateway.

How to eliminate wrong answers

Option B is wrong because single sign-on (SSO) with Azure AD would require the JSON to include a 'SingleSignOn' property set to true or a credential type of 'OAuth2' with an Azure AD token, which is not present in the given configuration. Option C is wrong because a cloud gateway (e.g., on-premises data gateway) is only required for on-premises or non-cloud data sources; an Azure SQL Database is a cloud-native data source and does not need a gateway for DirectQuery or import mode. Option D is wrong because while Azure SQL Database is a cloud-only data source, the statement is too broad and ignores that the dataset could still require a gateway if the Power BI service cannot directly connect due to network restrictions (e.g., VNet integration), but the JSON does not indicate any gateway configuration, so the correct inference is about authentication, not gateway necessity.

Practice this question →

161

MCQhard

You are designing a data model for a sales analysis report. The source data includes a Sales table with columns: OrderID, CustomerID, ProductID, OrderDate, Quantity, and UnitPrice. You also have a Customers table and a Products table. Which approach best optimizes query performance and storage?

A.Create a star schema with Sales as a fact table and Customers and Products as dimension tables.

B.Create separate fact tables for each dimension.

C.Create a single flat table by joining all columns from Sales, Customers, and Products into one table.

D.Create a snowflake schema by normalizing Customers into multiple related tables.

AnswerA

Star schema is the recommended modeling approach in Power BI because it separates measure-bearing numeric data (Sales) from descriptive attributes (Customers, Products). This structure leverages VertiPaq's columnar compression by storing dimensions as small, indexed tables, and enables unambiguous one-to-many relationships that make DAX filters propagate correctly. As a result, queries are simpler, more performant, and the model is easier for report consumers to navigate.

Why this answer

A star schema optimizes query performance and storage in Power BI by separating transactional data (Sales fact table) from descriptive attributes (Customers and Products dimension tables). This reduces data duplication, improves compression, and enables efficient aggregations and filter propagation via one-to-many relationships, which is the recommended modeling approach for analytical workloads.

Exam trap

The trap here is that candidates often choose a flat table (Option C) thinking it simplifies the model, but they overlook the severe storage and performance penalties from data duplication, which is a key anti-pattern in Power BI data modeling.

How to eliminate wrong answers

Option B is wrong because creating separate fact tables for each dimension would fragment the transactional data, requiring complex cross-filtering and joins, which degrades performance and increases storage overhead. Option C is wrong because a single flat table introduces massive data duplication (e.g., repeating customer and product attributes for every sale), inflating storage and slowing down query processing due to larger table scans. Option D is wrong because normalizing Customers into multiple related tables (snowflake schema) adds unnecessary join complexity in Power BI, which can degrade performance compared to a star schema, especially when using DirectQuery or large datasets.

Practice this question →

162

MCQeasy

You are preparing data for a Power BI report. The source data contains a 'CustomerName' column with values like 'John, Doe'. You need to split this column into two columns: 'FirstName' and 'LastName'. The comma is used as a delimiter, but some names have a space after the comma. Which split method should you use?

A.Split by number of characters using a fixed width

B.Split by delimiter using semicolon

C.Split by delimiter using comma, then use 'Trim' to remove extra spaces

D.Split by delimiter using comma, using 'Left-most delimiter'

AnswerC

Splitting by comma as the delimiter separates the name into two columns, typically breaking at the comma between last and first name. After the split, the resulting columns often retain leading spaces (e.g., ' Doe' or 'Jane ') that come from the source data's formatting. Applying the Trim transformation removes these extraneous spaces, yielding clean standardized name values ready for further analysis or reporting. This combination of delimiter splitting and trimming is a best practice when handling delimited text with incidental whitespace.

Why this answer

Splitting by comma and then trimming extra spaces handles the inconsistent spacing after the comma (e.g., 'John, Doe' vs 'John, Doe'). Power Query's 'Split Column by Delimiter' using comma will separate the values, and the subsequent 'Trim' step removes leading/trailing spaces from the resulting columns, ensuring clean 'FirstName' and 'LastName' values without manual cleanup.

Exam trap

The trap here is that candidates may think 'Left-most delimiter' or a simple split is sufficient, overlooking the need to trim extra spaces, which Power Query does not do automatically when splitting by delimiter.

How to eliminate wrong answers

Option A is wrong because splitting by number of characters using fixed width assumes a consistent character count for first and last names, which is not the case with variable-length names like 'John, Doe' vs 'Alexander, Hamilton'. Option B is wrong because splitting by semicolon ignores the actual delimiter in the data (comma), resulting in no split and leaving the column unchanged. Option D is wrong because using 'Left-most delimiter' would only split on the first comma if multiple commas existed, but the data has only one comma per entry; more critically, it does not address the trailing space after the comma, leaving ' Doe' with a leading space in the last name column.

Practice this question →

163

Multi-Selectmedium

Which TWO actions should you take to reduce the size of a Power BI dataset? (Choose two.)

Select 2 answers

A.Filter out rows that are not needed.

B.Remove unnecessary columns during import.

C.Disable query folding to improve performance.

D.Add calculated columns to precompute values.

E.Use DirectQuery instead of Import.

AnswersA, B

Filtering out rows that are not needed reduces the volume of data loaded into the Power BI model, directly lowering the number of values that VertiPaq stores and compresses. The VertiPaq engine compresses data column-by-column, so fewer rows mean smaller dictionaries and bitmaps, which can also improve compression ratios. However, filter pushdown via query folding may further improve refresh speed, but the size reduction is the primary benefit.

Why this answer

Option A is correct because filtering out rows that are not needed during import (for example, using Power Query filters or a WHERE clause in the source query) reduces the number of records loaded into the model, directly shrinking the dataset size in memory and on disk. Option B is correct because removing unnecessary columns during import eliminates entire columns of data from the model, which reduces both row-level storage and the columnar compression footprint in the VertiPaq engine. Option C is incorrect because disabling query folding typically hurts performance and does not reduce dataset size; folding pushes transformations back to the source.

Option D is incorrect because adding calculated columns increases the model's size by storing additional materialized values. Option E is incorrect because DirectQuery does not store data in the model at all, but it is a connectivity mode change rather than a size-reduction action, and it does not reduce the size of an existing Import-mode dataset.

Exam trap

The trap here is that candidates often confuse 'improving performance' with 'reducing dataset size' — disabling query folding can hurt performance and does not reduce size, while DirectQuery changes the architecture rather than reducing an existing Import dataset's size.

Practice this question →

164

Multi-Selecteasy

You are importing data from a folder containing multiple CSV files with identical structure. You use the 'Combine files' transform in Power Query. Which TWO statements are true about this process?

Select 2 answers

A.The sample file is automatically selected as the first file in alphabetical order.

B.Only the sample file is imported; other files are ignored.

C.The combine process uses Power Automate to merge files.

D.Power Query creates a function that applies the same transformations to each file.

E.Power Query creates a sample file query that serves as a template for all files.

AnswersD, E

This statement correctly describes the core mechanism Power Query uses when combining files. After you provide a sample file, Power Query auto-generates a parameterized function (often named 'Transform File') that captures the transformation steps applied to the sample file. It then calls that function for each file in the folder, passing the file's path and contents as parameters, which ensures the same cleaning, filtering, or reshaping logic is applied consistently across all files.

Why this answer

Option D is correct because when you use the 'Combine files' transform in Power Query, it generates a reusable function (typically named 'Transform File' or similar) that encapsulates the transformations applied to the sample file, and then invokes that function for every other file in the folder so each file undergoes the same steps. Option E is correct because Power Query creates a separate sample file query (often named 'Sample File' or 'Transform Sample File') that acts as a template, defining the schema and transformation logic that the function applies to all files. Option A is not correct because the sample file is not automatically chosen by alphabetical order; Power Query typically selects the first file it encounters (often based on the folder's file listing order) and lets the user pick a different sample file if needed.

Option B is not correct because all files in the folder are combined, not just the sample file. Option C is not correct because the combine process is handled entirely within Power Query's M engine, not by Power Automate.

Exam trap

The trap here is that candidates often confuse the 'sample file' as being the only file imported (Option B) or think the process uses an external tool like Power Automate (Option C), when in reality Power Query handles the entire merge natively with a generated function.

Practice this question →

165

MCQmedium

A company uses Power BI to analyze sales data from a SQL Server database. The database contains a table 'Sales' with 10 million rows. The business analysts need to create daily reports that aggregate sales by region and product category. To optimize report performance, which data preparation technique should be applied?

A.Increase the row limit in Power Query to load all rows.

B.Remove unused columns from the query.

C.Import the entire table and aggregate in Power BI.

D.Perform aggregation in SQL before importing.

AnswerD

Performing aggregation in SQL before importing is a classic pushdown optimization that leverages the database engine to pre-summarize the sales data, so only the aggregated results are transferred to Power BI. This dramatically reduces the row count and the size of the imported dataset, leading to faster refreshes, lower model memory usage, and quicker report responses. It also offloads compute from Power BI to the SQL server, which is generally more scalable for large fact tables.

Why this answer

Performing aggregation in SQL before importing reduces the data volume from 10 million rows to a much smaller aggregated result set. This minimizes memory consumption and speeds up report rendering in Power BI, as the heavy lifting is done on the SQL Server engine rather than in Power Query or the Power BI data model.

Exam trap

The trap here is that candidates often assume removing columns or filtering rows is sufficient, but the question specifically targets aggregation of millions of rows, where source-side aggregation is the only scalable solution.

How to eliminate wrong answers

Option A is wrong because increasing the row limit in Power Query does not improve performance; it forces Power Query to load all 10 million rows, increasing memory usage and refresh time. Option B is wrong because removing unused columns helps reduce data size but does not address the core issue of aggregating 10 million rows; the row count remains the same, and Power BI still must process all rows. Option C is wrong because importing the entire table and aggregating in Power BI moves the aggregation workload to the Power BI engine, which is less efficient than performing it at the database source, leading to higher memory and CPU usage during data refresh.

Practice this question →

166

MCQhard

You are debugging a Power Query that imports a CSV file. The exhibit shows the M code. The CSV file contains a header row and data. Some rows have a comma inside a quoted field (e.g., "Smith, John"). What issue will arise from this code?

A.The encoding 1252 is incorrect for the file.

B.The QuoteStyle.None option will cause commas inside quotes to be treated as delimiters.

C.The number of columns specified (5) is too many.

D.The Promoted Headers step will fail because the first row contains quotes.

AnswerB

Setting QuoteStyle.None tells the CSV parser to ignore the special meaning of double quotes, so it treats every comma as a field separator, even those that are inside a quoted string. For example, a line like "John, Doe" would become two columns instead of one, causing column count mismatches and data shifting. This is exactly the kind of symptom that would appear when debugging an import. The fix is to use QuoteStyle.Csv (or another style) so the parser honors quoted fields and keeps embedded commas as part of the same value.

Why this answer

The M code uses `QuoteStyle.None`, which tells Power Query to treat commas inside quoted fields as column delimiters rather than as part of the field value. This causes rows with values like "Smith, John" to be split incorrectly, resulting in extra columns and misaligned data. The correct option for CSV files with quoted fields is `QuoteStyle.Csv`, which respects the standard CSV quoting rules.

Exam trap

The trap here is that candidates may assume the issue is with encoding or column count, but the core problem is the misuse of `QuoteStyle.None` instead of `QuoteStyle.Csv`, which directly causes quoted commas to be misinterpreted as delimiters.

How to eliminate wrong answers

Option A is wrong because encoding 1252 (Windows Latin-1) is a common encoding for CSV files and is not inherently incorrect; the issue is unrelated to encoding. Option C is wrong because specifying 5 columns is not inherently too many; the problem is that quoted commas cause extra splits, not that the column count is excessive. Option D is wrong because the Promoted Headers step uses the first row as column names, and quotes in that row are handled by the CSV parser; the failure occurs in the data rows due to QuoteStyle.None, not in the header promotion.

Practice this question →

167

MCQmedium

You are preparing data for a Power BI report at a hospital network. You connect to an Azure Synapse Analytics dedicated SQL pool. The fact table contains encounter records, and a dimension table named Patients stores protected health information. Hospital policy requires that the Patients table be filtered to only active patients before it is loaded into the semantic model, and the filter must be applied as close to the source as possible to minimize data transfer. You also need to be able to refresh the model without modifying the source database. What should you do in Power Query?

A.In the Patients query, add a step that filters the IsActive column to true, and ensure this step is folded by keeping only foldable transformations before it.

B.Import the entire Patients table and apply a visual-level filter on IsActive in the report so only active patients appear.

C.Create a stored procedure in Azure Synapse Analytics that returns only active patients, and call it from Power Query.

D.Use a DAX calculated table in the semantic model that uses FILTER to keep only rows where IsActive is true.

AnswerA

Filtering on the IsActive column is a foldable operation for Azure Synapse Analytics, so Power Query can translate it into a WHERE clause in the native query sent to the source. This reduces the rows transferred over the network and satisfies the requirement to filter as close to the source as possible. No source schema change is needed.

Why this answer

Filtering the IsActive column inside Power Query is a foldable transformation for Azure Synapse Analytics, so the row restriction is pushed down into the source query. This minimizes data transfer and avoids modifying the database. The other options either filter after import, require source changes, or apply filtering at the wrong layer.

Exam trap

The trap here is assuming that any filter placed early in the query is automatically folded, when in fact a preceding non-foldable step can break folding and force the filter to run locally.

Practice this question →

168

MCQhard

You are troubleshooting a Power Query transformation that groups sales data by ProductID. The query runs slowly and you suspect the filter is being applied after loading all rows. What change would improve performance by pushing the filter to the source?

A.Disable the 'Enable load' option for the SalesTable

B.Use CALCULATE in DAX to filter

C.Add a 'Table.Buffer' step after the filter

D.Replace the first three lines with a native SQL query that includes the WHERE clause

AnswerD

Embedding the WHERE clause in a native SQL query lets the source database filter rows before Power Query ingests them, satisfying the stem's requirement to push filtering upstream. This avoids loading the full table and applying the filter locally, which is what causes the slow grouping by ProductID.

Why this answer

Pushing filter logic to the source database via a native SQL query with a WHERE clause reduces the amount of data loaded into Power Query. This leverages query folding, which allows the source (e.g., SQL Server) to perform the filtering before data is transferred, significantly improving performance for large datasets.

Exam trap

The trap here is that candidates often confuse in-memory buffering (Table.Buffer) or DAX filter functions with source-level query pushdown, failing to recognize that only native SQL or folding-compatible M steps can reduce data transfer from the source.

How to eliminate wrong answers

Option A is wrong because disabling 'Enable load' prevents the table from being loaded into the data model entirely, which does not address the filter pushdown issue and would remove the data needed for analysis. Option B is wrong because CALCULATE is a DAX function used in measures for filter context within the data model, not for optimizing Power Query transformation steps or pushing filters to the source. Option C is wrong because Table.Buffer caches the data in memory after the filter step, which can improve subsequent query performance but does not push the filter to the source; it still requires loading all rows before the buffer.

Practice this question →

169

MCQeasy

You are connecting to a SharePoint folder containing 100 Excel files. Each file has a similar structure but different column names. What is the best practice to combine these files into a single table while preserving the data?

A.Use Power Query's 'Combine Files' feature, selecting a sample file and promoting headers, then transforming column names to a standard set.

B.Load each file as a separate table and create relationships in the model.

C.Use Power Query's 'Merge Queries' to join all files into one table.

D.Use 'Append Queries' to stack all files, then rename columns manually.

AnswerA

This automates combining files with different structures.

Why this answer

Power Query's 'Combine Files' feature is designed specifically for this scenario: it uses a sample file to infer the transformation logic (e.g., promoting headers), then applies that logic to all files in the folder. By transforming column names to a standard set within the sample file step, you ensure consistent column names across all files, preserving data integrity while combining them into a single table.

Exam trap

The trap here is that candidates often confuse 'Combine Files' (which unions multiple files with a consistent transformation) with 'Merge Queries' (which joins tables horizontally) or 'Append Queries' (which stacks tables but lacks automated column standardization).

How to eliminate wrong answers

Option B is wrong because loading each file as a separate table and creating relationships would result in a fragmented model with many tables, making analysis cumbersome and violating the goal of combining into a single table. Option C is wrong because 'Merge Queries' performs a join (like SQL JOIN) on matching columns between two tables, not a union of multiple files; it would not stack rows from all files. Option D is wrong because 'Append Queries' can stack tables, but manually renaming columns for 100 files is impractical and error-prone; the 'Combine Files' feature automates this with a sample file transformation.

Practice this question →

170

MCQmedium

Refer to the exhibit. You are reviewing a Power BI data source credential configuration. The Azure Blob Storage data source uses 'Anonymous' credentials. However, the refresh fails with an error indicating that the blob container is private and requires authentication. Which change should you make?

A.Change credential type to 'Service Principal' and provide the app ID and secret.

B.Change credential type to 'Account Key' and provide the storage account key.

C.Change credential type to 'Basic' and provide the storage account name and key.

D.Change credential type to 'Windows' for the Azure Blob datasource.

AnswerB

When an Azure Blob Storage container is private, Power BI's anonymous (public) access is rejected, so you must authenticate with a credential the storage service actually recognizes. The Azure Blob connector in Power BI supports Account Key authentication as the straightforward shared-key method: supplying the storage account key authorizes your request as the account owner. This grants full access to the container and resolves the 'container is private' error without needing to configure Azure RBAC roles. Use the key from the storage account's Access keys blade in the Azure portal.

Why this answer

Azure Blob Storage containers that are private require authentication. The 'Account Key' credential type in Power BI uses the storage account key to authenticate via the Azure Storage REST API, which is the correct method for accessing private blob containers. Anonymous access only works when the container is configured for public access.

Exam trap

The trap here is that candidates may confuse 'Anonymous' with a valid credential type for private containers, or incorrectly assume that 'Basic' authentication is equivalent to providing a username and password for Azure Storage.

How to eliminate wrong answers

Option A is wrong because a Service Principal requires Azure AD registration and RBAC permissions, which is unnecessary and overly complex for accessing a single storage account; the account key is the simpler and correct method. Option C is wrong because 'Basic' authentication is not a valid credential type for Azure Blob Storage in Power BI; it is used for HTTP/HTTPS endpoints that support basic auth, not Azure Storage. Option D is wrong because 'Windows' authentication is for on-premises data sources like SQL Server, not for cloud-based Azure Blob Storage.

Practice this question →

171

MCQhard

You are a data analyst for a global retail company. The company uses Power BI Premium capacity. You are building a dataset that combines sales data from three sources: 1. An Azure SQL Database that stores transactional sales data (10 million rows per day, retained for 5 years). 2. A SharePoint Online folder containing monthly Excel reports from regional offices (each report has a different structure). 3. A Dataverse table that contains customer feedback scores. Requirements: - The dataset must support near real-time reporting for the current month's sales (maximum 15-minute latency). - Historical sales data (older than current month) can be refreshed daily. - Customer feedback scores should be updated every hour. - The Excel reports from SharePoint must be combined into a single table with consistent columns. - The final dataset should be optimized for fast query performance. You need to design the data preparation strategy. What should you do?

A.Use DirectQuery for all data sources and create views in Azure SQL to transform the SharePoint and Dataverse data. Use Power Query to combine SharePoint files in a view.

B.Import all data into Power BI using Import mode. Schedule refreshes every 15 minutes for the current month and daily for historical data.

C.Use a composite model: DirectQuery for the current month's sales data from Azure SQL, and Import mode for historical sales (with incremental refresh) and for customer feedback (with hourly refresh). Combine SharePoint files using Power Query and load them into the model using Import mode. Set up a DirectQuery connection for near real-time.

D.Use Azure Data Factory to copy all data to Azure SQL Database, then connect Power BI using DirectQuery.

AnswerC

This is correct because it uses a composite model to combine DirectQuery and Import modes: DirectQuery on Azure SQL for the current month's sales queries the source directly, providing near real-time visibility without data duplication or refresh latency. Historical sales are imported with incremental refresh, which partitions the data and only loads new or changed partitions, dramatically reducing refresh time and resource use. Customer feedback is imported on an hourly schedule since it does not require sub-hour latency, and SharePoint files are combined via Power Query and imported, as they lack DirectQuery support. This design maximizes performance while meeting all stated latency and freshness requirements, and it is a recognized best practice for hybrid data scenarios in Power BI.

Why this answer

It uses a composite model to meet all requirements: DirectQuery for near real-time current-month sales (≤15-minute latency), Import mode with incremental refresh for historical sales (daily refresh), Import mode for customer feedback (hourly refresh), and Power Query to combine SharePoint Excel files into a consistent table. This approach balances real-time needs with query performance and refresh flexibility, leveraging Power BI Premium's composite model capabilities.

Exam trap

The trap here is that candidates may choose Import mode for everything (Option B) without realizing the 48-refresh-per-day limit on Power BI Premium, which prevents 15-minute refreshes, or they may overlook composite models as the only way to combine real-time and historical data efficiently.

How to eliminate wrong answers

Option A is wrong because DirectQuery for all sources would cause poor query performance due to the large volume of historical data (10M rows/day for 5 years) and cannot handle combining SharePoint files with different structures in a view without transformation. Option B is wrong because Import mode with 15-minute refreshes for current-month sales would exceed the 48 daily refresh limit on Power BI Premium (48 refreshes/day = 30-minute minimum interval) and cannot achieve near real-time latency. Option D is wrong because copying all data to Azure SQL Database via Azure Data Factory introduces additional latency and complexity, and using DirectQuery for the entire dataset would still suffer from performance issues with large historical data.

Practice this question →