Forum Discussion

Dicken's avatar
Dicken
Post Prodigy
2 years ago
Solved

Power Query Grouped Totals offset

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. 

 

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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.

  • Dicken's avatar
    Dicken
    Post Prodigy

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

    Richard.