Custom Column Randbetween

[Asamadi]

I want to Create a Custom column ( Query Editor Environment) and generate random Numbers between for example 500 to 1000. 

I have tried this formula: Number.RandomBetween(500,1000)  

 but it creates same value in all cells in Column.

How can i do this?

[MarcelBeug]

In such cases I would use another number column, like in:

let
    Source = Table.FromColumns({List.Numbers(0,20,1)},type table[Number = Int64.Type]),
    #"Added Custom" = Table.AddColumn(Source, "Random", each Number.RandomBetween(500+[Number]-[Number],1000))
in
    #"Added Custom"

The part [Number]-[Number] forces recalculation of the random number for each row. You can use any number column for this.

[smpa01]

Hi @MarcelBeug,

I used your method here http://community.powerbi.com/t5/Desktop/Randomly-assiginng-a-specific-value-to-one-of-the-rows/m-p/3....

Raw Data - https://drive.google.com/open?id=1osjur8vQtLnW5UL4_E-17D2FKFpmsPjQj2_v61rlPkk

Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Email", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each 1),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Number.RandomBetween(1+[Index]-[Index],100000)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", Int64.Type}}),
    #"Inserted Division" = Table.AddColumn(#"Changed Type1", "Division", each [Custom.1] / 100, type number),
    #"Grouped Rows" = Table.Group(#"Inserted Division", {"Custom"}, {{"Max", each List.Max([Division]), type number}, {"AD", each _, type table}}),
    #"Expanded AD" = Table.ExpandTableColumn(#"Grouped Rows", "AD", {"Email", "Index", "Division"}, {"AD.Email", "AD.Index", "AD.Division"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded AD", "Custom.1", each if [AD.Division]=[Max] then 2 else 1)
in
    #"Added Custom2"

Now, once the code performs the randomization for the first time in step - Added Custom1, I do not want it recalculate in following steps as I intend to do my follow up calculation based on the "Custom.1 " column value derived in Added Custom1. 

Is there a way to do that?

Thank you in advance.

[MarcelBeug]

Alternatively and similarly you can create a (temporary) column with the lower limit value in each row.

let
    Source = Table.FromColumns({List.Numbers(500,20,0)},type table[NumberFrom = Int64.Type]),
    #"Added Custom" = Table.AddColumn(Source, "Random", each Number.RandomBetween([NumberFrom],1000))
in
    #"Added Custom"

[Asamadi · Accepted solution]

I found the solution: 

If i add Index Column the formula works correct.

[bspillar]

Adding the index prior to the rand function works. Thank you!

[rayOC]

do you have the code you used?