SOLVED

# Monetary unit sampling - Power Query

Copper Contributor

# Monetary unit sampling - Power Query

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.

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

# Re: Monetary unit sampling - Power Query

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

# Re: Monetary unit sampling - Power Query

Thank you very much! Will try on my database.

# Re: 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.

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

# Re: Monetary unit sampling - Power Query

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