Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

[SCC1991]

Hi guys

I am receiving the following error when invoking a custom function:

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

We have the following Custom Function:

let
Query2 = (Vardate as text)=>
let
Source = Web.BrowserContents("https://www.xe.com/currencytables/?from=USD&date=",[RelativePath=[Vardate]]),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.currencytables__Table-xlq26m-3.jaGdii > * > TR > :nth-child(1)"}, {"Column2", "TABLE.currencytables__Table-xlq26m-3.jaGdii > * > TR > :nth-child(2)"}, {"Column3", "TABLE.currencytables__Table-xlq26m-3.jaGdii > * > TR > :nth-child(3)"}, {"Column4", "TABLE.currencytables__Table-xlq26m-3.jaGdii > * > TR > :nth-child(4)"}}, [RowSelector="TABLE.currencytables__Table-xlq26m-3.jaGdii > * > TR"]),
#"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Currency", type text}, {"Name", type text}, {"Units per USD", type number}, {"USD per unit", type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Currency", "Currency code#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)▲▼"}, {"Name", "Currency name#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)▲▼"}, {"USD per unit", "USD per Unit"}})
in
#"Renamed Columns"
in
Query2

Which is then invoked into the following Query:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0jVSio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Offset = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Offset", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()),-2),"yyyy-MM-dd")),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type text}}),
#"Invoked Custom Function1" = Table.AddColumn(#"Changed Type1", "currency_conversion", each #"XE Date"([Date])),
#"Expanded currency_conversion" = Table.ExpandTableColumn(#"Invoked Custom Function1", "currency_conversion", {"Currency code#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)▲▼", "Currency name#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)▲▼", "Units per USD", "USD per Unit"}, {"Currency code#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)▲▼", "Currency name#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)▲▼", "Units per USD", "USD per Unit"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded currency_conversion",{"Offset", "Date"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Units per USD", type number}, {"USD per Unit", type number}})
in
#"Changed Type2"

---

Would anyone be able to assist?

Thanks

[Icey · Accepted solution]

Hi @SCC1991 ,

Try this:

let
Query2 = (Vardate as text)=>
let
    Source = Web.Contents("https://www.xe.com/currencytables/",[Query = [from="USD", date="2021-04-20"]]),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.currencytables__Table-xlq26m-3.jaGdii > * > TR > :nth-child(1)"}, {"Column2", "TABLE.currencytables__Table-xlq26m-3.jaGdii > * > TR > :nth-child(2)"}, {"Column3", "TABLE.currencytables__Table-xlq26m-3.jaGdii > * > TR > :nth-child(3)"}, {"Column4", "TABLE.currencytables__Table-xlq26m-3.jaGdii > * > TR > :nth-child(4)"}}, [RowSelector="TABLE.currencytables__Table-xlq26m-3.jaGdii > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Currency", type text}, {"Name", type text}, {"Units per USD", type number}, {"USD per unit", type number}})
in
    #"Changed Type"
in
Query2

And it can set scheduled refresh.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[watkinnc]

How bout:

Source = Web.BrowserContents("https://www.xe.com/currencytables/?from=USD&date="&Vardate),

[SCC1991]

I tried that route originally, it works yes, but it prevents Dynamic Refresh's from happening, which is why we went down the invoke custom function in the first place.

[SCC1991]

Hi

I did try this, and have tried this again now - I receive this error now:

DataSource.Error: Web.BrowserContents: This function doesn't support the query option 'RelativePath' with value '"2021-04-18"'.
Details:
2021-04-18

Thanks,

[watkinnc]

It's should be written in your query as [RelativePath = VarDate] (VarDate is a text variable, and doesn't need to be enclosed in [ ]
--Nate