cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Asamadi
Helper I
Helper I

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

I found the solution: 

If i add Index Column the formula works correct.

View solution in original post

6 REPLIES 6
MarcelBeug
Community Champion
Community Champion

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)
smpa01
Resident Rockstar
Resident Rockstar

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.


New Animated Dashboard: Sales Calendar


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)

I found the solution: 

If i add Index Column the formula works correct.

View solution in original post

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

rayOC
Regular Visitor

do you have the code you used?

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors