I have an Excel Workbook that gets data from an ODBC data source using an SQL statement that has an IN Clause in the Where Statement. In the Power Query Editor, I typed in my ODBC DSN and the SQL below. I would like to replace the IN clause with values from a column in an Excel table. How do I do that.
SELECT PYRL_NO, EMP_LST_NM LastName, EMP_FIR_NM FirstName, STS_CD Status
,STS_EFF_DT StatusDate, UNN_CD UnionCode
FROM OFSPROD.AG_EMP_MSTR
WHERE PYRL_NO IN ('00008','00016')
ORDER BY PYRL_NO
Load your excel Table into Power Query, prefix/suffix the values with a ' if necessary, then convert that column into a string in your IN statement. It would look something like this (I believe this must be done in the function bar or the advanced editor instead of the properties of the ODBC connection):
"
SELECT PYRL_NO, EMP_LST_NM LastName, EMP_FIR_NM FirstName, STS_CD Status ,STS_EFF_DT StatusDate, UNN_CD UnionCode
FROM OFSPROD.AG_EMP_MSTR
WHERE PYRL_NO IN (" & Text.Combine(MyTable[MyColumn], ",") & ")
ORDER BY PYRL_NO
"
