SOLVED

Monetary unit sampling - Power Query

New Contributor

Hello community,

 

I would very much appreciate any help. I'm trying to create dynamic MUS report (monetary unit sampling). I need to filter each line in Excel (in attachment as Source table) that exceeds certain amount (Criteria) of accumulated value (in example Accumulation) and create a report only with this lines (in attachment as Report table). Is there a way to do it? So I need to filter all lines for which accumulation exceeds 150 in my case.MUS example.png

4 Replies
best response confirmed by TomF1212 (New Contributor)
Solution

@TomF1212 

accumulation 150.JPG

You can try the attached file which seems to return the expected result.

Thank you very much! Will try on my database.

@Quadruple_Pawn 

 

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)

 

indexitemfrom tochosen to sample random variable2
13=J3 ( random variable)=E4 (from)+D4 (item)-1=AND($J$4>=E4;$J$4<=F4) Sampling Interwal5
22

=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)   
34=IF(G5;1;F5+1)=E6+D6-1=AND($J$4>=E6;$J$4<=F6)   
41=IF(G6;1;F6+1)=E7+D7-1=AND($J$4>=E7;$J$4<=F7)   
54etcetcetc   
61      
72      
85      

 

 

and below is result in excel

 

indexitemfrom tochosen to sample random variable2
1324FALSE Sampling Interwal5
2256TRUE   
3414FALSE   
4155TRUE   
5414FALSE   
6155TRUE   
7212FALSE   
8537TRUE   

 

 

@Chris1979 

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 Ecolumn Fcolumn Gcolumn Hcolumn Icolumn Jcolumn Kcolumn L
indexitemfrom tochosen to sample random variable2
1324FALSE Sampling Interwal5
2256TRUE   
3414FALSE   
4155TRUE   
5414FALSE   
6155TRUE   
7212FALSE   
8537TRUE