Forum Discussion
How to subtract columns values from two different tables using power query?
Hello,
I have two tables:
| Income | | Expenses |
-------- ---------
| Date | | Date |
| Value | | Value |
Using M, I want to create a new table, like this:
| Difference |
-----------
| Date |
| Value |
Where the Difference's Value is the Income's Value - Expenses's Value where Income's Date is equal to Expenses's Date.
One problem is:
A date may not contain a revenue; A date may not contain a expense.
This is something similar to my data:
How can I calculate the diference of incomes and expenses even when there is no incomes or expenses in date? In other words, all date's incomes should be added to it and all date's expenses should be subtracted from it.
Thanks.
Hi, Greg_Deckler, I tried your solution, but I realized that I have a bigger problem, so I edited my question with the new problem.
I believe that you would want to do a group by on Date for both of your Income and Expenses tables and then do your Merge as described.
5 Replies
You would do a Merge query based on Date columns, create a calculated column to do the subtraction and then remove the Income Values and Expenses Values columns.
let Source = Table.NestedJoin(Income,{"Date"},Expenses,{"Date"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", {"Value"}, {"NewColumn.Value"}), #"Added Custom" = Table.AddColumn(#"Expanded NewColumn", "Net", each [Value] - [NewColumn.Value]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value", "NewColumn.Value"}) in #"Removed Columns"Hi, Greg_Deckler, I tried your solution, but I realized that I have a bigger problem, so I edited my question with the new problem.
I believe that you would want to do a group by on Date for both of your Income and Expenses tables and then do your Merge as described.