Log In
Register
Home
January 13, 2023
Offline
I have 2 tables, both have the same format, the data in each table is added by someone else. Each table has the same headings
Due Action Debit/Credit Balance where the Balance column is a calculation of Debit/Credit + previous Balance (=F??+[@[Debit/Credit]]).
To combine the tables I use the following function =SORT(VSTACK(Table1,Table2),1,1,FALSE). This works fine except that the Balance column is not a calculation with the new table but of each individual table. Is there any way to get the Balance to be the sum of the Debit/Credit of the combined table?
In advance, thanks for your help
See Answer
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
Hi Sam,
As you have noticed, VSTACK (as does HSTACK) doesn't keep the formulas. It merely stacks the arrays as they are. You'd have to add a few extra steps. Since you have VSTACK, you have other DA functions at your disposal as well. The formula looks like this.
=LET(
stck, SORT(VSTACK(Table1,Table2),1,1,FALSE),
data, DROP(stck,,-1),
dc, TAKE(data,,-1),
rt, MMULT(--(SEQUENCE(COUNT(dc))>=SEQUENCE(,COUNT(dc))),dc),
result, HSTACK(data,rt),
result
)
In words, what this does is:
1) VSTACK both tables and SORT them (stck, that's your original formula)
2) DROP the last column (data)
3) TAKE the D/C column (dc)
4) Calculate the running total for D/C (rt)
5) HSTACK step 2 and 4 (result)
Step 4 is a subject by itself and you would need to study the MMULT function if you are not familiar with it.
The attached workbook contains a working example and I trust you are able to apply the same technique in your own. I'm open to hear about more effective ways to do this, as I can't think of any other solution just now.
Answers Post
Trusted Members
Moderators
November 1, 2018
Offline
You could use SCAN for the running total:
rt, SCAN(0,dc,LAMBDA(a,b,a+b))
The following users say thank you to Velouria for this useful post:
Mynda TreacyTrusted Members
Moderators
Power BI
January 31, 2022
Offline
Good point! Thx.
@Velouria
January 13, 2023
Offline
Thank you both, the solution works perfectly. Now I have to understand what you did 
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
The best way to analyze/understand a LET function is to see what every single step does. When you take my formula, replace the last word "return" with "stck" and press enter.
Now you see what the first step produces. That's your initial formula, stacking and sorting the two arrays.
Next, put "data" in as the last word (i.e. variable) of the formula. You'll see the stck-array with the last column removed.
Do that for the other variables as well, and see what each step does.
January 13, 2023
Offline
Once again, thank you, great tip for someone learning LET
1 Guest(s)
