Hi @unknown917,
Hope you're doing well!
In Power Query, create a New Blank Query and paste this M code:
let
// Reference your existing tables (rename to match your actual query names)
RouteTable = Excel.CurrentWorkbook(){[Name="RouteTable"]}[Content],
PlantTable = Excel.CurrentWorkbook(){[Name="PlantTable"]}[Content],
// Ensure correct types
RouteTyped = Table.TransformColumnTypes(RouteTable,{
{"ID", type text},
{"Day #", Int64.Type},
{"Day Seq", Int64.Type}
}),
PlantTyped = Table.TransformColumnTypes(PlantTable,{
{"ID", type text},
{"Number", Int64.Type},
{"Plant", type text}
}),
// Join Plant info onto Route rows
Joined = Table.NestedJoin(
RouteTyped, {"ID"},
PlantTyped, {"ID"},
"PlantData", JoinKind.Left
),
Expanded = Table.ExpandTableColumn(
Joined, "PlantData", {"Plant", "Number"}, {"Plant", "Number"}
),
// Group by Day # (and Number/Plant if multiple groups exist)
Grouped = Table.Group(
Expanded,
{"Day #", "Number", "Plant"},
{{"Rows", each _, type table}}
),
// For each group: add START row (plant, seq=1), re-seq original rows, add END row
Transformed = Table.TransformColumns(Grouped, {"Rows", each
let
grp = _,
dayNum = grp[Day #]{0},
plant = grp[Plant]{0},
rowCount = Table.RowCount(grp),
// Build START row
startRow = #table(
{"ID", "Day #", "Day Seq"},
{{plant, dayNum, 1}}
),
// Re-sequence original rows starting at 2
reSeqd = Table.AddColumn(
Table.SelectColumns(grp, {"ID", "Day #"}),
"Day Seq",
each Table.RowCount(
Table.SelectRows(
Table.SelectColumns(grp, {"ID"}),
(r) => r[ID] <= [ID] // positional trick — see note below
)
) + 1
),
// Better re-sequencing using index
withIndex = Table.AddIndexColumn(
Table.SelectColumns(grp, {"ID", "Day #"}),
"Day Seq", 2, 1
),
// Build END row
endRow = #table(
{"ID", "Day #", "Day Seq"},
{{plant, dayNum, rowCount + 2}}
),
// Stack all three parts
Final = Table.Combine({startRow, withIndex, endRow})
in
Final
}),
// Expand grouped results back into flat table
Result = Table.Combine(Transformed[Rows])
in
Result
Here's the clean, production-ready version with no noise:
let
RouteTable = YourRouteQueryName, // ← change to your query name
PlantTable = YourPlantQueryName, // ← change to your query name
Joined = Table.NestedJoin(RouteTable, {"ID"}, PlantTable, {"ID"}, "P", JoinKind.Left),
Expanded = Table.ExpandTableColumn(Joined, "P", {"Plant", "Number"}),
Grouped = Table.Group(Expanded, {"Day #", "Plant", "Number"}, {{"Rows", each _, type table}}),
AddedRows = Table.AddColumn(Grouped, "Final", each
let
src=[Rows],
day = [Day #],
plant = [Plant],
cnt = Table.RowCount(src),
startRow = #table({"ID","Day #","Day Seq"}, {{plant, day, 1}}),
middle = Table.AddIndexColumn(Table.SelectColumns(src,{"ID","Day #"}), "Day Seq", 2, 1),
endRow = #table({"ID","Day #","Day Seq"}, {{plant, day, cnt + 2}})
in
Table.Combine({startRow, middle, endRow})
),
Result = Table.Combine(AddedRows[Final])
in
Result
What This Produces
ID
Day #
Day Seq
456
2
1 ← Plant (Start)
B
2
2
C
2
3
D
2
4
E
2
5
F
2
6
G
2
7
H
2
8
I
2
9
J
2
10
456
2
11 ← Plant (End)
