Header promotion in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants you to clean up the sales data by promoting the first row of the dataset to be the column headers so the data is easier to analyze.

📊 Data: You have a table where the first row contains the actual column names, but it is currently part of the data rows. The columns are generic like Column1, Column2, etc., and the first row has the real headers like Date, Product, Region, Sales.

🎯 Deliverable: A cleaned table in Power BI where the first row is promoted to headers, ready for analysis and visualization.

Sample Data

Column1	Column2	Column3	Column4
Date	Product	Region	Sales
2024-01-01	Shirt	North	100
2024-01-02	Pants	East	150
2024-01-03	Hat	South	120
2024-01-04	Shoes	West	130

1

Step 1: Load the data into Power BI Desktop from your source file.

Use 'Get Data' and select your file, then load it into Power Query Editor.

Expected Result

Data appears in Power Query with generic column names and the first row containing actual headers.

2

Step 2: Promote the first row to be the column headers.

In Power Query Editor, go to the 'Home' tab and click 'Use First Row as Headers'.

Expected Result

The first row is now the column headers: Date, Product, Region, Sales.

3

Step 3: Check data types for each column and set them correctly.

Set 'Date' column to Date type, 'Product' and 'Region' to Text, and 'Sales' to Whole Number.

Expected Result

Columns have correct data types for analysis.

4

Step 4: Close and apply the changes to load the cleaned data into Power BI.

Click 'Close & Apply' in Power Query Editor.

Expected Result

Cleaned data with proper headers and data types is loaded into Power BI for reporting.

Final Result

-----------------------------------------
| Date       | Product | Region | Sales |
-----------------------------------------
| 2024-01-01 | Shirt   | North  | 100   |
| 2024-01-02 | Pants   | East   | 150   |
| 2024-01-03 | Hat     | South  | 120   |
| 2024-01-04 | Shoes   | West   | 130   |
-----------------------------------------

✓The data is now clean with proper headers.

✓You can now create visuals like sales by region or product easily.

✓Data types are set correctly for accurate calculations.

Bonus Challenge

Create a bar chart showing total sales by region using the cleaned data.

Show Hint

Use the 'Region' column for the axis and sum of 'Sales' for values in a bar chart visual.