Reply
Highlighted
Senior Member
Posts: 336
Registered: ‎09-14-2016

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

[ Edited ]

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?

Senior Member
Posts: 336
Registered: ‎09-14-2016

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

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.

New Contributor
Posts: 607
Registered: ‎02-29-2016

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

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

Senior Member
Posts: 336
Registered: ‎09-14-2016

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

[ Edited ]

Hi @OwenAuger,

 

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

 

good.PNG

 

Table.Buffer gives me the following:

 

Buffer error.PNG

 

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:

 

2nd index.PNG

New Contributor
Posts: 607
Registered: ‎02-29-2016

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

[ Edited ]

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

Super User
Posts: 1,564
Registered: ‎09-06-2015

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

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. 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries