Forum Discussion

MP-iCONN's avatar
MP-iCONN
Icon for Resolver I rankResolver 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_Da...
Need Help
smpa01's avatar
smpa01
Icon for Community Champion rankCommunity 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
Icon for Resolver I rankResolver 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
    Icon for Community Champion rankCommunity 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.