How to indicate the source sheet when merging multiple sheets into one

Question

There is a lot of tutorials that show how to merge multiple sheets, so basically there is 2 formulas:

={FILTER({sheet#1-range},LEN({sheet#1-range-first-column})>0);
FILTER({sheet#2-range},LEN({sheet#2-range-first-column})>0);...}

or

=QUERY({{sheet#1-range};{sheet#2-range};...,"Select * where Col1 is not null")

Both producing a result sheet but it is not possible to indicate the data provided by a specific sheet. What I want is the result sheet to contain a field that have a name of the source sheet for each row.

Is it possible to achieve by modifying any of the formulas?

Answer 1

Try something like:

=QUERY(
        {{sheet#1-range,index(substitute(sheet#1-range,sheet#1-range,"sheet1"))};
         {sheet#2-range,index(substitute(sheet#2-range,sheet#2-range,"sheet2"))};
         ...,
    "Select * where Col1 is not null")