Question bank
Microsoft Certified: Power BI Data Analyst Associate
42 Microsoft Certified: Power BI Data Analyst Associate questions with the correct answer and a written explanation, organised across 4 exam domains.
- 42 questions
- 4 domains
- Foundation level
certilumQuestion index
Jump straight to any question. Answers and explanations are inline.
Manage and secure Power BI
6 questions in this domain
- Question 1Single Choice
Which storage mode loads data into the Power BI in-memory engine to provide the fastest analytical performance?
- AImport modeCorrect answer
- BDirectQuery
- CComposite Model
- DLive Connection
Why this is the answer
Import mode copies data into the Power BI in-memory engine, maximizing query speed and DAX functionality, and needs scheduled refresh to remain current.
- Question 2Multiple Select
Which of the following are valid Power BI data source privacy levels? Select all that apply.
- APublicCorrect answer
- BOrganizationalCorrect answer
- CPrivateCorrect answer
- DIsolated
Why this is the answer
Power BI privacy levels are Public, Organizational, and Private. They control how data sources can be combined to prevent unintended data leakage.
- Question 3Interactive
Review the Power Query M code in the code block. What is the result of the Unpivoted Columns step?
- AConverts quarter columns into an attribute-value pair of Quarter and SalesCorrect answer
- BConverts rows into columns to improve readability
- CDeletes the Product column from the table
- DJoins the table horizontally with another table
Why this is the answer
Unpivoting creates a normalized table by turning multiple columns into attribute-value pairs, increasing row count and enabling easier analysis.
- Question 4Single Choice
Which connection type is used specifically to connect to Analysis Services or Power BI datasets?
- ALive ConnectionCorrect answer
- BDirectQuery
- CImport mode
- DComposite Model
Why this is the answer
Live Connection targets Analysis Services or Power BI datasets. DirectQuery connects to relational databases by sending native queries.
- Question 5Multiple Select
On-premises SQL Server is used as a data source for a scheduled Power BI refresh. What must be in place? Select all that apply.
- AAn On-premises data gateway configured for the environmentCorrect answer
- BValid credentials for the on-premises data sourceCorrect answer
- CA direct cloud-to-cloud connection without a gateway
- DPersonal gateway mode for shared departmental refresh
Why this is the answer
On-premises sources require an On-premises data gateway and valid credentials for scheduled refresh. Personal gateway mode is not appropriate for shared team scenarios.
- Question 6Interactive
Review the M code in the code block. Which transformation is being applied to the Orders and Customers tables?
- AAppending the Customers table to Orders
- BMerging tables using a CustomerID keyCorrect answer
- CUnpivoting columns to create long format
- DRemoving duplicate customer records
Why this is the answer
The M code uses Table.NestedJoin with CustomerID as the key, which is a merge. Expanding the nested table is part of the merge process.
Model the data
11 questions in this domain
- Question 7Single Choice
Which storage mode loads data into the Power BI in-memory engine for the fastest analytical query performance?
- AImport modeCorrect answer
- BDirectQuery mode
- CLive Connection mode
- DComposite model
Why this is the answer
Import mode loads data into the VertiPaq in-memory engine, making it the most common mode for high-speed analytical queries.
- Question 8Multiple Select
Which options correctly characterize DirectQuery storage mode? Select all that apply.
- ALeaves data in source systemCorrect answer
- BSends real-time queries to sourceCorrect answer
- CLoads columns into VertiPaq memory
- DWorks only in Power Query Desktop
Why this is the answer
DirectQuery leaves data in the source system and sends real-time queries to the source. It does not load data into memory and is not limited to Power Query Desktop.
- Question 9Interactive
Review the M code in the block. What action does this applied step perform in Power Query?
- AConverts wide columns into rowsCorrect answer
- BStacks queries vertically
- CJoins tables horizontally
- DRenames selected columns
Why this is the answer
The Table.UnpivotOtherColumns function unpivots specified columns, converting wide attribute columns into additional rows and forming a long, normalized table.
- Question 10Single Choice
Which privacy level should be selected for a sensitive source that must not be mixed with other data sources?
- APrivate privacy levelCorrect answer
- BPublic privacy level
- COrganizational privacy level
- DNo privacy level
Why this is the answer
Private is the most restrictive privacy level and can help prevent data leakage across data sources during merging and query folding.
- Question 11Multiple Select
Which categories are displayed by Column Quality in Power Query? Select all that apply.
- AValid value percentageCorrect answer
- BError value percentageCorrect answer
- CEmpty value percentageCorrect answer
- DUnique value percentage
Why this is the answer
Column Quality displays the percentage of Valid, Error, and Empty values in each column. Unique is not one of the categories shown by Column Quality.
- Question 12Interactive
Examine the M code in the code block. Which statements are correct about this transformation? Select all that apply.
- AStacks rows verticallyCorrect answer
- BKeeps duplicate rowsCorrect answer
- CJoins tables horizontally
- DRemoves duplicate rows
Why this is the answer
Table.Combine appends tables vertically, preserving duplicates in the result. It does not perform a horizontal join and does not eliminate duplicate rows.
- Question 13Single Choice
What kind of data source is DirectQuery intended for in contrast to Live Connection?
- ARelational databasesCorrect answer
- BPower BI datasets
- CAnalysis Services models
- DExcel workbooks
Why this is the answer
DirectQuery is intended for relational databases, while Live Connection is specifically for Analysis Services models or Power BI datasets. They are not identical.
- Question 14Multiple Select
Which statements are correct about data refresh requirements? Select all that apply.
- ARequires gateway for on-premises refreshCorrect answer
- BCloud-to-cloud refresh needs no gatewayCorrect answer
- CGateway is needed for every refresh
- DGateway is never used for DirectQuery
Why this is the answer
An On-premises Data Gateway is required when the source is on-premises, but cloud-to-cloud refresh generally needs no gateway. DirectQuery can also require a gateway for on-premises sources.
- Question 15Interactive
Review the code block. What does this M query step do to the data?
- AKeeps only rows where Region is WestCorrect answer
- BRemoves rows where Region is West
- CDeletes the Region column
- DRenames Region to West
Why this is the answer
Table.SelectRows filters the table by evaluating each row. This step keeps rows where the Region column equals 'West'.
- Question 16Single Choice
Which Power Query data profiling tool provides statistics such as Min, Max, Average, and Standard Deviation?
- AColumn Profile toolCorrect answer
- BColumn Quality summary
- CColumn Distribution view
- DQuery Settings panel
Why this is the answer
Column Profile is the deep-dive diagnostic tool that includes Min, Max, Average, Standard Deviation, and Count for a selected column.
- Question 17Multiple Select
Which practices are considered traps when preparing data in Power BI? Select all that apply.
- AFix issues with DAX calculated columnCorrect answer
- BFilter rows after a heavy mergeCorrect answer
- CIgnore null vs. empty stringCorrect answer
- DProfile data before transforming
Why this is the answer
Using calculated columns to fix cleanup, filtering after heavy merges, and confusing null with empty strings are traps. Profiling data before transforming is a recommended practice.
Prepare the data
13 questions in this domain
- Question 18Single Choice
What is the most common Power BI storage mode used to load data into the in-memory engine?
- AImport ModeCorrect answer
- BDirectQuery
- CLive Connection
- DComposite Model
Why this is the answer
Import Mode is the most common storage mode because it loads data into the Power BI in-memory engine, VertiPaq, for high-speed querying.
- Question 19Multiple Select
Which statements accurately describe DirectQuery in Power BI?
- AIt leaves the data in the source system.Correct answer
- BIt sends queries to the source in real time.Correct answer
- CIt loads the full data set into VertiPaq.
- DIt is the same as a Live Connection.
Why this is the answer
DirectQuery leaves source data in place and sends real-time queries to the source, so it is not imported into VertiPaq and should not be confused with Live Connection.
- Question 20Interactive
Review the code in the code block. Which applied step is executed last in this Power Query M query?
- ASource
- BChanged Type
- CFiltered RowsCorrect answer
- DSelectRows
Why this is the answer
The M query starts with Source, then changes data types, and finally filters rows. Filtered Rows is therefore the last applied step.
- Question 21Single Choice
Why is it critical to select the correct built-in connector when importing data into Power BI?
- AIt determines metadata availability and transformation methods.Correct answer
- BIt determines the visual layout of reports.
- CIt decides which DAX functions are available.
- DIt removes the need to profile data.
Why this is the answer
The selected connector determines the level of metadata available and the methods available for subsequent data transformation in Power Query.
- Question 22Multiple Select
Which two elements must be configured when Power BI analyzes how a data source is accessed?
- AAuthentication method, such as OAuth2 or WindowsCorrect answer
- BPrivacy level, such as Public or OrganizationalCorrect answer
- CApplied Step sequence
- DIn-memory engine size
Why this is the answer
Connecting to a data source requires configuring authentication methods such as OAuth2 or Windows, and privacy levels such as Public or Organizational.
- Question 23Interactive
What transformation does the code in the code block apply to the source table?
- AIt merges two tables horizontally.
- BIt appends two tables vertically.
- CIt unpivots columns into attribute-value pairs.Correct answer
- DIt duplicates the source table.
Why this is the answer
The M code uses Table.UnpivotOtherColumns, which converts wide columns into attribute-value pairs, a critical structural transformation.
- Question 24Single Choice
How does a Live Connection differ from DirectQuery in Power BI?
- AIt is used specifically for Analysis Services or Power BI datasets.Correct answer
- BIt always imports data into VertiPaq.
- CIt connects directly to relational databases.
- DIt mixes DirectQuery and Import in one model.
Why this is the answer
Live Connection is specifically for Analysis Services or Power BI datasets, while DirectQuery is for relational databases.
- Question 25Multiple Select
Which two statements are true when refreshing an on-premises data source in Power BI?
- AAn On-premises Data Gateway is required for scheduled refreshes.Correct answer
- BOn-premises source credentials are managed with the data source, not the gateway.Correct answer
- CEvery connection in Power BI requires a gateway even if the source is in the cloud.
- DPersonal gateway mode is ideal for departmental shared report refreshes.
Why this is the answer
An On-premises Data Gateway is non-negotiable for scheduled refresh of on-premises data, and source credentials belong with the data source, not the gateway.
- Question 26Interactive
What operation is performed by the Power Query code in the code block?
- AIt merges queries horizontally on a common key.Correct answer
- BIt appends queries vertically.
- CIt unpivots selected columns.
- DIt replaces null values.
Why this is the answer
The code uses Table.NestedJoin, which merges two tables horizontally based on a common ProductID key, similar to a SQL Join.
- Question 27Single Choice
Which Power Query profiling feature displays Valid, Error, and Empty percentages for columns?
- AColumn QualityCorrect answer
- BColumn Distribution
- CColumn Profile
- DTable Tools
Why this is the answer
Column Quality provides a visual summary showing the percentage of data that is Valid, Error, or Empty for each column.
- Question 28Multiple Select
Select all data profiling tools available in Power Query Editor.
- AColumn QualityCorrect answer
- BColumn DistributionCorrect answer
- CColumn ProfileCorrect answer
- DQuery Diagnostics
Why this is the answer
Power Query data profiling includes Column Quality, Column Distribution, and Column Profile tools.
- Question 29Interactive
Using Power Query terminology, how many values in the code block are unique?
- A4
- B2Correct answer
- C7
- D5
Why this is the answer
Unique values are those that appear exactly once. Green and Yellow appear once, so the unique count is 2; the distinct count would be 4.
- Question 30Single Choice
Which Power Query profiling feature visualizes the frequency of distinct values in a column?
- AColumn Profile
- BColumn DistributionCorrect answer
- CColumn Quality
- DApplied Steps
Why this is the answer
Column Distribution visualizes the frequency of unique and distinct values and helps detect high-cardinality columns and value concentrations.
Visualize and analyze the data
12 questions in this domain
- Question 31Single Choice
Which storage mode loads data into the Power BI in-memory engine and requires scheduled refreshes to stay current?
- AImport modeCorrect answer
- BDirectQuery
- CLive Connection
- DComposite Model
Why this is the answer
Import mode copies data into VertiPaq, Power BI's in-memory engine. It enables fast queries and full DAX usage but requires scheduled refreshes to keep information current.
- Question 32Multiple Select
Which of the following accurately describe DirectQuery storage mode? Select all that apply.
- AData stays in the original source system.Correct answer
- BQueries are sent to the source system in real time.Correct answer
- CData is compressed and loaded into VertiPaq.
- DDirectQuery removes all query load from the source system.
Why this is the answer
DirectQuery does not import data. It leaves data in the source system and sends native queries to it for real-time access, which can add latency and does not reduce source workload.
- Question 33Interactive
Refer to the code block. What transformation is performed on the source table?
- ATurns wide columns into attribute-value rowsCorrect answer
- BAdds rows from another table
- CCombines tables using a key column
- DChanges column data types
Why this is the answer
Unpivoting transforms wide tables with values spread across multiple columns into a normalized long format with attribute and value columns.
- Question 34Single Choice
Which Power Query profiling tool visually reports the Valid, Error, and Empty percentages of each column?
- AColumn QualityCorrect answer
- BColumn Distribution
- CColumn Profile
- DValue Distribution
Why this is the answer
Column Quality provides a high-level visual summary of each column, showing the percentage of values that are Valid, Error, or Empty.
- Question 35Multiple Select
Which options are valid privacy levels in Power BI? Select all that apply.
- APublicCorrect answer
- BOrganizationalCorrect answer
- CPrivateCorrect answer
- DShared
Why this is the answer
Power BI privacy levels are Public, Organizational, and Private. Shared is not a valid data privacy level.
- Question 36Interactive
Refer to the code block. What operation is performed after the Orders and Details tables are created?
- AA horizontal merge based on OrderIDCorrect answer
- BA vertical append
- CA column unpivot
- DA row-level filter
Why this is the answer
Table.NestedJoin joins tables horizontally using a common key, similar to a SQL JOIN. In this example the key is OrderID.
- Question 37Single Choice
Which Power Query tool provides Min, Max, Average, Standard Deviation, and row count for a selected column?
- AColumn ProfileCorrect answer
- BColumn Quality
- CColumn Distribution
- DValue Distribution
Why this is the answer
Column Profile is a deep-dive tool that shows statistics such as Min, Max, Average, Standard Deviation, and Count for a column.
- Question 38Multiple Select
In which scenarios is DirectQuery an appropriate choice? Select all that apply.
- ADataset is larger than the available memoryCorrect answer
- BNear-real-time data is requiredCorrect answer
- CFull DAX capability with no limitations is required
- DSource database workload should be minimized
Why this is the answer
DirectQuery handles very large data sets and near-real-time updates. However, it can introduce latency, place load on the source system, and limit certain DAX features.
- Question 39Interactive
Refer to the code block. The filter removes empty string values. Which missing-value problem remains?
- ARows with null Region values remainCorrect answer
- BRows with empty string Region values remain
- CRows with duplicate CustomerID remain
- DAll rows with missing Region are removed
Why this is the answer
In Power Query M, null is not equal to an empty string. The filter [Region] <> \"\" removes empty strings but leaves null values in the result.
- Question 40Single Choice
What is required to refresh an on-premises data source from the Power BI service on a schedule?
- AOn-premises data gatewayCorrect answer
- BPower BI Desktop installed in the cloud
- CA gateway is only needed for DirectQuery
- DNo gateway is required
Why this is the answer
An On-premises Data Gateway is required for scheduled refreshes when the source is on-premises. A personal gateway should not be used for shared departmental data.
- Question 41Multiple Select
Which statements about data source privacy levels are correct? Select all that apply.
- AThey help control data leakage when query folding combines sources.Correct answer
- BIgnoring them can cause errors when merging on-premises and cloud sources.Correct answer
- CThey determine the compression level of VertiPaq.
- DThey are required only when the data source is a cloud service.
Why this is the answer
Privacy levels protect data during query folding and cross-source joins. Wrong settings can cause errors when an on-premises source is merged with a cloud source.
- Question 42Interactive
Refer to the code block. What data type does the Date column have after the transformation?
- ADateCorrect answer
- BText
- CDate/Time
- DCurrency
Why this is the answer
Table.TransformColumnTypes changes the Date column from a text value to the Date data type using type date.
Frequently asked questions
Everything about the Microsoft Certified: Power BI Data Analyst Associate question bank and how to use it.