Excel Power Query custom function zscore

[jhenke6229]

Using simple table

Measurement	Value
1	32.5
2	30.4
3	31.6

I want to add a new column called ZSCORE which would be ( [Measurement] - average([Value]) ) / standard_deviation([Value])

I created a new query (custom function) called fnZScore

let ZScore = (column as list) =>
     let
     average = List.Average(column),
     sdev = List.StandardDeviation(column)
in
     Table.AddColumn( "Z-Score", each (average - [Value])/sdev)
in
ZScore

I then tried to add an Invoke Custom Function Step

However, this returns the following error:   Expression.Error: A cyclic reference was encountered during evaluation.

Any suggestions on how to alter this custom function?

[Syndicate_Admin]

(tbl as table,columnName as text)=>

let
average = List.Average(Table.Column(tbl,column)),
sdev = List.StandardDeviation(Table.Column(tbl,column))
in
Table.AddColumn( tbl,"Z-Score", each (average - Record.Field(_,column))/sdev)

[Syndicate_Admin]

Oh my gosh!  You did it!  Thank you, thank you, thank you.  I spent the better part of two days searching through so many different examples for custom functions and z-score in particular.  This is the first one that worked!  I appreciate your taking the time to teach me the subtle things I needed to change

[Syndicate_Admin]

This certainly works when invoke this function by itself so thanks again for that.

However when I try to invoke this function as a step in the query, the current table where I am trying to use it does not show up in the dropdown.  So, I am looking for the syntax on how to make the function accept the current table.

Thanks again for your initial reply!

[Syndicate_Admin]

try to input the name of preivous step directly

[Syndicate_Admin]

I took the table reference out so I could just use the Invoke Custom column step in my query

(columnName as text)=>

let
average = List.Average(Table.Column(columnName)),
sdev = List.StandardDeviation(Table.Column(columnName))
in
Table.AddColumn("Z-Score", each (average - Record.Field(_,columnName))/sdev)

This allowed me to proceed as shown below

However when I go to run this, I get this error, where 52.8 is the first record (value) in the table.

Expression.Error: We cannot convert the value 52.8 to type Text.
Details:
Value=52.8
Type=[Type]

[Syndicate_Admin]

Here is my function with a hard coded reference to the last step in the query where I want to run this (that step is called main)

(columnName as text)=>

let
average = List.Average(Table.Column(columnName)),
sdev = List.StandardDeviation(Table.Column(columnName))
in
Table.AddColumn("main", "Z-Score", each (average - Record.Field(_,columnName))/sdev)

// main is the last step in the query I want to call this from

Then:  Add Column -> Invoke Custom Function

This is what is returned (sorry I have to obfuscate a few sensitive parts)

When I click the error this is the message:

I don't know what to make of it trying to convert that number (the first number in the list) to text.  I have tried a few variations, playing around with coercing (or casting) parts of the function to number, but it always comes back to this.

Thanks again for your help.