Answered by:
A Tricky "Dare You" RandBetween functionality
Question

Hello,
So, I was wondering if it is possible to generate "random between" values keeping the same random values based on certain values in another column.
For example, as shown below, I am trying to generate a list of randombetween values in the RandBetweenColumn but the curve ball is that random values must be the same for duplicate numbers found in the Order Number Column
Number.RandBetween(1, 10)Any ideas?
Thanks
Answers

Here are two options  The first provides static positions, but the code is simpler:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], AddedCustom = Table.AddColumn(Source, "Position", each List.PositionOf(Source[Order Number], [Order Number]) + 1) in AddedCustom
For the random column, you can't use Number.RandomBetween because it doesn't generate whole numbers. What you need therefore is a list of numbers between 1 and 10 and a randomizer for the list. The following achieves the required goal:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], RandomSortedList = List.Buffer(List.Sort({1..10}, each Number.Random())), AddedCustom = Table.AddColumn(Source, "Random", each RandomSortedList{List.PositionOf(Source[Order Number], [Order Number])}) in AddedCustom
Please note that for the above code to work, the list that you're randomizing cannot have fewer items than the total rows in the table. For example, your sample table has 10 rows, so your random list must span at least from 1 to 10, or any combination of 10 numbers.
 Edited by Colin Banfield Thursday, May 18, 2017 7:51 PM
 Proposed as answer by Ehren  MSFTMicrosoft employee, Owner Tuesday, May 23, 2017 11:41 PM
 Marked as answer by Imke FeldmannMVP, Moderator Sunday, June 25, 2017 5:40 AM

Something along these lines should work:
let Source = (your table), Rollup = Table.Group(Source, "Order Number"), AddedRandomToRollup = Table.AddColumns(Rollup, "RandBetweenColumn", each Number.RoundDown(Number.Random()*10+1)), MergedSourceAndRollup = Table.NestedJoin(Source, {"Order Number"}, AddedRandomToRollup, {"Order Number"}, "RandTable", JoinKind.LeftOuter) in MergedSourceAndRollup
Then all you have to do is expand it and be on your merry way. :)
Apologies for any typos, or code errors. I'm on my tablet.
C
 Proposed as answer by Chris Dutch Friday, June 9, 2017 3:33 AM
 Edited by Chris Dutch Friday, June 9, 2017 4:18 AM
 Marked as answer by Imke FeldmannMVP, Moderator Sunday, June 25, 2017 5:40 AM
All replies



Here are two options  The first provides static positions, but the code is simpler:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], AddedCustom = Table.AddColumn(Source, "Position", each List.PositionOf(Source[Order Number], [Order Number]) + 1) in AddedCustom
For the random column, you can't use Number.RandomBetween because it doesn't generate whole numbers. What you need therefore is a list of numbers between 1 and 10 and a randomizer for the list. The following achieves the required goal:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], RandomSortedList = List.Buffer(List.Sort({1..10}, each Number.Random())), AddedCustom = Table.AddColumn(Source, "Random", each RandomSortedList{List.PositionOf(Source[Order Number], [Order Number])}) in AddedCustom
Please note that for the above code to work, the list that you're randomizing cannot have fewer items than the total rows in the table. For example, your sample table has 10 rows, so your random list must span at least from 1 to 10, or any combination of 10 numbers.
 Edited by Colin Banfield Thursday, May 18, 2017 7:51 PM
 Proposed as answer by Ehren  MSFTMicrosoft employee, Owner Tuesday, May 23, 2017 11:41 PM
 Marked as answer by Imke FeldmannMVP, Moderator Sunday, June 25, 2017 5:40 AM

Something along these lines should work:
let Source = (your table), Rollup = Table.Group(Source, "Order Number"), AddedRandomToRollup = Table.AddColumns(Rollup, "RandBetweenColumn", each Number.RoundDown(Number.Random()*10+1)), MergedSourceAndRollup = Table.NestedJoin(Source, {"Order Number"}, AddedRandomToRollup, {"Order Number"}, "RandTable", JoinKind.LeftOuter) in MergedSourceAndRollup
Then all you have to do is expand it and be on your merry way. :)
Apologies for any typos, or code errors. I'm on my tablet.
C
 Proposed as answer by Chris Dutch Friday, June 9, 2017 3:33 AM
 Edited by Chris Dutch Friday, June 9, 2017 4:18 AM
 Marked as answer by Imke FeldmannMVP, Moderator Sunday, June 25, 2017 5:40 AM