06-20-2018 10:34 AM - edited 06-21-2018 09:47 AM
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?
06-21-2018 12:11 PM
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.
06-21-2018 01:49 PM
06-21-2018 02:19 PM - edited 06-21-2018 02:20 PM
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:
06-21-2018 03:43 PM - edited 06-21-2018 03:44 PM
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.
06-21-2018 09:40 PM
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!