User defined M-Function throws error, cannot interpret Error-Message

[thomasreick]

Hi all,

I need some guidance on this user-defined M-function.

Based on a "Status" I want to calculate a usage time of an application into a new column

• When running this on "Offline" ist seems to work.
• Proceeding to "Online" or "Planned" an error is thrown: ExpressionError: There is an unkown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

Can you please give a hint?

The Code:

= (appstat as text, sdat as date, edat as date) =>
let
Source = if appstat="Offline"
then Duration.Days(Duration.From(([edat]-[sdat])))
else if appstat="online"
then Duration.Days(Duration.From((Date.From(DateTime.LocalNow())-[sdat])))
else if appstat="planned"
then 0
else 0
in
Source

[thomasreick · Accepted solution]

I think I 've found the error by myself ....

The code was adopted from a former Code based on fields. It used [bracketed] variables as if they were fields.

Mixed up variables with fields..., shame on me.

The corrected code works fine:

= (appstat as text, sdat as date, edat as date, heute as date) =>
let
Source = if appstat="Offline"
then Duration.Days(Duration.From((edat-sdat)))
else if appstat="online"
then Duration.Days(Duration.From(heute-sdat))
else if appstat="planned"
then 0
else 0
in
Source

[BA_Pete]

Hi @thomasreick ,

Just out of interest, is there a reason that you're using a function for this rather than just creating a basic custom column?

Pete

[thomasreick]

Hi BA_Pete,

first of all: I am quite new in PBI/M/DAX and come from MS-Access/VBA-World.

Reason(s) why:

• Practicing for dealing with M in PowerQuery
• Reusable if having a good blueprint
• Easier to maintain in terms of shorting the code
• I've been used to work on and with functions (VBA)

e.g. I use this function to set up an "envoked cusom function" to write data to a column. While fiddling around with the function (e.g. avoiding NULL-Fields) you can see immediate effects.

Using functions separates the Business-Logic from Data and gives room enough for try-and-error.

In my actual project I have to deal with LONG field names which makes writing expressions not handy.

[BA_Pete]

@thomasreick ,

Fair enough. I was just concerned that you may have been continuing an inherited process/procedure unnecessarily.

In my experience, functions tend to be laborious to create/maintain, and resource-intensive at runtime, but to each their own.

Thanks for taking the time to respond 🙂

Pete