Inserting a list of value into a Snowflake query

[Joshua_symons]

Hi,

I currently have a list of values which dynamically change either in value or length of list. E.g.

Kjn23409u

Nskjnfwo123

Slmdfwo10239

I then have another query which filters an extremely large database (Over 10 million rows) based on the above list of values.

SELECT *

FROM X_Database

WHERE ITEM_ID in (‘Kjn23409u’,’ Nskjnfwo123’,’ Slmdfwo10239’)

I currently have to update the list of values in the SQL whenever the original list updates, however I would like to have this done automatically using power query.

My question is it possible to place the first list of values generated from a query from an excel file into the snowflake sql query using the advance editor. E.g. Create a variable which reference another query (that is a list of value) and then place the variable in to the sql query.

Current Query

let

    Source = Value.NativeQuery(Snowflake.Databases("Warehouse","Warehouse"){[Name="Database"]}[Data], " SELECT * FROM X_Database WHERE ITEM_ID in (‘Kjn23409u’,’ Nskjnfwo123’,’ Slmdfwo10239’)” , null, [EnableFolding=true]),

in

    #"Table"

Idea

Let

    VAR Item_id_list = query1

    Source = Value.NativeQuery(Snowflake.Databases("Warehouse","Warehouse"){[Name="Database"]}[Data], " SELECT * FROM X_Database WHERE ITEM_ID in Item_id_list" null, [EnableFolding=true]),

in

    #"Table"

Thank you in advance

[lbendlin]

an extremely large database (Over 10 million rows) 

In Power BI we call this "medium sized".

My question is it possible to place the first list of values generated from a query from an excel file into the snowflake sql query using the advance editor. 

Technically possible but you will need to test if your code still works in the service refresh (if applicable)

Your "idea"  is only halfway viable. 

let

    Item_id_list = Text.Combine(query1,"','"),

    Source = Value.NativeQuery(Snowflake.Databases("Warehouse","Warehouse"){[Name="Database"]}[Data], " SELECT * FROM X_Database WHERE ITEM_ID in ('" & Item_id_list & "')", null, [EnableFolding=true]),

in

    Source