Forum Discussion

stenford23's avatar
stenford23
Helper I
4 years ago
Solved

[Bug] Number.Random is not random.

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 

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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.

6 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    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's avatar
      stenford23
      Helper I
      4 years ago

      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's avatar
        v-kkf-msft
        Community Support
        4 years ago

        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.