Unable to connect to snowflake in power bi

Question

Syntax written works in Snowflake but doesn't work when I connect to snowflake in power bi

Details: "ODBC: ERROR [42000] SQL compilation error: syntax error line 4 at position 4 unexpected 'set'. syntax error line 7 at position 0 unexpected ')'."

below is the code I wrote

set IP_EVENT_TYPE_ID = '*';
    select CriticalityLevelDescription, CriticalityLevelID, Department, DeviationID, EventTypeID
    from QUALITY_DEVIATIONS.CV_0346_SJ_QIMS_DEVIATIONS

Answer 1

The query would look something like this:

let 
 sql = "set IP_EVENT_TYPE_ID = '*';
    select CriticalityLevelDescription, CriticalityLevelID, Department, DeviationID, EventTypeID
    from QUALITY_DEVIATIONS.CV_0346_SJ_QIMS_DEVIATIONS",
 source = Snowflake.Databases("<locator>.snowflakecomputing.com","<warehouse_name>"){[Name="<database_name>"]}[Data],
    data = Value.NativeQuery(source, sql, null, [EnableFolding=false])
in
  data