The leader of Caerphilly County Borough Council has expressed his disappointment after Rhymney missed out on being home to a depot for the South Wales Metro project.