Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Passing a Date Parameter Through SQL Statement - Refreshing in the Service

Hi,   I have a number of parameters passing through SQL statements which refresh without any issue in Power BI Desktop. But when I schedule a refresh in the Power BI Service it falls over. I've nar...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi,

     

    I decided to split out the Date parameter into 3 separate number parameters, then pass these through the SQL, as follows:

     

    = Sql.Database("SERVER", "DATABASE", [Query="SELECT * FROM [TABLE] WHERE clientParent_ID='"&Number.ToText(#"Client Parent ID")&"' AND weekStartDate>='"&Number.ToText(#"Day (Number)")&"/"&Number.ToText(#"Month (Number)")&"/"&Number.ToText(Year)&"'"])

     

    I scheduled a refresh of this and it worked without any issues.

     

    So now, when someone opens my report, they see the following:

     

     

    Which then lets then amend the dates for IDs for all queries in the report without having to go into Power Query to do this manually.

     

    Thanks,

    MarkJames