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

Question

question

BrianSgro-3311 asked Mar 25, '22 AmeliaGu-msft edited Mar 28, '22

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

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.

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 @Date as Date = Month3

SELECT distinct
GL.CycleDate
, LI.PropertyAlphaStateCode
, GL.GlkDimension1ProductCode
, count(GL.LoanNumber)
, sum(GL.FirstPrincipalBalance)
FROM MspData.GlDimensionsForDate(@Date) 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 power-query-not-supported microsoft-graph-query-parameters

image.png (38.3 KiB)

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog · Mar 25 at 10:11 PM

I have the below query which is giving me all kinds of problems.

And what more exactly are those problems? Do you get an error message? If so, please share it. Or do you get incorrect results? Or is it something else? You mention "query tuning", is the problem that the query is slow?

Until we know what your problem is, we cannot really help you.

0 ·

Answer 1 · 2022-03-25T22:40:15.267Z

BrianSgro-3311 answered Mar 25, '22

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.

image.png (13.7 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2022-03-25T22:47:38.503Z

ErlandSommarskog answered Mar 25, '22

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.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question