Get an entire column as a reference to put in a SQL query

Yes, there is a way to grab the contents of a column in Power Query, concatenate them with commas, and assign them to a variable to use in your query. Here's how you can do it:

1. Load the Excel spreadsheet into Power Query by clicking on the "Data" tab in Excel, then selecting "From Table/Range".
2. In Power Query, select the column containing the customer IDs and click on the "Transform Data" button.
3. In the "Transform Data" window, select the "Text Filters" dropdown and choose "Does Not Equal".
4. In the "Custom" field, enter a single character that is not present in any of the customer IDs (e.g. "~").
5. Click on "OK" to filter the data.
6. Select the filtered column and click on the "Replace Values" button in the "Transform" tab.
7. In the "Replace Values" window, replace the character you used to filter the data with a comma.
8. Click on "OK" to replace the values.
9. Click on the "Close & Load" button to load the data into a table in Excel.

Now that you have the customer IDs concatenated with commas in a table, you can create a new query that references this table and uses the customer IDs in your SQL query. Here's an example:

1. Click on the "Data" tab in Excel, then select "From Table/Range".
2. In Power Query, select the table containing the concatenated customer IDs and click on the "Transform Data" button.
3. In the "Transform Data" window, click on the "Add Column" tab and select "Custom Column".
4. In the "Custom Column" window, enter a name for the column (e.g. "CustomerIDs") and enter the following formula: =Text.Combine(Table.Column(#"Changed Type", "CustomerID"),",")
5. Click on "OK" to create the custom column.
6. Click on the "Close & Load" button to load the data into a table in Excel.

Now, you can reference the "CustomerIDs" column in your SQL query by using the following syntax: WHERE CustomerID in ( $(CustomerIDs) ). Make sure to replace "CustomerIDs" with the name of your custom column. When you run the query, Power Query will replace "$(CustomerIDs)" with the concatenated customer IDs from the table, allowing you to use as many customer IDs as you need without hitting the character limit of a cell.