Forum Discussion

Joshua_symons's avatar
Joshua_symons
Regular Visitor
3 years ago

Inserting a list of value into a Snowflake query

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

General Comment
Need Help
Show and Tell
Tips and Tricks

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago
    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

    • Muniz_Felipe's avatar
      Muniz_Felipe
      Frequent Visitor
      2 months ago

      Hi.

       

      I applied this solution to get values from one query and input into a Snowflake query and it worked perfectly. It even updated on Power BI service.


      Below is how I created the string to concatenate in my Snowflake query. Don't forget the single quoter ( ' ) at the beginning and the end of each value of the list.

      String to be concatenated into the SQL query (named as aux_query in my example).

      let
    Source = fact_Table[Customer_number],
    Removed_duplicates = List.Distinct(Source),
    Transform_list = List.Transform( Removed_duplicates , each "'" & _ & "'" ),
    Combine_elements_into_single_string = Text.Combine( Transform_list ,  "," )
in
    Combine_elements_into_single_string

       

      SQL query

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

in
    Source