Adding a new column with row text for each vstack array

[benoj2005]

Hopefully a nice an easy one but I'm not sure what the right approach is to add a new column to vstacks that dosen't exist in the source data and then to add row text to this new column to show the source of each vstack array.

Simple example in the attached where I've done column I manually

Thankyou

[windknife]

One Way,

=VSTACK({"Area","Sales","Extra field to show source"},HSTACK(ExpData1,IF(ExpData1[Area]<>"","ExpData1")),HSTACK(ExpData2,IF(ExpData2[Area]<>"","ExpData2")))

[zbor]

Or add one more column with Table name:

[benoj2005]

Perfect thanks windknife.

Trying to avoid adding a manual helper column to all the real source data but good idea zbor

[windknife]

You are welcome.

[benoj2005]

is it possible to take the existing header array and add on a manual field rather than listing out all the headers manually? Tables 24 columns in reality to just trying to work out if theres a syntax to do this

[windknife]

Try this,

=VSTACK(HSTACK(ExpData1[#Headers],"Extra field to show source"),HSTACK(ExpData1,IF(ExpData1[Area]<>"","ExpData1")),HSTACK(ExpData2,IF(ExpData2[Area]<>"","ExpData2")))

[RhiSJ]

Try this formula,
it would expand extra 1 column with table name on the right, and VSTACK 2 expanded tables.

=LET(
AA, EXPAND(TableA,, COLUMNS(TableA)+1, OFFSET(TableA[#Headers],-1,0,1,1)),
BB, EXPAND(TableB,, COLUMNS(TableB)+1, OFFSET(TableB[#Headers],-1,0,1,1)),
VSTACK(HSTACK(TableA[#Headers],"Source"), AA,BB) )

[AliGW]

Remove the solved tag if you need further assistance.

[benoj2005]

thanks both