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

[Anonymous]

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

[ImkeF]

@Anonymous Hi ,

what does the error message in the service say?

[Anonymous]

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

[ImkeF]

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/ 

[Anonymous]

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

[Anonymous · Accepted solution]

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

[TeigeGao]

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