Monetary unit sampling - Power Query

[TomF1212]

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.

[#2 · Accepted solution]

@TomF1212 

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

[#3]

Thank you very much! Will try on my database.

[#4]

@OliverScheurich 

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

[#5]

@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 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