passing a cell value into SQL query using Power Query Editor in Excel

Question

I'm using Excel Power Query Editor to query my database to pull info into excel that I can easy manipulate for reporting purposes. I have written a query in Microsoft SQL and pasted it into Power Query Editor. I had a pretty dumbed down query and was just filtering the data by a date in the table using a parameters table and function shown in the snipit below.

fParameters

query being filtered

fParameters is just grabbing a table with 2 cells containing a start and finish date (week start, week end). The query would grab a couple 100k rows and then it would be filtered using the fParameters to the few hundred I needed that week.

I have written a new query so that I can grab more info that Ineeded, the trouble is I'm using a DECLARE statement to set those start/end dates and simply filtering the query afterwards is no longer an option.

It may be easier than what I was doing, but is there any way to simply pass the value of a cell into my query?

Query

DECLARE @StartDate DATE = '2020-10-25'
DECLARE @EndDate DATE = '2020-11-01'
DECLARE @Department VARCHAR(20) = 'Hotmill'
DECLARE @BonusCrew VARCHAR(100) = ''

    SET NOCOUNT ON;

DECLARE @StartDateTime DATETIME 
DECLARE @EndDateTime DATETIME 

BEGIN
    SET @StartDateTime = CAST(CAST(@StartDate AS VARCHAR) + ' 07:00' AS DATETIME)
    SET @EndDateTime = DATEADD(d,1,CAST(CAST(@EndDate AS VARCHAR) + ' 07:00' AS DATETIME))
END

DECLARE @tbl_crew table
    (
        crew varchar(1)
    )

    IF ISNULL(@BonusCrew,'') = ''
    BEGIN 
        --no crews, include everything
        INSERT INTO @tbl_crew VALUES ('A')
        INSERT INTO @tbl_crew VALUES ('B')
        INSERT INTO @tbl_crew VALUES ('C')
        INSERT INTO @tbl_crew VALUES ('D')
    END
    ELSE 
    BEGIN 
        declare @sql varchar(max)

        set @sql = 'select ltrim(param) from fn_MVParam(''' + @BonusCrew + ''', '','')'

        INSERT INTO @tbl_crew
        EXEC (@sql)
    END
    --Always include records with a missing crew designation
    INSERT INTO @tbl_crew VALUES ('')

SELECT DISTINCT
    CASE
        WHEN b.DefectCode = 'Chemistry out of range'        THEN 'CC'
        WHEN b.DefectCode = 'Chemistry transition'          THEN 'SCH'
        WHEN b.DefectCode = 'Comedown Bar'                  THEN 'SCH'
        WHEN b.DefectCode = 'Dimension - width over'        THEN 'SCH'
        WHEN b.DefectCode = 'Dimension - width over'        THEN 'SCH'
        WHEN b.DefectCode = 'Extra Steel'                   THEN 'SCH'
        WHEN b.DefectCode = 'First Bar'                     THEN 'SCH'
        WHEN b.DefectCode = 'Last Bar'                      THEN 'SCH'
        WHEN b.DefectCode = 'Melt/Cast - Lanced Open'       THEN 'CC'
        WHEN b.DefectCode = 'Surface - longitudinal crack'  THEN 'CC'
        WHEN b.DefectCode = 'Wrong Grade'                   THEN 'SCH'
        WHEN b.RespUnit = 'HRM1'                            THEN 'HM'
        ELSE b.RespUnit
    END AS 'Unit',
    b.matname 'Coil No.',
    b.weight / 2000.0 'Weight',
    b.Width,
    b.steelgradeid 'Grade',
    b.thickness 'Gauge',
    CASE 
        WHEN b.BonusType = 'Claim' THEN 'Claim'
        WHEN b.Disposition = 'SC' THEN 'SC'
        WHEN b.Disposition = 'HR' THEN '1B'
        WHEN b.Disposition = 'IS' THEN '2A'
        WHEN b.Disposition = 'EP' THEN '1X'
        WHEN b.Disposition = 'AD' THEN 'HA'
        ELSE '1A'
    END AS Disposition,
    hbcp.produced_dt_stamp 'HB Produciton Date',
    b.ProductionDate 'Production Date',
    b.AdjustmentDate 'Adjustment Date',
    CASE
        WHEN b.BonusType = 'Prime to EP'                            THEN 'Prime to 1X'
        WHEN b.BonusType = 'Prime to IS'                            THEN 'Prime to 2A'
        WHEN b.BonusType = 'Prime to AD'                            THEN 'Prime to HA'
        WHEN b.BonusType = 'Prime to HR'                            THEN 'Prime to 1B'
        WHEN b.BonusType = 'AD to IS'                               THEN 'HA to 2A'
        WHEN b.BonusType = 'AD to SC'                               THEN 'HA to SC'
        WHEN b.BonusType = 'EP to IS'                               THEN '1X to 2A'
        WHEN b.BonusType = 'AD to 1A within 7 days of production'   THEN 'HA to 1A within 7 days of production'
        WHEN b.BonusType = 'HR to 1A within 7 days of production'   THEN '1B to 1A within 7 days of production'
        WHEN b.BonusType = 'EP to 1A within 7 days of production'   THEN '1X to 1A within 7 days of production'
        WHEN b.BonusType = 'IS to 1A within 7 days of production'   THEN '2A to 1A within 7 days of production'
        ELSE b.BonusType
    END AS 'Bonus Type',
    CASE
        WHEN b.BonusType = 'claim'                                      THEN claim_reason.description
        WHEN b.DefectCode = 'Cobble - finishing mill'                   THEN 'Cobble'
        WHEN b.DefectCode = 'Cobble - coiler'                           THEN 'Cobble'
        WHEN b.DefectCode = 'Coil Package - collapsed coil'             THEN 'Coil Package'
        WHEN b.DefectCode = 'Coil Package - dished coil'                THEN 'Coil Package'
        WHEN b.DefectCode = 'Coil Package - folded edge'                THEN 'Coil Package'
        WHEN b.DefectCode = 'Coil Package - kinked ID'                  THEN 'Coil Package'
        WHEN b.DefectCode = 'Coil Package - loose wraps'                THEN 'Coil Package'
        WHEN b.DefectCode = 'Coil Package - oscillated'                 THEN 'Coil Package'
        WHEN b.DefectCode = 'Coil Package - telescoped coil'            THEN 'Coil Package'
        WHEN b.DefectCode = 'Coil Package - unfit to ship'              THEN 'Coil Package'
        WHEN b.DefectCode = 'Edges - damaged edges'                     THEN 'Edges'
        WHEN b.DefectCode = 'Edges - edge holes'                        THEN 'Edges'
        WHEN b.DefectCode = 'First Bar'                                 THEN 'First/Last Bar'
        WHEN b.DefectCode = 'Last Bar'                                  THEN 'First/Last Bar'
        WHEN b.DefectCode = 'Marks - bruises'                           THEN 'Marks'
        WHEN b.DefectCode = 'Marks - roll marks (other)'                THEN 'Marks'
        WHEN b.DefectCode = 'Melt/Cast - Lanced Open'                   THEN 'Lanced Open'
        WHEN b.DefectCode = 'Poor Physical Properties - CT variation'   THEN 'CT Variation'
        WHEN b.DefectCode = 'Scale - Held in Tunnel Furnace'            THEN 'Held in Tunnel Furnace'
        WHEN b.DefectCode = 'Scale - Pickled Out Streaks'               THEN 'Primary Scale'
        WHEN b.DefectCode = 'Scale - primary'                           THEN 'Primary Scale'
        WHEN b.DefectCode = 'Scale - Roll Wear/Roll Peel'               THEN 'Roll Peel'
        WHEN b.DefectCode = 'Scale - silicon streaks'                   THEN 'Primary Scale'
        WHEN b.DefectCode = 'Shape - center buckle'                     THEN 'Shape'
        WHEN b.DefectCode = 'Shape - edge wave'                         THEN 'Shape'
        WHEN b.DefectCode = 'Shape - quarter buckle'                    THEN 'Shape'
        WHEN b.DefectCode = 'Surface - laminations/holes'               THEN 'Lams/Holes'
        WHEN b.DefectCode = 'Surface - longitudinal crack'              THEN 'Longitudinal Cracks'
        WHEN b.DefectCode = 'Surface - nonmetallic inclusions'          THEN 'Inclusions'
        WHEN b.DefectCode = 'Surface - pits'                            THEN 'Pits'
        WHEN b.DefectCode IS NULL                                       THEN 'Reapplication'
        ELSE b.DefectCode
    END AS 'Defect',
    CASE
        WHEN b.BonusType = 'claim'  THEN claim_reason.description
        ELSE b.DefectCode 
    END AS 'Defect Description',
    b.REmark 'Remark',
    CASE b.REPORTINGUNIT
        WHEN 'B' THEN 'HRM1'
        WHEN 'P' THEN 'PL1'
        WHEN 'L' THEN 'PPPL'
        WHEN 'C' THEN 'TCM1'
        WHEN 'T' THEN 'TM1'
        WHEN 'A' THEN 'BAF'
        WHEN 'G' THEN 'CGL1'
        WHEN 'H' THEN 'CGL2'
        WHEN 'J' THEN 'CGL3'
        WHEN 'O' THEN 'OTHER'
    END AS REPORTINGUNIT,
    b.CREATEDBY,
    b.Crew,
    b.AdjustmentMultiplier 'Multiplier'

FROM 
    dbo.v_bonus_adjustment b
    INNER JOIN @tbl_crew c ON b.Crew = c.crew
    LEFT JOIN 
    (
        SELECT
            b.description,
            a.coil_no,
            a.claim_no,
            a.posted_date,
            a.controller_decision_date,
            claim.claim_close_date
        FROM
            dbo.coil_on_claim_credit a
            JOIN dbo.claim_reason b
                ON a.coil_credit_cd = b.claim_reason_cd
            JOIN claim
                ON a.claim_no=claim.claim_no
        WHERE 
            b.quality_approv_req = 'Y'
            AND a.coil_credit_cd NOT IN 
            (
                SELECT
                    claim_reason_cd
                FROM
                    dbo.claim_reason
                WHERE
                    bonus_related <> 'Y'
            )
            AND a.accepted_weight <> 0
            AND a.sequence_no = 
            (
                SELECT
                    MIN( sequence_no )
                FROM
                    dbo.coil_on_claim_credit c
                    JOIN dbo.claim_reason d
                        ON c.coil_credit_cd = d.claim_reason_cd
                WHERE
                    d.quality_approv_req = 'Y'
                    AND c.coil_credit_cd NOT IN
                    (
                        SELECT
                            claim_reason_cd
                        FROM
                            dbo.claim_reason
                        WHERE bonus_related <> 'Y'
                    )
                    AND c.coil_no = a.coil_no
                GROUP BY
                    c.coil_no
            )
    ) claim_reason
        ON b.matname = claim_reason.coil_no
        AND b.claim_no = claim_reason.claim_no
    LEFT JOIN all_produced_coils apc
        ON
        (
            b.production_coil_no = apc.production_coil_no
            OR claim_reason.coil_no = apc.production_coil_no
        )
    LEFT JOIN
    (
        SELECT
            production_coil_no,
            coil_defect_seq_no
        FROM
            regrade_history
        WHERE
            root_cd IS NOT NULL
            AND root_cd != 0
    ) AS rh
        ON apc.production_coil_no = rh.production_coil_no
    LEFT JOIN sdipdb.dbo.coil_defect cd
        ON rh.coil_defect_seq_no = cd.seq_no
    LEFT JOIN defect_root_cause rc
        ON cd.root_cd = rc.root_cd
    LEFT JOIN hb_coil_product_data hbcp
        ON hbcp.slab_no = apc.slab_no

WHERE
    ISNULL(claim_close_date, b.AdjustmentDate) >= @StartDateTime AND
    ISNULL(claim_close_date, b.AdjustmentDate) < @EndDateTime AND
    (
        (
            BonusType = 'Claim' AND RespUnit IN
            (
                SELECT 
                    ResponsibleUnit
                FROM dbo.bonus_departments
                WHERE 
                    Application = 'Claim'
                    AND Department = @Department
            )
        )
        OR
        (  
        RespUnit IN 
            (
                SELECT 
                    ResponsibleUnit
                FROM dbo.bonus_departments
                WHERE 
                    Application = 'Reject'
                    AND Department = @Department
            )
        )
    )

Basically, I just want those dates in the first 2 lines for declaring the start and end date to come from the 2 cells I have in my excel sheet. Then the queries and other calculations I have in this sheet will update together.

Answer 1

After this is working, adjust the code section step to include the remainder of your required procedure SQL, this example has a reduced complexity.

Steps:

1. Create a new Excel Workbook
2. Populate Sheet1 A1 with text: StartDate
3. Populate Sheet1 B1 with a date: 01/01/2022
4. With Sheet1 B1 Selected, click the Name Range Box to the left of the Formula Box Above the sheet data. It will Contain B1 before the next step.
5. Overwrite B1 in the Name Range Box to be the following and then Press Enter: StartDate
6. Populate Sheet1 A2 with text: EndDate
7. Populate Sheet1 B2 with a date: 03/31/2022
8. With Sheet1 B2 Selected, click the Name Range Box to the left of the Formula Box Above the sheet data. It will Contain B2 before the next step.
9. Overwrite B2 in the Name Range Box to be the following and then Press Enter: EndDate
10. At this point you have a sheet with 2 labels and 2 date values, with the value fields referenced as 2 Named Ranges
11. Click the Data Ribbon, Get Data, From Other Sources, Blank Query
12. Click Advanced Editor
13. Delete the content in the Advanced Editor
14. Paste the following into the Advanced Editor, this creates a custom function which will be used in the main query to pass in the Named Ranges (StartDate & EndDate)

(rangeName) =>
    Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]

15. Click Done
16. Populate the following value in the Name field of the Query Properties: GetValue
17. Click the Close Window X for the Power Query Editor.
18. Click Keep when prompted.
19. Click the Data Ribbon, Get Data, From Other Sources, Blank Query
20. Click Advanced Editor, Delete the content in the Advanced Editor
21. Replace the 2 values in the code below for XXX_YOUR_SERVER_NAME_XXX XXX_YOUR_DATABASE_NAME_XXX and then Copy/Paste the following into the Advanced Editor:

let
    StartDateText= DateTime.ToText(GetValue("StartDate"),"yyyy-MM-dd"),
    EndDateText=   DateTime.ToText(GetValue("EndDate"),"yyyy-MM-dd"),
    Query_Stmt= 
    "DECLARE @StartDate DATE = '" & StartDateText & "'" &
    "#(lf)" &
    "DECLARE @EndDate DATE = '" & EndDateText & "'"  & 
    "#(lf)" &
    "DECLARE @Department VARCHAR(20) = 'Hotmill'
DECLARE @BonusCrew VARCHAR(100) = ''

SET NOCOUNT ON;
DECLARE @StartDateTime DATETIME 
DECLARE @EndDateTime DATETIME 
     
BEGIN
SET @StartDateTime = CAST(CAST(@StartDate AS VARCHAR) + ' 07:00' AS DATETIME)
SET @EndDateTime = DATEADD(d,1,CAST(CAST(@EndDate AS VARCHAR) + ' 07:00' AS DATETIME))
END
             
DECLARE @tbl_1 table (list_values varchar(1) )
DECLARE @tbl_2 table (datetime_value datetime )

BEGIN 
INSERT INTO @tbl_1 VALUES ('A')
INSERT INTO @tbl_1 VALUES ('B')
INSERT INTO @tbl_1 VALUES ('C')
INSERT INTO @tbl_1 VALUES ('D')
INSERT INTO @tbl_2 VALUES (GETDATE()-30)
INSERT INTO @tbl_2 VALUES (GETDATE()-60)
INSERT INTO @tbl_2 VALUES (GETDATE()-70)
INSERT INTO @tbl_2 VALUES (GETDATE()+10)
END
SELECT * from @tbl_1 a left join @tbl_2 b on 1=1"
     &
    //"where b.datetime_value >= " & StartDateText & " and b.datetime_value <= " & EndDateText & "",
    "where b.datetime_value >= @StartDateTime and b.datetime_value <= @EndDateTime",
    
    Source = Sql.Database("XXX_YOUR_SERVER_NAME_XXX", "XXX_YOUR_DATABASE_NAME_XXX", [Query=Query_Stmt])
in
    Source
    //To Confirm the Query and Date Variable Text, Comment our Source on the line above as //Source  and
    //uncomment the below line by removing the // prefix and click Done
    //StartDateText & "#(lf)" & EndDateText & "#(lf)" & Query_Stmt

Note that in the above code, the following code is what calls the function defined in the first query, passing the 2 named ranges (StartDate, EndDate) into the custom function, GetValue, created in query 1:

    StartDateText= DateTime.ToText(GetValue("StartDate"),"yyyy-MM-dd"),
    EndDateText=   DateTime.ToText(GetValue("EndDate"),"yyyy-MM-dd"),

21. Click Done
22. Close the Power Query Editor Window by clicking X, Click Keep when prompted
23. Respond to any prompts for permission/privacy to execute the query.
24. Change the Dates on the first sheet and Refresh the Query Table as needed to see the results