How to Filter SQL Query in Power Query Without Creating Parameters?

In many business scenarios, users need the ability to dynamically filter data based on their specific requirements. For instance, when working with sales data pulled from a SQL Server database into Excel, it can be extremely useful to let users define date ranges to view relevant data. Today, we’ll explore how you can enable this functionality in Excel’s Power Query without creating formal parameters. This method not only simplifies the process but also integrates seamlessly with user inputs directly from an Excel sheet.

Step 1: Set Up Your Excel Sheet for Input

First, we need to create a place where users can enter their date filters:

• Create a new Excel sheet named “Input.”
• In cell A1, type “Start Date” and in cell A2, type “End Date.”
• Instruct users to enter the start date in cell B1 and the end date in cell B2.

Step 2: Connect to Your SQL Server

To begin pulling data into Excel:

• Navigate to the Data tab, select Get Data, then From Database, and finally From SQL Server Database.
• Enter your server and database details. Initially, just connect to the database and select the table or view you need without writing a query.

Step 3: Load the Excel Input Into Power Query

To make use of the Excel input within Power Query:

• Go to Data > Get Data > From Other Sources > From Table/Range.
• Choose your “Input” sheet. Ensure the “My table has headers” box is checked and click “OK”.

Step 4: Dynamically Filter the SQL Query

With the SQL data and input dates loaded, we now tweak the query to use the dates from the Excel sheet:

• Open the Power Query Editor and navigate to Home > Advanced Editor.
• Assuming your date input is loaded as a query, amend your main SQL query as follows:

let

    InputData = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
sDate = Date.ToText(DateTime.Date(InputData{0}[Start Date]), "MM/dd/yyyy"),
eDate = Date.ToText(DateTime.Date(InputData{0}[End Date]), "MM/dd/yyyy"),

pQuery = "SELECT 
EnglishProductName,
Color,
ModelName,
StandardCost,
DealerPrice,
StartDate,
EndDate
from DimProduct Where StartDate >=",
fQuery = pQuery & "'" & sDate & "'" & "AND EndDate <=" & "'" & eDate & "'",

    Source = Sql.Database("Your server", "Your database", [Query=fQuery])
in
    Source

The Date.ToText function in Power Query (M language) is used to convert date values into text (string) format, according to a specified date format. This is particularly useful when you need to present date data in a readable or standardized format, or when you need to perform operations that require date values to be in text form.

Code Explanation

1. InputData = Excel.CurrentWorkbook(){[Name=”Input”]}[Content]
   • Excel.CurrentWorkbook() is a Power Query function that accesses the current workbook where the query is being run.
   • {[Name="Input"]} specifies that we are accessing a specific object in the workbook, namely a table or named range called “Input”.
   • [Content] retrieves the actual data from this named range or table. This line effectively loads the entire content of the “Input” table into InputData.
2. StartDate = Date.ToText(InputData{0}[Start Date], “yyyy-MM-dd”)
   • InputData{0} accesses the first row of the InputData table. In Excel, {0} refers to the first item in a list or table because counting starts from zero.
   • [Start Date] specifies that we are retrieving the value from the column named “Start Date” in that first row.
   • Date.ToText(..., "yyyy-MM-dd") converts the date value to text in the format “yyyy-MM-dd”, which is a standard SQL date format. This ensures compatibility with SQL queries and avoids format mismatches that could lead to errors in the query.
3. EndDate = Date.ToText(InputData{1}[End Date], “yyyy-MM-dd”)
   • InputData{1} accesses the second row of the InputData table, assuming that the user enters the end date in the second row. This might be an error in explanation or an uncommon setup; usually, both start and end dates would be in the same row, in different columns. If that’s the case, it should be {0} for both start and end dates.
   • [End Date] retrieves the value from the “End Date” column.
   • Date.ToText(..., "yyyy-MM-dd") again converts the date to a string in the SQL-friendly format.

Potential Correction

If the start and end dates are indeed in the same row (which is typical), the references should be corrected as follows:

StartDate = Date.ToText(InputData{0}[Start Date], "yyyy-MM-dd"),
EndDate = Date.ToText(InputData{0}[End Date], "yyyy-MM-dd"),

This correction assumes both dates are in the first row of the “Input” table, each in their respective columns.

Step 5: Load Your Data into Excel

Once your query is set:

• Use Close & Load in the Power Query Editor to load the dynamically filtered data back into an Excel worksheet for analysis.

Final Thoughts

This approach allows you to bypass the formal creation of parameters in Power Query, streamlining data interaction directly through Excel. It’s particularly effective for dashboards and reports where date ranges frequently change.

However, be mindful of SQL injection risks since the query involves direct input from an Excel sheet. Always validate and sanitize inputs where possible.

This method can revolutionize how you handle data queries in Excel, providing a more interactive and user-friendly experience.