[Bug] Number.Random is not random.

[stenford23]

Hi PBPQ team,

I found a strange behavious which seems a bug to me.

Number.Random doesn't work in the correct way when used inside Table.Group or Table.TransformColumns.

- Expected behaviour:

Two table with column random_number containing different random value

- Real behaviour:

Two table with a column random_number containing the same value

Until the last update i never see this behaviour and Number.Random was working correctly.

The following link contains the sample pbix to reproduce the bug

Dropbox to example Pbix 

[v-kkf-msft]

Hi @stenford23 ,

I looked up the documentation and found that this problem seems to persist, when using Number.Random() to build transformations, at some point PQ will replace all numbers with same value.

There are several ways to solve this problem discussed in these documents.

Random Number being changed to the same number for all rows 

How to get a random sample of data with Power Query 

Random Number Generation in Power BI 

One way is to use the List.Random function, i.e. the following code:

= Table.Group(
        Source, "Column",
        {{"random_number", each List.Random(Table.RowCount(_)){0}, type number}}
    )

= Table.TransformColumns(grouped_table, {"random_number", each _, type number})

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[stenford23]

Hi @v-kkf-msft 

Thanks for the reply

what about Table.TransformColumns?

you use only an each _ referring to previous step results.

[v-kkf-msft · Accepted solution]

Hi @stenford23 ,

How about this approach? Use List.Random(1) to get random number, then use the Table.ExpandListColumn function to expand the list.

= Table.ExpandListColumn(Table.TransformColumns(grouped_table, {"random_number", each List.Random(1), type number}),"random_number")

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[stenford23]

Hi @v-kkf-msft 

yes that's perfect.

Why Number.Random isn't working but List.Random is working as expected?

Shouldn't both command be idempotent and return same generated number?

[lbendlin]

Both are calculated once.

[lbendlin]

You can raise an issue at https://issues.powerbi.com . If you have a Pro license you can consider raising a Pro ticket at https://powerbi.microsoft.com/en-us/support/pro/