Search code examples
sql-serverexcelm

Dynamic SQL query with cell reference in Excel

I have a query connected to a pivot table and I want it to show results based on a date from cell (so the user can change it without editing the query). I've read one way to do it is creating a connection to the cell

enter image description here

And drilling down that query so its data that can be referenced by another query

enter image description here

I've read I have to reference that connected table (just a date) through

    SQL1 = Text.Combine({SQL,Date}),
    SQL2 = Text.Combine({SQL1, "‘" }),
    
    Source = Sql.Database("servername", "db", [Query= SQL2])

So I think it should look like this:

let

SQL = 
"select     trim(codart) AS REF, 
            descart AS 'DESCRIPTION', 
            codalm AS WAREHOUSE, 
            descalm AS WAREHOUSEDESCRIP, 
            unidades AS UNITS, 
            entran AS 'IN', 
            salen AS 'OUT', 
            m.entran*1 + m.salen*-1 as MOVEMENT,
            (select sum(m1.entran*1 + m1.salen*-1)
                    from    MOVSTOCKS m1 
                    where   m1.codart = m.codart and m1.codalm = 
                m.codalm and m.fecdoc >= m1.fecdoc) as 'CUMULATIVE',
            PRCMEDIO as 'VALUE', 
            FECDOC as 'DATE', 
            REFERENCIA as 'REF', 
            tipdoc as 'DOCUMENT'
from        (select m.*,
            sum(m.entran*1 - m.salen*-1) over (partition by m.codart, m.codalm order by fecdoc) as cumulative,
            max(fecdoc) over (partition by m.codart, m.codalm) as max_fecdoc
                from MOVSTOCKS m
                where fecdoc <= ‘ ), m
                where fecdoc = max_fecdoc
and         (m.entran <> 0 or m.salen <> 0)
order       by fecdoc",
    
SQL1 = Text.Combine({SQL,Date}),
SQL2 = Text.Combine({SQL1, "‘" }),

Source = Sql.Database("servername", "db", [Query= SQL2])

in

   Source

The problem is... if that dynamic cell is formated as date, the power query shows this error:

Expression.Error: We cannot convert the value #date (2020, 9, 30) to type Text.
Details:
    Value= 30/09/2020
    Type=Type

And if I format date as text, then it shows this error (after changing privacy options):

DataSource.Error: Microsoft SQL: Incorrect syntax near '‘'.
Details:
    DataSourceKind=SQL
    DataSourcePath=servername;db
    Message=Incorrect syntax near '‘'.
    Number=102
    Class=15

I want SQL to read this

where fecdoc <= ‘ ), m

as

where fecdoc <= 30/09/2020 ), m

Having in mind that date comes from an excel cell that can be changed by the user.

I'm new with SQL and M, I don't even know if what I'm trying makes any sense.

Any feedback will be helpful. Thank you very much

Solution

  • Instead of whatever you're trying to do with SQL1 and SQL1, in your query, replace

    where fecdoc <= ‘ ), m

    with

    where fecdoc <= '" & Date & "' ), m

    where Date is a reference to your date in string format.