average and max of grouped sum by two columns when one column is a range

[saeidrasti]

I have a dataset with 5 columns as follows. bucket range is from 1 to 96. bucket_start and bucket_end show the range of valid buckets for given row. For example, first row is reapeated for buckets 1,2, and 3.  First, i want to group data by two columns day and bucket and sum over values. Second, I want to calculate average and max of sums over buckets. I do not want to expand the table by reapeating rows for baucket ranges.

I would appreciate if you can help me to figure this out. 

unit_num	day	value	start_bucket	end_bucket
1	6/10/2023	10	1	3
1	6/10/2023	5	2	4
2	6/11/2023	8	1	2
Day	bucket	Sum of value
6/10/2023	1	10
6/10/2023	2	15
6/10/2023	3	15
6/10/2023	4	5
6/11/2023	1	8
6/11/2023	2	8
bucket	average	max
1	9	10
2	11.5	15
3	15	15
4	5	5

[ManuelBolz · Accepted solution]

Hello @saeidrasti,

If my post helped you, please give me a 👍kudos and mark this post with Accept as Solution.

Replace the first "SOURCE STEP" with your Source Step:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLTNzTQNzIwMgayDQ1ABBAbK8XqYEqbArEREJuAZY0gsoYwWQuoXiOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [unit_num = _t, day = _t, value = _t, start_bucket = _t, end_bucket = _t]),
    Type = Table.TransformColumnTypes(Source,{{"value", Int64.Type}, {"unit_num", Int64.Type}, {"day", type date}, {"start_bucket", Int64.Type}, {"end_bucket", Int64.Type}}),

    BucketList = Table.AddColumn(Type, "BucketList", each List.Numbers([start_bucket], [end_bucket] - [start_bucket] + 1)),
    ExpandBucketList = Table.ExpandListColumn(BucketList, "BucketList"),
    Type2 = Table.TransformColumnTypes(ExpandBucketList,{{"BucketList", Int64.Type}}),
    GroupedBucketList = Table.Group(Type2, {"day", "BucketList"}, {{"Sum of value", each List.Sum([value]), type number}}),
    GroupedByBucket = Table.Group(GroupedBucketList, {"BucketList"}, {
        {"average", each List.Average([Sum of value]), type number},
        {"max", each List.Max([Sum of value]), type number}
    })
in
    GroupedByBucket

Best regards from Germany
Manuel Bolz

🟦Follow me on LinkedIn
🟨How to Get Your Question Answered Quickly
🟩Fabric Community Conference
🟪My Solutions on Github