Forum Discussion

Connor888's avatar
Connor888
Frequent Visitor
6 years ago
Solved

Help with a custom function to calculate Z-score

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!

Need Help

4 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    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's avatar
      Connor888
      Frequent Visitor
      6 years ago

      Hi Mariusz , 

       

      Given this input table:

      IndexData
      121
      225
      327
      420

       

      I'd like the result to be:

       

      IndexDataZscore
      1210.786334
      2250.611593
      3271.310556
      4201.135815

       

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

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    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.