1

I'am working on a Monte Carlo visualisation in PowerBI. So far i was able to perform the Monte Carlo simulation, but i'am stuck on creating the visualisation.

I created a table with two columns, iteration and result, which can have any number of rows (let's say 1.000 for the example). I want to display the result in a bar graph of about 20 bars, however, since the result can be any number i have to group the results on 20 ranges, which i will display in the graph. I have all of this working, i get a nice table with a name(which is a textual representation of the range), the min value, max value, and a count of how many iterations are within this range.

But then i noticed the total count of iterations does not match 1.000, I checked why this could be, and i noticed multiple variables seemed to be recalculated. So i created some test code to see what happened:

    tableMinGevolg = Record.Field(Table.Min(MonteCarlo_runs, "gevolgTotaal"), "gevolgTotaal"),
    test1 = tableMinGevolg,
    test2 = tableMinGevolg,
    test3 = tableMinGevolg,

Running this code can/will result in 3 different values for the test variables. So i went digging and discovered that Power Query M uses lazy evaluation for Record and Table expressions. Since Monte Carlo simulations uses randomness i assume this causes my problem. Everytime the table is referenced the values are evaluated and the randomness can create different results, which messes with my results.

I would like to know if it is possible to force eager evaluation on a variable so i can be sure the Monte Carlo simulation is not performed again while i'am calculating these ranges?

ABos
  • 56
  • 1
  • 1
  • 7

1 Answers1

1

Try using the Table.Buffer function to load the table into memory.

tableMinGevolg = Table.Buffer(Record.Field(Table.Min(MonteCarlo_runs, "gevolgTotaal"), "gevolgTotaal"))
Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • Using Table.Buffer does not seem to work in this case. I finally got around to trying this solution, and tried it in multiple ways, but i still get different results for the test variables. The line of code won't work, since it requires a Table and a Record is given. But using Table.Buffer on MonteCarlo_runs doesn't solve the problem either. Also, creating a new variable MCRuns = Table.Buffer(MonteCarlo_runs) and referencing this doesn't work. – ABos Feb 01 '19 at 11:28
  • Hmm. How is your MonteCarlo calculated? Is there anywhere you can set a seed? – Alexis Olson Feb 04 '19 at 15:06
  • I already learned Number.Random() can't be seeded, and one of the funny things of Power Query is that it's smart enough to remember function calls with the same parameters, and if it is called again it won't rerun the function but just use the same result. Which is really terrible for the Number.Random() function (all calls have the same random number). I solved this by using Number.RandomBetween(0, [id])/[id]. – ABos Feb 15 '19 at 11:36
  • Until now i was unable to solve my original problem, but i did find a workaround. The visualisation part of Power BI is able to create the bar graph for me, and as far as i can tell this does include all the iterations, so my own calculation to create this is no longer necessary. – ABos Feb 15 '19 at 11:39
  • `Number.Random()` does evaluate unique values now, it was updated. Previously you had add an index column. -- "buffer does not seem to work": [Table.Buffer is shallow copy](https://learn.microsoft.com/en-us/powerquery-m/table-buffer) – ninMonkey Aug 31 '22 at 23:39