title: passing a cell value into SQL query using Power Query Editor in Excel
body: <p>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.</p>
  <p><a href="https://i.stack.imgur.com/MSwSL.png" rel="nofollow noreferrer">fParameters</a></p>
  <p><a href="https://i.stack.imgur.com/BJsDM.png" rel="nofollow noreferrer">query being filtered</a></p>
  <p>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.</p>
  <p>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.</p>
  <p>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?</p>
  <p>Query</p>
  <pre><code>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 &lt;&gt; 'Y'
              )
              AND a.accepted_weight &lt;&gt; 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 &lt;&gt; '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) &gt;= @StartDateTime AND
      ISNULL(claim_close_date, b.AdjustmentDate) &lt; @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
              )
          )
      )
  </code></pre>
  <p><strong>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.</strong> Then the queries and other calculations I have in this sheet will update together.</p>