Need help with a Query trying to pull in a date parameter from an excel file

Brian Sgro 1 Reputation point
2022-03-25T19:40:37.89+00:00

I have the below query which is giving me all kinds of problems. I believe issue has to do with the date and trying to pass the date as a parameter from an excel workbook. The query runds fine when the Month3 name line is removed and a date of '2021-12-31' in place of Month3. I'm a novice, so it maybe more than just a date parameter issue. I attached a snipit of where the date parameter is coming from. The cell name is Month3, cell date 2021-12-31 is a text format. Any help to solve, or steer me in the right direction would be greatly appreciated.

187027-image.png

let
Source = Sql.Database("sm-sqldev.public.97600fbb54a7.database.windows.net,3342", "DM_Finance", [Query="use DM_Finance

Month3 = Excel.CurrentWorkbook(){[Name=Month3]}[Content]{0}[Column1]
declare @apl as Date = Month3

SELECT distinct
GL.CycleDate
, LI.PropertyAlphaStateCode
, GL.GlkDimension1ProductCode
, count(GL.LoanNumber)
, sum(GL.FirstPrincipalBalance)
FROM MspData.GlDimensionsForDate(@apl ) AS GL
INNER JOIN MspData.LoanInfo AS LI ON LI.LoanNumber = GL.LoanNumber
left join [DM_Finance].[DataImport].[InvestorMatrix] AS IM on IM.Investor = GL.InvestorId

where
GL.FirstPrincipalBalance <> '0.00'
and GL.InvestorId <> 'SM1'
and HiType <> '2'

group by
GL.CycleDate
, LI.PropertyAlphaStateCode
, GL.GlkDimension1ProductCode"])
in
Source

Azure SQL Database
Microsoft Graph
Microsoft Graph
A Microsoft programmability model that exposes REST APIs and client libraries to access data on Microsoft 365 services.
11,448 questions
Not Monitored
Not Monitored
Tag not monitored by Microsoft.
37,802 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Brian Sgro 1 Reputation point
    2022-03-25T22:40:15.267+00:00

    187084-image.png

    Below is the error message so far. I can't get the proper coding, so it's not a processing issue, but get the proper coding syntex for the query to execute. What I'm trying accomplish is getting
    cell L9 on a workseet tab in my orig message to be able to bring back data from a SQL Database for the date in that cell per the rest of the coding. I guessing the problem is how it's coded/linked to the cell reference. I hope this help clarify my issue. This is my first time trying this, so there might be to this than just this error preventing the query to execute. Thanks.

    0 comments No comments

  2. Erland Sommarskog 107.2K Reputation points
    2022-03-25T22:47:38.503+00:00

    I don't know exactly what this is, but you have Query=" and this obviously intended to be followed by an SQL batch the does something. In the middle of this, you splice in something which has no relation to T-SQL go that month value. That cannot work out.

    You would need to read the Month value, and then pass the Month as a parameter to the SQL code. But since I don't recognize the environment, I can't say how you do that.

    0 comments No comments