Forum Discussion

Antoinette123's avatar
Antoinette123
Icon for Helper I rankHelper I
2 years ago

Sum of every 2 weeks

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

Need Help

3 Replies

  • Antoinette123's avatar
    Antoinette123
    Icon for Helper I rankHelper I
    2 years ago
    Table 1  
    idvalue 1value 2I need =>idvalue
    car; week 016 car; week 3average(3;4;5;2;8;3;2;8)
    car; week 134 car; week 4average(8;3;2;8;1;1;9;9)
    car; week 152 byke; week 3average(7;6;5;2;8;3;1;4)
    car; week 283 byke; week 4average(8;3;1;4;1;1;9;9)
    car; week 228   
    car; week 311   
    car; week 399   
    car; week 443   
    car; week 422   
    byke; week 004   
    byke; week 176   
    byke; week 152   
    byke; week 283   
    byke; week 214   
    byke; week 311   
    byke; week 399   
    byke; week 443   
    byke; week 422   
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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

  • ronrsnfld's avatar
    ronrsnfld
    Icon for Super User rankSuper User
    2 years ago

    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