Select random record from one table to populate another table

[insightmwarrick]

I have a table of Sales Reps and a table of Customers.  The customers table has one rep per customer (currently blank).  I want to select a sales rep at random from the Sales Reps table to populate the Customer Table Sales Rep field.  There are thousands more customer than there are reps, so a single rep can be assigned to more than one company.  It would be ideal to evenly distribute the reps among the companies if possible, but that's not entirely necessary.

Help!

For example:

Sales Reps Table

Sales Rep

Bob Carlson

Carl Drake

David Rogan

Customers Table

Customer Name	Sales Rep
ACME	Carl Drake
Dunder Mifflin	David Rogan
Stark Industries	Bob Carlson
Wonka	David Rogan
Duff	Carl Drake
Sterlin Cooper	David Rogan
Weyland Corp	Bob Carlson
Initech	Carl Drake

[AlexisOlson · Accepted solution]

If you want a nice even distribution, then randomly sort your Customer table, add an index mod N, and merge with the Sales Reps table (which needs an index too).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcuxCoAgFEDRXxHnfiKswcGpoUEcJJ8kylOeOvT3ibUe7tWar0Lt3Cyabx0dEFPB+xRw0tEsRSbR9dooQJ14Zoz2P7z/O6DxMJFzAfoqeJJFN4jKBImhwXVzY14=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Name" = _t]),
    #"Added Custom" = Table.Buffer(Table.AddColumn(Source, "Random", each Number.Random(), type number)),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Random", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.Mod(_, Table.RowCount(SalesReps)), Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Integer-Divided Column", {"Index"}, SalesReps, {"Index"}, "SalesReps", JoinKind.LeftOuter),
    #"Expanded SalesReps" = Table.ExpandTableColumn(#"Merged Queries", "SalesReps", {"Sales Rep"}, {"Sales Rep"})
in
    #"Expanded SalesReps"

If you just want random and care less about an even distribution, then you can generate an index for each row separately without any sorting going on.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcuxCoAgFEDRXxHnfiKswcGpoUEcJJ8kylOeOvT3ibUe7tWar0Lt3Cyabx0dEFPB+xRw0tEsRSbR9dooQJ14Zoz2P7z/O6DxMJFzAfoqeJJFN4jKBImhwXVzY14=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Name" = _t]),
    #"Added Custom" = Table.Buffer(Table.AddColumn(Source, "Index", each Number.RoundDown(Number.RandomBetween(0, Table.RowCount(SalesReps))), Int64.Type)),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Index"}, SalesReps, {"Index"}, "SalesReps", JoinKind.LeftOuter),
    #"Expanded SalesReps" = Table.ExpandTableColumn(#"Merged Queries", "SalesReps", {"Sales Rep"}, {"Sales Rep"})
in
    #"Expanded SalesReps"

[wdx223_Daniel]

you can sort CustomerTable by a random base, then split the table to the rows number of Reps.

[insightmwarrick]

That may be a solution for half the problem.  But how do I select reps at random to fill the customers table?  Example?