Forum Discussion
Monetary unit sampling - Power Query
Hello,
I have a similar problem, how to do a MUS report (monetary unit sampling) in power query.
however, I have to add a random variable.
therefore I would like to prepare the report in a different way. Below I present how I did it in EXCEL.
please help me translate this into POWER QUERY.
thank you in advance
Formulas in EXCEL (sorry that I didn't paste image, but I don't know how to do it on this blog)
| index | item | from | to | chosen to sample | random variable | 2 | |
| 1 | 3 | =J3 ( random variable) | =E4 (from)+D4 (item)-1 | =AND($J$4>=E4;$J$4<=F4) | Sampling Interwal | 5 | |
| 2 | 2 | =IF(G4;1;F4+1) (if above sample is chosen then start from 1 else start from previous (to))
| =E5+D5-1 | =AND($J$4>=E5;$J$4<=F5) | |||
| 3 | 4 | =IF(G5;1;F5+1) | =E6+D6-1 | =AND($J$4>=E6;$J$4<=F6) | |||
| 4 | 1 | =IF(G6;1;F6+1) | =E7+D7-1 | =AND($J$4>=E7;$J$4<=F7) | |||
| 5 | 4 | etc | etc | etc | |||
| 6 | 1 | ||||||
| 7 | 2 | ||||||
| 8 | 5 |
and below is result in excel
| index | item | from | to | chosen to sample | random variable | 2 | |
| 1 | 3 | 2 | 4 | FALSE | Sampling Interwal | 5 | |
| 2 | 2 | 5 | 6 | TRUE | |||
| 3 | 4 | 1 | 4 | FALSE | |||
| 4 | 1 | 5 | 5 | TRUE | |||
| 5 | 4 | 1 | 4 | FALSE | |||
| 6 | 1 | 5 | 5 | TRUE | |||
| 7 | 2 | 1 | 2 | FALSE | |||
| 8 | 5 | 3 | 7 | TRUE |
Unfortunately i can't help you with this. Maybe you want to start a new discussion. There are several experts who might be able to help you with a Power Query solution. And could you add the column names to your tables like in the example below:
column E column F column G column H column I column J column K column L index item from to chosen to sample random variable 2 1 3 2 4 FALSE Sampling Interwal 5 2 2 5 6 TRUE 3 4 1 4 FALSE 4 1 5 5 TRUE 5 4 1 4 FALSE 6 1 5 5 TRUE 7 2 1 2 FALSE 8 5 3 7 TRUE