Pass date parameter to a SQL function

[DukeCarey]

I need to pull data via a SQL function that requires a date parameter.  The SQL query is very simple: SELECT * FROM dbo.FunctionName('20230630').  

All the search results I've found focus on using date parameters to filter the dataset once it is returned to Power Query. However, this function does not return a date column.  Thus, filtering the dataset in Power Query is not an option.  

Is there any way to do this?

Thanks

[Fowmy · Accepted solution]

@DukeCarey 

I created a parameter, called startdate, in your case it is linked to an Excel sheet, it;s fine. Here is the modified code, which should work for as well. Modify the Date format as per your source system.

let
    DateParam = "'" & Date.ToText(StartDate, "yyyy-MM-dd") & "'",
    Source = Sql.Database(".", "ContosoRetailDW_2019", [Query="SELECT * FROM dbo.GetCustomersByDateFirstPurchase(" & DateParam & ")", CreateNavigationProperties=false])
in
    Source

[Fowmy]

@DukeCarey 
When you connect using the SQL as the source, your query should look like this. I created a function with a date parameter and and it works:

let
    Source = Sql.Database(".", "ContosoRetailDW_2019", [Query="SELECT * FROM dbo.GetCustomersByDateFirstPurchase('2004-03-14')", CreateNavigationProperties=false])
in
    Source

[DukeCarey]

Thanks for the response.  I should have been a little clearer with my question.  I'm setting this up for a user who will be unwilling/unable to edit a single query to use a different date, and in this case there are several queries in the workbook that will use the date. Only one will use it as the input to the SQL function, though.

So, the goal is to use an Excel table (residing in K1:K2) where the user could change the date and the one query would use THAT date for the function's parameter, while the other queries will filter their results using the same date parameter.

I hope I've made that clearer rather than confusing the issue

The particulars are that I've converted the table into a parameter named StartDate that can be used to filter other queries, but I also want it to go in as the parameter to that SQL function.

[Fowmy · Accepted solution]

@DukeCarey 

I created a parameter, called startdate, in your case it is linked to an Excel sheet, it;s fine. Here is the modified code, which should work for as well. Modify the Date format as per your source system.

let
    DateParam = "'" & Date.ToText(StartDate, "yyyy-MM-dd") & "'",
    Source = Sql.Database(".", "ContosoRetailDW_2019", [Query="SELECT * FROM dbo.GetCustomersByDateFirstPurchase(" & DateParam & ")", CreateNavigationProperties=false])
in
    Source

[DukeCarey]

Thanks Fowmy, I think we're inching closer now.  After mimicking what you posted I ran into the formula.firewall error and I'm stumped there now.

Formula.Firewall: Query 'PivotPurchaseTypesSince' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

[Fowmy · Accepted solution]

@DukeCarey 

As there are two data sources talking to each other, the privacy level needs to be set, you may set it to None in this case: Go to Options and Settings then set it up for each source.

[DukeCarey]

BTW, I tried to "flatten" the query based on a blog post and tried this:

let
SourceX = Excel.CurrentWorkbook(){[Name="StartDate"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
Column1 = #"Changed Type"{0}[Column1],
DateParam = "'" & Date.ToText(Column1, "yyyy-MM-dd") & "'",

Source = Sql.Database("test.database.windows.net", "testdata", [Query="SELECT * FROM rpt.PivotPurchaseTypesSince(" & DateParam & ")"])
in
Source

but then got this error:

Expression.Error: A cyclic reference was encountered during evaluation.