Courseiva

CCNA Prepare the data Questions

21 of 96 questions · Page 2/2 · Prepare the data · Answers revealed

Practice these questionsDomain overviewAll questions
76
MCQhard

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

A.Create a composite model with an imported aggregated table.
B.Define aggregations in Power BI on the DirectQuery tables.
C.Change the storage mode to Import for the fact table.
D.Create a SQL Server view that aggregates data and use it as the source.
AnswerB

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
77
MCQmedium

You are loading data from an Excel workbook that has multiple sheets. Each sheet contains sales data for a different region. You need to combine all sheets into one table. What is the most efficient approach?

A.Use 'Append Queries' for each sheet individually.
B.Use 'Merge Queries' to join all sheets into one.
C.Load each sheet as a separate table in the model and create a calculated table to union them.
D.Use Power Query's 'From Folder' option to combine all Excel files in a folder, then select 'Combine & Transform' to use the workbook as a sample.
AnswerA

Correct. The Append Queries feature can combine multiple queries (each representing a sheet) into one table. This is the most efficient method among the given options for combining sheets from a single workbook.

Why this answer

Using 'Append Queries' in Power Query allows you to combine multiple queries (one per sheet) into a single table. You can load all sheets as queries, then use the Append Queries feature to union them all at once, which is efficient and avoids manual repetition. Power Query's Append operation is designed for this purpose and is more efficient than loading separate tables into the model (C) or using Merge (B) which performs joins, not unions. 'From Folder' (D) is intended for combining multiple files, not sheets within a single workbook.

Exam trap

The trap here is confusing 'Append Queries' (union) with 'Merge Queries' (join). Candidates may also think that the 'From Folder' option can combine sheets within a single workbook, but that feature is designed to combine multiple files from a folder, not multiple sheets in one file. The most efficient approach for combining multiple sheets from the same workbook is to load each sheet as a query and then use 'Append Queries' to union them into one table.

How to eliminate wrong answers

Option A is wrong because 'Append Queries' requires manually selecting each sheet and appending them one by one, which is inefficient and not the most efficient approach when dealing with multiple sheets in a single workbook. Option B is wrong because 'Merge Queries' performs a join operation (like SQL JOIN) based on matching columns, not a union of rows, so it would not combine all sheets into one table; it would create a combined table with duplicate or merged columns. Option C is wrong because loading each sheet as a separate table and then creating a calculated table to union them is inefficient and unnecessary, as Power Query can combine them directly without adding extra tables to the model.

Practice this question →
78
MCQhard

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

A.Switch the dataset to Import mode.
B.Disable the 'Enable query reduction' option in Power BI Desktop.
C.Create aggregated tables in Power BI using the Aggregations feature.
D.Increase the memory limit of the on-premises data gateway.
AnswerC

Aggregations reduce the amount of data queried from the source.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
79
MCQmedium

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

A.Use DAX to create a date table using CALENDAR function in Power BI Desktop, then mark it as a date table.
B.Enable the 'Auto date/time' option in Power BI Desktop and hide the generated date hierarchy.
C.In the model view, create a relationship between PatientVisits[VisitDate] and DiagnosisLookup[DiagnosisCode].
D.In Power Query, create a blank query that generates a date table using the List.Dates function with a custom column for Year, Month, etc. Load it into the model and mark it as a date table.
AnswerD

This creates a proper date dimension in Power Query, which can be used for relationships.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
80
MCQmedium

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

A.The FILTER function overrides the slicer filter context.
B.The CALCULATE function removes all filters by default.
C.The measure should use ALL(Sales[Date]) to respect slicers.
D.The DATE function syntax is incorrect.
AnswerA

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
81
MCQmedium

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

A.Apply row-level filters in Power Query to import only relevant rows
B.Use calculated tables in DAX to summarize data
C.Disable the Auto Date/Time feature
D.Configure incremental refresh with a date filter
AnswerA

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
82
MCQhard

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

A.Right Anti
B.Full Outer
C.Inner
D.Left Outer
AnswerC

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →
83
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 data sources require a gateway for scheduled refresh, even in Premium.

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 →
84
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 →
85
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 →
86
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

This explicitly sets the data type.

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

You have a Power BI report that uses a date table connected to a fact table. You need to ensure that all dates in the fact table are covered by the date table. Which relationship property should you configure?

A.Make this relationship active
B.Cardinality
C.Assume referential integrity
D.Cross filter direction
AnswerC

Ensures all fact table dates exist in date table.

Why this answer

The 'Assume referential integrity' property, when enabled, tells Power BI that every value in the foreign key column of the fact table exists in the primary key column of the date table. This ensures that all dates in the fact table are covered by the date table, allowing Power BI to use more efficient storage and query execution (e.g., INNER JOIN semantics) rather than a full OUTER JOIN.

Exam trap

The trap here is that candidates often confuse 'Assume referential integrity' with 'Make this relationship active' or 'Cross filter direction', thinking that activating a relationship or changing filter direction will enforce date coverage, when in fact only referential integrity guarantees that all fact table dates are present in the date table.

How to eliminate wrong answers

Option A is wrong because 'Make this relationship active' controls which relationship is used by default for filtering, not whether all fact table dates exist in the date table. Option B is wrong because 'Cardinality' defines the type of relationship (e.g., many-to-one, one-to-one) and does not enforce that every foreign key value has a matching primary key. Option D is wrong because 'Cross filter direction' determines how filters propagate between tables (single or both directions) and has no effect on referential integrity or date coverage.

Practice this question →
88
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 →
89
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 →
90
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 →
91
MCQmedium

You are preparing data from multiple sources for a Power BI report. You need to create a star schema with a single fact table and several dimension tables. Which of the following is a best practice when designing the data model?

A.Include calculated measures in dimension tables.
B.Normalize dimension tables into multiple related tables.
C.Ensure each dimension table has a unique key and contains descriptive attributes.
D.Use natural keys from the source system as the primary key in dimension tables.
AnswerC

This is a fundamental best practice for star schema design.

Why this answer

In a star schema, dimension tables should have a unique key (surrogate or natural) and contain descriptive attributes to enable filtering and grouping in Power BI. This ensures efficient relationships with the fact table and supports intuitive report interactions. Option C directly aligns with this best practice.

Exam trap

Microsoft often tests the misconception that normalizing dimension tables (snowflake schema) is a best practice for performance, but in Power BI, denormalized star schemas are preferred to reduce joins and leverage VertiPaq compression.

How to eliminate wrong answers

Option A is wrong because calculated measures should be defined in the fact table or as explicit measures in the data model, not in dimension tables, as dimension tables are meant for attributes and keys, not aggregations. Option B is wrong because normalizing dimension tables into multiple related tables creates a snowflake schema, which can degrade query performance in Power BI due to additional joins and is generally avoided in star schema design. Option D is wrong because natural keys from the source system can be non-unique, change over time, or be composite, making them unreliable as primary keys; surrogate keys are preferred for stability and performance in dimension tables.

Practice this question →
92
Multi-Selectmedium

You are connecting to a data source that contains Personally Identifiable Information (PII). You need to ensure that only authorized users can view the data in Power BI reports. Which TWO actions should you take?

Select 2 answers
A.Enable encryption at rest for the dataset.
B.Use Power Query to mask PII columns by replacing values with '***'.
C.Define row-level security (RLS) roles in Power BI Desktop.
D.Apply sensitivity labels to the dataset.
E.Implement object-level security (OLS) to hide sensitive tables from certain users.
AnswersC, E

RLS filters data for users based on their role, restricting access to rows they are authorized to see.

Why this answer

Row-Level Security (RLS) allows you to restrict data access at the row level based on user roles defined in Power BI Desktop. By creating roles and using DAX filters, you can ensure that only authorized users see specific rows of PII data when the report is published to the Power BI service and users are mapped to roles.

Exam trap

The trap here is that candidates often confuse data masking (Option B) with dynamic security, not realizing that Power Query masking is irreversible and applies to all users, whereas RLS and OLS provide granular, user-specific access control without altering the underlying data.

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

Native SQL query allows the database to apply the filter before returning data.

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 →
94
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 →
95
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 →
96
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 →
← PreviousPage 2 of 2 · 96 questions total

Ready to test yourself?

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

Start 20-question session