Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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"
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.
That may be a solution for half the problem. But how do I select reps at random to fill the customers table? Example?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |