List.Sum

D

Back to top

T

Back to top

#

Back to top

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

In this article, we will be taking a closer look at the M code behind the List.Sum function and how it works. We will also explore some practical examples of how you can use this function in your own data analysis projects.

Understanding the List.Sum Function

The List.Sum function is a part of the M language, which is the programming language used in Power Query. This function takes a list as its input and returns the sum of all the values in that list. The syntax for the List.Sum function is as follows:

List.Sum(list as list) as number

The argument for this function is a list, which can be either a column or a list of values. The function returns a number that represents the sum of all the values in the list.

The M Code Behind List.Sum

The M code behind the List.Sum function is relatively simple. When you use the List.Sum function in Power Query, the M code that is generated looks something like this:

= List.Sum({1, 2, 3, 4})

This code creates a list of values (1, 2, 3, and 4) and then passes that list to the List.Sum function. The function then adds up the values in the list and returns a result of 10.

Of course, you can also use the List.Sum function with a column of data. In this case, the M code would look something like this:

= Table.AddColumn(#”PreviousStep”, “ColumnSum”, each List.Sum([Column]))

This code creates a new column in the table, called ColumnSum, and then uses the List.Sum function to add up the values in the Column column for each row of data in the table.

Practical Examples of List.Sum

Now that we understand how the List.Sum function works and the M code behind it, let's take a look at some practical examples of how you can use this function in your data analysis projects.

Example 1: Finding the Total Sales for a Product

Suppose you have a table of sales data that includes a product name and a sales amount for each sale. If you want to find the total sales for a particular product, you can use the List.Sum function in combination with a filter.

Here is an example of the M code you could use to find the total sales for a product called "Product A":

= List.Sum(Table.SelectRows(#”PreviousStep”, each [Product] = “Product A”)[Sales])

This code filters the table to only include rows where the product name is "Product A" and then uses the List.Sum function to add up the sales amounts for those rows.

Example 2: Calculating the Average Temperature for a Month

Suppose you have a table of temperature data that includes a date and a temperature reading for each day of the month. If you want to find the average temperature for the month, you can use the List.Sum function in combination with a division.

Here is an example of the M code you could use to find the average temperature for the month of January:

= List.Sum(Table.SelectRows(#”PreviousStep”, each Date.Month([Date]) = 1)[Temperature]) / List.Count(Table.SelectRows(#”PreviousStep”, each Date.Month([Date]) = 1)[Temperature])

This code filters the table to only include rows where the date is in January and then uses the List.Sum function to add up the temperature readings for those rows. The result is then divided by the number of rows to calculate the average temperature for the month.

The List.Sum function is a powerful tool in Power Query that allows you to quickly add up values in a list. By understanding the M code behind this function, you can gain a deeper understanding of how it works and how you can use it in your own data analysis projects. Whether you are looking to find the total sales for a product or calculate the average temperature for a month, the List.Sum function can help you achieve your data analysis goals.

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)