Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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)

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.

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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.

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

do you have the code you used?

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.