List.Covariance

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function List.Covariance

What is Power Query M Language?

Power Query M language is a functional language used to query and transform data. It is used in Microsoft Excel, Power BI, and other data analysis tools. The language is designed to be simple and easy to learn, with a syntax similar to Excel formulas. The language is used to create custom functions, automate data transformation, and extract data from various sources.

Understanding Covariance

Before we dive into the List.Covariance function, let’s take a moment to understand covariance. Covariance is a measure of the relationship between two variables. It is calculated as the average of the product of the deviations of each variable from its mean. A positive covariance indicates that the two variables tend to move together, while a negative covariance indicates that they tend to move in opposite directions.

Syntax of List.Covariance Function

The List.Covariance function in Power Query M language takes two lists of numbers as input and returns the covariance between them. The syntax of the function is as follows:

List.Covariance(list1 as list, list2 as list, optional biased as nullable logical) as number

- list1: The first list of numbers.

- list2: The second list of numbers.

- biased: An optional parameter that indicates whether to use a biased or unbiased estimator of covariance. If this parameter is omitted, the function uses a biased estimator by default.

How to Use List.Covariance Function

To use the List.Covariance function, you first need to create two lists of numbers. These lists can be created using other Power Query functions or by manually entering the data. Once you have the two lists, you can call the List.Covariance function and pass the two lists as arguments.

Here is an example of using the List.Covariance function to calculate the covariance between two lists of numbers:

let

list1 = {1, 2, 3, 4, 5},

list2 = {2, 4, 6, 8, 10},

covariance = List.Covariance(list1, list2)

in

covariance

In this example, we have created two lists of numbers (list1 and list2) and passed them as arguments to the List.Covariance function. The function returns the covariance between the two lists, which is 5.

Biased vs. Unbiased Estimator of Covariance

The List.Covariance function allows you to choose between a biased or unbiased estimator of covariance. A biased estimator is a method of estimating the covariance that tends to overestimate or underestimate the true covariance. An unbiased estimator, on the other hand, is a method that gives the same expected value as the true covariance.

By default, the List.Covariance function uses a biased estimator of covariance. This means that the function tends to overestimate the true covariance. If you want to use an unbiased estimator, you can pass the optional parameter biased as false.

Here is an example of using the List.Covariance function with an unbiased estimator:

let

list1 = {1, 2, 3, 4, 5},

list2 = {2, 4, 6, 8, 10},

covariance = List.Covariance(list1, list2, false)

in

covariance

In this example, we have passed the optional parameter biased as false to indicate that we want to use an unbiased estimator of covariance. The function returns a covariance of 6, which is the unbiased estimator.

The List.Covariance function in Power Query M language is a useful tool for calculating the covariance between two lists of numbers. It is easy to use and allows you to choose between biased and unbiased estimators of covariance. By understanding the M Code behind this function, you can use it effectively to analyze and transform your data.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)