Power Query SQL: Bring in Cell Contents for the WHERE Statement

Power Query is a powerful tool that allows you to easily connect to, transform, and load data from various sources into Excel. In this article, we will show you how to bring in cell contents for the WHERE statement in Power Query SQL.

Before we can use the cell contents in the WHERE statement, we need to first load the data into Power Query. To do this, follow these steps:

1. Click on the Data tab in Excel.
2. Click on the From Table/Range button in the Get & Transform Data group.
3. Select the range of cells that you want to load into Power Query.
4. Click on the OK button.

Once the data is loaded into Power Query, we need to create a new column that will contain the cell contents. To do this, follow these steps:

1. Click on the Add Column tab in the ribbon.
2. Click on the Custom Column button.
3. In the Custom Column dialog box, enter the following formula:

   each [ColumnName]

   Replace ColumnName with the name of the column that contains the cell contents. For example, if the column name is MyColumn, the formula would be:

   each [MyColumn]

   Click on the OK button.

This will create a new column that contains the cell contents. We can now use this column in the WHERE statement. To do this, follow these steps:

1. Click on the Home tab in the ribbon.
2. Click on the Advanced Editor button.
3. In the Advanced Editor window, locate the Source step.
4. Add the following code to the Source step:

   let
   Source = Sql.GetData(ServerName, DatabaseName, Query),
   Filtered = Table.SelectRows(Source, each [ColumnName] = FilterValue)
   in
   Filtered

   Replace ServerName with the name of the SQL server. Replace DatabaseName with the name of the SQL database. Replace Query with the SQL query that you want to use. Replace ColumnName with the name of the column that contains the cell contents. Replace FilterValue with the value that you want to filter on.

   For example, if the SQL query is:

   SELECT * FROM TableName

   And the cell contents are in column MyColumn and the filter value is 10, the code would be:

   let
   Source = Sql.GetData(ServerName, DatabaseName, SELECT * FROM TableName),
   Filtered = Table.SelectRows(Source, each [MyColumn] = 10)
   in
   Filtered

   Click on the Done button.

This will apply the filter to the data and return only the rows that match the filter value. You can now load the data into Excel by clicking on the Close & Load button.

In summary, to bring in cell contents for the WHERE statement in Power Query SQL, you need to first load the data into Power Query. Then, you need to create a new column that contains the cell contents. Finally, you need to add the filter code to the Source step in the Advanced Editor window. This will apply the filter to the data and return only the rows that match the filter value. You can now load the data into Excel.

References