CCNA Prepare the data Questions — Page 3 of 4

151

Multi-Selecthard

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

Select 2 answers

A.Using the time intelligence functions like TOTALYTD.

B.Marking an existing table as a date table.

C.Enabling the auto date/time feature.

D.Using DAX function CALENDAR or CALENDARAUTO.

E.Using Power Query with List.Dates.

AnswersD, E

Creates a date table with a range of dates.

Why this answer

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

Exam trap

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

Practice this question →

152

MCQmedium

You are building a Power BI report for a logistics company. The data is stored in a CSV file on a SharePoint Online document library. The CSV file is updated daily with new rows. You need to ensure that the Power BI dataset reflects the latest data every morning at 7:00 AM. The data volume is small, so full refresh is acceptable. You have already published the report to the Power BI service. What should you do to automate the refresh?

A.In the Power BI service, configure a scheduled refresh for the dataset with the desired time.

B.Use Power Automate to trigger a refresh via the Power BI REST API every morning.

C.Enable incremental refresh for the dataset and set the refresh frequency to daily.

D.Install an on-premises data gateway and configure a scheduled refresh.

AnswerA

Correct. Scheduled refresh is the standard way to automate cloud data source refreshes.

Why this answer

Option A is correct because the Power BI service supports scheduled refresh natively for datasets that connect to cloud data sources like SharePoint Online CSV files. By configuring a scheduled refresh in the dataset settings, you can set it to run daily at 7:00 AM without any additional tools or gateways. This ensures the dataset reflects the latest data from the CSV file each morning.

Exam trap

The trap here is that candidates often overcomplicate the solution by choosing Power Automate or incremental refresh, not realizing that the Power BI service's built-in scheduled refresh is sufficient and the simplest option for cloud-based data sources with small data volumes.

How to eliminate wrong answers

Option B is wrong because while Power Automate can trigger a refresh via the Power BI REST API, it is unnecessary overhead for a simple daily refresh of a small dataset from a cloud source; the built-in scheduled refresh in the Power BI service is simpler and more direct. Option C is wrong because incremental refresh is designed for large datasets to partition data and refresh only new or changed rows, but the question states data volume is small and full refresh is acceptable, so incremental refresh adds complexity without benefit. Option D is wrong because an on-premises data gateway is required only for on-premises data sources (e.g., SQL Server on a local network), but the CSV file is stored in SharePoint Online, which is a cloud source accessible directly by the Power BI service without a gateway.

Practice this question →

153

MCQmedium

You are reviewing a Power Query M expression in the advanced editor. The exhibit shows the query. What is the final output of this query?

A.A table with total sales amount per customer for all orders

B.A table with total sales amount per customer for orders after 2023

C.A table with all sales records after 2023

D.A table with all sales records after 2023, excluding Discount column

AnswerB

The query filters, removes columns, and groups by CustomerID summing Amount.

Why this answer

The query filters the Sales table to keep only rows where the OrderDate is in 2024 or later (i.e., after 2023), then groups by CustomerID, summing the SalesAmount for each customer. The final output is a table with one row per customer showing their total sales amount for orders placed after 2023.

Exam trap

The trap here is that candidates often overlook the filter step and assume the query returns all records or all customers, failing to recognize that the date filter and grouping fundamentally change both the row set and the structure of the output.

How to eliminate wrong answers

Option A is wrong because it describes total sales per customer for all orders, but the query includes a filter step that removes orders from 2023 and earlier. Option C is wrong because it suggests all sales records after 2023 are returned, but the query groups the data and aggregates (sums) the sales, so individual records are not preserved. Option D is wrong because it mentions excluding a Discount column, but the query does not remove any columns; it only filters rows and then groups/aggregates.

Practice this question →

154

MCQmedium

You are examining a Power BI dataflow configuration. The dataflow is scheduled to refresh the 'Sales' entity daily and the 'Product' entity hourly. However, the 'Product' entity refresh fails every hour with an error indicating the SharePoint list data source is not accessible. You need to diagnose the issue. What is the most likely cause?

A.The refresh interval is too frequent for the incremental granularity of 'Day'

B.Incremental refresh is not supported for SharePoint list data sources

C.The workspace ID is missing from the dataflow configuration

D.The on-premises data gateway is not configured for the SharePoint list

AnswerA

Hourly refresh with daily granularity can cause overlapping partitions and high load.

Why this answer

Option A is correct because the 'Product' entity is configured to refresh hourly, but the dataflow's incremental refresh policy is set to a 'Day' granularity. Incremental refresh in Power BI requires the refresh frequency to align with or be less frequent than the incremental period; an hourly refresh on a daily partition causes the engine to attempt to query the entire day's data each time, which can lead to timeouts or errors if the source (SharePoint list) cannot handle the repeated full scans. The error 'data source is not accessible' often masks this underlying mismatch between refresh frequency and incremental granularity.

Exam trap

The trap here is that candidates often assume the error is due to gateway configuration or source incompatibility, but the real issue is a misalignment between the refresh schedule and the incremental refresh granularity, which is a subtle but common configuration mistake in dataflows.

How to eliminate wrong answers

Option B is wrong because incremental refresh is supported for SharePoint list data sources when using Power BI dataflows, provided the list has a date/time column for filtering. Option C is wrong because a missing workspace ID would prevent the dataflow from being saved or published, not cause a recurring refresh failure with a specific 'not accessible' error. Option D is wrong because an on-premises data gateway is only required for on-premises SharePoint (e.g., SharePoint Server), not for SharePoint Online lists accessed via the cloud; the error would also typically mention gateway connectivity if that were the issue.

Practice this question →

155

MCQmedium

You are reviewing a Power BI data source configuration for an Azure SQL Database. The connection uses OAuth2 authentication. However, when scheduling a refresh, the refresh fails with an error that authentication fails. What is the most likely cause?

A.The authority URL should point to the specific Azure AD tenant, not 'common'

B.The connection string has 'Encrypt=True' which is incompatible with OAuth2

C.The server name in the connection string is incorrect

D.The resource URL should be 'https://management.azure.com/'

AnswerA

Using 'common' can cause authentication failures; it should be the specific tenant ID.

Why this answer

When using OAuth2 authentication with Azure SQL Database in Power BI, the authority URL must point to the specific Azure AD tenant (e.g., 'https://login.microsoftonline.com/your-tenant-id') rather than the generic 'common' endpoint. The 'common' endpoint is designed for multi-tenant applications and cannot resolve the tenant-specific authentication context required for scheduled refreshes, leading to authentication failures.

Exam trap

The trap here is that candidates often assume 'common' is always acceptable for OAuth2 flows, but Power BI scheduled refreshes require a tenant-specific authority URL to ensure the authentication context matches the Azure SQL Database's tenant.

How to eliminate wrong answers

Option B is wrong because 'Encrypt=True' is a recommended setting for Azure SQL Database connections and is fully compatible with OAuth2; it does not cause authentication failures. Option C is wrong because an incorrect server name would result in a connection timeout or 'cannot find server' error, not an authentication failure. Option D is wrong because the resource URL for Azure SQL Database should be 'https://database.windows.net/' (the scope for SQL Database), not 'https://management.azure.com/', which is used for Azure Resource Manager APIs.

Practice this question →

156

Drag & Dropmedium

Drag and drop the steps to create a calculated column in Power BI Desktop into the correct order.

Drag steps to the numbered slots on the right, or tap a step then tap a slot.

Steps

Order

Why this order

Creating a calculated column involves selecting the table, using the New Column button, and writing a DAX expression that evaluates row by row.

Practice this question →

157

MCQeasy

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

A.Change the system region settings of the Power BI service to US

B.Use the 'Using Locale' option in the Change Type step to select the appropriate locale (e.g., English (United States))

C.Change the column data type to Text and then manually replace separators

D.Split the column into day, month, and year, then combine them in the correct order

AnswerB

Using locale allows Power Query to interpret dates correctly based on regional settings.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

158

MCQmedium

When importing data from a CSV file, Power Query detects that the first row contains column headers. However, the actual data starts from row 2. The analyst notices that some rows have extra columns due to commas within quoted fields. What is the most efficient way to handle this issue?

A.Remove the top row and then split columns manually.

B.Change the file encoding from UTF-8 to ANSI.

C.Use 'Split Column by Delimiter' and choose 'Comma' with the option to split at each occurrence.

D.Use 'Replace Values' to replace commas with semicolons.

AnswerC

This correctly handles quoted commas.

Why this answer

Option C is correct because the 'Split Column by Delimiter' feature in Power Query, when configured to split at each occurrence of the comma delimiter, correctly handles commas that appear inside quoted fields. Power Query's M engine respects the standard CSV quoting rules (RFC 4180), so quoted commas are not treated as delimiters. This approach is the most efficient as it requires no manual cleanup and preserves data integrity.

Exam trap

The trap here is that candidates may think manual splitting or replacing commas is simpler, but they overlook that Power Query's built-in split function natively respects CSV quoting rules, making it the most efficient and accurate solution.

How to eliminate wrong answers

Option A is wrong because removing the top row and manually splitting columns is inefficient and error-prone; it does not automatically handle commas within quoted fields, requiring manual intervention for each row. Option B is wrong because changing file encoding from UTF-8 to ANSI does not affect how commas inside quoted fields are parsed; encoding only affects character representation, not delimiter logic. Option D is wrong because replacing commas with semicolons would corrupt data by altering legitimate commas inside quoted fields, and it does not solve the problem of extra columns caused by quoted commas.

Practice this question →

159

Multi-Selecteasy

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

Select 2 answers

A.Pivot Column

B.Append Queries

C.Unpivot Columns

D.Merge Queries

E.Group By

AnswersB, D

Appends rows from one query to another.

Why this answer

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

Exam trap

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

Practice this question →

160

Multi-Selecteasy

Which TWO data sources can be used with DirectQuery mode in Power BI? (Choose two.)

Select 2 answers

A.Excel workbook

B.SQL Server

C.Azure SQL Database

D.CSV file

E.SharePoint list

AnswersB, C

SQL Server supports DirectQuery.

Why this answer

SQL Server is correct because it is a relational database that supports DirectQuery mode in Power BI, allowing queries to be sent directly to the source without importing data. This enables real-time access to large datasets while leveraging SQL Server's query processing capabilities.

Exam trap

The trap here is that candidates often confuse DirectQuery with Import mode, assuming any data source can be used with DirectQuery, but only relational databases like SQL Server and Azure SQL Database support it, while file-based sources like Excel and CSV do not.

Practice this question →

161

MCQeasy

You are preparing a Power BI dataset that uses a SQL Server database as the source. Your manager asks you to ensure that the dataset refreshes automatically every morning at 6:00 AM. You have configured a scheduled refresh in the Power BI service. However, after the first scheduled refresh, you notice that it failed with an error: 'The data source credentials are invalid.' What is the most likely cause and solution?

A.The scheduled refresh time is not set correctly.

B.The data source credentials are not configured in the gateway.

C.An on-premises data gateway is not installed.

D.The dataset is too large for scheduled refresh.

AnswerB

Credentials must be set for the gateway to connect.

Why this answer

The error 'The data source credentials are invalid' indicates that the credentials stored in the Power BI service for the data source are incorrect or missing. Since the dataset uses a SQL Server database, which is an on-premises data source, the credentials must be configured within the on-premises data gateway settings in the Power BI service. Option B correctly identifies that the gateway credentials were not set up, causing the refresh to fail despite the scheduled refresh being configured.

Exam trap

The trap here is that candidates often confuse the scheduled refresh configuration with the data source credentials, assuming that setting the refresh time is sufficient, or they mistakenly think the error relates to the user's Power BI login rather than the gateway's stored credentials for the SQL Server database.

How to eliminate wrong answers

Option A is wrong because the scheduled refresh time being set incorrectly would not produce a 'credentials invalid' error; it would either not trigger or trigger at the wrong time. Option C is wrong because if no on-premises data gateway were installed, the error would typically indicate that a gateway is required or that the data source is unreachable, not specifically that credentials are invalid. Option D is wrong because a dataset being too large for scheduled refresh would result in a timeout or capacity error, not a credential validation failure.

Practice this question →

162

MCQhard

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

A.Change Type to Decimal, then Replace Values (',' with ''), then Replace Values ('(' with '-')

B.Replace Values (',' with ''), then Replace Values ('(' with '-'), then Replace Values (')' with ''), then Change Type to Decimal

C.Replace Values (',' with ''), Change Type to Decimal, then Replace Values ('(' with '-')

D.Replace Values ('(' with '-'), Replace Values (')' with ''), Replace Values (',' with ''), then Change Type to Decimal

AnswerD

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

163

MCQeasy

You are a business analyst at a manufacturing company. You receive weekly CSV files from different plants. Each file contains columns: PlantID, Date, ProductID, UnitsProduced, and ScrapUnits. However, the files sometimes have missing values in the ScrapUnits column, and occasionally there are duplicate rows (same PlantID, Date, ProductID). You need to prepare a clean dataset for reporting. The requirements are: 1. Combine all CSV files from a folder into a single table. 2. Replace null values in ScrapUnits with 0. 3. Remove duplicate rows based on PlantID, Date, and ProductID, keeping the first occurrence. 4. Ensure the data types are appropriate (e.g., Date as date, UnitsProduced as whole number). Which sequence of Power Query steps should you use?

A.Connect to folder, replace nulls in each file, combine files, remove duplicates, set data types.

B.Connect to folder, combine files, set data types, replace nulls, remove duplicates.

C.Connect to folder, combine files, remove duplicates, replace nulls, set data types.

D.Connect to folder, combine files, replace nulls, remove duplicates, set data types.

AnswerD

This order ensures nulls are handled before deduplication, and data types are set last to avoid conversion issues.

Why this answer

Option C is correct because the logical order is: connect to folder, combine files, then clean data (replace nulls, remove duplicates) and set data types. Option A is incorrect because setting data types before cleaning can cause errors. Option B is incorrect because removing duplicates before replacing nulls may remove rows that could be fixed.

Option D is incorrect because removing duplicates before combining files would apply to each file individually, not across files.

Practice this question →

164

MCQhard

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

A.Change the connection string to use a SQL account instead of integrated security.

B.Rename the parameter 'StartDate' to 'RangeStart' and add a 'RangeEnd' parameter.

C.Enable query folding on the SQL Server source.

D.Define the parameters in the Power Query editor instead of the JSON file.

AnswerB

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

165

Multi-Selecthard

You are using Copilot for Power BI to assist with data preparation. Which THREE tasks can Copilot help you with?

Select 3 answers

A.Generate DAX expressions for calculated columns and measures.

B.Create Power Query M code snippets for common transformations.

C.Set up incremental refresh policies.

D.Suggest transformations to clean and shape data.

E.Configure data source credentials for refresh.

AnswersA, B, D

Copilot can write DAX based on natural language descriptions.

Why this answer

Copilot for Power BI can generate DAX expressions for calculated columns and measures by interpreting natural language prompts, which helps users create complex formulas without deep DAX knowledge. This is a core feature of Copilot's AI assistance in Power BI Desktop.

Exam trap

The trap here is that candidates may assume Copilot can automate administrative tasks like refresh policies or credential configuration, but Copilot is limited to data preparation and transformation assistance within the Power BI Desktop environment.

Practice this question →

166

Multi-Selecthard

You are preparing data for a Power BI report. The source data has a column 'OrderDate' with date values. You need to create a date hierarchy for drill-down (Year, Quarter, Month). Which TWO actions are required?

Select 2 answers

A.Create a separate date table with a continuous range of dates.

B.Enable auto date/time in Power BI Desktop.

C.Sort the Month column by MonthNumber to ensure proper order.

D.Mark the date table as a date table in Power BI Desktop.

E.Add calculated columns for Year, Quarter, and Month to the date table.

AnswersA, D

A proper date table is needed to create a date hierarchy and support time intelligence.

Why this answer

Options B and D are correct. You need to mark a date table as a date table to enable time intelligence and create a date hierarchy. Creating a date table with a continuous range ensures all periods are represented.

Option A is wrong because auto date/time creates hidden hierarchies but may not be sufficient for custom requirements. Option C is wrong because calculated columns do not create a hierarchy. Option E is wrong because sorting by month number is useful but not required for a hierarchy.

Practice this question →

167

Multi-Selecteasy

Which THREE are data sources that can be imported into Power BI Desktop?

Select 3 answers

A.SQL Server database

B.CSV file

C.PDF file

D.Excel workbook

E.Word document

AnswersA, B, D

Supported relational source.

Why this answer

A is correct because Power BI Desktop includes native connectors for SQL Server databases, allowing direct import of data via T-SQL queries or table selection. This is a first-class data source in the Get Data experience, supporting Windows and SQL Server authentication.

Exam trap

Microsoft often tests the misconception that PDF and Word documents are importable data sources in Power BI Desktop, when in reality only structured data sources like databases, CSV, and Excel are natively supported for import.

Practice this question →

168

MCQmedium

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

A.Use a fixed start and end date in the CALENDAR function

B.Create a disconnected date table

C.Create the date table using CALENDAR(MIN('Fact'[Date]), MAX('Fact'[Date]))

D.Enable Auto Date/Time in the model

AnswerC

This dynamic approach updates with the fact table's date range.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

169

MCQmedium

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

A.Create a composite model using DirectQuery for the fact table and Import for dimensions.

B.Use DirectQuery mode for the dataset.

C.Configure incremental refresh on the fact table.

D.Use Import mode but filter rows and reduce columns in Power Query to only those needed.

AnswerD

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

170

MCQmedium

You are a data analyst for a retail company. You receive daily sales data in CSV files stored in an Azure Blob Storage container. The files have a consistent naming convention: Sales_YYYYMMDD.csv. You need to load only the latest file into Power BI each day, using Power Query. You want to automate this process so that each time you refresh the dataset, it picks up the most recent file. You have already created a connection to the container. What should you do in Power Query to ensure only the latest file is loaded?

A.Use 'From Azure Blob Storage' and filter by last modified date.

B.Use 'Combine Files' and then filter to the latest date.

C.Use 'From Folder' connector, filter by creation date to keep only the latest file.

D.Create a parameter for the file name and update it manually.

AnswerC

Automatically picks latest file on refresh.

Why this answer

Option C is correct because the 'From Folder' connector in Power Query allows you to connect to a folder (including Azure Blob Storage containers mapped as folders) and then filter files by creation date or last modified date. By filtering to keep only the latest file, you ensure that each refresh loads the most recent CSV without manual intervention. This approach fully automates the process, as Power Query will evaluate the filter each time the query refreshes.

Exam trap

The trap here is that candidates often confuse 'From Azure Blob Storage' with 'From Folder', not realizing that 'From Folder' provides direct access to file metadata like creation date, which is essential for filtering to the latest file, while the blob storage connector is more suited for direct file selection without metadata filtering.

How to eliminate wrong answers

Option A is wrong because 'From Azure Blob Storage' connector in Power Query does not natively support filtering by last modified date within the connector itself; it requires additional steps like listing blobs and filtering via Power Query functions, which is less direct than using 'From Folder'. Option B is wrong because 'Combine Files' is used to merge multiple files into a single table, not to select a single latest file; filtering after combining would still process all files, defeating the purpose of loading only the latest. Option D is wrong because creating a parameter for the file name and updating it manually defeats the automation requirement; the goal is to pick the latest file automatically without manual intervention.

Practice this question →

171

MCQmedium

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

A.Increase the scheduled refresh frequency to every 15 minutes.

B.Enable Query Folding on all steps in Power Query.

C.Change the storage mode to DirectQuery.

D.Remove unused columns from the table in Power Query.

AnswerD

Reduces data volume and improves refresh speed.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

172

MCQhard

Refer to the exhibit. You are reviewing a Power BI data source privacy configuration. The SalesSQL data source is set to 'Organizational' and the MarketingCSV is set to 'Private'. You plan to combine these two sources in a query. What will happen when the query is executed?

A.The query will succeed, but the MarketingCSV data will be treated as Organizational.

B.The query will succeed without any restrictions because the combine is allowed between Organizational and Private.

C.The query will fail with a privacy level error because the levels are incompatible.

D.The query will succeed, but the combination may be blocked or data may be limited to protect the Private source.

AnswerD

Power BI will restrict data flow to prevent the Private source from being exposed to the Organizational source.

Why this answer

Option C is correct because when combining an Organizational source with a Private source, Power BI restricts data propagation to protect the Private source. The Organizational data will be limited to what can be sent to the Private source, potentially causing performance degradation or blocking the combine operation. Option A is wrong because the combination is not entirely blocked, but limited.

Option B is wrong because the Private level does not override the Organizational; instead, the most restrictive applicable rules apply. Option D is wrong because the combine is not allowed at full speed.

Practice this question →

173

MCQeasy

You are merging two tables in Power Query: Orders and Customers. The Orders table has a CustomerID column, and the Customers table has a CustomerID column. You want to keep all rows from Orders and only matching rows from Customers. Which join kind should you use?

A.Full Outer

B.Inner

C.Left Outer

D.Right Outer

AnswerC

Left outer join preserves all rows from the left table.

Why this answer

Option C (Left Outer) is correct because it returns all rows from the Orders table regardless of whether a matching CustomerID exists in the Customers table. When no match is found, the Customers columns will contain null values. This is the standard behavior for a left outer join in Power Query, which preserves the left table's rows entirely.

Exam trap

The trap here is that candidates often confuse Left Outer with Right Outer, mistakenly thinking that 'keeping all rows from Orders' means the Orders table should be on the right side, when in Power Query the join direction is determined by which table is selected as the primary table in the Merge dialog.

How to eliminate wrong answers

Option A is wrong because a Full Outer join would return all rows from both tables, including unmatched rows from both sides, which is not what the requirement specifies. Option B is wrong because an Inner join would only return rows where CustomerID exists in both tables, discarding any Orders rows without a matching Customer in Customers. Option D is wrong because a Right Outer join would keep all rows from Customers and only matching rows from Orders, which is the opposite of the stated requirement.

Practice this question →

174

MCQmedium

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

A.Use the 'Column from Examples' feature to create a new column with numeric values.

B.In Power Query, split the Response column by delimiter and then use the first part.

C.Use a DAX calculated column to convert text to number.

D.Change the data type of Response to whole number in Power Query, then filter out or replace null values.

AnswerD

Directly resolves the type issue and handles nulls.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

175

MCQhard

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

A.Use the CALENDAR function in DAX to generate the date range

B.Mark the date column from the fact table as a date table and disable Auto Date/Time

C.Create a date dimension by using DISTINCT on the fact table's date column

D.Create a date table in Power Query by generating a list of dates from 1/1/2010 to 12/31/2030 and then add columns for attributes

AnswerD

Power Query can generate a complete date range and allows adding custom columns for holidays.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

176

Multi-Selecthard

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

Select 2 answers

A.Disable the 'Enable background refresh' option in the query settings.

B.Merge tables as early as possible in the query to combine data.

C.Keep all columns in the table to avoid missing data.

D.Filter data as early as possible in the query to reduce row counts.

E.Split columns by delimiter to normalize data.

AnswersA, D

Disabling background refresh can improve performance by preventing simultaneous refreshes.

Why this answer

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

Exam trap

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

Practice this question →

177

MCQhard

You are a data analyst at a retail company. You are building a Power BI report to analyze sales performance across stores. The data source is a SQL Server database with a table called 'SalesTransactions' containing 500 million rows. The table has columns: TransactionID, StoreID, ProductID, Quantity, UnitPrice, Discount, TransactionDate. You have imported the data into Power BI using Import mode. The report is slow when users filter by date or store. The initial data load took 45 minutes, and scheduled refreshes are failing because they exceed the 2-hour refresh limit. You need to reduce the refresh time and improve query performance. The business requires that users can see all historical data and that the report is always up-to-date (refreshed daily). What should you do?

A.Create aggregations on the SalesTransactions table.

B.Implement Incremental refresh with a policy to keep the last 5 years of data and detect changes.

C.Switch to DirectQuery mode to avoid data import.

D.Use a live connection to the SQL Server database.

AnswerB

Reduces refresh time and keeps history.

Why this answer

Option B is correct because incremental refresh in Power BI allows you to partition large tables (like 500M rows) into smaller date-based ranges, refreshing only the most recent data (e.g., last 5 years) while keeping historical partitions static. This drastically reduces refresh time and avoids the 2-hour refresh limit, while still meeting the requirement for daily updates and full historical visibility. The 'detect changes' option further optimizes by only refreshing partitions where data has changed.

Exam trap

The trap here is that candidates often choose aggregations (Option A) thinking they solve performance issues, but they fail to realize that aggregations do not reduce the volume of data imported during refresh—only incremental refresh addresses both refresh time and query performance for massive fact tables.

How to eliminate wrong answers

Option A is wrong because creating aggregations improves query performance for summarized queries but does not reduce the data import or refresh time; the full 500M rows still need to be loaded during refresh, so it fails to address the refresh timeout. Option C is wrong because switching to DirectQuery would avoid data import but would result in poor query performance on 500M rows due to live query overhead, and it would not meet the 'always up-to-date' requirement as effectively as incremental refresh with daily scheduling. Option D is wrong because a live connection (DirectQuery to SQL Server) has the same performance drawbacks as DirectQuery and does not reduce refresh time—it eliminates import but shifts performance burden to the source, which is not optimal for large fact tables.

Practice this question →

178

MCQeasy

You are connecting Power BI to a SQL Server database. The database contains a table with millions of sales transactions. You need to design a data model that minimizes load time and memory usage while still allowing analysis of sales by date, product, and customer. Which modeling approach should you use?

A.Use a composite model with some tables in DirectQuery and others in Import.

B.Use DirectQuery mode to avoid storing data in Power BI.

C.Import the data into Power BI, creating a star schema with date, product, and customer dimension tables and the sales fact table.

D.Use a live connection to an existing SQL Server Analysis Services tabular model.

AnswerC

Import mode with a star schema optimizes performance and memory usage.

Why this answer

Option C is correct because importing the data into Power BI and modeling it as a star schema with separate dimension tables (date, product, customer) and a sales fact table minimizes load time and memory usage through columnar compression and optimized query performance. Import mode stores data in the VertiPaq engine, which compresses data efficiently, especially when using a star schema, reducing memory footprint and enabling fast in-memory analysis. This approach balances storage efficiency with analytical flexibility for millions of rows.

Exam trap

The trap here is that candidates often choose DirectQuery (Option B) thinking it saves memory by not storing data, but they overlook that Import mode with a star schema actually minimizes memory usage through compression and is faster for interactive analysis, while DirectQuery increases load on the source and can degrade performance.

How to eliminate wrong answers

Option A is wrong because a composite model with some tables in DirectQuery and others in Import introduces complexity and potential performance overhead from cross-source query federation, which does not inherently minimize load time or memory usage compared to a fully imported star schema. Option B is wrong because DirectQuery mode avoids storing data in Power BI, but it does not minimize load time or memory usage; instead, it shifts query execution to the source database, which can lead to slower performance for interactive analysis and does not leverage Power BI's in-memory compression. Option D is wrong because a live connection to an existing SSAS tabular model does not minimize load time or memory usage in Power BI itself—it delegates processing to SSAS, which may not be optimized for the specific star schema design needed, and adds dependency on an external service without reducing Power BI's resource consumption.

Practice this question →

179

MCQhard

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

A.Use the 'Run KQL command' option in Power Query to pass the query directly.

B.Add additional transformations in Power Query to reduce rows.

C.Enable query folding to push the query to the Kusto source.

D.Disable query folding to improve performance.

AnswerA

Running the KQL command directly in Power Query avoids pulling all rows into Power Query for additional transformations, improving performance.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

180

Multi-Selectmedium

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

Select 2 answers

A.Split columns by delimiter to create more columns.

B.Filter out empty rows.

C.Sort the data by a key column.

D.Promote headers to use the first row as column names.

E.Merge queries to combine data from multiple sources.

AnswersB, D

Removing empty rows reduces the amount of data loaded.

Why this answer

Filtering out empty rows in Power Query Editor directly reduces the number of rows loaded into the data model, which decreases memory usage and improves refresh performance. This action also enhances data quality by removing incomplete records that could cause errors in measures or relationships.

Exam trap

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

Practice this question →

181

MCQmedium

You are importing data from a large CSV file (5 GB) into Power BI. The import takes too long and you need to reduce the data volume. What is the most effective approach in Power Query?

A.Disable loading of unrelated queries.

B.Increase query parallelism in options.

C.Filter rows and remove unnecessary columns in Power Query.

D.Enable 'Query Reduction' settings.

AnswerC

Directly reduces the amount of data loaded.

Why this answer

Option C is correct because filtering rows and removing unnecessary columns in Power Query directly reduces the amount of data loaded into Power BI's data model. This is the most effective way to reduce data volume from a large CSV file, as it minimizes the data that Power Query must process and store, directly addressing the import time issue.

Exam trap

The trap here is that candidates often confuse 'Query Reduction' settings (which affect report interaction queries) with data volume reduction during import, leading them to incorrectly select Option D.

How to eliminate wrong answers

Option A is wrong because disabling loading of unrelated queries does not reduce the data volume of the specific CSV file being imported; it only prevents other queries from being loaded into the model, which does not address the size of the target CSV. Option B is wrong because increasing query parallelism in Power Query options can improve processing speed by running multiple queries concurrently, but it does not reduce the data volume of the large CSV file; it may even increase resource contention without addressing the root cause of excessive data. Option D is wrong because 'Query Reduction' settings in Power BI are designed to optimize report performance by reducing the number of queries sent to the data source during report interaction (e.g., disabling cross-highlighting), not to reduce the volume of data imported during the initial data load from a CSV file.

Practice this question →

182

MCQmedium

You are importing a large CSV file (200 MB) into Power BI Desktop. The import is very slow and sometimes fails. What should you do to improve performance?

A.Use Power BI Service to import the file instead.

B.Remove all relationships before import.

C.Filter rows and columns during import using Power Query to reduce data size.

D.Upgrade to Power BI Premium.

AnswerC

Reducing data volume before loading improves performance.

Why this answer

Option C is correct because filtering rows and columns during import using Power Query reduces the data volume loaded into the Power BI Desktop model, directly addressing the root cause of slow imports and failures when handling a 200 MB CSV file. Power Query's query folding capabilities push data reduction operations (e.g., removing unnecessary columns, filtering rows based on conditions) upstream, minimizing memory and processing overhead during the import phase. This approach is a standard best practice for optimizing data ingestion in Power BI Desktop, especially for large flat files.

Exam trap

The trap here is that candidates often assume upgrading to Premium or using the Service will magically fix performance issues, but the PL-300 exam emphasizes that data reduction during import (via Power Query filtering) is the primary technique to optimize large file ingestion in Power BI Desktop.

How to eliminate wrong answers

Option A is wrong because Power BI Service does not import CSV files directly; it relies on Power BI Desktop or dataflows to prepare and publish data, and the import speed bottleneck is typically local memory and processing, not the destination service. Option B is wrong because removing all relationships before import does not reduce the data size or improve import performance; relationships are metadata that affect query performance after loading, not the initial data ingestion from a CSV file. Option D is wrong because upgrading to Power BI Premium increases capacity limits (e.g., dataset size up to 400 GB) but does not inherently speed up the import of a 200 MB CSV file in Power BI Desktop; the import process is constrained by local resources and data volume, not licensing tier.

Practice this question →

183

MCQmedium

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

A.Use an inactive relationship between Sales and Marketing and activate it with USERELATIONSHIP in measures.

B.Create a bridge table containing unique combinations of OrderID and CampaignID.

C.Create a separate table for each campaign and relate to Sales.

D.Merge the Marketing table into the Sales table using a left outer join.

AnswerB

A bridge table properly resolves the many-to-many relationship between Sales and Marketing.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

184

Multi-Selecteasy

Which TWO of the following are valid options when connecting to an on-premises SQL Server database from Power BI service? (Select TWO.)

Select 2 answers

A.DirectQuery mode with an on-premises data gateway

B.Using a SharePoint Online list connector pointing to SQL Server

C.DirectQuery using cloud credentials without a gateway

D.Import mode with an on-premises data gateway

E.Scheduling refresh in Power BI Desktop

AnswersA, D

DirectQuery also requires a gateway for on-premises sources.

Why this answer

Option A is correct because DirectQuery mode in Power BI service requires an on-premises data gateway to translate cloud-based queries into native SQL queries against the on-premises SQL Server. The gateway acts as a secure bridge, enabling real-time query execution without moving data to the cloud.

Exam trap

The trap here is that candidates often confuse 'connection mode' (DirectQuery vs. Import) with 'authentication method' (cloud credentials vs. gateway), mistakenly thinking DirectQuery can bypass the gateway for on-premises sources.

Practice this question →

185

MCQeasy

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

A.Use 'Replace Values' to remove the dollar sign and comma, then change the column type to decimal.

B.Use 'Detect Data Type' and hope it automatically converts.

C.Change the column type to decimal directly and ignore errors.

D.Split the column by delimiter ',' and then convert the first part.

AnswerA

This directly cleans the text and converts to number.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

186

Multi-Selecteasy

You are preparing data from multiple Excel files. Each file has a different structure; some have merged cells, empty rows, and inconsistent column names. Which TWO actions should you take to clean the data in Power Query? (Choose two.)

Select 2 answers

A.Remove blank rows that result from merged cells.

B.Group rows by a key column to summarize data.

C.Merge columns to create a single identifier.

D.Unpivot columns to normalize the data.

E.Promote the first row to headers.

AnswersA, E

Merged cells often create blank rows; removing them cleans the data.

Why this answer

Option A is correct because merged cells in Excel often introduce blank rows when the data is loaded into Power Query. Using 'Remove Blank Rows' in Power Query eliminates these extraneous rows that do not contain meaningful data, ensuring the dataset is clean and ready for transformation.

Exam trap

The trap here is that candidates often confuse data-cleaning actions (like removing blank rows and promoting headers) with data-transformation actions (like grouping, merging, or unpivoting), leading them to select options that reshape data rather than fix structural inconsistencies.

Practice this question →

187

MCQhard

You review the output of a PowerShell command listing datasets in a Power BI workspace. Which dataset can be refreshed using the Power BI service and also supports push data via the REST API?

A.None of the datasets satisfy both conditions.

B.Profit Report

C.Inventory Dataset

D.Sales Analysis

AnswerA

No dataset has both properties set to True.

Why this answer

Option A is correct because none of the listed datasets can be refreshed via the Power BI service and also support push data via the REST API. Push datasets are designed for real-time streaming and do not support scheduled or on-demand refresh in the service; they are updated only by pushing new data through the REST API. Conversely, datasets that support refresh (e.g., import or DirectQuery) cannot accept push data because their data source is external and not designed for real-time REST API ingestion.

Exam trap

The trap here is that candidates confuse the term 'dataset' with 'report' or assume that all datasets support both refresh and push operations, failing to recognize that push datasets are mutually exclusive with refresh capabilities.

How to eliminate wrong answers

Option B (Profit Report) is wrong because it is a report, not a dataset; reports cannot be refreshed or receive push data. Option C (Inventory Dataset) is wrong because if it supports push data via the REST API, it is a push dataset and therefore cannot be refreshed in the Power BI service. Option D (Sales Analysis) is wrong because it is a report, not a dataset; reports do not support refresh or push data operations.

Practice this question →

188

MCQhard

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

A.Use DirectQuery for StockPrices (CSV) and import the other tables.

B.Import only StockPrices and Company, and use the auto date/time feature; ignore Earnings data.

C.Import all tables, create a date table with CALENDAR, and establish relationships: StockPrices[Date] -> DateTable[Date], StockPrices[Ticker] -> Company[Ticker], Earnings[Ticker] -> Company[Ticker], and create a many-to-many relationship between Earnings and DateTable using a bridge table.

D.Import all tables, then in Power Query merge StockPrices with Company and Earnings into a single flat table using left outer joins.

AnswerC

Star schema with proper relationships optimizes performance.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

189

Multi-Selecthard

Which THREE considerations are important when designing a star schema for a Power BI semantic model? (Choose three.)

Select 3 answers

A.Dimension tables should use natural keys instead of surrogate keys for better performance.

B.Role-playing dimensions should be implemented by creating multiple dimension tables or using views.

C.Fact tables should contain numeric measures and foreign keys to dimension tables.

D.Date dimensions should be created to enable time intelligence calculations.

E.Dimensions should be normalized (snowflaked) to reduce data redundancy.

AnswersB, C, D

Role-playing dimensions (e.g., OrderDate, ShipDate) need separate tables or views to function correctly.

Why this answer

Option B is correct because role-playing dimensions, such as a Date dimension used for Order Date, Ship Date, and Due Date, require multiple references to the same dimension table. In Power BI, you implement this by creating multiple dimension tables (e.g., via calculated tables or referencing the same source table) or using database views to provide unique foreign key relationships, enabling correct time intelligence and filtering.

Exam trap

The trap here is that candidates often assume natural keys are better for readability or that snowflaking is acceptable for reducing redundancy, but the PL-300 exam emphasizes that star schemas with surrogate keys and denormalized dimensions are optimal for Power BI performance and DAX time intelligence.

Practice this question →

190

MCQmedium

You have a Power BI dataset that uses a live connection to an Analysis Services tabular model. The model contains a measure that uses the ALL function. You need to create a report page that shows sales by region, but you want to display the percentage of total sales regardless of region filter. How should you achieve this?

A.Modify the measure in the Analysis Services model to use ALLSELECTED instead of ALL.

B.Use the existing measure and add a visual-level filter to show percentage.

C.Change the connection to Import mode and then create the measure.

D.Create a new measure in the Power BI dataset using DAX that divides the region sales by total sales.

AnswerB

Visual-level filters can be applied, but for percentage of total, you need a measure that uses ALL; you must request the model owner to create a percentage measure.

Why this answer

Option B is correct because with a live connection to Analysis Services, you cannot modify the underlying measure in Power BI; you must work with the existing measures. By using the existing measure and adding a visual-level filter to show the percentage (e.g., by using the 'Show value as' option set to 'Percent of grand total'), you can display the percentage of total sales regardless of the region filter without altering the model.

Exam trap

The trap here is that candidates often think they need to modify the measure or change the connection mode to achieve percentage calculations, but Power BI's visual-level formatting options can handle this without altering the source model.

How to eliminate wrong answers

Option A is wrong because you cannot modify measures in the Analysis Services model from Power BI; the live connection is read-only, and changing the measure to use ALLSELECTED would require editing the source model, which is not possible from Power BI. Option C is wrong because changing the connection to Import mode would require redesigning the data model and is unnecessary; you can achieve the desired result without changing the connection type. Option D is wrong because you cannot create new measures in the Power BI dataset when using a live connection; the dataset is read-only, and all measures must be defined in the Analysis Services model.

Practice this question →

191

MCQeasy

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

A.The column was imported as text because the data source is a CSV file

B.Power Query detected that the column contains text values in some rows, so it set the data type to text

C.The 'Detect data type' option was disabled in Power Query settings

D.The source data is stored as text in the database

AnswerB

Power Query automatically detects the data type based on the values in the column; mixed types default to text.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

192

MCQeasy

You are cleaning data in Power Query. A column contains customer names with inconsistent capitalization (e.g., 'john smith', 'JANE DOE'). You need to standardize the names to proper case (first letter uppercase, rest lowercase). Which transformation should you use?

A.Use 'Format' > 'Trim'.

B.Use 'Format' > 'Capitalize Each Word'.

C.Use 'Format' > 'Lowercase'.

D.Use 'Format' > 'Uppercase'.

AnswerB

This converts the first letter of each word to uppercase and the rest to lowercase.

Why this answer

The 'Capitalize Each Word' transformation in Power Query converts the first letter of each word to uppercase and the rest to lowercase, which is exactly what proper case requires. This is the correct choice because it directly addresses the need to standardize inconsistent casing (e.g., 'john smith' becomes 'John Smith', 'JANE DOE' becomes 'Jane Doe').

Exam trap

The trap here is that candidates may confuse 'Capitalize Each Word' with 'Uppercase' or 'Lowercase', thinking any casing transformation will suffice, but only 'Capitalize Each Word' produces the specific proper case format required.

How to eliminate wrong answers

Option A is wrong because 'Trim' only removes leading and trailing whitespace from text, it does not alter character casing. Option C is wrong because 'Lowercase' converts all characters to lowercase (e.g., 'JANE DOE' becomes 'jane doe'), which does not achieve the required first-letter uppercase format. Option D is wrong because 'Uppercase' converts all characters to uppercase (e.g., 'john smith' becomes 'JOHN SMITH'), which does not produce proper case.

Practice this question →

193

Multi-Selecthard

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

Select 2 answers

A.Configure dynamic data masking on the Azure SQL Database.

B.Use row-level security (RLS) in Power BI to hide sensitive columns.

C.Transform the data in Power Query by replacing sensitive values with a placeholder.

D.Use Microsoft Purview sensitivity labels to mask data.

E.Apply column-level security in Power BI Desktop.

AnswersA, C

Dynamic data masking obfuscates data at the database level for certain users, so Power BI will receive masked data.

Why this answer

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

Exam trap

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

Practice this question →

194

MCQmedium

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

A.Import all tables as they are and create a single flat table by merging Sales, Stores, and Products in Power Query.

B.Import Sales, Stores, and Products tables, create a separate date table using CALENDAR, and establish relationships between Sales and dimension tables.

C.Import Sales table only and create calculated columns for StoreName and ProductName using RELATED.

D.Import Sales table and use the auto date/time feature for time intelligence.

AnswerB

Star schema with date table improves performance and enables time intelligence.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

195

MCQmedium

You are importing data from an Excel workbook that has multiple worksheets. You only need data from the 'Sales' worksheet. When you connect via Power Query, all worksheets appear in the Navigator. What should you do to load only the 'Sales' worksheet?

A.Load all worksheets, then delete the unwanted ones

B.Select the 'Sales' worksheet in Navigator and click 'Load'

C.Select all worksheets and click 'Load'

D.Select the 'Sales' worksheet and click 'Transform Data'

AnswerB

Directly loads only that worksheet.

Why this answer

Option A is correct because selecting the worksheet and clicking Load loads it. Option B is incorrect because Transform Data opens Power Query for transformation. Option C is incorrect because that would load all.

Option D is incorrect because deleting steps is unnecessary.

Practice this question →

196

Matchingmedium

Match each visualization type to its typical use case.

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

Concepts

Matches

Show trends over time

Show proportions of a whole

Show relationship between two variables

Compare parts of a category

Show stages in a process

Why these pairings

Different visuals are suited for different analytical tasks.

Practice this question →

197

MCQmedium

You are loading data from a folder containing multiple Excel files with identical structure. Some files have inconsistent column names due to manual edits. You need to ensure that all data is loaded correctly without errors. What should you do in Power Query?

A.Use the 'Combine Files' feature with a sample file, then in the transformation step, promote headers and rename columns using a mapping table.

B.Use 'Merge Queries' to join the files based on row position.

C.Change the data source to a SharePoint folder and use 'Load to Data Model' directly.

D.In Power Query, use 'Enter Data' to manually create the schema.

AnswerA

This approach handles variations by standardizing column names.

Why this answer

Option A is correct because the 'Combine Files' feature in Power Query uses a sample file to infer the schema, and then you can apply transformations like promoting headers and renaming columns using a mapping table to handle inconsistent column names across files. This ensures all data loads without errors by standardizing the column names before combining.

Exam trap

The trap here is that candidates assume 'Combine Files' works automatically without any transformation steps, overlooking the need to handle inconsistent column names, which leads to errors during data load.

How to eliminate wrong answers

Option B is wrong because 'Merge Queries' joins tables based on matching columns or row positions, but it does not resolve inconsistent column names across multiple files; it would fail or produce incorrect results if column names differ. Option C is wrong because changing the data source to a SharePoint folder and using 'Load to Data Model' directly does not address the column name inconsistency; Power Query would still encounter errors when combining files with mismatched headers. Option D is wrong because 'Enter Data' manually creates a static table schema, which cannot dynamically adapt to multiple Excel files with varying column names, and it does not automate the loading process.

Practice this question →

198

MCQmedium

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

A.Enable query folding in Power Query to push filters to the source.

B.Configure incremental refresh on the table using the LastModifiedDate column.

C.Schedule a full refresh every hour.

D.Create a Power BI dataflow that performs a full load and then use that dataflow as a source.

AnswerB

Correct. Incremental refresh loads only new/changed data.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

199

Multi-Selectmedium

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

Select 2 answers

A.Use the 'Enter Data' feature and manually type dates.

B.Use the CALENDAR DAX function in a calculated table.

C.Reference another query that already has dates.

D.Use the List.Dates function to generate a list of dates.

E.Create a blank query and use #date and List.Transform to generate dates.

AnswersD, E

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

Why this answer

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

Exam trap

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

Practice this question →

200

Multi-Selectmedium

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

Select 3 answers

A.Use the 'Fill Down' or 'Fill Up' option to propagate non-null values into null cells.

B.Remove rows that contain null values using the 'Remove Rows' > 'Remove Blank Rows' option.

C.Replace null values with a default value using the 'Replace Values' transform.

D.Merge the table with another table that has no nulls.

E.Change the data type of the column to a non-nullable type.

AnswersA, B, C

Fill Down/Fill Up replaces nulls with the nearest non-null value.

Why this answer

Option A is correct because the 'Fill Down' and 'Fill Up' transforms in Power Query propagate the last non-null value into adjacent null cells within a column. This is a common technique for cleaning data where nulls represent missing values that should inherit the previous or next valid entry, such as in hierarchical or grouped datasets.

Exam trap

The trap here is that candidates may confuse 'Remove Blank Rows' (which removes entire rows where all cells are null) with 'Remove Rows' > 'Remove Blank Rows' (which is actually a valid method to remove rows with nulls in any column, as option B states), but the real pitfall is assuming that changing a data type to non-nullable will automatically handle nulls, which Power Query does not support.

Practice this question →

201

Multi-Selecteasy

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

Select 2 answers

A.Exchange Online mailbox

B.PDF file

C.Power BI dataset

D.SharePoint Online list

E.Azure SQL Database

AnswersD, E

SharePoint lists are supported in dataflows.

Why this answer

SharePoint Online list (Option D) is a correct answer because Power BI dataflows can connect to SharePoint Online lists as a data source, allowing users to ingest list data directly into a dataflow for transformation and loading into the Power BI service. Azure SQL Database (Option E) is also correct because dataflows support Azure SQL Database as a data source, enabling direct extraction of relational data from Azure SQL databases into the dataflow engine for ETL processes.

Exam trap

The trap here is that candidates may confuse the connectors available in Power BI Desktop (which supports PDF and Exchange) with those supported in Power BI dataflows, leading them to select options that are valid in Desktop but not in dataflows.

Practice this question →

202

MCQhard

You are a Power BI administrator at Contoso Ltd. The company has a Power BI tenant with Premium capacity. A data analyst has published a dataset that uses DirectQuery mode to a SQL Server database. Users report that the report is slow. You need to improve query performance without changing the data source. You decide to implement a composite model by adding an imported table for a frequently used dimension. After importing, you notice that the relationship between the imported table and the DirectQuery table is Many-to-Many, which causes ambiguity. What should you do to resolve the ambiguity and ensure correct filtering?

A.Remove the imported table and use only DirectQuery.

B.Create a bridge table with a calculated column to create a one-to-many relationship.

C.Use the CROSSFILTER function to ignore the relationship.

D.Change the relationship to One-to-Many (1:*).

AnswerB

This resolves the many-to-many by providing a unique key.

Why this answer

Option B is correct because a bridge table resolves the Many-to-Many ambiguity by creating a separate table that contains only the unique key values from both sides, enabling a star-like schema with one-to-many relationships. This ensures that filters from the imported dimension table propagate correctly to the DirectQuery fact table without ambiguity.

Exam trap

The trap here is that candidates mistakenly think changing the relationship cardinality or using CROSSFILTER can fix Many-to-Many ambiguity, but only a bridge table or a calculated table with unique keys can properly resolve it in a composite model.

How to eliminate wrong answers

Option A is wrong because removing the imported table abandons the performance improvement from caching the frequently used dimension, failing to address the slow report issue. Option C is wrong because the CROSSFILTER function modifies filter direction but does not resolve Many-to-Many ambiguity; it can cause incorrect or missing filtering. Option D is wrong because changing the relationship to One-to-Many (1:*) is impossible when the underlying data inherently has a Many-to-Many cardinality; Power BI will not allow this change without data transformation.

Practice this question →

203

Multi-Selecteasy

Which TWO actions are required when configuring a Power BI dataset to use incremental refresh?

Select 2 answers

A.Set the dataset storage mode to DirectQuery.

B.Enable query caching on the dataset.

C.Create a calculated table to store the refresh history.

D.Set the incremental refresh policy in the dataset settings.

E.Define rangeStart and rangeEnd parameters in Power Query.

AnswersD, E

Correct. The policy defines the refresh window and detection.

Why this answer

Option D is correct because configuring an incremental refresh policy in the dataset settings is a required step to enable incremental refresh in Power BI. This policy defines how data is partitioned and refreshed incrementally based on date/time columns. Option E is correct because you must define `rangeStart` and `rangeEnd` parameters in Power Query to filter data into historical and incremental ranges, which Power BI uses to generate the refresh partitions.

Exam trap

The trap here is that candidates often confuse the required steps (defining parameters and setting the policy) with optional or unrelated features like query caching or storage mode changes, leading them to select options A or B.

Practice this question →

204

MCQmedium

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

A.In the Power BI service, go to the dataset settings and configure the scheduled refresh.

B.In Power Query Editor, apply the rangeStart and rangeEnd filters to the data and then close and apply.

C.In the Power BI service, create a new refresh schedule and set the incremental refresh period.

D.In Power BI Desktop, on the model view, select the table and set the incremental refresh policy.

AnswerD

Correct. Incremental refresh policy is set in the model view table properties.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

205

Multi-Selecthard

You are preparing data from a SQL Server database. The query includes a WHERE clause that filters rows based on a date column. You want to ensure that the filter is pushed back to the database (Query Folding). Which THREE conditions must be met?

Select 3 answers

A.The transformations applied must be supported for query folding.

B.All columns from the table must be loaded to the data model.

C.The query must use a DirectQuery connection.

D.The query must not combine data from multiple sources.

E.The source must be a relational database like SQL Server.

AnswersA, D, E

Not all transformations support folding.

Why this answer

Option A is correct because query folding in Power Query requires that all transformations applied to the source data are natively supported by the source database engine. If a transformation (e.g., certain date calculations or custom columns) cannot be translated into a SQL statement, Power Query will break folding and perform the operation locally, negating the performance benefit.

Exam trap

The trap here is that candidates often assume DirectQuery is required for query folding, but folding works in both Import and DirectQuery modes as long as the source and transformations support it.

Practice this question →

206

Multi-Selecteasy

You are importing data from an Excel workbook. The workbook has multiple sheets. You want to combine two sheets that have the same columns but different row data. Which TWO Power Query operations can you use?

Select 2 answers

A.Merge Queries

B.Group By

C.Append Queries

D.Pivot Column

E.Append Queries as New

AnswersC, E

Appends rows of one table to another.

Why this answer

Append Queries and Append Queries as New are both correct because they combine rows from two or more tables with identical columns into a single table. In Power Query, 'Append' is the operation designed for stacking rows vertically, which matches the requirement of combining sheets with the same columns but different row data.

Exam trap

The trap here is that candidates confuse 'Merge' (horizontal join) with 'Append' (vertical union), or think only one of the Append options is valid, but both 'Append Queries' and 'Append Queries as New' are correct operations for combining rows.

Practice this question →

207

MCQeasy

You have a Power BI data model with a 'Sales' fact table and a 'Date' dimension. You need to create a calculated column in the 'Sales' table that shows the fiscal year based on a 'Date' column. The fiscal year starts on July 1. Which DAX expression should you use?

A.SWITCH(TRUE(), MONTH(Sales[Date]) >= 7, YEAR(Sales[Date]), YEAR(Sales[Date])-1)

B.YEAR(Sales[Date])

C.YEAR(Sales[Date]) + 1

D.FORMAT(Sales[Date], "YYYY")

AnswerA

Correctly calculates fiscal year starting July.

Why this answer

Option A is correct because it uses SWITCH with TRUE() to evaluate a logical condition: if the month of the date is July or later (MONTH >= 7), it returns the current year; otherwise, it returns the previous year. This correctly implements a fiscal year starting on July 1, as required.

Exam trap

The trap here is that candidates often assume YEAR() alone is sufficient for fiscal year calculations, overlooking the need to adjust for the fiscal year start month, or they incorrectly add 1 to all years instead of conditionally shifting only the first half of the calendar year.

How to eliminate wrong answers

Option B is wrong because YEAR(Sales[Date]) returns the calendar year, not the fiscal year, so dates from January to June would be assigned to the wrong fiscal year. Option C is wrong because YEAR(Sales[Date]) + 1 always adds one year, which would incorrectly shift all dates forward by one year, not handle the July 1 start. Option D is wrong because FORMAT(Sales[Date], 'YYYY') simply returns the calendar year as a text string, with no fiscal year logic applied.

Practice this question →

208

MCQhard

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

A.Use DirectQuery for the SQL Server table and Import mode for the SharePoint list.

B.Set both tables to DirectQuery mode.

C.Set the SQL Server table to Dual mode and the SharePoint list to Import mode.

D.Import both tables into the model and disable incremental refresh.

AnswerA

DirectQuery avoids loading the large table into memory, reducing refresh time. Import is suitable for the small SharePoint list.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Practice this question →

209

MCQmedium

You have a table with a column 'Date' in text format (e.g., '2024-01-15'). You need to convert it to a date type. In Power Query, what is the best approach?

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

B.Use the Excel Power Query add-in.

C.Create a calculated column in DAX using DATEVALUE.

D.Change the column data type to Date in Power Query Editor.

AnswerD

Changing data type is straightforward and efficient.

Why this answer

Option C is correct because changing the data type in Power Query is the standard method. Option A is wrong because DAX calculated columns are for the data model, not for Power Query. Option B is wrong because Power Query can handle this natively.

Option D is wrong because it is not necessary to split the column.

Practice this question →

210

MCQhard

During data refresh in Power BI, an error occurs: 'The column 'OrderID' of the table 'Orders' contains a duplicate value and this column is part of a primary key.' The table 'Orders' is imported from an Azure SQL database. What is the most likely cause of this error?

A.The 'Orders' table was reordered in Power Query.

B.Data type mismatch between the source and Power BI.

C.A calculated column is referencing the 'Orders' table.

D.The source table has duplicate 'OrderID' values.

AnswerD

Duplicate values violate the primary key constraint.

Why this answer

Option D is correct because the error message explicitly states that the 'OrderID' column contains a duplicate value and is part of a primary key. In Power BI, when importing from a source like Azure SQL Database, the data model enforces uniqueness on primary key columns. If the source table has duplicate 'OrderID' values, the refresh fails because Power BI cannot maintain the required unique constraint.

Exam trap

The trap here is that candidates may confuse a primary key violation with other common refresh errors like data type mismatches or query folding issues, but the error message's explicit reference to 'duplicate value' and 'primary key' directly points to source data duplication.

How to eliminate wrong answers

Option A is wrong because reordering columns in Power Query does not affect data integrity or primary key uniqueness; it only changes the column sequence in the dataset. Option B is wrong because a data type mismatch would cause a conversion error, not a duplicate value error on a primary key column. Option C is wrong because a calculated column referencing the 'Orders' table does not introduce duplicate values; it computes values based on existing rows and does not alter the source data's uniqueness.

Practice this question →

211

MCQhard

You are a data analyst for a financial services company. You have a Power BI dataset that combines data from two sources: a CSV file in SharePoint Online and an on-premises SQL Server database. The CSV file contains exchange rates that are updated daily. The SQL Server database contains transaction data. You need to ensure that the dataset can be refreshed automatically in the Power BI service. The CSV file is updated at 6:00 AM daily, and the SQL Server database is updated continuously. You have already published the report. What should you do to enable automated refresh?

A.Use Power Automate to refresh the dataset after the CSV is updated.

B.Enable incremental refresh for the SQL Server table to reduce refresh time.

C.Install and configure an on-premises data gateway, then set up a scheduled refresh.

D.Configure a scheduled refresh in the dataset settings. The gateway is not required because the CSV file is in SharePoint Online.

AnswerC

Correct. A gateway is needed for the on-premises SQL Server, and then scheduled refresh works.

Why this answer

Option C is correct because the on-premises SQL Server database requires an on-premises data gateway to bridge the Power BI service with the local network. Even though the CSV file is in SharePoint Online, the dataset combines both sources; the gateway is mandatory for the SQL Server component. Without it, scheduled refresh cannot access the on-premises data, and the dataset will fail to refresh automatically.

Exam trap

The trap here is that candidates assume a gateway is unnecessary because one data source (SharePoint Online) is cloud-based, forgetting that the on-premises SQL Server requires a gateway for any automated refresh in the Power BI service.

How to eliminate wrong answers

Option A is wrong because Power Automate can trigger a refresh but does not solve the underlying connectivity issue for the on-premises SQL Server; the gateway is still required. Option B is wrong because incremental refresh reduces refresh time and data volume but does not enable connectivity to an on-premises data source; it is a performance optimization, not a connectivity solution. Option D is wrong because while the CSV file is in SharePoint Online and does not need a gateway, the on-premises SQL Server database absolutely requires an on-premises data gateway for the Power BI service to reach it; omitting the gateway will cause the scheduled refresh to fail.

Practice this question →

212

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

Power BI can use these aggregations for faster queries.

Why this answer

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

213

MCQhard

You are reviewing a Power BI dataset definition (in JSON format). The dataset refreshes daily but takes over 2 hours. You need to reduce the refresh time while ensuring that the most recent 4 years of data is always available. What is the most effective change to the dataset?

A.Add an index on the OrderDate column in the source database.

B.Remove the Amount column from the dataset.

C.Implement Incremental Refresh policy with a date range filter on OrderDate.

D.Modify the M expression to use a native SQL query with a WHERE clause on OrderDate.

AnswerD

This enables Query Folding, reducing data transferred.

Why this answer

Option D is correct because using a native SQL query with a WHERE clause on OrderDate pushes the filtering of data to the source database, reducing the amount of data transferred and processed by Power BI. This is the most effective change to reduce refresh time while ensuring the most recent 4 years of data is available, as it leverages the database engine's indexing and query optimization capabilities.

Exam trap

The trap here is that candidates often choose Incremental Refresh (Option C) thinking it automatically reduces refresh time, but they overlook that the question requires the most effective change and that native SQL query pushdown can be more efficient for reducing data volume at the source, especially when the source database supports query optimization.

How to eliminate wrong answers

Option A is wrong because adding an index on the OrderDate column in the source database can improve query performance but does not directly reduce the volume of data loaded into Power BI; the dataset still imports all rows, so refresh time remains high. Option B is wrong because removing the Amount column reduces the dataset size but does not address the root cause of long refresh time—loading 4+ years of data—and may break report requirements that depend on that column. Option C is wrong because implementing an Incremental Refresh policy in Power BI requires a date-range filter on OrderDate, but the question specifies that the dataset refreshes daily and takes over 2 hours; incremental refresh reduces the amount of data refreshed each time, but it still requires the initial full load and ongoing processing of partitions, which may not be as efficient as pushing the filter to the source via native query.

Practice this question →

214

MCQhard

Refer to the exhibit. You are reviewing the privacy levels for a Power BI semantic model that combines data from an on-premises SQL Server (SalesDB) and a local CSV file (MarketingCSV). The SQL Server data source has privacy level 'Organizational' and the CSV file has 'Private'. When you refresh the model, you encounter an error: 'A privacy violation occurred. Please check your privacy settings.' What is the most likely cause?

A.The on-premises data gateway is not configured for the CSV file.

B.The CSV file's privacy level is set to 'Private', which prevents it from being combined with 'Organizational' sources.

C.The SQL Server connection string uses Integrated Security, which is not supported when combining data.

D.The SQL Server table is partitioned, causing a conflict with the CSV file.

AnswerB

'Private' means data cannot be shared with other sources; changing it to 'Organizational' or 'Public' would resolve.

Why this answer

Option B is correct because Power BI enforces privacy level restrictions when combining data from different sources. The CSV file's 'Private' privacy level prevents it from being combined with the SQL Server data source set to 'Organizational', as Power BI will not merge data across these boundaries to avoid leaking private data to organizational sources. This results in the 'privacy violation' error during refresh.

Exam trap

Microsoft often tests the misconception that privacy level errors are caused by gateway configuration or authentication issues, when in fact they are strictly about data isolation rules between sources with incompatible privacy levels.

How to eliminate wrong answers

Option A is wrong because the on-premises data gateway is only required for on-premises data sources like SQL Server, not for local CSV files; the CSV file is accessed directly from the local machine. Option C is wrong because Integrated Security is fully supported when combining data from SQL Server and CSV files; the error is unrelated to authentication methods. Option D is wrong because table partitioning in SQL Server does not cause privacy conflicts with CSV files; partitioning is a storage optimization and does not affect privacy level enforcement.

Practice this question →

215

MCQhard

You are troubleshooting a Power BI report that uses a DAX query to summarize sales data. The query returns rows but the TotalSales column shows blank for some ProductID/Region combinations. What is the most likely cause?

A.The syntax of SUMMARIZECOLUMNS is incorrect; it should use ADDCOLUMNS.

B.The filter condition is applied after aggregation, so it cannot filter out rows.

C.The relationships between tables are missing, causing blank values.

D.The FILTER function removes rows where Quantity <= 10 from the entire Sales table, so those combinations have no data to aggregate.

AnswerD

FILTER is applied as a table filter, not a row filter within groups.

Why this answer

Option D is correct because the FILTER function in DAX, when used as a table filter argument in SUMMARIZECOLUMNS, applies a row-level filter to the entire Sales table before aggregation. If Quantity <= 10 for a given ProductID/Region combination, all rows for that combination are removed, leaving no data to aggregate, which results in a blank TotalSales. This is a common cause of unexpected blanks when using FILTER inside SUMMARIZECOLUMNS.

Exam trap

The trap here is that candidates often assume FILTER only affects the measure calculation, not the row context of the entire table, leading them to overlook that FILTER removes rows before aggregation, causing blanks for filtered-out combinations.

How to eliminate wrong answers

Option A is wrong because SUMMARIZECOLUMNS is the correct function for grouping and aggregating columns; ADDCOLUMNS is used to add calculated columns to an existing table, not to replace SUMMARIZECOLUMNS. Option B is wrong because in SUMMARIZECOLUMNS, filter conditions are applied before aggregation (as table filters), not after; the order of operations is filter first, then group and aggregate. Option C is wrong because missing relationships typically cause errors or unexpected behavior in measures or row expansion, but they would not cause blank values for specific combinations if the data exists; blanks here are due to filtering, not relationship issues.

Practice this question →

216

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.

AnswerD

This automatically combines all sheets.

Why this answer

Option D is correct because the 'From Folder' option in Power Query is designed to combine multiple Excel files with the same structure into a single table. By pointing to a folder containing the workbook, Power Query automatically detects all sheets as separate files and uses the 'Combine & Transform' feature to union them, which is more efficient than manually appending each sheet individually.

Exam trap

The trap here is that candidates often confuse 'Append Queries' (union) with 'Merge Queries' (join), or think that loading separate tables and using DAX is more efficient, but Power Query's folder-based combination is the most efficient for multiple sheets in a single workbook.

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 →

217

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

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

218

MCQhard

A Power BI report contains a table with a column 'Date' of type date. The report users need to filter data by fiscal year, which starts on April 1. What is the best practice to support this requirement during data preparation?

A.Create a separate date table in Power Query with a fiscal year column.

B.Split the date column into year, month, and day columns.

C.Use a DAX calculated table to generate fiscal year dates.

D.Add a calculated column in the existing table using DAX.

AnswerA

A dedicated date table is the recommended approach.

Why this answer

Option A is correct because creating a separate date table in Power Query with a fiscal year column is the best practice for handling fiscal year filtering. This approach ensures the date dimension is independent of fact tables, supports star schema design, and allows you to define fiscal year logic (starting April 1) directly in M code during data preparation, which is more efficient and maintainable than using DAX calculated columns or tables.

Exam trap

The trap here is that candidates often think a DAX calculated column or table is acceptable for fiscal year logic, but the exam emphasizes that data preparation (Power Query) is the correct phase for such transformations to maintain performance and star schema design.

How to eliminate wrong answers

Option B is wrong because splitting the date column into year, month, and day columns does not inherently create a fiscal year hierarchy; it only breaks the date into parts, requiring additional logic to map months to fiscal years, which is inefficient and does not provide a proper date dimension for filtering. Option C is wrong because using a DAX calculated table to generate fiscal year dates is less performant than doing so in Power Query; DAX calculated tables are computed after data load and can increase model size and refresh time, whereas Power Query transformations are applied during data preparation and are more efficient. Option D is wrong because adding a calculated column in the existing table using DAX introduces redundancy and violates star schema best practices; it also computes the fiscal year at query time rather than during data preparation, leading to potential performance issues and lack of reusability across multiple fact tables.

Practice this question →

219

MCQmedium

You have a Power BI dataset that combines sales data from two Excel files: Sales2023.xlsx and Sales2024.xlsx. Both files have the same schema. You need to combine them into a single table without duplicating rows. What is the best approach in Power Query?

A.Use Union in DAX.

B.Use Group By to summarize data.

C.Use Append Queries.

D.Use Merge Queries as a new query.

AnswerC

Append Queries combines rows from two tables.

Why this answer

Option C is correct because Append Queries in Power Query is specifically designed to combine rows from two or more tables with the same schema into a single table, stacking them vertically without duplicating rows. This operation is performed in the Power Query Editor (M language) and is the standard approach for unioning data from multiple sources during the data preparation phase, before loading into the Power BI data model.

Exam trap

The trap here is that candidates often confuse Append Queries (vertical stacking) with Merge Queries (horizontal joining), or mistakenly think DAX Union is appropriate for data preparation, when Power Query is the correct tool for this task.

How to eliminate wrong answers

Option A is wrong because Union in DAX is a function used within calculated tables or measures in the data model, not in Power Query; it operates on tables already loaded into the model and can cause performance issues and duplicate rows if not handled carefully, whereas the requirement is to combine data during the preparation phase. Option B is wrong because Group By is used to aggregate data (e.g., sum, count) by grouping rows based on columns, not to combine two separate tables into one; it would summarize the data rather than preserving all rows. Option D is wrong because Merge Queries is used to join tables horizontally (like SQL JOIN) based on matching keys, adding columns from one table to another, not to stack rows vertically; it would create a wider table, not a longer one, and could introduce duplicates if not configured correctly.

Practice this question →

220

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

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

221

MCQmedium

You are preparing data in Power BI Desktop. You have a table that includes a 'SalesAmount' column and a 'CurrencyCode' column. You need to create a measure that calculates the total sales amount in USD only, filtering out other currencies. Which DAX expression should you use?

A.SUMX(FILTER(Sales, Sales[CurrencyCode] = "USD"), Sales[SalesAmount])

B.CALCULATE(SUM(Sales[SalesAmount]), Sales[CurrencyCode] = "USD")

C.CALCULATE(SUM(Sales[SalesAmount]), Sales[CurrencyCode] = "USD") + 0

D.SUM(Sales[SalesAmount])

AnswerB

Correctly filters to USD currency.

Why this answer

Option B is correct because it uses CALCULATE to modify the filter context, applying a filter that restricts the SUM of Sales[SalesAmount] to only rows where Sales[CurrencyCode] equals 'USD'. This is the standard DAX pattern for conditional aggregation in Power BI.

Exam trap

The trap here is that candidates often choose SUMX (Option A) because they think they need to explicitly filter rows before summing, not realizing that CALCULATE with a simple filter is the more efficient and correct DAX pattern for this scenario.

How to eliminate wrong answers

Option A is wrong because SUMX iterates over a filtered table and sums the SalesAmount column, which works but is less efficient and not the idiomatic DAX pattern for a simple filter; it also requires an explicit table filter. Option C is wrong because adding zero (+ 0) is unnecessary and does not change the result; it may mislead candidates into thinking it prevents blanks, but CALCULATE already handles blanks naturally. Option D is wrong because SUM(Sales[SalesAmount]) calculates the total sales amount across all currencies, ignoring the USD filter requirement entirely.

Practice this question →

222

Multi-Selectmedium

You are a Power BI developer at a healthcare organization. You are building a report that must comply with HIPAA regulations. You need to ensure that patient data is not exposed to unauthorized users. You plan to use Row-Level Security (RLS) with roles defined in Power BI Desktop. However, you also need to limit the data imported into the model to only necessary columns. The source is an Azure SQL Database with a table 'Patients' containing columns: PatientID, Name, SSN, Diagnosis, AdmissionDate, DischargeDate. Which two actions should you take? (Choose TWO)

Select 2 answers

A.Create RLS roles to restrict access by Diagnosis.

B.Store data source credentials in the Power BI service.

C.Disable query caching for the dataset.

D.Use encrypted connection to the database.

E.Remove the SSN and Name columns in Power Query before loading.

AnswersA, E

Limits what users see based on role.

Why this answer

Option A is correct because creating RLS roles in Power BI Desktop allows you to restrict data access based on the user's identity, ensuring that only authorized users can view specific rows (e.g., by Diagnosis). This is a key requirement for HIPAA compliance, as it prevents unauthorized exposure of patient data. Option E is correct because removing sensitive columns like SSN and Name in Power Query before loading into the model minimizes the data footprint and reduces the risk of exposing personally identifiable information (PII), aligning with the principle of data minimization.

Exam trap

The trap here is that candidates may confuse data security measures (like encrypted connections or credential storage) with data minimization and access control, leading them to select options that protect data in transit or enable refresh but do not directly limit imported columns or enforce row-level filtering.

Practice this question →

223

MCQhard

You are preparing data for a sales analysis report. The source system provides a table with columns: 'ProductID', 'ProductName', 'Category', 'SubCategory', 'Price', 'Cost'. You need to create a star schema. Which columns should be moved to dimension tables?

A.ProductID, ProductName, Category, and SubCategory

B.ProductID and ProductName only

C.Price and Cost

D.ProductID, ProductName, Price, and Cost

AnswerA

These are all attributes of the product.

Why this answer

In a star schema, dimension tables contain descriptive attributes that provide context to measures. ProductID, ProductName, Category, and SubCategory are all descriptive attributes that should be moved to a dimension table (e.g., DimProduct) to reduce redundancy and enable efficient filtering and grouping. Measures like Price and Cost, however, are numeric values that typically belong in the fact table as additive or semi-additive measures.

Exam trap

The trap here is that candidates often confuse numeric columns like Price and Cost as dimensions because they are static per product, but in a star schema, any column that is a measure (aggregatable numeric value) belongs in the fact table, not the dimension table.

How to eliminate wrong answers

Option B is wrong because it omits Category and SubCategory, which are essential descriptive attributes for hierarchical analysis and should be part of the dimension table to support drill-down and slicing. Option C is wrong because Price and Cost are numeric measures, not descriptive attributes; they should remain in the fact table for aggregation and calculation. Option D is wrong because it includes Price and Cost, which are measures that belong in the fact table, not in a dimension table, and mixing them with descriptive attributes violates the star schema design principle of separating measures from dimensions.

Practice this question →

224

Multi-Selecthard

Which THREE are valid methods to handle duplicate rows in Power Query when preparing data?

Select 3 answers

A.Use 'Reverse Rows'

B.Use 'Remove Duplicates' based on key columns

C.Use 'Keep Duplicates'

D.Use 'Sort Ascending'

E.Use 'Group By' to aggregate duplicates

AnswersB, C, E

Removes duplicate rows.

Why this answer

Options A, B, and D are correct. Remove Duplicates deletes duplicates. Group By with aggregation can combine duplicates.

Keep Duplicates retains only duplicates. Option C reverses rows, not handle duplicates. Option E sorts, not handle duplicates.

Practice this question →

225

MCQeasy

You are importing data from a folder containing multiple CSV files with the same structure. You want to combine all files into a single table, but only include files that have been modified in the last 7 days. What Power Query transformation should you use?

A.Use 'Sample File' and manually filter

B.Filter the folder contents by 'Date Modified' before combining

C.Use 'Merge Queries' with the folder

D.Combine files using 'Combine & Load', then filter by date modified

AnswerB

This filters files first, then combines only recent ones.

Why this answer

Option D is correct because filtering by date modified before combining reduces data volume. Option A is wrong because combining then filtering loads all data first. Option B is wrong because sample file is for structure.

Option C is wrong because merging is not for appending.

Practice this question →