Using variables in M-Script: each statement returning error

[Anonymous]

Hi

I want to optimize M-Script and use variables for expressions that can be reused, however system is returning error:

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

MojMesec variable has been defined, however it triggers error when executing, but syntax seem to be correct.

[Fiscal Period] is text field with format MM.YYYY.

Thanks for any suggestion, Marko.

Section of code that works:

#"Added Custom5" = Table.AddColumn(#"Pivoted Column", "Month Year", each if Number.FromText(List.First(Text.Split([Fiscal Period],"."))) > 6 then "1/"&Number.ToText(Number.Mod(Number.FromText(List.First(Text.Split([Fiscal Period],".")))+5,12)+1)&"/"&Number.ToText(Number.FromText(List.First(List.Reverse(Text.Split([Fiscal Period],"."))))+1) else "1/"&Number.ToText(Number.Mod(Number.FromText(List.First(Text.Split([Fiscal Period],".")))+5,12)+1)&"/"&Number.ToText(Number.FromText(List.First(List.Reverse(Text.Split([Fiscal Period],".")))))),

Optimized code that is returning error:

MojMesec = Number.FromText(List.First(Text.Split([Fiscal Period],"."))),
#"Added Custom5" = Table.AddColumn(#"Pivoted Column", "Month Year", each if MojMesec > 6 then "1/"&Number.ToText(Number.Mod(Number.FromText(List.First(Text.Split([Fiscal Period],".")))+5,12)+1)&"/"&Number.ToText(Number.FromText(List.First(List.Reverse(Text.Split([Fiscal Period],"."))))+1) else "1/"&Number.ToText(Number.Mod(Number.FromText(List.First(Text.Split([Fiscal Period],".")))+5,12)+1)&"/"&Number.ToText(Number.FromText(List.First(List.Reverse(Text.Split([Fiscal Period],".")))))),

[ImkeF · Accepted solution]

you can only turn this into a function for a variable like so:

MojMesec = (FiscalPeriod) =>  Number.FromText(List.First(Text.Split(Fiscal Period,"."))),
#"Added Custom5" = Table.AddColumn(#"Pivoted Column", "Month Year", each if MojMesec([Fiscal Period]) > 6 then "1/"&Number.ToText(Number.Mod(Number.FromText(List.First(Text.Split([Fiscal Period],".")))+5,12)+1)&"/"&Number.ToText(Number.FromText(List.First(List.Reverse(Text.Split([Fiscal Period],"."))))+1) else "1/"&Number.ToText(Number.Mod(Number.FromText(List.First(Text.Split([Fiscal Period],".")))+5,12)+1)&"/"&Number.ToText(Number.FromText(List.First(List.Reverse(Text.Split([Fiscal Period],".")))))),

[Anonymous]

Hi Imke Thanks for your proposal. It works with correction of a typo in your function definition: FiscalPeriod should be used without white space: MojMesec = (FiscalPeriod) => Number.FromText(List.First(Text.Split(FiscalPeriod,"."))), Otherwise very good solution 🙂

[Greg_Deckler]

Invoking @ImkeF