powerquery - Parse Error on formula. Can someone assist? - Stack Overflow

I keep getting the following error: "Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered "<QUOTED_ID> "COL3" at line 1, column 16. Was expecting one of: "("..."("...

Here's the formula I'm using. I've verified that the number of columns and column headers match for both sheets. What I'm attempting to do is pull in data from MarketInstalls1 and then pull in data from TeamInstalls if the same data doesn't exist on MarketInstalls1

=QUERY({MarketInstalls1!A:K; TeamInstalls!A:K}, "SELECT * WHERE Col3 NOT IN (SELECT Col3 FROM MarketInstalls1!!A:K)",1)

I keep getting the following error: "Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered "<QUOTED_ID> "COL3" at line 1, column 16. Was expecting one of: "("..."("...

Here's the formula I'm using. I've verified that the number of columns and column headers match for both sheets. What I'm attempting to do is pull in data from MarketInstalls1 and then pull in data from TeamInstalls if the same data doesn't exist on MarketInstalls1

=QUERY({MarketInstalls1!A:K; TeamInstalls!A:K}, "SELECT * WHERE Col3 NOT IN (SELECT Col3 FROM MarketInstalls1!!A:K)",1)

• powerquery

Share Improve this question Follow asked Jan 11 at 15:23 Erik LillyErik Lilly 1 2

• Also, this formula was copied directly from a Google search and just updated with the correct sheet names and columns – Erik Lilly Commented Jan 11 at 17:31
• Can you share the link? – Anonymous Commented Jan 12 at 20:36

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

As the error message says:
It expected only one ”(“ in the query expression.

You may want to change the keyword/tag as well:
This looks like Google Sheets’ QUERY function (not related to MS PowerQuery)

It supports some basic SQL query functions but not all. Especially, nested queries are not supported. And the FROM clause has been eliminated from the language as well.

Feel free to check for more details here: https://support.google.com/docs/answer/3093343?hl=en

There are different possible approaches, e.g.:

• Instead of NOT IN (SELECT …) you may want to use NOT MATCHES () and “inject” the UNIQUE() rows of that column of interest as a regex string with TEXTJOIN().

• Another way with query is to use GROUP BY Col3 and e.g. MAX each of the other columns in the SELECT statement/string

• Alternatively to the query you could also use the FILTER function.
  See, e.g., here: What is the function for filtering data that is not in a list in google sheets?