Use Cell value in Sql Query

[rki1966]

I am trying to use a cell value(Parameter), the same as I use with Microsoft query, but it is not working.

Example: Select * from table where Date = ?

Using Power Query

Defined the Criteria

let
Source = Excel.CurrentWorkbook(){[Name="AsOfDateCriteria"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"AsOfDate", type date}}),
ANDCriteria01 = Record.Field(#"Changed Type"{0},"AsOfDate")
in
ANDCriteria01



Sql Query

let
ANDCriteria01 = AsOfDateCriteria,
Source = Sql.Database("plrs-sql", "polaris", [Query="SELECT * FROM Polaris.dbo.MyTable Where AsOFDate = AsOfDateCriteria"])
in
Source


Error
Message=Invalid column name 'AsOfDateCriteria'.

The first step shows the value of 04/11/2019.

 

[alansidman]

Try this:

Add a column and then create an if statement that looks in a particular column for the date and the else statement is " ". You can then filter on the new column to only show the non blanks.

 

[theBardd]

I think you need to build the queryt string, like so

[LEFT][COLOR=#333333][FONT=Verdana][Query="SELECT * FROM Polaris.dbo.MyTable Where AsOFDate = '" & AsOfDateCriteria & "'"][/FONT][/COLOR][/LEFT]

and the date might be datetime and not text so maybe even

[/FONT][/COLOR][COLOR=#333333][FONT=Verdana][Query="SELECT * FROM Polaris.dbo.MyTable Where AsOFDate = [LEFT][COLOR=#222222][FONT=Verdana]'" & Date.ToText(DateTime.Date(myDate)) & "'"][/FONT][/COLOR][/LEFT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana]

​

 

[rki1966]

Thanks,
I made the change and got this error. I copied the data from the first query and pasted to the Second query and it worked.

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

 

[theBardd]

This usually means that you query is accessing multiple sources.
See if this blog helps you resolve the issue, https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/.