Forum Discussion
Power Query Grouping *-1
Good Morning,
Thanks in advance for your assistance!
I have a grouping in PowerQuery but would like to also multiple the result of the grouping by -1. Traditionally I would add a custom column after grouping to do so and then remove the original. Is there a way to do this during the same step as the grouping? Would likely speed up performance...
Here is my grouping code, where you'll notice I've named some of the resulting aggregates with the negative sign:
= Table.Group(#"dbo_DATABASE$Value Entry", {"Item Ledger Entry No_"}, {{"Sales Amount", each List.Sum([#"Sales Amount (Actual)"]), type number}, {"-Cost Amount (Actual)", each List.Sum([#"Cost Amount (Actual)"]), type number}, {"-Cost Amount (Expected)", each List.Sum([#"Cost Amount (Expected)"]), type number}})
Thanks
Michael
You can manually edit that step in the formula by and put -1 * in it as follows:
= Table.Group(#"dbo_DATABASE$Value Entry", {"Item Ledger Entry No_"}, {{"Sales Amount", each List.Sum([#"Sales Amount (Actual)"]), type number}, {"-Cost Amount (Actual)", each -1 * List.Sum([#"Cost Amount (Actual)"]), type number}, {"-Cost Amount (Expected)", each -1 * List.Sum([#"Cost Amount (Expected)"]), type number}})
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
2 Replies
You can manually edit that step in the formula by and put -1 * in it as follows:
= Table.Group(#"dbo_DATABASE$Value Entry", {"Item Ledger Entry No_"}, {{"Sales Amount", each List.Sum([#"Sales Amount (Actual)"]), type number}, {"-Cost Amount (Actual)", each -1 * List.Sum([#"Cost Amount (Actual)"]), type number}, {"-Cost Amount (Expected)", each -1 * List.Sum([#"Cost Amount (Expected)"]), type number}})
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat