Forum Discussion

MP-iCONN's avatar
MP-iCONN
Resolver I
4 years ago

Pass date range in SQL Statement - Power Query

We are migrating away from a system called Chartio that allowed us to pass parameters (filtered date ranges) through to our SQL Statements.  

 

Chartio SQL Statement:

 

The above Last_30_Days is a control filter which you by default is whatever date range you enter in that filter.  It then passes it through to this above SQL Statement.

 

I am wondering if there is a way to do that in Power Query?

 

 

Thank you for any help on this.

 

Chad

 

Need Help

3 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    MP-iCONN  answered a similar question here 

     

    TLDR You can passs on a values to SQL dynamically lke following

     

    SELECT x, y, z FROM myTable WHERE dateColumn < '" &myValue& "

or

let
    Source = Sql.Database("serverName", "dbName", [Query="select * from tbl where dateColumn<'"&Table[C1]{0}&"'"])
in
    Source

     

    • MP-iCONN's avatar
      MP-iCONN
      Resolver I
      4 years ago

      But can you use some sort of filter that the user can enter in the report (like a slicer type, date range)?

       

      This way the user can select instead of the 30 day default range I put in the query a different date range.

       

      Thank you.

      • smpa01's avatar
        smpa01
        Community Champion
        4 years ago

        MP-iCONN you can create an excel in a sharepoint location for the user to provide the input for the filter and query that sharepoint excel in your report for that value to filter the sql query.

         

        Else you can get all the data from the server and create a dax slicer and letvthe input from the slicer to slice the table.