I have a master spreadsheet that contains columns like so:FirstName   LastName   Gender   etc, etc, etcI need to import from that master sheet into another sheet that filters by one of the other columns. In the new sheet, I need name to be a single column, joining FirstName and LastName together. How can I do that? Here is the current query I am using:=query(importrange($SPREADSHEET_KEY, Full Overview!A2:AH), select Col1, Col2, Col5 where Col8 contains 'Migrators', 0)I need to merge Col1 and Col2 from the master sheet into Col1 of the new sheet and put Col5 in Col2 of the new sheet.
Merge two columns by using QUERY in Google Sheets
google spreadsheets
Short answerQUERY select argument can't merge columns.ExplanationThe QUERY built-in function uses Google Visualization API Query Language. It doesn't include a concatenate operator. One alternative is to concatenate the data.ExamplesAssume that First Name and Last Name columns are columns A and B respectively.Example 1Add an auxiliary column to concatenate the desired columns in the source sheet and include this column in the IMPORTRANGE. Add one of the following formulas to an empty cell in the row 2:=A2& &B2. Fill down as necessary. =ARRAYFORMULA(A2:A& &B2:B) (Tip: Delete empty rows or use FILTER to only concatenate non-empty rows).Example 2Use several IMPORTRANGE, the concatenate operator & and arrays.=ARRAYFORMULA(  QUERY(    {      importrange($SPREADSHEET_KEY, Full Overview!A2:A)&       &      importrange($SPREADSHEET_KEY, Full Overview!B2:B)),      importrange($SPREADSHEET_KEY, Full Overview!C2:AH)    },    select Col1, Col2, Col5 where Col8 contains 'Migrators',     0  )) 