Hi
Welcome! You're very close—and this is a classic Power Query (Power BI or Excel) cleanup scenario. You're dealing with header metadata rows (START/END dates) that you want to transform into columns for each data row.
Let me walk you through how to do this step-by-step in Power Query, with a visual result like the one you're aiming for.
🛠️ Step-by-Step Solution in Power Query
✅ 1. Load Your Data
-
In Excel: Select your table → Data tab → From Table/Range → Load to Power Query.
-
In Power BI: Use Get Data → Excel → load the sheet/table to Power Query.
✅ 2. Promote Proper Headers
If your data looks like:
Row 1: Model, Invoice Price, Standard RRP, SOA1, SRP1
Row 2: START, , , 01/01/2025, 01/01/2025
Row 3: END, , , 07/07/2025, 07/07/2025
Then the actual data starts on row 4, and rows 2 & 3 contain metadata. So:
-
In Power Query: Remove top 1 row → Home tab → Remove Rows → Remove Top Rows → 1
-
Now you’ll see START and END as your first two rows.
-
Select Home > Use First Row as Headers — now your headers are the column names.
✅ 3. Extract START and END Dates
Create two new custom columns:
➕ Custom Column: START
= #"Changed Type"{0}[SOA1]
➕ Custom Column: END
= #"Changed Type"{1}[SOA1]
Replace "Changed Type" with the step name before this one if it differs.
What this does:
-
{0} = 1st row = START values.
-
{1} = 2nd row = END values.
-
We’re grabbing a representative column (SOA1) assuming dates are same across those columns.
✅ 4. Remove Metadata Rows
Remove the first two rows (START and END):
