Tag: PL-300 Exam Hub

Microsoft Certification, PL-300, Power BI

Practice Questions: Group and aggregate rows (PL-300 Exam Prep)

 
This post is a part of the PL-300: Microsoft Power BI Data Analyst Exam Prep Hub; and this topic falls under these sections:
Prepare the data (25–30%)
   --> Transform and load the data
      --> Group and aggregate rows

Below are 10 practice questions (with answers and explanations) for this topic of the exam.
There are also 2 practice tests for the PL-300 exam with 60 questions each (with answers) available on the hub.

Practice Questions

Question 1

You have a sales table with one row per transaction. You need a table that shows total sales per customer, and this logic will not change. Where should you perform this aggregation?

A. Create a DAX measure using SUM()
B. Use Power Query Group By on Customer
C. Create a calculated column in DAX
D. Use a visual-level aggregation

Correct Answer: B

Explanation:
Power Query Group By is ideal for static aggregations that do not need to respond to slicers. This reduces data volume and improves performance, which aligns with PL-300 best practices.

Question 2

Which aggregation option in Power Query counts the number of rows per group, regardless of column values?

A. Count
B. Count Distinct
C. Count Rows
D. Sum

Correct Answer: C

Explanation:
Count Rows counts all rows in each group. This is commonly tested and often confused with Count Distinct.

Question 3

You group a fact table in Power Query by Product and Year. What is the resulting grain of the table?

A. One row per product
B. One row per year
C. One row per product and year
D. One row per transaction

Correct Answer: C

Explanation:
When grouping by multiple columns, the output grain is the unique combination of those columns.

Question 4

What is a key impact on the data model when you group and aggregate rows in Power Query?

A. Measures become faster but less accurate
B. Relationships are automatically removed
C. Detailed transaction-level data is no longer available
D. DAX calculations are no longer required

Correct Answer: C

Explanation:
Grouping in Power Query permanently removes lower-level detail, which can limit drill-down and analytical flexibility.

Question 5

Which scenario indicates you should NOT group data in Power Query?

A. The dataset is very large
B. Aggregation logic is fixed
C. Users need slicer-driven calculations
D. You want to reduce model size

Correct Answer: C

Explanation:
If calculations must respond dynamically to slicers or filters, aggregation should be handled with DAX measures, not Power Query grouping.

Question 6

A Power BI report requires time intelligence calculations such as Year-to-Date sales. Where should aggregation occur?

A. Power Query Group By
B. Calculated columns
C. DAX measures
D. Visual-level filters

Correct Answer: C

Explanation:
Time intelligence requires dynamic context, which only DAX measures can provide. Grouping in Power Query would prevent proper time-based calculations.

Question 7

What does the All Rows aggregation option produce in Power Query?

A. A single aggregated value
B. A calculated column
C. A nested table per group
D. A distinct count

Correct Answer: C

Explanation:
All Rows creates a nested table containing all rows for each group, often used for advanced transformations or custom calculations.

Question 8

You mistakenly use Sum instead of Count Rows during a Group By operation. What is the most likely issue?

A. Incorrect totals due to data type mismatch
B. Missing relationships in the model
C. Duplicate rows created
D. Slower refresh times

Correct Answer: A

Explanation:
Using the wrong aggregation function can produce incorrect results, especially if the column contains non-additive values.

Question 9

Which benefit is most directly associated with grouping data in Power Query?

A. Improved visual formatting
B. Reduced dataset size
C. Increased DAX flexibility
D. Automatic relationship creation

Correct Answer: B

Explanation:
Grouping reduces the number of rows loaded into the model, improving performance and memory usage.

Question 10

Which statement best reflects a PL-300 best practice regarding grouping and aggregation?

A. Always aggregate data before loading
B. Group data only when DAX cannot be used
C. Group data when the required grain is known and fixed
D. Use grouping to replace all measures

Correct Answer: C

Explanation:
Grouping is appropriate when the required grain is known, stable, and does not require interactivity. Knowing when not to group is just as important for the exam.

Final Exam Tips for This Topic

  • Expect decision-based questions, not just “how-to”
  • Be clear on Power Query vs DAX responsibilities
  • Understand grain, performance, and flexibility trade-offs
  • Watch for Count vs Count Rows vs Count Distinct
  • Remember: Grouping is irreversible

Go back to the PL-300 Exam Prep Hub main page

Analytics, Business Intelligence, Microsoft Certification, PL-300, Power BI

Practice Questions: Pivot, Unpivot, and Transpose Data (PL-300 Exam Prep)

 
This post is a part of the PL-300: Microsoft Power BI Data Analyst Exam Prep Hub; and this topic falls under these sections:
Prepare the data (25–30%)
   --> Transform and load the data
      --> Pivot, Unpivot, and Transpose Data

Below are 10 practice questions (with answers and explanations) for this topic of the exam.
There are also 2 practice tests for the PL-300 exam with 60 questions each (with answers) available on the hub.

Practice Questions

Question 1

A dataset contains monthly sales stored in separate columns named Jan, Feb, Mar, and so on. You need to create a time-series visual that supports filtering by month. What should you do?

A. Pivot the Month columns
B. Unpivot the Month columns
C. Transpose the table
D. Create calculated columns in DAX

Correct Answer: B

Explanation:
Unpivoting converts repeated value columns into a single attribute–value pair, producing a normalized structure suitable for time-series analysis.

Question 2

You want to convert unique values from a column into individual columns, displaying aggregated results per category. Which transformation should you use?

A. Unpivot Columns
B. Transpose Table
C. Pivot Columns
D. Group By

Correct Answer: C

Explanation:
Pivoting turns values from one column into multiple columns, often requiring an aggregation function such as Sum or Count.

Question 3

Which transformation flips all rows into columns and columns into rows?

A. Pivot
B. Unpivot
C. Group By
D. Transpose

Correct Answer: D

Explanation:
Transpose rotates the entire table structure and is typically used when the table orientation is incorrect.

Question 4

After transposing a table, the first row now contains the desired column headers. What should you do next?

A. Unpivot Columns
B. Rename columns manually
C. Promote the first row to headers
D. Create a calculated table

Correct Answer: C

Explanation:
Promoting the first row to headers finalizes the transformation after transposing data.

Question 5

Which scenario best indicates that unpivoting is required?

A. Values in a column need to be summarized
B. Categories should become column headers
C. Repeating attribute columns must become rows
D. Data needs to be grouped by multiple keys

Correct Answer: C

Explanation:
Unpivoting is used to normalize data where multiple columns represent the same attribute (such as months or measures).

Question 6

What is a potential negative impact of pivoting data in Power Query?

A. Increased DAX complexity
B. Reduced model performance due to many columns
C. Loss of relationships
D. Automatic removal of measures

Correct Answer: B

Explanation:
Pivoting can significantly increase the number of columns, bloating the model and negatively affecting performance.

Question 7

You receive a dataset where column headers are stored in the first column instead of the first row. Which transformation is most appropriate?

A. Unpivot
B. Pivot
C. Transpose
D. Group By

Correct Answer: C

Explanation:
Transposing the table re-orients rows and columns so headers can be promoted correctly.

Question 8

Which transformation often requires you to specify an aggregation function?

A. Transpose
B. Unpivot
C. Pivot
D. Remove Columns

Correct Answer: C

Explanation:
Pivoting may result in multiple values for a new column and therefore requires aggregation (Sum, Count, Average, etc.).

Question 9

You want to keep identifier columns (such as ProductID and Region) while converting all other columns into rows. Which option is best?

A. Pivot Selected Columns
B. Unpivot Other Columns
C. Transpose Table
D. Group By

Correct Answer: B

Explanation:
Unpivot Other Columns preserves key identifier columns while normalizing all remaining columns.

Question 10

Which statement reflects a PL-300 best practice for reshaping data?

A. Always pivot data before loading
B. Use transpose for large fact tables
C. Normalize data before modeling when possible
D. Avoid unpivoting to improve performance

Correct Answer: C

Explanation:
Normalized (unpivoted) data supports better filtering, relationships, and DAX flexibility, which aligns with PL-300 modeling principles.

Final Exam Tips for This Topic

  • Identify whether the data is wide or tall
  • Pivot = rows → columns
  • Unpivot = columns → rows
  • Transpose = rotate entire table
  • Watch for aggregation requirements when pivoting
  • The exam emphasizes choosing the right transformation, not UI memorization

Go back to the PL-300 Exam Prep Hub main page

Analytics, Business Intelligence, Data Education & Training, Microsoft Certification, PL-300, Power BI

Practice Questions: Convert Semi-Structured Data to a Table (PL-300 Exam Prep)

 
This post is a part of the PL-300: Microsoft Power BI Data Analyst Exam Prep Hub; and this topic falls under these sections:
Prepare the data (25–30%)
   --> Transform and load the data
      --> Convert Semi-Structured Data to a Table

Below are 10 practice questions (with answers and explanations) for this topic of the exam.
There are also 2 practice tests for the PL-300 exam with 60 questions each (with answers) available on the hub.

Practice Questions

Question 1

You load a JSON file into Power BI. The resulting table contains a single column where each row shows List. What is the first step to analyze the data?

A. Expand the column
B. Convert the list to a table
C. Promote headers
D. Split the column by delimiter

Correct Answer: B

Explanation:
Lists must be converted into tables before they can be expanded or analyzed as rows.

Question 2

A column in Power Query displays Record in each row. What does this indicate?

A. The column contains duplicated values
B. The column contains nested structured fields
C. The column contains multiple rows per record
D. The column contains untyped data

Correct Answer: B

Explanation:
A Record represents a nested structure with named fields that can be expanded into columns.

Question 3

Which Power Query action is used to expose fields stored inside a record?

A. Convert to Table
B. Pivot Column
C. Expand Column
D. Transpose Table

Correct Answer: C

Explanation:
Expanding a record reveals its internal fields as individual columns.

Question 4

An API response loads as a table with a column containing lists of values. What is the correct transformation sequence?

A. Expand → Promote Headers
B. Convert to Table → Expand
C. Split Column → Fill Down
D. Group By → Expand

Correct Answer: B

Explanation:
Lists must be converted into tables first, after which they can be expanded.

Question 5

After expanding nested data, you notice duplicate rows in your fact table. What is the most likely cause?

A. Incorrect data type
B. Expanding without understanding data granularity
C. Missing relationships
D. Failure to promote headers

Correct Answer: B

Explanation:
Expanding nested structures without considering the grain can duplicate rows and inflate fact tables.

Question 6

You import an Excel file where headers appear in multiple rows instead of a single row. What is the most appropriate approach?

A. Expand the column
B. Convert the table to a list
C. Transpose the table and promote headers
D. Group rows by column

Correct Answer: C

Explanation:
Transposing realigns rows and columns so headers can be promoted properly.

Question 7

Which Power Query feature is most useful when category labels appear only once and apply to multiple rows below?

A. Replace Values
B. Fill Down
C. Unpivot Columns
D. Merge Queries

Correct Answer: B

Explanation:
Fill Down propagates header or category values to related rows, common in semi-structured spreadsheets.

Question 8

Why is it recommended to expand only required fields when converting semi-structured data?

A. To reduce report refresh frequency
B. To improve visual formatting
C. To reduce model size and complexity
D. To enable DirectQuery mode

Correct Answer: C

Explanation:
Expanding unnecessary fields increases model size and can negatively impact performance and usability.

Question 9

Which transformation should be completed before creating relationships in the data model?

A. Creating measures
B. Flattening semi-structured data
C. Formatting visuals
D. Applying row-level security

Correct Answer: B

Explanation:
Relationships require clean, tabular data. Semi-structured data must be flattened first.

Question 10

Which statement best reflects a PL-300 best practice for handling semi-structured data?

A. Leave nested data unexpanded until report creation
B. Use DAX to flatten semi-structured data
C. Normalize and flatten data in Power Query
D. Always transpose semi-structured tables

Correct Answer: C

Explanation:
Power Query is the correct place to normalize and flatten semi-structured data before modeling and analysis.

Final Exam Tips for This Topic

  • Recognize lists vs records vs tables
  • Lists → Convert to table
  • Records → Expand
  • Inspect data grain before expanding
  • Clean data before flattening
  • This topic is about recognition and transformation choices, not memorizing UI clicks

Go back to the PL-300 Exam Prep Hub main page

Analytics, Business Intelligence, Data Education & Training, Microsoft Certification, PL-300, Power BI

Practice Questions: Create Fact Tables and Dimension Tables (PL-300 Exam Prep)

 
This post is a part of the PL-300: Microsoft Power BI Data Analyst Exam Prep Hub; and this topic falls under these sections:
Prepare the data (25–30%)
   --> Transform and load the data
      --> Create Fact Tables and Dimension Tables

Below are 10 practice questions (with answers and explanations) for this topic of the exam.
There are also 2 practice tests for the PL-300 exam with 60 questions each (with answers) available on the hub.

Practice Questions

Question 1

A table contains SalesAmount, Quantity, ProductName, ProductCategory, CustomerName, and OrderDate. Which columns should remain in the fact table?

A. ProductName, ProductCategory
B. CustomerName, OrderDate
C. SalesAmount, Quantity
D. ProductName, CustomerName

Correct Answer: C

Explanation:
Fact tables store numeric measures that are aggregated, such as SalesAmount and Quantity. Descriptive attributes belong in dimension tables.

Question 2

What is the primary purpose of a dimension table?

A. Store transaction-level data
B. Provide descriptive context for facts
C. Improve visual formatting
D. Store calculated measures

Correct Answer: B

Explanation:
Dimension tables provide descriptive attributes (such as names, categories, and dates) that are used to filter and group fact data.

Question 3

Which relationship type is most appropriate between a dimension table and a fact table?

A. Many-to-many
B. One-to-one
C. One-to-many
D. Bi-directional

Correct Answer: C

Explanation:
A dimension table contains unique keys, while the fact table contains repeated foreign keys, creating a one-to-many relationship.

Question 4

You create a Product dimension table but forget to remove duplicate ProductID values. What issue is most likely?

A. Measures will return blank values
B. Relationships cannot be created correctly
C. Visuals will fail to render
D. DAX functions will not work

Correct Answer: B

Explanation:
Dimension tables must have unique key values. Duplicates prevent proper one-to-many relationships.

Question 5

Which schema design is recommended by Microsoft for Power BI models?

A. Snowflake schema
B. Flat table schema
C. Galaxy schema
D. Star schema

Correct Answer: D

Explanation:
The star schema is recommended for performance, simplicity, and easier DAX calculations in Power BI.

Question 6

Where should fact and dimension tables typically be created?

A. In DAX measures
B. In Power Query during data preparation
C. In visuals after loading data
D. In the Power BI Service

Correct Answer: B

Explanation:
Fact and dimension tables should be shaped in Power Query before loading into the data model.

Question 7

A model uses the same Date table for Order Date and Ship Date. What type of dimension is this?

A. Slowly changing dimension
B. Degenerate dimension
C. Role-playing dimension
D. Bridge table

Correct Answer: C

Explanation:
A role-playing dimension is used multiple times in different roles, such as Order Date and Ship Date.

Question 8

Which is a valid reason not to split a dataset into fact and dimension tables?

A. The dataset is extremely small and static
B. The dataset contains numeric measures
C. The model requires relationships
D. The data will be refreshed regularly

Correct Answer: A

Explanation:
For very small or simple datasets, splitting into facts and dimensions may add unnecessary complexity.

Question 9

What is the primary performance benefit of separating fact and dimension tables?

A. Faster visual rendering due to fewer measures
B. Reduced memory usage and simpler filter paths
C. Automatic indexing of columns
D. Improved DirectQuery support

Correct Answer: B

Explanation:
Star schemas reduce duplication of descriptive data and create efficient filter paths, improving performance.

Question 10

Which modeling mistake often leads to the unnecessary use of bi-directional relationships?

A. Using too many measures
B. Poor star schema design
C. Too many dimension tables
D. Using calculated columns

Correct Answer: B

Explanation:
Bi-directional relationships are often used to compensate for poor model design. A clean star schema usually requires only single-direction filtering.

Final Exam Tips for This Topic

  • Measures → Fact tables
  • Descriptive attributes → Dimension tables
  • Use Power Query to shape tables before modeling
  • Ensure unique keys in dimension tables
  • Prefer star schema over flat or snowflake models
  • Know when not to over-model

Go back to the PL-300 Exam Prep Hub main page

Analytics, Business Intelligence, Data Education & Training, Microsoft Certification, PL-300, Power BI

Practice Questions: Identify when to use reference or duplicate queries and the resulting impact (PL-300 Exam Prep)

 
This post is a part of the PL-300: Microsoft Power BI Data Analyst Exam Prep Hub; and this topic falls under these sections:
Prepare the data (25–30%)
   --> Transform and load the data
      --> Identify when to use reference or duplicate queries and the resulting impact

Below are 10 practice questions (with answers and explanations) for this topic of the exam.
There are also 2 practice tests for the PL-300 exam with 60 questions each (with answers) available on the hub.

Practice Questions

Question 1

You have a query that cleans and standardizes sales data. You need to create several dimension tables from this cleaned dataset. Which option should you use?

A. Duplicate the query for each dimension
B. Reference the query for each dimension
C. Import the source data multiple times
D. Merge the query with itself

✅ Correct Answer: B

Explanation:
Referencing allows multiple tables to inherit the same cleaned logic from a single base query. This ensures consistency and reduces repeated transformation steps, which is a recommended best practice for production models.

Question 2

What is the primary difference between a referenced query and a duplicated query?

A. Referenced queries refresh faster
B. Duplicated queries do not support transformations
C. Referenced queries depend on the original query
D. Duplicated queries cannot be loaded to the model

✅ Correct Answer: C

Explanation:
A referenced query is dependent on its source query and will reflect any changes made to it. A duplicated query is an independent copy with no dependency.

Question 3

A change made to a base query causes multiple downstream queries to fail during refresh. What is the most likely reason?

A. The downstream queries were duplicated
B. The downstream queries were referenced
C. The model relationships were deleted
D. The data source credentials expired

✅ Correct Answer: B

Explanation:
Referenced queries rely on the base query. If a breaking change is introduced (such as removing or renaming a column), all dependent referenced queries may fail.

Question 4

When should you duplicate a query instead of referencing it?

A. When you want transformations to stay consistent
B. When creating multiple dimension tables
C. When experimenting with major changes
D. When reducing refresh dependencies

✅ Correct Answer: C

Explanation:
Duplicating a query is ideal when testing or experimenting, because changes will not affect other queries or downstream dependencies.

Question 5

Which impact is most commonly associated with excessive query duplication?

A. Improved refresh reliability
B. Reduced data volume
C. Increased maintenance effort
D. Better data lineage visibility

✅ Correct Answer: C

Explanation:
Duplicating queries can lead to repeated transformation logic, making the model harder to maintain and increasing the risk of inconsistent data shaping.

Question 6

How does Power BI’s View Lineage represent referenced queries?

A. As independent branches
B. As disconnected tables
C. As upstream and downstream dependencies
D. As hidden queries

✅ Correct Answer: C

Explanation:
Referenced queries appear as downstream dependencies in View Lineage, clearly showing how data flows from base queries to derived queries.

Question 7

You want to ensure that a change to data cleansing logic automatically applies to all derived tables. What should you do?

A. Duplicate the query
B. Reference the query
C. Disable query loading
D. Create calculated tables

✅ Correct Answer: B

Explanation:
Referencing ensures that any change to the base query propagates to all dependent queries automatically.

Question 8

Which of the following is a common mistake when using referenced queries?

A. Using them for experimentation
B. Using them for dimension creation
C. Forgetting that changes propagate downstream
D. Using them to centralize data cleaning

✅ Correct Answer: C

Explanation:
A frequent mistake is forgetting that changes to a referenced base query can unintentionally affect multiple dependent queries.

Question 9

Which approach generally results in a cleaner and more maintainable data model?

A. Duplicating all queries
B. Referencing a well-designed base query
C. Importing data separately for each table
D. Performing transformations in DAX

✅ Correct Answer: B

Explanation:
Using a base query with referenced downstream queries centralizes transformation logic and simplifies maintenance, which aligns with Microsoft’s recommended modeling practices.

Question 10

Which scenario best illustrates when NOT to use a referenced query?

A. Creating a product dimension
B. Applying consistent formatting rules
C. Testing a new transformation approach
D. Creating multiple tables from a single source

✅ Correct Answer: C

Explanation:
Referenced queries should not be used when testing or experimenting with transformations, because changes may impact other dependent queries. Duplicating is safer in this case.

PL-300 Exam Tip

Expect Microsoft to test:

  • Dependency awareness
  • Impact of changes
  • Maintainability vs flexibility
  • Correct use of Reference vs Duplicate

Go back to the PL-300 Exam Prep Hub main page

Analytics, Business Intelligence, Microsoft Certification, PL-300, Power BI

Practice Questions: Merge and append queries (PL-300 Exam Prep)

 
This post is a part of the PL-300: Microsoft Power BI Data Analyst Exam Prep Hub; and this topic falls under these sections:
Prepare the data (25–30%)
   --> Transform and load the data
      --> Merge and append queries

Below are 10 practice questions (with answers and explanations) for this topic of the exam.
There are also 2 practice tests for the PL-300 exam with 60 questions each (with answers) available on the hub.

Practice Questions

Question 1

You receive three Excel files containing sales data for Q1, Q2, and Q3. Each file has identical columns. You want to analyze total sales for the year. What is the BEST approach?

A. Merge the queries using an inner join
B. Merge the queries using a left outer join
C. Append the queries
D. Create relationships between the tables

✅ Correct Answer: C

Explanation:
Appending stacks rows from tables with the same structure. Since the quarterly files have identical columns, Append is the correct transformation. Merge is used for combining columns, not rows.

Question 2

You have a Sales table with ProductID and a Products table with ProductID and ProductName. You want to add product names to the Sales table. Which transformation should you use?

A. Append queries
B. Merge queries with a left outer join
C. Merge queries with a full outer join
D. Create a calculated column

✅ Correct Answer: B

Explanation:
This is a classic lookup scenario. A left outer merge keeps all sales rows while adding matching product details.

Question 3

What is the primary difference between Merge and Append in Power Query?

A. Merge works only with SQL sources
B. Append combines columns; merge combines rows
C. Merge combines rows; append combines columns
D. Merge combines columns; append combines rows

✅ Correct Answer: D

Explanation:
Merge combines tables side-by-side (columns) based on keys, while append stacks tables top-to-bottom (rows).

Question 4

You append two tables with similar but not identical column names. What happens?

A. Power Query fails with an error
B. Columns are automatically renamed
C. New columns are created with null values where data doesn’t exist
D. Only matching columns are appended

✅ Correct Answer: C

Explanation:
When appending, Power Query keeps all columns and fills missing values with nulls, which can create sparse tables if structures don’t align.

Question 5

Which join type returns only rows that do NOT have matching values between two tables?

A. Inner join
B. Left outer join
C. Anti join
D. Full outer join

✅ Correct Answer: C

Explanation:
Anti joins return non-matching rows and are often used for data validation and quality checks — a subtle but testable concept in PL-300.

Question 6

A merge operation produces duplicate rows unexpectedly. What is the MOST likely cause?

A. Append was used instead of merge
B. The join key is not unique
C. The data types are mismatched
D. The wrong join type was selected

✅ Correct Answer: B

Explanation:
If the join key appears multiple times in either table, a merge can create many-to-many matches, resulting in duplicated rows.

Question 7

Where should merge and append operations typically be performed for best model performance?

A. In DAX measures
B. In Power BI visuals
C. In the Power Query Editor
D. In calculated tables

✅ Correct Answer: C

Explanation:
Merge and append are data transformation tasks and should be done in Power Query before loading data into the model.

Question 8

You want to combine historical and current transaction tables that represent the same data structure. What should you do FIRST?

A. Merge the tables
B. Append the tables
C. Create a relationship
D. Create a calculated column

✅ Correct Answer: B

Explanation:
Historical and current tables with the same structure should be appended to form a single fact table before modeling.

Question 9

After merging two queries, the related table appears as a column with the word “Table” in each row. What must you do next?

A. Change the column data type
B. Expand the merged column
C. Rename the column
D. Create a relationship

✅ Correct Answer: B

Explanation:
After a merge, you must expand the nested table to select the columns you want to bring into the main query.

Question 10

Which scenario BEST indicates that append should NOT be used?

A. Combining monthly sales files
B. Combining yearly budget tables
C. Adding customer details to a sales table
D. Combining regional sales data

✅ Correct Answer: C

Explanation:
Appending is for stacking similar datasets. Adding customer details requires merge, not append. This aligns with the PL-300 best practice: know when not to append.

Key PL-300 Takeaways

  • Append = same structure, more rows
  • Merge = related tables, more columns
  • Left outer join is the most common merge type
  • Always validate keys and data types
  • Do transformations in Power Query, not DAX

Go back to the PL-300 Exam Prep Hub main page

Analytics, Business Intelligence, Data Education & Training, Microsoft Certification, PL-300, Power BI

Practice Questions: Identify and Create Appropriate Keys for Relationships (PL-300 Exam Prep)

 
This post is a part of the PL-300: Microsoft Power BI Data Analyst Exam Prep Hub; and this topic falls under these sections:
Prepare the data (25–30%)
   --> Transform and load the data
      --> Identify and Create Appropriate Keys for Relationships

Below are 10 practice questions (with answers and explanations) for this topic of the exam.
There are also 2 practice tests for the PL-300 exam with 60 questions each (with answers) available on the hub.

Practice Questions

Question 1

You want to create a one-to-many relationship between a Sales table and a Customers table. Which column should be used as the key on the one side?

A. Customer Name
B. Customer Email
C. CustomerID
D. Sales Amount

✅ Correct Answer: C

Explanation:
The key on the “one” side must be unique and non-null. CustomerID is a stable identifier designed to uniquely identify customers.

Question 2

Power BI automatically creates a many-to-many relationship between two tables. What is the MOST likely cause?

A. The relationship is inactive
B. The join direction is incorrect
C. Duplicate values exist in the key columns
D. The relationship was created in DAX

✅ Correct Answer: C

Explanation:
Many-to-many relationships occur when neither side of the relationship has unique values. This is often caused by poor key design or missing dimension tables.

Question 3

You need to create a relationship using two columns (OrderDate and StoreID) because no single column is unique. What should you do?

A. Create two relationships
B. Use a many-to-many relationship
C. Create a composite key in Power Query
D. Use a calculated column in DAX

✅ Correct Answer: C

Explanation:
Power BI does not support native composite relationships. You must combine columns into a single key—preferably in Power Query before loading data.

Question 4

A relationship fails to work even though the key values appear identical. What should you check FIRST?

A. Relationship cardinality
B. Column data types
C. Storage mode
D. Table visibility

✅ Correct Answer: B

Explanation:
Keys must have the same data type on both sides. Text vs numeric mismatches are a common cause of broken relationships.

Question 5

Which table should typically contain foreign keys in a star schema?

A. Dimension tables
B. Fact tables
C. Bridge tables only
D. Lookup tables only

✅ Correct Answer: B

Explanation:
In a star schema, fact tables contain foreign keys that reference the primary keys in dimension tables.

Question 6

Which of the following is the BEST candidate for a primary key in a dimension table?

A. Product Name
B. Product Category
C. ProductID
D. Product Description

✅ Correct Answer: C

Explanation:
Primary keys must be unique, stable, and non-descriptive. IDs are ideal, while names and descriptions are subject to change and duplication.

Question 7

You discover duplicate values in a dimension table’s key column. What is the BEST next step?

A. Enable many-to-many relationships
B. Remove duplicates or redesign the dimension
C. Use bidirectional filtering
D. Create a calculated table

✅ Correct Answer: B

Explanation:
Dimension tables must have unique keys. Allowing duplicates introduces ambiguity and breaks proper filter propagation.

Question 8

Where should keys ideally be created or cleaned for best model performance?

A. In DAX measures
B. In calculated columns
C. In Power Query
D. In Power BI visuals

✅ Correct Answer: C

Explanation:
Keys should be created and cleaned before data is loaded. Power Query is the correct environment for shaping and preparing keys.

Question 9

Which situation MOST strongly indicates that a surrogate key should be used?

A. A column contains null values
B. No stable unique identifier exists
C. The table contains many rows
D. The table is a fact table

✅ Correct Answer: B

Explanation:
Surrogate keys are often introduced when natural keys are missing, unstable, or composite, enabling reliable relationships.

Question 10

Why are descriptive columns (such as names or emails) poor choices for relationship keys?

A. They increase storage size
B. They slow down visuals
C. They are not guaranteed to be unique or stable
D. They cannot be indexed

✅ Correct Answer: C

Explanation:
Descriptive fields can change, contain duplicates, or include formatting inconsistencies, making them unreliable as relationship keys.

Key PL-300 Takeaways

  • Keys must be unique, non-null, and stable
  • Dimension tables define the one side
  • Fact tables contain foreign keys
  • Composite keys must be explicitly created
  • Poor key design leads to incorrect results
  • Prepare keys in Power Query, not DAX

Go back to the PL-300 Exam Prep Hub main page

Analytics, Business Intelligence, Data Education & Training, Microsoft Certification, PL-300, Power BI

Practice Questions: Configure Data Loading for Queries (PL-300 Exam Prep)

 
This post is a part of the PL-300: Microsoft Power BI Data Analyst Exam Prep Hub; and this topic falls under these sections:
Prepare the data (25–30%)
   --> Transform and load the data
      --> Configure Data Loading for Queries

Below are 10 practice questions (with answers and explanations) for this topic of the exam.
There are also 2 practice tests for the PL-300 exam with 60 questions each (with answers) available on the hub.

Practice Questions

Question 1

You create a Power Query that cleans raw data and is referenced by several other queries. The query should not appear as a table in the data model. What should you do?

A. Hide the table in Report View
B. Disable Load for the query
C. Delete the query after use
D. Set the query to DirectQuery mode

✅ Correct Answer: B

Explanation:
Staging or helper queries should have Enable Load turned off so they don’t create unnecessary tables in the data model.

Question 2

Which setting determines whether a Power Query is loaded into the Power BI data model?

A. Include in report refresh
B. Enable Load
C. Query folding
D. Incremental refresh

✅ Correct Answer: B

Explanation:
The Enable Load option controls whether a query results in a table in the data model.

Question 3

You disable load for a query that other queries reference. What is the impact?

A. Dependent queries will fail
B. The query is deleted
C. The query still runs but does not load into the model
D. Relationships are automatically removed

✅ Correct Answer: C

Explanation:
Disabling load does not prevent the query from executing. It simply prevents it from creating a table in the model.

Question 4

A dataset contains historical data spanning 10 years and refresh times are very slow. What configuration would MOST improve refresh performance?

A. Disable query folding
B. Hide the table from report view
C. Enable incremental refresh
D. Convert the table to DirectQuery

✅ Correct Answer: C

Explanation:
Incremental refresh limits refresh operations to new or changed data, dramatically improving performance for large datasets.

Question 5

Which requirement must be met before configuring incremental refresh?

A. The table must use Import mode
B. The table must contain a Date/Time column
C. The query must be disabled for load
D. The model must use DirectQuery

✅ Correct Answer: B

Explanation:
Incremental refresh requires a Date/Time column to define data partitions using parameters like RangeStart and RangeEnd.

Question 6

Why is it a best practice to disable load for staging queries?

A. It improves DAX performance
B. It reduces the number of visuals
C. It reduces model size and complexity
D. It enables bidirectional filtering

✅ Correct Answer: C

Explanation:
Loading only final tables keeps the model lean, efficient, and easier to maintain — a key PL-300 principle.

Question 7

Which Power BI feature visually shows how queries depend on each other?

A. Model View
B. Query Diagnostics
C. Query Dependencies
D. Performance Analyzer

✅ Correct Answer: C

Explanation:
Query Dependencies provides a visual map showing which queries reference others, helping validate load configuration decisions.

Question 8

Which action helps preserve query folding and improve data loading performance?

A. Creating calculated columns
B. Filtering data early in Power Query
C. Disabling load for fact tables
D. Hiding columns in Data View

✅ Correct Answer: B

Explanation:
Applying filters early allows Power Query to push transformations back to the data source, improving performance during load.

Question 9

A query is hidden from Report View but still increases dataset size. Why?

A. Hidden tables still load into the model
B. Hidden tables are cached
C. Hidden tables are duplicated
D. Hidden tables disable query folding

✅ Correct Answer: A

Explanation:
Hiding a table only affects visibility. It does not prevent data from being loaded into the model.

Question 10

Which configuration prevents a query from refreshing but keeps it in the model?

A. Disable Load
B. Remove the query
C. Uncheck “Include in report refresh”
D. Convert to DirectQuery

✅ Correct Answer: C

Explanation:
Unchecking Include in report refresh keeps the table in the model but excludes it from refresh operations.

Key PL-300 Takeaways

  • Enable Load controls whether a query creates a model table
  • Disable load for staging and helper queries
  • Incremental refresh is critical for large datasets
  • Query folding affects how data is loaded
  • Hidden tables still consume memory

Go back to the PL-300 Exam Prep Hub main page

Analytics, Business Intelligence, Data Education & Training, Microsoft Certification, PL-300, Power BI

Practice Questions: Configure table and column properties (PL-300 Exam Prep)

 
This post is a part of the PL-300: Microsoft Power BI Data Analyst Exam Prep Hub; and this topic falls under these sections:
Model the data (25–30%)
   --> Design and implement a data model
      --> Configure table and column properties

Below are 10 practice questions (with answers and explanations) for this topic of the exam.
There are also 2 practice tests for the PL-300 exam with 60 questions each (with answers) available on the hub.

Practice Questions

Question 1

You notice that a column containing Customer IDs is being summed in report visuals by default.
What is the best way to prevent this behavior?

A. Change the data type to Text
B. Hide the column
C. Set Default Summarization to Do not summarize
D. Create a measure instead

✅ Correct Answer: C

Explanation:
Default Summarization controls how numeric columns aggregate in visuals. ID columns should not be summed, so setting them to Do not summarize prevents incorrect totals while still allowing the column to be used where appropriate.

Question 2

A report displays month names alphabetically instead of chronologically.
Which column property should you configure to correct this?

A. Data Category
B. Default Summarization
C. Sort By Column
D. Column Visibility

✅ Correct Answer: C

Explanation:
The Sort By Column property allows a text column (Month Name) to be sorted using a numeric column (Month Number), ensuring correct chronological order.

Question 3

You want to prevent report authors from accidentally using surrogate key columns in visuals.
What should you do?

A. Change the data type
B. Hide the columns
C. Disable summarization
D. Remove the columns from the model

✅ Correct Answer: B

Explanation:
Hiding columns removes them from the Fields pane while keeping them available for relationships and internal model use.

Question 4

A column containing website URLs should appear as clickable links in reports.
Which property must be configured?

A. Data Type
B. Format
C. Data Category
D. Default Summarization

✅ Correct Answer: C

Explanation:
Setting the Data Category to Web URL enables Power BI to render values as clickable links in visuals.

Question 5

You are preparing a shared dataset for enterprise use. Users frequently ask what certain fields represent.
Which feature best addresses this issue?

A. Column formatting
B. Column descriptions
C. Data categories
D. Column visibility

✅ Correct Answer: B

Explanation:
Descriptions provide tooltips in Power BI Desktop and Service, improving documentation and usability without affecting calculations.

Question 6

Which column should typically have Default Summarization set to “Do not summarize”?

A. Sales Amount
B. Quantity Sold
C. Discount Percentage
D. Customer ID

✅ Correct Answer: D

Explanation:
Identifiers should never be aggregated. Summing or averaging IDs produces meaningless results.

Question 7

You want a column representing cities to work correctly in map visuals.
Which property should you configure?

A. Sort By Column
B. Data Category
C. Format
D. Column Visibility

✅ Correct Answer: B

Explanation:
Setting the Data Category to City helps Power BI geocode the data correctly for map visuals.

Question 8

Does hiding a table or column improve model performance?

A. Yes, it reduces memory usage
B. Yes, it reduces refresh time
C. No, it only affects usability
D. Only for calculated columns

✅ Correct Answer: C

Explanation:
Hiding objects only impacts the user interface. It does not affect performance, refresh time, or memory usage.

Question 9

Which table type most commonly contains hidden key columns and visible descriptive attributes?

A. Fact table
B. Dimension table
C. Bridge table
D. Calculated table

✅ Correct Answer: B

Explanation:
Dimension tables expose descriptive fields (Name, Category) while hiding primary keys used only for relationships.

Question 10

A numeric column representing profit margin (percentage) is being summed in visuals, causing incorrect results.
What is the best configuration?

A. Change the data type to Text
B. Set Default Summarization to Average or Do not summarize
C. Hide the column
D. Convert it to a measure

✅ Correct Answer: B

Explanation:
Percentages are non-additive. Changing the default summarization prevents misleading totals while keeping the column usable.

Key PL-300 Exam Takeaways

  • Default Summarization is frequently tested
  • Sort By Column is essential for time-based attributes
  • Data Category impacts maps and URLs
  • Hiding fields improves usability, not performance
  • Descriptions matter for shared and certified datasets

Go back to the PL-300 Exam Prep Hub main page

Analytics, Business Intelligence, Data Education & Training, Microsoft Certification, PL-300, Power BI

Practice Questions: Implement Role-Playing Dimensions (PL-300 Exam Prep)

 
This post is a part of the PL-300: Microsoft Power BI Data Analyst Exam Prep Hub; and this topic falls under these sections:
Model the data (25–30%)
   --> Design and implement a data model
      --> Implement Role-Playing Dimensions

Below are 10 practice questions (with answers and explanations) for this topic of the exam.
There are also 2 practice tests for the PL-300 exam with 60 questions each (with answers) available on the hub.

Practice Questions

Question 1

A Sales table contains OrderDate, ShipDate, and DeliveryDate. All three columns reference the same Date dimension. What is the recommended modeling approach?

A. Create one Date table and mark two relationships inactive
B. Use calculated columns to combine the dates
C. Duplicate the Date dimension for each role
D. Use USERELATIONSHIP in every measure

✅ Correct Answer: C

Explanation:
Duplicating the Date dimension allows all relationships to remain active, avoids complex DAX, and creates a clearer and more intuitive model—this is the preferred PL-300 approach.

Question 2

Why is using a single Date table with multiple inactive relationships generally discouraged?

A. It prevents refresh
B. It increases storage requirements
C. It requires explicit DAX to activate filters
D. It violates star schema rules

✅ Correct Answer: C

Explanation:
Inactive relationships do not filter visuals automatically. Measures must use USERELATIONSHIP(), increasing complexity and risk of errors.

Question 3

Which DAX function is required when using an inactive relationship?

A. CROSSFILTER
B. TREATAS
C. RELATED
D. USERELATIONSHIP

✅ Correct Answer: D

Explanation:
USERELATIONSHIP() temporarily activates an inactive relationship within a calculation.

Question 4

A report user wants to analyze sales by both Order Date and Ship Date using slicers. What model design best supports this?

A. One Date table with calculated columns
B. One Date table with inactive relationships
C. Two duplicated Date dimension tables
D. A single Date table with bidirectional filtering

✅ Correct Answer: C

Explanation:
Duplicated role-playing dimensions allow independent slicers that filter correctly without special DAX logic.

Question 5

Which of the following is a common example of a role-playing dimension?

A. Product Category
B. Calendar Date
C. Fact table surrogate keys
D. Measure tables

✅ Correct Answer: B

Explanation:
Date dimensions commonly play multiple roles such as Order Date, Ship Date, and Due Date.

Question 6

What is the primary benefit of role-playing dimensions in a Power BI model?

A. Reduced data refresh time
B. Smaller model size
C. Improved filter clarity and usability
D. Automatic time intelligence

✅ Correct Answer: C

Explanation:
Role-playing dimensions provide clear business meaning and ensure intuitive filtering behavior in reports.

Question 7

Which action should be taken after duplicating a dimension table for a role-playing scenario?

A. Enable bidirectional filters
B. Rename the table to reflect its role
C. Disable data load
D. Hide all columns

✅ Correct Answer: B

Explanation:
Renaming the table (e.g., Date – Order, Date – Ship) improves model readability and user understanding—important for exam and real-world models.

Question 8

What is the impact of duplicating a dimension table on model performance?

A. Significant performance degradation
B. No change in filtering behavior
C. Slight increase in model size with minimal impact
D. Increased query execution time for all visuals

✅ Correct Answer: C

Explanation:
Duplicated dimensions slightly increase model size but typically have negligible performance impact, especially compared to the benefits.

Question 9

Which scenario most strongly indicates the need for role-playing dimensions?

A. A fact table with multiple numeric measures
B. A dimension table with many attributes
C. A fact table with multiple foreign keys to the same dimension
D. A model with many-to-many relationships

✅ Correct Answer: C

Explanation:
Multiple foreign keys pointing to the same dimension is the classic role-playing dimension scenario.

Question 10

Which modeling choice is most likely to cause confusion for report consumers?

A. Duplicating dimensions with clear names
B. Using inactive relationships without explanation
C. Using a star schema
D. Hiding surrogate keys

✅ Correct Answer: B

Explanation:
Inactive relationships do not filter automatically and can lead to confusing or incorrect visuals if not carefully managed.

Exam Tips for This Topic 🧠

  • When in doubt, duplicate the dimension
  • Avoid unnecessary USERELATIONSHIP() usage
  • Look for questions involving multiple dates or roles
  • Favor clarity and simplicity over clever DAX
  • Think from a report consumer’s perspective

Go back to the PL-300 Exam Prep Hub main page