cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MarkJames Member
Member

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

1 ACCEPTED SOLUTION

Accepted Solutions
MarkJames Member
Member

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

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:

 

01.PNG

 

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

View solution in original post

6 REPLIES 6
Community Support Team
Community Support Team

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

Hi @MarkJames ,

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

Super User III
Super User III

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

@MarkJames Hi ,

what does the error message in the service say?

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




MarkJames Member
Member

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

Hi @ImkeF ,

 

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

01.PNG

 

 

 

 

 

 

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:

 

02.PNG 

 

Thanks,

MarkJames

Super User III
Super User III

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

Hi @MarkJames ,

the step "AutoRemovedColumns1" is causing the trouble: 

 

image.png

 

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/ 

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




MarkJames Member
Member

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

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

MarkJames Member
Member

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

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:

 

01.PNG

 

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

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors