google sheets additional custom column in filter

Question

I like to add an additional column in filter formula. This one is working, but not exactly what i need:

=FILTER({detroit!A2:N,"detroit"&row(detroit!A2:A)},detroit!A2:A<>"")

I need only the text "detroit" without the row number, something like this:

=FILTER({detroit!A2:N,"detroit"},detroit!A2:A<>"")

I think i need to use a function for every cell, like row, what gives back nothing?

It also have to be fast to calculate, because of the size of the data.

Any ideas to solve this?

Answer 1

try this where Z column is empty:

=FILTER({detroit!A2:N, detroit!Z2:Z&"detroit"}, detroit!A2:A<>"")

if you dont have any empty column use:

=FILTER({detroit!A2:N, 
 IFERROR(SEQUENCE(ROWS(detroit!A2:A))/0)&"detroit"}, detroit!A2:A<>"")

also you could do:

=QUERY(detroit!A2:N, 
 "select A,B,C,D,E,F,G,H,I,J,K,L,M,N,'detroit' 
  where A is not null 
  label 'detroit'''", 0)