none
Random key column in Power Query doesn't transfer to PowerPivot RRS feed

  • Question

  • Greetings, all, 

    I'm in a bit of a quandary. I have a large data set that I want to take a random slice of. M has a function Number.Random() that appears to be just the thing, so I added a column to my Power Query and it looks great. I see unique values for each row like this:  

    0.030501688
    0.354649675
    0.524276507

    Perfect. However, when I close and load to the Power Pivot table, there is one single value in the column repeated for every row of the table:

    0.434930301
    0.434930301
    0.434930301


    I've pivoted the table, and sure enough, there's only a single value in that column. Any idea what's going on here, or how to get the unique values to populate in the Power Pivot table? 

    Thanks. 


    George W. (different George W.)

    Monday, October 21, 2019 2:55 PM

Answers

  • I can't speak to the details of it, but I think the general idea is that part of PQ's optimizations make it process the function only once because it's the "same" function every time. To get it to recalculate the function needs to be "different" every time. I don't know if you can do this with Number.Random() because it doesn't have any inputs, but you can do it with Number.RandomBetween(). So here you just add and remove the same number from another field in the data (an added Index column in this example) and it forces the function to be evaluated uniquely each time.

    Number.RandomBetween(0+[Index]-[Index],1)

    Also this is Marcel Beug's solution, I just remembered reading about it in a random post before here:
    https://social.technet.microsoft.com/Forums/en-US/81ca82f3-fa9f-4e49-935b-33b42b257e41/random-number-being-changed-to-the-same-number-for-all-rows?forum=powerquery

    • Marked as answer by G Dubya Monday, October 21, 2019 4:49 PM
    Monday, October 21, 2019 4:04 PM

All replies

  • I can't speak to the details of it, but I think the general idea is that part of PQ's optimizations make it process the function only once because it's the "same" function every time. To get it to recalculate the function needs to be "different" every time. I don't know if you can do this with Number.Random() because it doesn't have any inputs, but you can do it with Number.RandomBetween(). So here you just add and remove the same number from another field in the data (an added Index column in this example) and it forces the function to be evaluated uniquely each time.

    Number.RandomBetween(0+[Index]-[Index],1)

    Also this is Marcel Beug's solution, I just remembered reading about it in a random post before here:
    https://social.technet.microsoft.com/Forums/en-US/81ca82f3-fa9f-4e49-935b-33b42b257e41/random-number-being-changed-to-the-same-number-for-all-rows?forum=powerquery

    • Marked as answer by G Dubya Monday, October 21, 2019 4:49 PM
    Monday, October 21, 2019 4:04 PM
  • Hi George,

    Interesting. And if we create two random columns with Number.Random function there will be uniform values in each column:

    

    To solve this problem you may use Table.Buffer (for small tables) or something like this:

    let
        Source = #table(1, List.Zip({{1..5}})),
        add = Table.AddColumn(Source, "random", each [a = 0, b = Replacer.ReplaceValue(a,a,Number.Random())][b])
    in
        add
    Monday, October 21, 2019 4:04 PM
  • Thanks for the quick reply, Mark. This works like a charm! 

    George W. (different George W.)

    Monday, October 21, 2019 4:50 PM
  • Thanks for the quick reply Aleksei. I went with Mark's solution because it seams easier to understand. 

    George W. (different George W.)

    Monday, October 21, 2019 4:51 PM