Help with a custom function to calculate Z-score

[Connor888]

Hi,

I'm trying to use a custom function to add a column which contains a Z-score for another column to a table, something I have to do fairly often. I'm not all that experienced with custom functions however, so I'm going wrong. My code is as follows:

let ZScore = (datatable as table, column as list) =>
    let 
        average = List.Average(column),
        sdev = List.StandardDeviation(column)
    in    
        Table.AddColumn(datatable, "Z-Score", each Number.Abs(average - column)/sdev)
in
    ZScore

I'm fairly sure I can see that the error is in my Table.AddColumn, where I refer to the column - ordinarily I could use [column], but as in this case my column variable is a list I get an error.

Any help would be greatly appreciated!

[v-alq-msft]

Hi, @Connor888 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Query1(custom function):

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

Here are the codes for 'Table' query in 'Advanced editor'.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyVIrViVYyAjFNwUxjENMczDQBMQ2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Data", Int64.Type}}),
    #"Custom" = Query1(#"Changed Type",Table.Column(#"Changed Type","Data"))
in
    Custom

Result:

Best Regards

Allan

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

[Mariusz]

Hi @Connor888 

Can you provide a data sample ( past a table from excel into the body of this post ) and expected outcome?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Connor888]

Hi @Mariusz , 

Given this input table:

Index	Data
1	21
2	25
3	27
4	20

I'd like the result to be:

Index	Data	Zscore
1	21	0.786334
2	25	0.611593
3	27	1.310556
4	20	1.135815

For each line Zscore = ABS(average of the data column - that line of the data column)/standard deviation of the data column

[Mariusz · Accepted solution]

Hi @Connor888 

see the attached file with a solution.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn