Sum of every 2 weeks

[Antoinette123]

I have a column with id, it has repeated items and columns with values. Data are like week 1, week 1, week 2, week 2, etc. I need to create a table where for week 3 I have an average value between 2 whole previous weeks (week 1, week 1 week 2, week 2), for week 4 I have an average value between week 2, week 2, week 3, week 3 from the original table etc. How can I do it in Power Query.

 The data is in the comment below

[ronrsnfld]

This should do it, although the result also includes Week 2 as it will average Weeks 0 and 1.

Depending on what you want to show, you can filter on the nulls, and you could also eliminate Week 1 with a minor code change.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY85DoAgEEWvQqgtWF3iUQiFGio6G+PtZQsyA8Vv3s8szxh6HfdOHuc8YXSiPGSmdmp5ZDJEDbgOEYiLwNY00/PcQS7LXT7gWwrkKv3S71dlf/7nfL1rxFgVaIp4danGqNCjVdANFXx0A9qh4tdrCuiHiiJoPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, #"value 1" = _t, #"value 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"value 1", Int64.Type}, {"value 2", Int64.Type}}),
    
//Group by id and return a list of all the values
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {
        {"Values", each [value 1] & [value 2],type {Int64.Type}}
        }),

//Split of the type (car or byke) to group on this
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "id Type", each List.First(Text.Split([id],";")), type text),

//Create a list of the rolling two week average, and add it as a colum
    #"Grouped Rows1" = Table.Group(#"Added Custom", {"id Type"}, {
        {"2 wk avg", (t)=>
            let 
                RA = List.Generate(
                    ()=>[ra=null, idx=1],
                    each [idx] <= Table.RowCount(t),
                    each[ra=if [idx]<2 then null
                            else List.Average((t{[idx]-1}[Values] & t{[idx]-2}[Values])), idx=[idx]+1],
                    each [ra])
            in Table.FromColumns(
                Table.ToColumns(t)
                & {RA}, {"id","a","b","value"}
            ), type table [id=nullable text, a=any, b=any,value=nullable number]}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows1",{"id Type"}),
    #"Expanded 2 wk avg" = Table.ExpandTableColumn(#"Removed Columns", "2 wk avg", {"id", "value"})
in
    #"Expanded 2 wk avg"

Results from your data

[Antoinette123]

Table 1
id	value 1	value 2	I need =>	id	value
car; week 0	1	6		car; week 3	average(3;4;5;2;8;3;2;8)
car; week 1	3	4		car; week 4	average(8;3;2;8;1;1;9;9)
car; week 1	5	2		byke; week 3	average(7;6;5;2;8;3;1;4)
car; week 2	8	3		byke; week 4	average(8;3;1;4;1;1;9;9)
car; week 2	2	8
car; week 3	1	1
car; week 3	9	9
car; week 4	4	3
car; week 4	2	2
byke; week 0	0	4
byke; week 1	7	6
byke; week 1	5	2
byke; week 2	8	3
byke; week 2	1	4
byke; week 3	1	1
byke; week 3	9	9
byke; week 4	4	3
byke; week 4	2	2

[Anonymous]

Hi @Antoinette123 ,

Did ronrsnfld's answer help? If so please help mark it as a solution, this time increase the weight when searching for similar questions, thus helping more people, thanks~

Best Regards,
Gao
Community Support Team