Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Last 12 Months Column in Power Query

Let's say I have this fake data:   Reference Date Component Monthly Removals 09/01/2021 A 1 12/01/2021 A 2 03/01/2022 A 5 05/01/2022 A 0 04/01/2022 B 3 08/01/2022 ...
Need Help
  • ppm1's avatar
    ppm1
    3 years ago

    Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc9LDsAgCATQu7A2cUBt7LK9hvH+1xA/qZbFhMVLBiiFcHuwFwiTo0fDI9UVYjEkmjAIYZEsSpo8KRnCpnjQO8q+wmyo74zzDBjCJjbU5/U/IxyF+ldt", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Reference Date" = _t, Component = _t, #"Monthly Removals" = _t, #"Removals L12M" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reference Date", type date}, {"Component", type text}, {"Monthly Removals", Int64.Type}, {"Removals L12M", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let
thisdate = [Reference Date],
thiscomponent = [Component],
prev12mrows = Table.SelectRows(#"Changed Type", each ([Component] = thiscomponent and ([Reference Date] <= thisdate and [Reference Date] >= Date.AddMonths(thisdate, -12)))),
sum = List.Sum(prev12mrows[Monthly Removals])
in 
sum)
in
    #"Added Custom"

    Pat

jnfl's avatar
jnfl
New Member
1 year ago

I have a similar need, but can't seem to implement ppm1 's code.  In power query, I need to create a new column (l12modap) that, for all ids with at least 12 months of dap data, will sum the last 12 months of said data. 

 

I have the first 4 columns and would like to see the 5th, ei.e., column E:

 

 ABCDE 
1iddatemodapl12modap 
210016/1/202210 <= 12 = (blank)
310017/1/202220 <= 12 = (blank)
410018/1/202231 <= 12 = (blank)
510019/1/202240 <= 12 = (blank)
6100110/1/202250 <= 12 = (blank)
7100111/1/202260 <= 12 = (blank)
8100112/1/202270 <= 12 = (blank)
910011/1/202380 <= 12 = (blank)
1010012/1/202390 <= 12 = (blank)
1110013/1/2023100 <= 12 = (blank)
1210014/1/2023110 <= 12 = (blank)
1310015/1/2023121 <= 12 = (blank)
1410016/1/20231302=SUM(D2:D13)
1510017/1/20231402=SUM(D3:D14)
1610018/1/20231502=SUM(D4:D15)
1710019/1/20231601=SUM(D5:D16)
18100110/1/20231701=SUM(D6:D17)
19100111/1/20231801=SUM(D7:D18)
20100112/1/20231901=SUM(D8:D19)
2110011/1/20242001=SUM(D9:D20)
2210012/1/20242101=SUM(D10:D21)
2310013/1/20242201=SUM(D11:D22)
2410014/1/20242301=SUM(D12:D23)
2510015/1/20242401=SUM(D13:D24)
2610016/1/20242500=SUM(D14:D25)
2710017/1/20242600=SUM(D15:D26)
2810025/1/201810 <= 12 = (blank)
2910026/1/201820 <= 12 = (blank)
3010027/1/201830 <= 12 = (blank)
3110028/1/201840 <= 12 = (blank)
3210029/1/201850 <= 12 = (blank)
33100210/1/201860 <= 12 = (blank)
34100211/1/201870 <= 12 = (blank)
35100212/1/201880 <= 12 = (blank)
3610021/1/201990 <= 12 = (blank)
3710022/1/2019101 <= 12 = (blank)
3810023/1/2019110 <= 12 = (blank)
3910024/1/2019121 <= 12 = (blank)
4010025/1/20191302=SUM(D28:D39)
4110026/1/20191402=SUM(D29:D40)
4210027/1/20191502=SUM(D30:D41)
43100312/1/202010 <= 12 = (blank)
4410031/1/202120 <= 12 = (blank)
4510032/1/202130 <= 12 = (blank)
4610033/1/202140 <= 12 = (blank)
4710034/1/202150 <= 12 = (blank)
4810035/1/202160 <= 12 = (blank)
4910036/1/202170 <= 12 = (blank)
5010037/1/202180 <= 12 = (blank)
5110038/1/202190 <= 12 = (blank)
5210039/1/2021100 <= 12 = (blank)
53100310/1/2021110 <= 12 = (blank)
54100311/1/2021121 <= 12 = (blank)
55100312/1/20211301=SUM(D43:D54)
5610031/1/20221401=SUM(D44:D55)
5710032/1/20221501=SUM(D45:D56)
5810033/1/20221601=SUM(D46:D57)
5910034/1/20221701=SUM(D47:D58)
6010035/1/20221801=SUM(D48:D59)

 

ppm1, if you have any advice for this, that'd be terrific.  I should also say that when I tried to use your code before, I kept getting the error message that "We cannot convert a value of type Table to type list."  So you can see that I didn't get very far.  Thanks!

  • jgeddes's avatar
    jgeddes
    Super User
    1 year ago

    jnfl This should have been created as a new topic with a reference to any previous similar solutions.
    That being said, you can add a column to your existing query with the following code...

    = Table.AddColumn(#"Changed Type", "last12dap", each if [mo] > 12 then List.Sum(Table.SelectRows(#"Changed Type", (x)=> x[id]= [id] and x[date] >= Date.AddMonths([date], -12))[dap]) else null, type number)

    Any references to #"Changed Type" should be replaced with the previous step in your query.

    • jnfl's avatar
      jnfl
      New Member
      1 year ago

      Oh, wow--this is terrific.  Thank you!  I haven't completed testing it (my dataset is very large), but the first time I ran it, I realized that there were numbers greater than 12 (which shouldn't happen if it's only summing the 12 months prior to that date).  So, I added another part to the Table.SelectRows function so that it now reads,

       

      = Table.AddColumn(#"Changed Type", "last12ddelq", each if [month] > 12 then List.Sum(Table.SelectRows(#"Changed Type", (x)=> x[loan_id]= [loan_id] and x[date] >= Date.AddMonths([date], -12) and x[date]< [date])[delinquency_b]) else null, type number)

       

      I'll post again if/when it completes and hopefully works.  Thanks, jgeddes!  (And duly noted on the new thread--thanks.)