January 13, 2023

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

Trusted Members

Moderators

Power BI

January 31, 2022

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

Trusted Members

Moderators

Power BI

January 31, 2022

Trusted Members

Moderators

Power BI

January 31, 2022

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.

1 Guest(s)