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

ImkeF's avatar
ImkeF
Community Champion
7 years ago

Anonymous Hi ,

what does the error message in the service say?

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi ImkeF ,

     

    I've attached an image of the error I get:

     

     

     

     

     

     

    This is the last step in the first query of my report. I have attempted a number of refreshes. Eventually the error messges changes to this:

     

     

     

    Thanks,

    MarkJames

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi ImkeF ,

         

        There's no step in my query called AutoRemovedColumns - could the Power BI Service be introducing this?

        Also, if the issue the scheduled refresh not happening because I'm passing a Date parameter through the SQL in the PBIX file, would the technique in the link you've provided even work? I'll still be passing the parameter through a query at the start - splitting out the query would happen after the SQL statement.

         

        MarkJames