Forum Discussion
Select random record from one table to populate another table
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 |
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"
3 Replies
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"you can sort CustomerTable by a random base, then split the table to the rows number of Reps.
