List.RemoveLastN

D

Back to top

T

Back to top

#

Back to top

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

In this article, we will explore the M code behind the List.RemoveLastN function and how it can be used to manipulate data in Power Query.

Understanding List.RemoveLastN

The List.RemoveLastN function is used to remove the last n items from a list. The function takes two arguments: the list and the number of items to remove. The syntax of the function is as follows:

List.RemoveLastN(list as list, count as number) as list

The function returns a list that contains all the items of the input list except the last n items. If the count argument is greater than or equal to the number of items in the list, the function returns an empty list.

The M Code Behind List.RemoveLastN

The M code behind the List.RemoveLastN function is relatively simple. It consists of a single expression that uses the List.Take function to extract the first n items from the list and the List.Count function to calculate the number of items in the list.

list => List.Take(list, List.Count(list) – count)

The code above takes the list as input and returns a new list that contains all the items of the input list except the last n items. The List.Count function is used to determine the number of items in the list, and this value is subtracted from the count argument to determine the number of items to extract using the List.Take function.

Using List.RemoveLastN in Power Query

The List.RemoveLastN function can be used in a wide range of scenarios in Power Query. Here are a few examples:

Example 1: Removing the last row from a table

Suppose you have a table that contains data for multiple years, and you want to remove the last row, which contains data for the current year. You can use List.RemoveLastN to achieve this.

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WcixKzQEYwM6ItlSK1YhJLSjKLSjXyC8vzEnNzixJLEjMzNQ0tfPz89VSi0uSS0rKU9JzAwMzE2NzZJNTYwBkNz8wSzkMlMzUDA0JzUwpSS0Gc4tLzcxJzC2JzchJzE2NLLOz8pPSwqSaxJLTQ0sQrNzs8vOKM8q2SzJzUxNzVIBlG2E4JLsV5RZkZqTk64YyBzUvcvzCvJzI0vT0lMS8wBQA=”, BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#”Year/Month” = _t, Sales = _t]),

#”Removed Last Row” = Table.RemoveLastN(Source, 1)

in

#”Removed Last Row”

In the code above, the Table.RemoveLastN function is used to remove the last row from the table. The count argument is set to 1 to remove the last row.

Example 2: Removing the last n rows from a table

Suppose you have a table that contains data for multiple years, and you want to remove the last n rows, which contain data for the current and previous years. You can use List.RemoveLastN to achieve this.

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WcixKzQEYwM6ItlSK1YhJLSjKLSjXyC8vzEnNzixJLEjMzNQ0tfPz89VSi0uSS0rKU9JzAwMzE2NzZJNTYwBkNz8wSzkMlMzUDA0JzUwpSS0Gc4tLzcxJzC2JzchJzE2NLLOz8pPSwqSaxJLTQ0sQrNzs8vOKM8q2SzJzUxNzVIBlG2E4JLsV5RZkZqTk64YyBzUvcvzCvJzI0vT0lMS8wBQA=”, BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#”Year/Month” = _t, Sales = _t]),

#”Removed Last 2 Rows” = Table.FromList(List.RemoveLastN(Table.ToList(Source)[Column1], 2), Splitter.SplitByNothing(), {“Year/Month”, “Sales”}),

#”Changed Type” = Table.TransformColumnTypes(#”Removed Last 2 Rows”,{{“Year/Month”, type date}, {“Sales”, Int64.Type}})

in

#”Changed Type”

In the code above, the Table.ToList function is used to convert the table to a list, and then the List.RemoveLastN function is used to remove the last 2 rows from the list. The resulting list is then converted back to a table using the Table.FromList function.

Example 3: Removing the last n items from a list

Suppose you have a list of email addresses and you want to remove the last n items. You can use List.RemoveLastN to achieve this.

let

Source = {“john.doe@example.com”, “jane.doe@example.com”, “bob.smith@example.com”, “sara.jones@example.com”, “mike.brown@example.com”},

#”Removed Last 2 Items” = List.RemoveLastN(Source, 2)

in

#”Removed Last 2 Items”

In the code above, the List.RemoveLastN function is used to remove the last 2 items from the list of email addresses.

List.RemoveLastN is a powerful function that can be used for a wide range of data manipulation tasks in Power Query. By understanding the M code behind the function, you can gain a deeper understanding of how Power Query works and how to use it more effectively.

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)