Forum Discussion

curiouspbix0's avatar
curiouspbix0
Helper IV
6 years ago
Solved

Pass input parameters across queries off different DB

Hello Power Query Experts.

 

How could I pass a list of input values (eg - City) into a SQL query A running off a System A from an output of another query B running off a different database like Oracle or Sharepoint ?

 

Need Help
Tips and Tricks
  • dax's avatar
    dax
    6 years ago

    Hi curiouspbix0 , 

    I need to check requirement to you, did you want to filter data from SQL by parameter(p1), then use SQL's result as parameter value(p2) to filter data from sharepoint, right? You could use parameter(p1) like below to filter multiple value in SQL

    let
    Source = Sql.Database("servername", "newsql", [Query="select *  from t#(lf) where id in (SELECT        Value
                               FROM            dbo.Split_String('"&Parameter&"', ',') AS Split_String_1)"])
in
    Source

    Then you could click one of SQL result column "Add a new query" and use this in another parameter(p2) in sharepoint. But I can't filter sharepoint dynamically, the p2 value can't change dynamic, it still based on p1's current value in edit queries windows instead of dynamic value when you change in parameter tool.  I am not sure whether this is my design problem or this is by default. 

    By the way, I suggest you could @ super users (@Imkef,@Greg_Deckler and @edhans) for more suggestions.

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi curiouspbix0 , 

    I need to check requirement to you, did you want to filter data from SQL by parameter(p1), then use SQL's result as parameter value(p2) to filter data from sharepoint, right? You could use parameter(p1) like below to filter multiple value in SQL

    let
    Source = Sql.Database("servername", "newsql", [Query="select *  from t#(lf) where id in (SELECT        Value
                               FROM            dbo.Split_String('"&Parameter&"', ',') AS Split_String_1)"])
in
    Source

    Then you could click one of SQL result column "Add a new query" and use this in another parameter(p2) in sharepoint. But I can't filter sharepoint dynamically, the p2 value can't change dynamic, it still based on p1's current value in edit queries windows instead of dynamic value when you change in parameter tool.  I am not sure whether this is my design problem or this is by default. 

    By the way, I suggest you could @ super users (@Imkef,@Greg_Deckler and @edhans) for more suggestions.

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.