How to turn randbetween results into a static column in Power Query?

[RMDNA]

I need to fabricate data in a table, so I created a number.randombetween() custom column in Power Query, which gives me what I need. However, doing anything after creates duplicate values. I'd like to lock down the initial values and turn them into a static column.

Is there a Power Query equivalent of Excel's "take only the values and ignore the source formula" to achieve that?

[Anonymous]

As a workarand 😁, try one of these (duplicate the query to see how it works) :

let

tab=Table.FromList(
{"gen",
"feb",
"mar",
"apr",
"mag",
"giu",
"lug",
"ago",
"set",
"ott",
"nov",
"dic",
"gen",
"feb",
"mar",
"apr",
"mag",
"giu",
"lug",
"ago",
"set",
"ott",
"nov",
"dic"
},null,{"Column1"}),

Origine = tab,
#"Aggiunta colonna indice" = Table.AddIndexColumn(Origine, "Indice", 1, 1),
#"Aggiunta colonna personalizzata" = Table.AddColumn(#"Aggiunta colonna indice", "RandNoSeed", each List.Random(1){0}),
#"Aggiunta colonna personalizzata1" = Table.AddColumn(#"Aggiunta colonna personalizzata", "RandWithRowSeed", each List.Random(1,[Indice]){0}),
#"Aggiunta colonna personalizzata2" = Table.AddColumn(#"Aggiunta colonna personalizzata1", "RandWithTabSeed", each List.Random(1,1){0})
in
#"Aggiunta colonna personalizzata2"

[Anonymous]

Here a function to call to get rando number between low and high

let FrozendRandBetween=(low as number, high as number, optional seed) => 

    let
        FrRndBtw=low+List.Random(1,seed){0}*(high-low)
    in
        FrRndBtw
in
    FrozendRandBetween

here some example on how to call the function:

1. giving a column seed (unique seed for overall list)

List.Accumulate({1..20},{},(s,c)=> s&{Number.Round(#"RandBetween"(3, 7,1))})

1. giving a RowSeed (different seed for each list element)

List.Accumulate({1..20},{},(s,c)=> s&{Number.Round(RandBetween(3, 7,c))})

A function to get  frequences of occurrence

let
getFreqListElement= ( lista as list) => 
    let 
      TextList=List.Transform(lista, each Text.From(_)),
      piuno=(n) =>  [ n=n+1] [n],
      freq=List.Accumulate(TextList,[],(s,c)=>if Record.HasFields(s,c) then Record.TransformFields(s, {c,piuno}) else Record.AddField(s,c,1))           
    in 
      freq
in
getFreqListElement

 

[RMDNA]

Bump - tried a few things, but the table's too large to export to excel (so I can't add a RANDBETWEEN and reimport), and I can't add it to the source.

Add as New Query, or any other step, makes all values identical. This was initially solved with an index, but I can't figure it out now.

[OwenAuger]

Hi @RMDNA

See the discussion here for some different options.

These worked for me:

Immediately after the Number.RandomBetween step, either:

1. Add a step =Table.Buffer(PreviousStep)
2. Or add an index column (which you can then remove)

Regards,

Owen

[RMDNA]

Hi @OwenAuger,

Both steps were unsuccessful. Below is the table before any addtional steps. All I need is for Plan_Fab to become static.

Table.Buffer gives me the following:

The first index column was needed to get randbetween to work in the first place. Adding a second index column refreshes the data, which is what I'm trying to avoid:

[OwenAuger]

Hmm...

Just re-confirming, your original issue was that you were getting the same random number repeated on every row wasn't it?

Not sure why Table.Buffer returned a text value rather than the buffered table. Could you post your complete M code?

Also, as far as I know there's no way of preventing the random numbers changing when a preview refresh is triggered. i.e. there's no way to save a particular instance of the random numbers while in the Power Query editor. But Once the table is loaded to the data model, it should be stable until the next data model refresh.

I'll just call in some M specialists to see if they can help @ImkeF @MarcelBeug ?

Regards,

Owen

[ImkeF]

Can confirim @OwenAuger s statements: Thats all you can do today. There is no way to freeze the random numbers in the query editor. 

So a workaround could be to export to csv using R and then re-import or use R to generate the random numbers itself.