Hi wil1john,
First, note that in the query editor, the random numbers are generated even after removing Table.Buffer from #"Added Custom." In fact, Table.Buffer slows down the recalculation of the random numbers. There are two reasons why buffering makes no
difference in this case:
1) The table is constructed literally, i.e. it is not coming from an external source. Therefore, the entire table is already "fixed" in memory, so buffering is not required.
2) Wrapping Table.Buffer after the random numbers are already generated (in the inner function) doesn't make sense. If the original source was external, then it would make sense to buffer the table in a step before adding the random number column.
In any case, as you indicated, after loading to the data model, the random numbers from the query editor are all fixed to the same value. I am not sure why this is happening.
The technique that I described in a previous post works, but only to a point. Modifying the code to work in your case, we end up with:
let
Source = #table({"A"}, List.Transform({1..10000}, each {_})),
AddedIndex = Table.AddIndexColumn(Source, "Index", 0, 1),
RandBetweenList = List.Transform(List.Random(Table.RowCount(Source)), each Number.Round(_ * (30-1) + 1)),
AddedCustom = Table.AddColumn(AddedIndex, "RandomNumber", each RandBetweenList{[Index]}),
RemovedIndex = Table.RemoveColumns(AddedCustom,{"Index"})
in
RemovedIndex
The technique works up to 10,000 rows order of magnitude. Note that with just these many rows, you will get a lot of duplicated "random" numbers because there are only 30 values to allocate. Loading to the data model with 100,000 rows is very,
very slow, and loading 1,000,000 rows is out of the question. I believe that the random numbers are being recalculated over and over as each row is loaded, which would explain the slow loading process.
The moral of the story is that you use random numbers in Power Query if you need to perform a calculation based on a random value, or if you need a random sample of your source data(sort on the random column and select the top n rows). Otherwise, you are
better off creating a random number calculated column in the data model using DAX.
