How do I check a column in a table in a SQL query

[EaglesTony]

Hi,

  I currently have the following (which works):

= Sql.Database("Northwest", "Reporting", [Query="SELECT DISTINCT SUBSTRING([IssueKey],1,(CHARINDEX('-', [IssueKey])-1) ) AS TeamProject#(lf)FROM [Reporting].[dbo].[SprintIssuesSummaryEOS] WHERE Sprint IN#(lf)(SELECT Sprint#(lf)FROM dbo.SprintDates#(lf)WHERE SprintStartDate <= CAST(GETDATE() AS DATE) and SprintEndDate >= CAST(GETDATE() AS DATE))"])

  Now I need to modify it to not look at the current date, but a date in a table within Powerquery:

The table is as follows:

TableName: CurrentSprintInfo

Column: PriorSprintStartDate

Column: PriorSprintEndDate

So now I want something like:

= Sql.Database("Northwest", "Reporting", [Query="SELECT DISTINCT SUBSTRING([IssueKey],1,(CHARINDEX('-', [IssueKey])-1) ) AS TeamProject#(lf)FROM [Reporting].[dbo].[SprintIssuesSummaryEOS] WHERE Sprint IN#(lf)(SELECT Sprint#(lf)FROM dbo.SprintDates#(lf)WHERE SprintStartDate = CurrentSprintInfo.PriorSprintDate and SprintEndDate = CurrentSprintInfo.PriorSprintEndDate)

I don't think it is the correct syntax though.

[ToddChitt]

Check your query syntax. In the below setup, probably similar to yours, I have Query1, which is simple text that will get turned into a SQL SELECT statement. It concatenates Parameter1, which has a Current Value of FooBar. The evaluation of the concatenation is displayed for Query1, and just by looking at it I can see that, as is, it would fail if I tried to execute it against a SQL database. Why? Because the Parameter value is of type string and NOT enclosed in single quotes. 

For you, copy the contents of the Query text and paste it into something to execute against the database. That may give you a clue.

[EaglesTony]

I have Parameter1 as :

CurrentSprintInfo[PriorSprintStartDate]{0}

but now an error "Expression.SyntaxError: Token ',' expected."

I have the end as :

WHERE SprintStartDate = " & #"Parameter1")]

[EaglesTony]

Giving me an error "Expressions.Syntax.Error: Token ']' expected.

[EaglesTony]

I fixed one thing, but with this:

= Sql.Database("Northwest", "Reporting", [Query="SELECT DISTINCT SUBSTRING([IssueKey],1,(CHARINDEX('-', [IssueKey])-1) ) AS TeamProject#(lf)FROM [Reporting].[dbo].[SprintIssuesSummaryEOS] WHERE Sprint IN#(lf)(SELECT Sprint#(lf)FROM dbo.SprintDates#(lf) WHERE SprintStartDate = " & CurrentSprintInfo[PriorSprintStartDate]{0} & " and SprintEndDate = " & CurrentSprintInfo[PriorSprintEndDate]{0} & ")"])

I am getting the following error(note: CurrentSprintInfo[PriorSprintStartDate]{ and CurrentSprintInfo[PriorSprintEndDate] I converted the type to date) The SprintStartDate is in a sql table defined as datetime:

Expression.Error: We cannot apply operator & to types Text and Date. Details: Operator=& Left=SELECT DISTINCT SUBSTRING([IssueKey],1,(CHARINDEX('-', [IssueKey])-1) ) AS TeamProject FROM [Reporting].[dbo].[SprintIssuesSummaryEOS] WHERE Sprint IN (SELECT Sprint FROM dbo.SprintDates WHERE SprintStartDate = Right=3/13/2024

[EaglesTony]

There is only ever one row in the CurrentSprintInfo table.

[PawelWrona]

So, you should do the following:

= Sql.Database("Northwest", "Reporting", [Query="SELECT DISTINCT SUBSTRING([IssueKey],1,(CHARINDEX('-', [IssueKey])-1) ) AS TeamProject#(lf)FROM [Reporting].[dbo].[SprintIssuesSummaryEOS] WHERE Sprint IN#(lf)(SELECT Sprint#(lf)FROM dbo.SprintDates#(lf)WHERE SprintStartDate = " & CurrentSprintInfo[PriorSprintDate]{0} & " and SprintEndDate = " & CurrentSprintInfo[PriorSprintEndDate]{0} & ")"

If all the naming I used is correc,t this should work right away.

[ToddChitt]

You are asking Power Query to take values from tables it has at its disposal and inject some values into a SQL Query? doesn't work like that. 

One option: Separate the SQL Query text from the query itself. Do this by starting a new Blank Query and manually editing it like this:

= "SELECT  ... FROM

WHERE..."

Then in your data query, edit the Source line to be

= Sql.Database("Northwest", "Reporting", [Query=#MySQLtext])

Now you can 'break into' the SQL Query text and concatenate in things like date parameters. I have not done it with values from a table, but I suppose it is possible.

[EaglesTony]

So what your saying is to build the #MySQLtext which would represent the "Where" clause with my values ?

[PawelWrona]

You should do something like this:

= Sql.Database("Northwest", "Reporting", [Query="SELECT DISTINCT SUBSTRING([IssueKey],1,(CHARINDEX('-', [IssueKey])-1) ) AS TeamProject#(lf)FROM [Reporting].[dbo].[SprintIssuesSummaryEOS] WHERE Sprint IN#(lf)(SELECT Sprint#(lf)FROM dbo.SprintDates#(lf)WHERE SprintStartDate = " & CurrentSprintInfo[PriorSprintDate] & " and SprintEndDate = " & CurrentSprintInfo[PriorSprintEndDate] & ")"

So, idea is to have a concatenated string here, where you will pass your values. Instead of "." notation you use square brackets to provide the column names. But now the question is what are the values that you want to pass there? Are these a single values only, or you want to apply it for the entire table, row by row? If the second option is valid here, you should ditch the table name, and reference columns only. If the first option is your scenario, then you must additionally drill down to single value within column structure like this: CurrentSprintInfo[PriorSprintDate]{0}.