Power Query Grouped Totals offset

[Dicken]

Hi, 
 If I  do a group total by year  so ; 

= Table.Group(Source, {"Year"}, {{"YT", each List.Sum([Unit]), type nullable number}})

 and then aslo want  example the previous year as another column to compare I can just add a column to this ; 

 Table.AddColumn(#"Grouped Rows", "Custom", (A) => 
let result= 
        List.Last(
            Table.SelectRows(#"Grouped Rows", (B) => B[Year] < A[Year])[YT]
        ) in if result = null then "NA" else result) 

But is is possibel to do this all withingthe Grouped Rows aggregartion? 
I have tried adding a column within the group but it's not right

 Table.Group(  StartTable, { "Year"}, { {"ThisYear",each List.Sum([Unit])  } , 
{"All",  each   Table.AddColumn(  StartTable, "N", (A)=> 
List.Sum( 
  Table.SelectRows( StartTable, (B)=> B[Year] < A[Year] )[Unit] ) ) [N]    }} )

so Im trying to end up with somthing like this; 

Any adivice on how to proceed welcome, 

Richard. 

[Dicken]

  Thanks, to be honest I did not think I could, but  you never know, 
Thank you for taking the time to reply .

Richard. 

[v-junyant-msft · Accepted solution]

Hi @Dicken ,

I'm sorry you can't do that. Because in Power Query you need to specify an operation object for each step of the transformation, for example, the object specified in the Addcolumn step you mentioned here is the result of the previous Group by step.

But if you want to combine the Group by and Addcolumn steps, it means that your Addcolumn step converts the specified operation object to itself, and you can't refer the result from the first half of the step to the second half in Power Query, so the Addcolumn won't be able to find the specified operation object, and the result will definitely be error.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.