Forum Discussion

Asamadi's avatar
Asamadi
Helper I
9 years ago
Solved

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?

Need Help
  • Asamadi's avatar
    Asamadi
    9 years ago

    I found the solution: 

    If i add Index Column the formula works correct.

6 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    9 years ago

    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.

     

    • MarcelBeug's avatar
      MarcelBeug
      Community Champion
      9 years ago

      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's avatar
        Asamadi
        Helper I
        9 years ago

        I found the solution: 

        If i add Index Column the formula works correct.

    • smpa01's avatar
      smpa01
      Community Champion
      8 years ago

      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/362627#M163811.

       

      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.