none
A Tricky "Dare You" RandBetween functionality RRS feed

  • 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


    Wednesday, May 17, 2017 10:09 PM

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.


    Thursday, May 18, 2017 7:41 PM
  • 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


    Friday, June 9, 2017 3:31 AM

All replies

  • Hi Natasha,

    Could you explain why the number has to be random, and not just simply an index? Is it because you need the number to change on each refresh?

    Thursday, May 18, 2017 1:23 AM
  • Hi Colin, 

    That's the requirement of the project. 

    Thanks

    Thursday, May 18, 2017 1:05 PM
  • 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.


    Thursday, May 18, 2017 7:41 PM
  • 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


    Friday, June 9, 2017 3:31 AM