Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
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.
@Greg_Deckler, It worked, I merged it using a full outer join and expanded the new table.
Awesome!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |