Excel Power Query Expression Error

[dwglot]

How do I correct a Excel Power Query "Expression Error: We cannot apply field access to the type DateTime" ?

[dwglot]

Thank you for responding, This error appears when saving from the Excel Query Advanced Editor.

I hope this helps. Thank you

let
Parameter = Excel.CurrentWorkbook() {[Name="ParamCalcDate"]}[Content]{0}[CalculationDate],
Source = Sql.Database("server", "database", [Query="SELECT [MasterFileSeq]#(lf) ,[SourceSeqNum]#(lf) ,[SourceID]#(lf) ,[CalculationDate]#(lf) ,[ProcessDate]#(lf) ,[ContractNo]#(lf) ,[SectionNo]#(lf) ,[AdditionalContractNo]#(lf) ,[AdditionalSectionNo]#(lf) ,[SecontRefNo]#(lf) ,[MSContractSeq]#(lf) ,[MSReinsurerSeq]#(lf) ,[BlockSeq]#(lf) ,[ReinsurerID]#(lf) ,[BrokerID]#(lf) ,[PoolID]#(lf) ,[RetentionInd]#(lf) ,[AcctingItem]#(lf) ,CASE WHEN [DebitCredit] = -1 THEN [AcctingAmt] ELSE [AcctingAmt]*-1 END AS [AcctingAmt]#(lf) ,[ContractShare]#(lf) ,[ReinsurerShare]#(lf) ,[DebitCredit]#(lf) ,[CompanyCode]#(lf) ,[DataSource]#(lf) ,[Policy]#(lf) ,[PolicyEffDt]#(lf) ,[DocumentType]#(lf) ,[RecordInd]#(lf) ,[ClaimNo]#(lf) ,[Exposure]#(lf) ,[DtTime]#(lf) ,[Srce]#(tab)#(lf) FROM [database].[xxx].[GW1_FinancialTrans_Paids]#(lf) WHERE [AcctingItem] <> 'RPR' #(lf)#(tab)AND CalculationDate > "& DateTime.ToText(Parameter[ParamCalcDate]{0}) &" #(lf)#(tab)AND CalculationDate NOT IN (SELECT RSDE.ExclusionDate FROM database.ref.rein_Sapiens_DateExclusions AS RSDE)"])
in
Source

Here is the actual error message;

Expression.Error: We cannot apply field access to the type DateTime.
Details:
Value=6/13/2022 12:00:00 AM
Key=ParamCalcDate

[dwglot]

Thank you so much, the chages worked perfectly, exactly what I needed.😀

[Daryl-Lynch-Bzy · Accepted solution]

Hi @dwglot , one thing I like to do when using Native Query (i.e. providing SQL string to database) is to put the sql string in a standalone applied step in "Advanced Editor" screen.  This allow you to see the formatted SQL and copy it into another tool for testing without the #(lf) #(tab) strings.  It also helps to convert the parameter in a separate step before the main SQL.  You will be able to preview and change without do it in the longer string.

I put in the DateString step and added something.  This is only a guess because I don't know the parameter data type or format.  In your situation, I think the DateTime.ToText is not necessary because the parameter might be Text already.  This might take a bit of trial and error, but hopefully you can see the benefit using this approach.

let
Parameter = Excel.CurrentWorkbook() {[Name="ParamCalcDate"]}[Content]{0}[CalculationDate],
DateString = Text.From( Parameter ),
sql = "
SELECT [MasterFileSeq]
 ,[SourceSeqNum]
 ,[SourceID]
 ,[CalculationDate]
 ,[ProcessDate]
 ,[ContractNo]
 ,[SectionNo]
 ,[AdditionalContractNo]
 ,[AdditionalSectionNo]
 ,[SecontRefNo]
 ,[MSContractSeq]
 ,[MSReinsurerSeq]
 ,[BlockSeq]
 ,[ReinsurerID]
 ,[BrokerID]
 ,[PoolID]
 ,[RetentionInd]
 ,[AcctingItem]
 ,CASE WHEN [DebitCredit] = -1 THEN [AcctingAmt] ELSE [AcctingAmt]*-1 END AS [AcctingAmt]
 ,[ContractShare]
 ,[ReinsurerShare]
 ,[DebitCredit]
 ,[CompanyCode]
 ,[DataSource]
 ,[Policy]
 ,[PolicyEffDt]
 ,[DocumentType]
 ,[RecordInd]
 ,[ClaimNo]
 ,[Exposure]
 ,[DtTime]
 ,[Srce]	
 FROM [database].[xxx].[GW1_FinancialTrans_Paids]
 WHERE [AcctingItem] <> 'RPR' 
	AND CalculationDate > '" & DateString & "' 
	AND CalculationDate NOT IN (SELECT RSDE.ExclusionDate FROM database.ref.rein_Sapiens_DateExclusions AS RSDE)
",
Source = Sql.Database("server", "database", [Query=sql])
in
Source

[Daryl-Lynch-Bzy]

Hi @dwglot - could you please provide more information.  What power query function are you using and what does the source date look like.  The error message suggest the function cannot be applied to the selected column because it has a DateTime data type.  For example apply a Text function to Date Time value.