Forum Discussion
Equitable distribution by shorter distance - Geolocation
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.
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