Equitable distribution by shorter distance - Geolocation

mmhhhh ... as far as I remember the code makes use of the list of customer ids only to associates the various managers. Each customer id is only treated once, and the list is not manipulated.

Have you checked that the duplicates are not already in your original list?

Could you just provide the list of IDs you use?

Yes.. you'r correct, one client has only one location and will be asigned to only one  manager

Here's my Test Dataset https://we.tl/t-HJUVuUihTZ

Also If we asign clients in blocks to each manager, we have the problem that some clients will not be assigned to their closest manager. Using your code (thanks for it) i made changes trying to reduce this issue sorting the whole table first

disTabLoc2 = (loc as text)=> let

	tabLoc = clTab{[Location=loc]}[clLoc],

//Distances Matrix
    ac=List.Accumulate(
		manTab{[Location=loc]}[manLoc][Manager],
		tabLoc, 
		(s,c)=>Table.AddColumn(s, c, 
			each distance([latitude],[longitude],
						  manTab{[Location=loc]}[manLoc]{[Manager=c]}[latitude],
						  manTab{[Location=loc]}[manLoc]{[Manager=c]}[longitude]))), 
						  
// Get Manager ordered by shortest Distances    	
	cols= Table.Sort(
			Table.AddColumn(manTab{[Location=loc]}[manLoc], "Min Dis",each List.Min(Table.Column(ac,_[Manager])))
		,"Min Dis")[Manager],

	size=Number.RoundUp(Table.RowCount(ac)/List.Count(cols)),

//Sort the Table by Manager Ordered and get ID	
    Ids = Table.Sort(ac,cols)[ID],

// Asign Manager
	Loop = List.Accumulate(cols,
		[row=0,
		IdsbyMan=List.Range(Ids,row*size,size),
		Man=List.Repeat({cols{row}},List.Count(IdsbyMan)),
		Final = Table.FromColumns({IdsbyMan,Man},{"ID","Manager"})
		]
	,(s,c)=> 
		[row=s[row]+1,
		IdsbyMan=List.Range(Ids,row*size,size),
		Man=List.Repeat({cols{row}},List.Count(IdsbyMan)),
		Final = s[Final]&Table.FromColumns({IdsbyMan,Man},{"ID","Manager"})
		]
	)
    	
in Loop[Final]
	

The setting of the last code assigns each manager the group of closest customers, starting from the "first", continuing assigning the group of the closest of the remaining customers to the "second" and so on.
IN EVERY CASE the result depends on the order in which the managers are chosen and IN EVERY CASE some clients will not be assigned to their closest.
Of this it is not difficult to give examples.

Hence the following observation makes very limited sense as is limited the position of the problem .

"Also If we asign clients in blocks to each manager, we have the problem that some clients will not be assigned to their closest manager. Using your code (thanks for it) i made changes trying to reduce this issue sorting the whole table first"

If it is a concrete problem, as I imagine it is, try to find the solution that best suits your needs. As a mathematical theoretical problem it is not well posed.

I would like to suggest the following strategy, if you or someone else would like to implement it.
In a given locatlity, for each customer the closest manager is associated.
There will be, in general, an imbalance with respect to the desired balanced distribution. So for managers who have had more customers associated with them than they were entitled to, the extra customers are put into play and the manager is eliminated from subsequent processing. So in the next step, there are surplus customers and underemployed managers.

One proceed with the assignment of unallocated customers to the closest underemployed managers and so on until all the managers are occupied.

i don't know what happend with my last reply. i edited it to remove empty lines and now it deseapeared

Thank you very much for your suggestion.

After struggling trying to figure out how to implement your suggestion (I'm not used to programming anymore) i share the code; also, I think I know why the IDs were duplicated. After sorting the table, Power "optimizes" the table and changes the original order of the IDs,

This is what i mean .

The step Just sorting the data

The same step Using Buffer

Then, when we take the first N records and they have the same distance it seems to choose the same IDs twice,

Anyway Here's the code

FinalAsign =

(Clients as table, Managers as table ) => let

TotalManager = Table.RowCount(Managers),
size = Number.RoundUp(Table.RowCount(Clients)/TotalManager), 

ClientswithDistance =
	List.Accumulate(
		Managers[Manager],
		Clients, 
		(s,c)=>Table.AddColumn(s, c, 
			each distance([latitude],[longitude],
						Managers{[Manager=c]}[latitude],
					 	Managers{[Manager=c]}[longitude]))),

Man=Managers[Manager],

TableProcess = 
	Table.TransformColumns( 
		Table.Group(
			Table.SelectColumns(
				Table.Unpivot(ClientswithDistance,Man,"Manager","Distance")
				,{"ID","Manager","Distance"})
			, "ID", {{"Managerss",each _}})
	, {"Managerss",each Table.Sort(_,"Distance")}),

Asigning = (RestofClients as table, RestofManagers as list) => let 
	  
	Loop = if List.Count(RestofManagers)>1 then
	List.Accumulate(RestofManagers,
		[ 
		Pending = #table( Table.ColumnNames(RestofClients),  {}  ) ,
		Final=#table(List.RemoveItems(Table.ColumnNames(RestofClients),{"Managerss"})&{"Manager", "Distance"},{} ),
		IterManager = RestofManagers
		]
		, (s,c)=>
		[
			ClientsSorted = Table.Buffer(Table.Sort(Table.SelectRows(	
			Table.ExpandRecordColumn(Table.AddColumn(RestofClients,"Ideal",each _[Managerss]{0} ),  "Ideal", {"Manager", "Distance"}, {"Manager", "Distance"}) 	 
			,each  [Manager] = c[Manager])
			,{"Manager", "Distance"}	
			)),
		Dispo = c[Capacity],
		Asigned = Table.Buffer( Table.FirstN(ClientsSorted,Dispo)),		
		NumAsigned = Table.RowCount(Asigned),
		NewCounter = Dispo - NumAsigned,		
		Final = s[Final]&Table.RemoveColumns(Asigned,"Managerss" ),
		Pending = s[Pending] & Table.Skip(Table.SelectColumns(ClientsSorted,{"ID","Managerss"}),NumAsigned),
		IterManager = if NewCounter > 0 then List.Skip(s[IterManager]) &  {[Manager = s[IterManager]{0}[Manager], Capacity = NewCounter]} else  List.Skip(s[IterManager])
		]
	)	
	
	else 
	[	Final=Table.RemoveColumns(
			Table.ExpandRecordColumn(Table.AddColumn(RestofClients,"Ideal",each _[Managerss]{0} ),  "Ideal", {"Manager", "Distance"}, {"Manager", "Distance"}) 	 
			,"Managerss" ),
	Pending = #table( Table.ColumnNames(RestofClients),  {} ) 		
	]
		
	 in if Table.RowCount(Loop[Pending]) > 0 then 
	 	Loop[Final] &
		 @Asigning(Table.TransformColumns(Loop[Pending],{"Managerss",(T_Man)=>Table.Buffer(Table.SelectRows(T_Man,each List.Contains(Table.FromRecords(Loop[IterManager])[Manager], _[Manager] )))}),Loop[IterManager]) 
	 else 
	 	Loop[Final] 

	
in Asigning(TableProcess,List.Transform(Man, each [Manager=_, Capacity=size]))

Another thing that made me struggle was that Powerquery did not correctly calculate the number of records in a table

i had to use Table.Buffer before counting the number of records because otherwise it did not return the correct value

As in my previous code, we only call the function on the grouped tables of both clients and managers

I used this method, and it is faster than my previous ones, although I don't know if it would be faster to recalculate the entire distance table each time the clients to be reassigned instead of affecting the table by removing the records with the saturated managers.

Greetings