Forum Discussion

Joshua_symons's avatar
Joshua_symons
Regular Visitor
2 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...
General Comment
Need Help
Show and Tell
Tips and Tricks
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 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
    1 month 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