Forum Discussion
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 narrowed it down to the DATE parameter. Below I've given an example of the SQL statement with 2 parameters:
= Sql.Database("SERVER", "DATABASE", [Query="SELECT * FROM [TABLENAME] WHERE clientParent_ID='"&Number.ToText(#"Client Parent ID")&"' AND dayDate>='"&Date.ToText(Date.From(#"Enter a Calendar Start Date"),"yyyy-MM-dd")&" 00:00:00'"])
The Client Parent ID parameter is a Number. The Enter Calendar Start Date parameter is a Date.
The research I'd done said I need to convert the Date parameter into text and reformat it from dd-MM-yyyy to yyyy-MM-dd so it can work in the SQL statement. Is this correct?
If I run this query in the Power BI Service without the Date parameter in the statement, it runs without any issue. But for some reason passing the Date parameter through the statement makes it fall over. I've reached out to the Power BI Support team but they've so far not been able to help me.
Any ideas on how I can do this? ImkeF ?
Thanks,
MarkJames
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
6 Replies
Hi Anonymous ,
In your scenario, I think you need to check the SQL Server error log to find what has been transferred to SQL Server and what has caused this problem, the query is finally converted to a string before transferring to SQL Server, you will need to check which query can work normally in SQL Server first.
Best Regards,
Teige
Anonymous Hi ,
what does the error message in the service say?
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
Hi Anonymous ,
the step "AutoRemovedColumns1" is causing the trouble:
Check out this article how to tackle the problem: https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/
