I have a working solution for you, but I feel that it is overly complex. I think there must be a better way. I would like to hear from MarcelBeug or Imke to hear if there is a better way
In short, I unpivoted the data, counted the number of rows per driver, merged that back to the unique list and then created a custom column to determine if it was the first school or second school. I feel there should be a way to insert a nested ID column - I just don't know how.
let
Source = Excel.CurrentWorkbook(){[Name="TableMain"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Bus Driver Name", type text}, {"Kindergarten", type text}, {"Boys School", type text}, {"Girls School Cycle1", type text}, {"Girls School Cycle2", type text}, {"Girls School Cycle3", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Bus Driver Name"}, "Attribute", "Value"),
List = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
Count = Table.Group(List, {"Bus Driver Name"}, {{"Count", each Table.RowCount(_), type number}}),
Custom1 = List,
#"Merged Queries" = Table.NestedJoin(Custom1,{"Bus Driver Name"},Count,{"Bus Driver Name"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Count"}, {"NewColumn.Count"}),
#"Added Index" = Table.AddIndexColumn(#"Expanded NewColumn", "Index", 0, 1),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
#"Merged Queries1" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries1", "NewColumn", {"Bus Driver Name"}, {"NewColumn.Bus Driver Name"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn1",{"Index", "Index.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"NewColumn.Bus Driver Name", "Next Line"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each if [NewColumn.Count] = 1 then 1 else if [Next Line] = [Bus Driver Name] and [NewColumn.Count] = 2 then 1 else 2),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"NewColumn.Count", "Next Line"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Custom", "Bus Driver Name", "Value"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Custom", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Custom]), "Custom", "Value")
in
#"Pivoted Column"