none
Problem while implementing the correlation coefficient RRS feed

  • Question

  • I'm new to PowerBi/powerquery and I was trying to write a function that calculates the correlation coefficient of 2 given lists.

    let Function = (l1 as list , l2 as list) => let CorCoefNumerator = List.Sum((l1 - List.Average(l1)) * (l2 - List.Average(l2))), Denominator1 = List.Sum(Number.Power(l1 - List.Average(l1), 2)), Denominator2 = List.Sum(Number.Power(l2 - List.Average(l2), 2)), CorCoefDenominator = Number.Sqrt(Denominator1 - Denominator2), CorCoef = Value.Divide(CorCoefNumerator, CorCoefDenominator) in CorCoef in Function(Table.ToList([Sales]), Table.ToList([Profit]))

    The Error message I'm getting is : An error occurred in the ‘’ query. Expression.Error: There is an unknown identifier. Did you use the

    [field] shorthand for a _[field] outside of an 'each' expression?

    One more question : Is there a way to use DAX function while writing power query queries ? becaus when

    first I tried to compute this correlation coefficient I needed it to work on columns, but since I

    couldn't use the DAX functions I had to use my columns as Lists !

    Monday, July 22, 2019 2:57 PM

Answers

  • Hi,

    You can use the following code that I created several years ago as part of my angry list of stat functions. I refer to the function as List_CorrelationCoefficient:

    (listX as list, listY as list) as number => 
    let
        SeriesCountX = List.Count(listX),
        SeriesCountY = List.Count(listY),
        Covariance =  List.Covariance(listX,listY)*SeriesCountX/(SeriesCountX-1),
        StandardDeviationSeries1 = List.StandardDeviation(listX),
        StandardDeviationSeries2 = List.StandardDeviation(listY),
        CorrelationCoefficient = if SeriesCountX = SeriesCountY then
                                    Covariance/(StandardDeviationSeries1*StandardDeviationSeries2)
                                 else error "The lists must have the same number of elements"
    in
        CorrelationCoefficient

    Monday, July 22, 2019 7:12 PM