Power Query in Excel 2016

Power Query cannot throw up a dialog box asking for input like VBA can, but you can still pass variables to it. There are several ways to do it, but this is the easiest:

1. Create a table that is one record. Each column is a variable you want. Things like Start Date, End Date, Customer Number, etc. Whatever. One column for each variable.
2. Enter the data in the first record of the table.
3. Load the table into Power Query. Change the types to be accurate, but do nothing else.
4. For the first variable, right-click the Excel table query in step 3 and select Reference.
5. Remove all columns except for the first field.
6. Right-click on that value, and select Drill Down.
7. Name the query something useful, and preferably without spaces. I use pm for parameter, so pmStartDate, pmEndDate, pmCustomerNumber, etc.
8. Repeat steps 4-7 for all fields in the table.
9. Now you can reference those variables in your queries. For example, if you filter a query by date, just pick a bogus date. it will show something like = #date(2019,1,1) in the filter. Replace "#date(2019,1,1)" in the formula bar with pmStartDate.

Now when values are changed in the Excel table, your queries will use that data accordingly. You can use these in filters, mathematical functions, etc. Anywhere you would want to use a variable.