Want to create a power query to solve water filling problem from Depots to Buckets

[ABCS]

The problem is that there are 2 depots with various sizes of buckets. They have some prefill volume of water.

I have different water allotment of these 2 depots.

The volume utilisation is defined as the amount of water in the bucket divided by the volume of bucket. The goal is to maximise the volume utilised of each bucket given the constraint of water allotment for 2 depots

Here is the input file for bucketID with relationship to DepotID: DepotID BucketID Volumeofbucket PrefillValue Volumeutilised Depot 1 Bucket 1 100 50 50% Depot 1 Bucket 2 1000 600 60% Depot 1 Bucket 3 100 20 20% Depot 1 Bucket 4 10 9 90% Depot 1 Bucket 5 10000 9000 90% Depot 1 Bucket 6 1000 300 30% Depot 1 Bucket 7 100 40 40% Depot 2 Bucket 1 200 55 28% Depot 2 Bucket 2 400 222 56% Depot 2 Bucket 3 50 11 22% Depot 2 Bucket 4 4000 600 15%

This is Depot alloted water values: DepotID AllotedWater Depot 1 900 Depot 2 400

This is the output for Depot1. I will need the output for Depot2 also in the same sheet. Bucket ID Volume of bucket Prefill Value Extra fill from Alloted volume After Fill Volume Volume utilised Bucket 1 100 50 30 80 80% Bucket 2 1000 600 200 800 80% Bucket 3 100 20 60 80 80% Bucket 4 10 9 9 90% Bucket 5 10000 9000 9000 90% Bucket 6 1000 300 500 800 80% Bucket 7 100 40 40 80 80%

e.g. a bucket (call it bucket A) lowest utilisation is at 20%. The bucket with 20% utilisation will be filled first. lets say the next lowest utilisation of bucket (call it bucket B)is at 35%. Then once the bucket A's utilisation is at 35% then bucket A & bucket B will be filled simultaneously till next bucket utilisation is not achieved say Bucket C with 40% utilisation. Then all 3 will be filled, so and so forth till all are 100% or alloted water gets exhausted

[v-pagayam-msft]

Hi @ABCS ,

As we have not received a response from you yet, I would like to confirm whether you have successfully resolved the issue or if you require further assistance. If the issue has been resolved, please mark the helpful reply as a "solution" to indicate that the question has been answered and to assist others in the community.
Thank you for your cooperation. Have a great day.

[v-pagayam-msft]

Hi @ABCS ,
Has the issue been resolved on your end? If so, please share your solution and mark it as "Accept as Solution." This will assist others in the community who are dealing with similar problems and help them find a solution more quickly.
Thank you.

[v-pagayam-msft]

Hi @ABCS ,
Thank you @AlienSx , @ronrsnfld and @lbendlin for the helpful repsonse!
I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution. It would be greatly appreciated by others in the community who may have the same question.
Thank you.

[AlienSx]

let
    fx_spill = (tbl as table) => 
        [
            spill_volume = alloted_water{[DepotID = tbl{0}[DepotID]]}[Alloted Water], 
            rows = List.Buffer(Table.ToList(Table.Sort(tbl, {"Volumeutilized"}), (x) => x)), 
            gen = List.Generate(
                () => [i = 0, vol = rows{0}{2}, filled = rows{0}{3}, pct = (filled + spill_volume) / vol], 
                (x) => x[i] < List.Count(rows) and rows{x[i]}{4} < x[pct], 
                (x) => [i = x[i] + 1, vol = x[vol] + rows{i}{2}, filled = x[filled] + rows{i}{3}, pct = (filled + spill_volume) / vol], 
                (x) => x[[i]] & (if x[pct] >= 1 then [pct = 1] else [pct = x[pct]]) 
            ), 
            last = List.Last(gen), 
            spilled = List.Transform(
                List.Positions(rows), 
                (x) => ((r) => r & (if last = null or x > last[i] then {0, r{4}} else {r{2} * last[pct] - r{3}, last[pct]}))(rows{x})
            )
        ][spilled],
    input_file = Excel.CurrentWorkbook(){[Name="input_file"]}[Content],
    alloted_water = Excel.CurrentWorkbook(){[Name="alloted_water"]}[Content], 
    group = Table.Group(input_file, "DepotID", {"x", fx_spill}), 
    result = Table.FromList(List.Combine(group[x]), (x) => x, Table.ColumnNames(input_file) & {"filled amount", "filled pct"})
in
    result

[ronrsnfld]

Another Option:

Your Depot table:

DepotID	BucketID	Volumeofbucket	PrefillValue
Depot 1	Bucket 1	100	50
Depot 1	Bucket 2	1000	600
Depot 1	Bucket 3	100	20
Depot 1	Bucket 4	10	9
Depot 1	Bucket 5	10000	9000
Depot 1	Bucket 6	1000	300
Depot 1	Bucket 7	100	40
Depot 2	Bucket 1	200	55
Depot 2	Bucket 2	400	222
Depot 2	Bucket 3	50	11
Depot 2	Bucket 4	4000	600

Your Allocation Table:

DepotID	Alloted Water
Depot 1	900
Depot 2	400

• Calculate the maximum possible fill percentage (sum Prefilled Values + Amt allocated to the depot) / sum Bucket Volumes
• Since we cannot empty buckets that are above the maximum fill percentage, we need to remove those buckets from the calculation of the allowable fill percentage.
  • This is done by the custom function below, which recurses
• Once we determine the allowable fill percentage, the calculations are relatively straightforward.

Custom Function

Rename as noted in the comments

//Rename this query:  fnFilterNonAdds

let
    Source = (tbl as table, wtr as number)=>

[a=tbl,
 b=List.Sum(a[Volumeofbucket]),
 c=List.Sum(a[PrefillValue]),
 d=List.Min({1,(c+wtr)/b}),
 e=Table.SelectRows(a,each [PrefillValue] / [Volumeofbucket] < d),
 f=if Table.RowCount(e) < Table.RowCount(a) then fnFilterNonAdds(e,wtr) 
        else Table.AddColumn(e,"Percent",each d)
][f]
in
    Source

Main Code body

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckktyC9RMFTSUXIqTc5OhTANDQyApKmBUqwOFhVGEBUgJWYGONQYw00xwqHCBKwCSFhilzeF2gJWYoDLHjOEW4xxqTGHu8UEWYURqp+NIH42xa7CCKwb7B8jI+xKjCFBBrTLELsCE4gZ8HCLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DepotID = _t, BucketID = _t, Volumeofbucket = _t, PrefillValue = _t]),
   
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"DepotID", type text}, {"BucketID", type text}, {"Volumeofbucket", Int64.Type}, {"PrefillValue", Int64.Type}}),
   
   #"Grouped Rows" = Table.Group(#"Changed Type", {"DepotID"}, {
        {"Fill Buckets", (t)=>
            [a=Table.SelectRows(#"Allocation", each [DepotID]=t[DepotID]{0})[Alloted Water]{0},
             b=fnFilterNonAdds(t,a),
             c=Table.AddColumn(b, "FilledBucket", each [Volumeofbucket]*[Percent]),
             d=Table.AddColumn(c,"AmtToAdd", each [FilledBucket]-[PrefillValue])
            ][d]


             , type table [DepotID=nullable text, BucketID=nullable text, Volumeofbucket=nullable number,
                               PrefillValue=nullable number, Percent=Percentage.Type, 
                               FilledBucket=nullable number, AmtToAdd=nullable number]
        }}),
    #"Expanded Fill Buckets" = Table.ExpandTableColumn(#"Grouped Rows", "Fill Buckets",
        {"BucketID", "Volumeofbucket", "PrefillValue", "Percent", "FilledBucket", "AmtToAdd"}),

    #"Combine with zero fill" = 
        [a=Table.NestedJoin(#"Expanded Fill Buckets",{"DepotID","BucketID"},
                            #"Changed Type",{"DepotID","BucketID"},
                            "Join", JoinKind.RightAnti),
         b = Table.SelectColumns(a,{"Join"}),
         c=Table.ExpandTableColumn(b,"Join",{"DepotID","BucketID","Volumeofbucket","PrefillValue"}),
         d=Table.Combine({#"Expanded Fill Buckets",c})
        ][d],

//We don't need all the columns listed, but they were there for proofing the results
    #"Sorted Rows" = Table.Sort(#"Combine with zero fill",{{"DepotID", Order.Ascending}, {"BucketID", Order.Ascending}}),
    #"Total nonFilled" = Table.ReplaceValue(
        #"Sorted Rows",
        each [FilledBucket],
        each [PrefillValue],
        (x,y,z) as number=>if y=null then z else x,
        {"FilledBucket"}),
    #"Add Percent" = Table.AddColumn(#"Total nonFilled", "Percent Filled", each [FilledBucket] / [Volumeofbucket], Percentage.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Add Percent",{"Percent"})
in
    #"Removed Columns"

Results in PQ:

There are extra columns in the results table for "proofing" but I think this is what you want.

Note that I did not include your initial volume utilization as it is not needed for the calculation.

Some of the column names are different as I initially answered this question on the stackoverflow forum and you had slightly different naming there in your example, but you should be able to edit the code as needed.

[ABCS]

Thanks for the reply.

It seems to work for Depot 1. It has one problem:

DepotID	BucketID	Volumeofbucket	PrefillValue	CurrentValue	Step	VolumeUtilised
Depot 1	Bucket 4	10	9	9	0	90%
Depot 1	Bucket 4	10	9	9	14	90%
Depot 1	Bucket 3	100	20	20	0	20%
Depot 1	Bucket 3	100	20	90	14	90%
Depot 1	Bucket 7	100	40	40	0	40%
Depot 1	Bucket 7	100	40	90	14	90%
Depot 1	Bucket 1	100	50	50	0	50%
Depot 1	Bucket 1	100	50	90	14	90%
Depot 1	Bucket 6	1000	300	300	0	30%
Depot 1	Bucket 6	1000	300	900	14	90%
Depot 1	Bucket 2	1000	600	600	0	60%
Depot 1	Bucket 2	1000	600	600	14	60%
Depot 1	Bucket 5	10000	9000	9000	0	90%
Depot 1	Bucket 5	10000	9000	9000	14	90%

It leaves the bucket 2 at 60& while bucket 6 is filled till 90%. 

Ideally, it should have distributed an equal %ages to both as the available water is limited.

Also, how do I copy it across all depots. 

[lbendlin]

It leaves the bucket 2 at 60& while bucket 6 is filled till 90%. 

As I mentioned before I am not aware of a way to fill them in parallel. Power Query M is a sequential (streaming) language. One at a time.

As you can see Bucket 2 is pulling the short straw as the remaining allotment is insufficient to bring it up to 90% in step 15

Here is the pbix again - the posted code was just a first rough draft.

[lbendlin]

Here is an adjusted version that "works" for both depots

One way to tackle the sequential issue would be to allocate one unit at a time (let's assume you are talking about cubic yards or something).  That would create many more steps (900 steps for Depot 1, for example) but would fill the buckets more evenly.

[lbendlin]

Here is the result when adding one "unit"  at a time.

[lbendlin]

Here is an alternative approximative approach that does only require two iterations.

1. eliminate all buckets that are above the average of (Prefill Value + Allotment ) / Total Volume   (since they cannot be "filled lower").

2.  For the remaining buckets calculate the average of (Prefill Value + Allotment ) / Total Volume again

3. For each bucket calculate (<the value from #2>-[PrefillValue]/[Volumeofbucket])*[Volumeofbucket] + [PrefillValue])

That will get all "lesser"  buckets filled to the same level, plus/minus a rounding error. In the case of Depot1 this would fill all buckets except 4 and 5 to 83%  (4 and 5 are already at 90%).

Would that be an acceptable approach?

[lbendlin]

Do you want just the final result or do you want an animation of the allotment process?

If a smaller and a larger bucket have the same lowest percentage, which one should be filled first?

then bucket A & bucket B will be filled simultaneously

I think this is beyond the capabilities of Power Query. Has to be done sequentially because you need to be able to tell when the allotment runs out.

Here is a first draft that seems to get the result you want for Depot1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckktyC9RMFTSUXIqTc5OhTANDQyApKmBUqwOFhVGEBUgJWYGONQYw00xwqHCBKwCSFhilzeF2gJWYoDLHjOEW4xxqTGHu8UEWYURqp+NIH42xa7CCKwb7B8jI+xKjCFBBrTLELsCE4gZ8HCLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DepotID = _t, BucketID = _t, Volumeofbucket = _t, PrefillValue = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Volumeofbucket", Int64.Type}, {"PrefillValue", Int64.Type}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type", "CurrentValue", each [PrefillValue]),
    #"Added Custom" = Table.AddColumn(#"Added Custom3", "Step", each 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([DepotID] = "Depot 1")),
    NextLevel = (tbl)=>
        let
            #"Added Custom" = Table.AddColumn(Table.Buffer(tbl), "Percent", each [CurrentValue]/[Volumeofbucket],Percentage.Type),
            #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Percent", Order.Ascending},{"Volumeofbucket", Order.Ascending}}),
            #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
            #"Added Custom1" = Table.AddColumn(#"Added Index", "NextHighest Percent", (k)=> List.Min(Table.SelectRows(#"Added Index",each [Percent]>k[Percent])[Percent]) ?? 1,Percentage.Type),
            #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Delta", each if [Index]=0 then [Volumeofbucket]*([NextHighest Percent]-[Percent]) else 0,Int64.Type),
            #"Replaced Value" = Table.ReplaceValue(#"Added Custom2",each [CurrentValue],each [CurrentValue]+[Delta] ,Replacer.ReplaceValue,{"CurrentValue"}),
            #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value",each [Step],each [Step]+1 ,Replacer.ReplaceValue,{"Step"}),
            #"Removed Columns" = Table.RemoveColumns(#"Replaced Value2",{"Index", "Percent", "NextHighest Percent", "Delta"})
        in
            #"Removed Columns",
    Levels = List.Generate(
        () => #"Filtered Rows",
        each List.Sum(_[CurrentValue])<900+List.Sum(#"Filtered Rows"[PrefillValue]),
        each NextLevel(_)
    ),
    #"Converted to Table" = Table.FromList(Levels, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"DepotID", "BucketID", "Volumeofbucket", "PrefillValue", "CurrentValue", "Step"}, {"DepotID", "BucketID", "Volumeofbucket", "PrefillValue", "CurrentValue", "Step"})
in 
    #"Expanded Column1"