Header promotion in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

What does header promotion do in Power Query?

In Power Query, what is the main effect of using the 'Use First Row as Headers' feature?

A It sorts the data based on the first row values.

B It deletes the first row of data permanently.

C It duplicates the first row and adds it as a new column.

D It converts the first row of data into column headers.

💡 Hint

Think about how the table's columns get their names.

Header promotion changes the first row of data into the column headers, making the data easier to understand and work with.

❓ dax_lod_result

intermediate

DAX measure after header promotion

After promoting headers in Power Query, you load a table named 'SalesData' with columns: 'Product', 'Region', and 'Sales'. You create this DAX measure:

Total Sales = SUM(SalesData[Sales])

What will be the result of Total Sales if the 'Sales' column contains values 100, 200, and 300?

A Error: Column 'Sales' not found

B 600

C 0

D 100

💡 Hint

Check if the column name matches exactly after header promotion.

Promoting headers ensures the column is named 'Sales'. The SUM adds 100 + 200 + 300 = 600.

🔧 Formula Fix

advanced

Why does this header promotion step fail?

You have a table where the first row contains mixed data types. You apply 'Use First Row as Headers' but get an error. What is the most likely cause?

A The first row has duplicate values causing invalid column names.

B The first row contains null values which cannot be headers.

C The table has more than 1000 rows, which is unsupported.

D The data source is not connected.

💡 Hint

Headers must be unique and valid.

If the first row has duplicate values, Power Query cannot create unique column headers, causing the error.

❓ visualization

advanced

Best visualization after header promotion

You have promoted headers in a sales dataset with columns: 'Date', 'Product', 'Quantity', and 'Revenue'. Which visualization best shows total revenue by product?

A Bar chart with 'Product' on X-axis and sum of 'Revenue' on Y-axis.

B Line chart with 'Date' on X-axis and 'Quantity' on Y-axis.

C Scatter plot with 'Quantity' on X-axis and 'Date' on Y-axis.

D Pie chart showing count of 'Product' entries.

💡 Hint

Think about comparing totals by category.

A bar chart with products on the X-axis and total revenue on the Y-axis clearly shows revenue comparison by product.

❓ data_modeling

expert

Impact of header promotion on data model relationships

You import two tables: 'Orders' and 'Customers'. 'Orders' has a first row with customer IDs but no headers. You promote headers in 'Orders' and the customer ID column is named 'CustomerID'. 'Customers' already has a 'CustomerID' column. What is the impact on creating a relationship between these tables?

A You cannot create a relationship because promoted headers are not recognized in the model.

B Relationships are created automatically regardless of header names.

C You can create a relationship on 'CustomerID' because headers match after promotion.

D You must rename columns manually after promotion to create relationships.

💡 Hint

Headers define column names used in relationships.

Promoting headers correctly names the column 'CustomerID', allowing you to create relationships with the 'Customers' table on that column.

Practice

1. What does header promotion do in Power BI's Power Query Editor?

easy

A. It removes all empty rows from the data.

B. It turns the first data row into column headers.

C. It filters data based on header names.

D. It merges two tables based on headers.

Solution

1. Step 1: Understand header promotion concept

   Header promotion means using the first row of data as the column headers instead of default generic headers.

2. Step 2: Recognize its use in Power Query

   Power Query's Table.PromoteHeaders function performs this action to make data easier to read and analyze.

3. Final Answer:

   It turns the first data row into column headers. -> Option B

4. Quick Check:

   Header promotion = first row becomes headers [OK]

Hint: Remember: header promotion swaps first row to headers [OK]

Common Mistakes:

• Confusing header promotion with filtering rows
• Thinking it deletes the first row instead of promoting it
• Assuming it merges tables automatically

2. Which of the following is the correct syntax to promote headers in Power Query M language?

easy

A. Table.Promote(SourceHeaders)

B. PromoteHeaders(Table)

C. Source.PromoteHeaders()

D. Table.PromoteHeaders(Source)

Solution

1. Step 1: Recall Power Query function names

   The correct function to promote headers is Table.PromoteHeaders, which takes a table as input.

2. Step 2: Match syntax with options

   Table.PromoteHeaders(Source) uses the correct function and syntax: Table.PromoteHeaders(Source). Other options are invalid or incorrect syntax.

3. Final Answer:

   Table.PromoteHeaders(Source) -> Option D

4. Quick Check:

   Correct function name and syntax = Table.PromoteHeaders(Source) [OK]

Hint: Function starts with Table.PromoteHeaders and takes table input [OK]

Common Mistakes:

• Using incorrect function names like PromoteHeaders alone
• Calling promote headers as a method on source
• Mixing parameter order or names

3. Given this Power Query M code snippet:

let
  Source = Table.FromRows({{"Name", "Age"}, {"Alice", 30}, {"Bob", 25}}),
  Promoted = Table.PromoteHeaders(Source)
in
  Promoted

What will be the column headers of the resulting table?

medium

A. ["Alice", 30]

B. ["Column1", "Column2"]

C. ["Name", "Age"]

D. ["Bob", 25]

Solution

1. Step 1: Analyze the initial table rows

   The first row is {"Name", "Age"}, which looks like header names, followed by data rows.

2. Step 2: Understand effect of Table.PromoteHeaders

   This function uses the first row as column headers, so the columns become "Name" and "Age".

3. Final Answer:

   ["Name", "Age"] -> Option C

4. Quick Check:

   First row becomes headers = ["Name", "Age"] [OK]

Hint: First row values become headers after promotion [OK]

Common Mistakes:

• Thinking default headers remain after promotion
• Confusing data rows as headers
• Assuming column names are generic like Column1

4. You applied Table.PromoteHeaders but got an error saying "Expression.Error: The column 'Name' of the table wasn't found." What is the most likely cause?

medium

A. The first row does not contain valid header names.

B. The data types of columns are incompatible.

C. You used the wrong function name.

D. The table is empty with no rows.

Solution

1. Step 1: Understand error context

   The error "The column 'Name' of the table wasn't found" occurs when code tries to reference [Name] after promotion, but the column doesn't exist.

2. Step 2: Identify promotion effect

   Table.PromoteHeaders sets column names from the first row's values. If 'Name' is missing from the first row, no 'Name' column is created.

3. Final Answer:

   The first row does not contain valid header names. -> Option A

4. Quick Check:

   First row lacks expected header 'Name' [OK]

Hint: Ensure first row contains expected header names like 'Name' [OK]

Common Mistakes:

• Thinking empty table causes this specific column error (promotion fails differently)
• Blaming data types (causes type errors, not missing columns)
• Assuming wrong function (causes syntax error)

5. You imported a CSV file where the first two rows contain metadata, and the third row has actual headers. How do you promote the correct headers in Power Query?

hard

A. Remove the first two rows, then apply Table.PromoteHeaders.

B. Apply Table.PromoteHeaders directly on the original table.

C. Rename columns manually without removing rows.

D. Use Table.Skip to skip the third row, then promote headers.

Solution

1. Step 1: Remove unwanted metadata rows

   The first two rows are metadata, so remove them using Table.Skip(Source, 2) to start from the third row.

2. Step 2: Promote the third row as headers

   After removing the first two rows, apply Table.PromoteHeaders to use the third row as headers.

3. Final Answer:

   Remove the first two rows, then apply Table.PromoteHeaders. -> Option A

4. Quick Check:

   Skip metadata rows before promoting headers [OK]

Hint: Skip metadata rows first, then promote headers [OK]

Common Mistakes:

• Promoting headers without removing metadata rows
• Skipping wrong number of rows
• Renaming columns manually instead of promoting