Forum Discussion
How do I check a column in a table in a SQL query
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.
So what your saying is to build the #MySQLtext which would represent the "Where" clause with my values ?
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}.
