Hi Gabriel,
Thanks for your answer and the Loom video - I didn’t realise table mirroring was so feature rich.
Yes I definitely have a query filter - I was just pasting a shortened version of my formula 
My concern with the ability to rename columns afterwards and it being smart, is that for someone else working on the workbook it’s not obvious how the table column naming is/was done/mapped.
Providing column names within the formula would make it clear either using SQL style aliases, or as a seperate argument.
Alternatively I could turn off table headers within SI.QUERY, and then specify them manually, but not the cleanest approach.
I think I originally went with TOTABLE because the output table shrunk if there were no results and so it would shift cells on the right leftwards. My more complicated formula which after testing won’t be needed. (Note the IFERROR to handle the shrinking table issue I bumped into. And I found I had to specify every output column too to make it more stable)
=TOTABLE(
IFERROR(
SI.QUERY(Connection_Name, "GLDETAIL", TX_FILTER, TX_COLUMNS, TRUE, HSTACK({"Sort"}, {"BOOKID,ITEMID,EMPLOYEEID,LOCATIONID,DEPARTMENTID"})),
VSTACK({"ITEMID", "EMPLOYEEID", "LOCATIONID", "DEPARTMENTID", "BOOKID", "BASECURR", "SUM(AMOUNT)"})
),
,
TRUE,
$Q$9, $R$9, $S$9, $T$9, $U$9, $V$9, $W$9
)
The new formula I’m going with is:
=SI.QUERY(Connection_Name, "GLDETAIL", TX_FILTER, TX_Columns, TRUE, HSTACK({"Sort"},{"BOOKID,ITEMID,EMPLOYEEID,LOCATIONID,DEPARTMENTID"}), $Q$9, $R$9, $S$9, $T$9, $U$9, $V$9, $W$9)
Do you see any issues with that?
Also, currently if there’s no output I get my columns but the formula cell shows #N/A.
Can that error message be changed to “No results” or is that an excel limitation?
Kind regards
Ben