Hi,
I have current year GL in excel file like GL1718. That I want to replace within SQL query (refer to red text below) but data table is empty.
let
CFY = Excel.CurrentWorkbook(){[Name="CFY"]}[Content],
REF = CFY{0}[Actual Financial Year],
Source = Sql.Database("fc", "fin", [Query="SELECT Substring(GLF_LDG_ACCT_PBAL.ACCNBRI,1,3), Substring(GLF_LDG_ACCT_PBAL.ACCNBRI,4,2), Substring(GLF_LDG_ACCT_PBAL.ACCNBRI,6,3), GLF_LDG_ACCT_PBAL.PERIOD, GLF_LDG_ACCT_PBAL.BAL_AMT1#(lf)FROM fin.dbo.GLF_LDG_ACCT_PBAL#(lf)WHERE ((GLF_LDG_ACCT_PBAL.LDG_NAME In ('REF'))) AND (GLF_LDG_ACCT_PBAL.PERIOD in ('7','8','9','10','11','12','1','2','3','4','5','6')) AND (Substring(GLF_LDG_ACCT_PBAL.ACCNBRI,4,2) not in ('00','70','74','76','99'))#(lf)"]),
#"Renamed Columns" = Table.RenameColumns(Source,{{"", "RC"}, {"2", "Activity"}, {"3", "RES"}, {"BAL_AMT1", "CYAmt"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"PERIOD", Int64.Type}, {"CYAmt", Currency.Type}})
in
#"Changed Type"
How to include value in cell (GL1718) in SQL query?
Please help.
Kind Regards
Arun
Hi Arun,
It looks like you have pasted your own SQL statement into the 'Advanced Options' field (Get Data From > SQL Server dialog box) rather than using Power Query to build the statement, and I'm not sure you can just insert a variable like this into a SQL statement.
Note: It's always more efficient to let Power Query build the query because this allows it to fold the query back to the SQL server and let it do the work, whereas when you paste in a pre-written SQL statement into the 'Advanced Options' Power Query has to work much harder to get the data.
That said, it might be a simple fix, e.g. you don't need the single quotes around REF in the SQL statement. Or maybe your Excel table isn't named properly i.e. the table name should be CFY and the column containing the REF should be called 'Actual Financial Year'.
It's difficult to tell without seeing the file or more information about the Excel tables.
Also, I assume a table was returned before you modified it for the CFY parameter etc.
Mynda
