Forum Discussion

ritanoori's avatar
ritanoori
Icon for Resolver I rankResolver I
6 years ago

Expression.Error: We cannot apply field access to the type Function.

Hi, Can someone help on this error 

Expression.Error: We cannot apply field access to the type Function.
Details:
Value=[Function]
Key=Reference

 

Below is from advanced Editor

let
Source = Excel.Workbook(File.Contents("C:\Users\RNoori\OneDrive - Schlumberger\Business Insight\FP28 PBI Project\Realized-Unrealized\XR222.xlsx"), null, true),
XR222_Sheet = Source{[Item="XR222",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(XR222_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"BaseAmount", type number}, {"AccountingPeriod", type text}, {"FiscalYear", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Date2", each [AccountingPeriod]&"/1/"&[FiscalYear]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Date2", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "EOMONTH", each Date.EndOfMonth([Date2])),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"EOMONTH", type date}, {"System", type text}, {"Description", type text}, {"Reference", type text}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type2", "Correct Curr", each if [System]="GL" then
Text.Start([Description],3) else
[CurrencyCode]),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"Reference", type text}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type3", "Reference2", each if Text.Length[Reference] = 9 and
Text.StartsWith[Reference] ="FX"

then
Text.End([Reference],7)
else [Reference]),
Reference2 = #"Added Custom3"{0}[Reference2]
in
Reference2

 

Appreciate it. 

3 Replies

  • I fixed it. It was syntax error.

    if Text.Length([Reference]) = 9 and
    Text.StartsWith([Reference], "FX")

    then
    Text.End([Reference],7)
    else [Reference]

  • ritanoori , have you added any new formula? , if yes share that

    Is there a column which showing error as the data value, if yes, what is the column name