Equitable distribution by shorter distance - Geolocation

Greg_Deckler- Thank you very much for your fast response, at the moment I require to be able to make the assignment / distribution in power query .

If I can't find another alternative, I would appreciate your help to make the process in DAX

Greetings

an aesthetic change, to make the control of the end of the cycle less brutal.

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