Equitable distribution by shorter distance - Geolocation

Thank you so much Anonymous  ,

After doing a lot of tests and learning how to use the List.Generetate function, -thank you for your codes, it helped me a lot to understand how does it work - i did the following code

Although, I have tried to improve the algorithm, it only works with few records, otherwise, it takes too much time that is impossible to process.

I also tested your last code, and likewise, it takes a lot of time.

Any suggestions to make it faster? ,

I am not sure if the process of removing records is the one that slows down the process.

BTW I'm reading the data from an Excel file

PS. I share the code of the whole process in case someone helps , You only need to paste it in the advanced query editor

// Manager's Table
let
    Managers = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdA9CsMwDAXgqxTPqdCvJd+hPUHIEErp1kLvP9RxCEGdZPD34EnzXO7re309vxcqU7l9HtvQBoRSnfv76grC1tzLMp2ak2Y1atgGF3DG4Ehcdr4NI0CyCI3BCdg4FGvy+udDpIYcgUrRmHLC9sQWtIDmnY8+gcBObpp0PXVFQBbEUScIwpyIk/ZdbzsbA1PvO05THfoaqi3pyNqEXA/NpJqbtKSl/5P7waMG9zWXHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Manager = _t, Location = _t, latitude = _t, longitude = _t]),{{"latitude", type number}, {"longitude", type number}}),
   
// Client's Table
    DATA = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZQ5coNAFETvQixTf1/u4BuoFPkAvn/mBkZIowgroQaePt09Pdzvi7IQRy235fv3R3FxWk2YvGNbfSWvkmLeqcvjdl8aT9J54rW4rXjg+Cdpau14UgpJTnhqS1X54DUiuDpPnvc3X+OJBK/ODzllZVqD78pQjqE+RY0m3KXYVGzglVgmycEzpfE83txg9pQTaUJhevLsH3KEUkn15Btxag89ZbAz8VLUgSmDz3aYH3EW8ZbGrN9wq+KpnzmIpWXwzU764bddsmTnaS3hDbcnDzc980QKQJ88GxFMH/GjPU528NslCHkble6hfRXsNCRIHfkzCUHSi/dYyy2Qz7FdvSJOTvcXz0/9l/ite/LG5+pIv1GTwbcSu482MxVZ5j/0o/82+8UEA7DnWbImZyET3vlNPTFPfCgq6Of8LlZFyQbvJPHBp9VxbxtPjqVZv/CsKZ5m/JJj2A1pFRyYF9962e7BX99e3bbX+6p8JUOfr6e/8+1Xx+MQe/B7eXplw8chqcb46hIVnK3HHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Location = _t, latitude = _t, longitude = _t]),{{"latitude", type number}, {"longitude", type number}}),
    
// Function to calculate the distance
    Calculate_Distance = (Latitude1 as number, Longitude1 as number, Latitude2 as number, Longitude2 as number) => 
    let 
		EarthRadius = 6378.1,
		constante = Number.PI / 180,
		DifferenceLat = Latitude1 - Latitude2,
		DifferenceLon = Longitude1 - Longitude2,
		a = Number.Power(Number.Sin(DifferenceLat * constante / 2) , 2) + Number.Cos(Latitude1 * constante) * Number.Cos(Latitude2 * constante) * Number.Power(Number.Sin(DifferenceLon * constante / 2) , 2),
		b = 2 * Number.Asin(Number.Sqrt(a)),
		FinalDistance = b * EarthRadius 
	in FinalDistance,

//Function Asign Managers
    Asign = (ManagersP as table, Records as table) => 
	let 
		TotalMan= Table.RowCount(ManagersP),
		ClientbyMan= Number.RoundUp(Table.RowCount(Records)/TotalMan),
		TableDistances=Table.Sort(Table.Buffer(Table.ExpandTableColumn(Records,"Distances",	{"Manager",  "Distance"}, {"Manager",  "Distance"})),"Distance"),
		
	//Function to get the available manager  
	Next= (TableO,Asigned)=>
				List.Last(List.Generate(()=> 
					[Continue = 1, TableI=TableO]
					, each [Continue]=1 
					, each  [
							Continue= if Asigned{List.PositionOf(ManagersP[Manager], [TableI]{0}[Manager])} < ClientbyMan then 0 else 1,
							TableI= if Continue = 1 then Table.RemoveRows([TableI],0) else [TableI]							
							]
					, each [TableI]
					)),
					
// Loop the table asigning the closest manager
	Final=List.Generate( ()=> 
		[	Record=Table.First( TableDistances,[ID=null, Manager=null,Distance=null]),		
			Dispo= List.PositionOf(ManagersP[Manager],Record[Manager]),
			TableSort= Table.RemoveMatchingRows(TableDistances,{[ID=Record[ID]]},"ID"),
			Counter=  List.ReplaceRange(List.Repeat({0},Table.RowCount(ManagersP)),Dispo, 1,{1}),
			RecordOut=[ Id=Record[ID], Manager=Record[Manager], Distance=Record[Distance] ],
			Continue= if Table.RowCount( TableSort)>0 then true else false			
		]
		, each [Continue]
		, each 
		[	Record=Table.First( Next([TableSort],[Counter]),[Manager=null] ),
			Dispo=List.PositionOf(ManagersP[Manager],Record[Manager]),			
			TableSort=	Table.RemoveMatchingRows([TableSort],{[ID=Record[ID]]},"ID"),
			Counter=  List.ReplaceRange([Counter],Dispo, 1,{[Counter]{Dispo}+1}),			
			RecordOut = [ Id=Record[ID],Manager=Record[Manager], Distance=Record[Distance] ],									
			Continue= if Table.RowCount( [TableSort])>0 then true else false
		]
	, each [RecordOut]
	)

	in Table.FromList(Final,Record.FieldValues,{"Id","Manager","Distance"}),



//Funcion to add a table with the calculate distances
	#"Data with Distance" = (Locations as table, DATA as table) as table =>
	let
	DataWithDistance=Table.AddColumn(DATA,"Distances", each
		Table.Sort(
			Table.AddColumn(Locations,"Distance", (RECORD)=>
			Calculate_Distance(RECORD[latitude],RECORD[longitude],_[latitude],_[longitude])
			), {"Distance"} )
		)
	in DataWithDistance,


// Final Process    
Locations = Table.Group(Managers, {"Location"}, {{"Count", each Table.RowCount(_), type number}, {"Managers", each _, type table}}),

Records = Table.AddColumn(Locations, "Data", each Table.SelectRows(DATA,(MainTable) => if MainTable[Location]=[Location] then true else false),type table),

#"Records with distance" = Table.AddColumn(Records,"Records_Distance", each #"Data with Distance"(_[Managers],_[Data]), type table),

AsignarProcess = Table.AddColumn(#"Records with distance","Final Asign", each Asign(_[Managers],_[Records_Distance])),

#"Remove Columns" = Table.RemoveColumns(AsignarProcess,{"Location", "Count", "Managers", "Data", "Records_Distance"}),

Final = Table.ExpandTableColumn(#"Remove Columns", "Final Asign", {"Id", "Manager", "Distance"}, {"Id", "Manager", "Distance"})
in
    Final