cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Asamadi Regular Visitor
Regular Visitor

Custom Column Randbetween

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Asamadi Regular Visitor
Regular Visitor

Re: Custom Column Randbetween

I found the solution: 

If i add Index Column the formula works correct.

6 REPLIES 6
MarcelBeug Super Contributor
Super Contributor

Re: Custom Column Randbetween

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.

 

Specializing in Power Query Formula Language (M)
MarcelBeug Super Contributor
Super Contributor

Re: Custom Column Randbetween

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"
Specializing in Power Query Formula Language (M)
Asamadi Regular Visitor
Regular Visitor

Re: Custom Column Randbetween

I found the solution: 

If i add Index Column the formula works correct.

rayOC Frequent Visitor
Frequent Visitor

Re: Custom Column Randbetween

do you have the code you used?

smpa01 Established Member
Established Member

Re: Custom Column Randbetween

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.

bspillar Frequent Visitor
Frequent Visitor

Re: Custom Column Randbetween

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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 105 members 1,831 guests
Please welcome our newest community members: