List.Reverse

D

Back to top

T

Back to top

#

Back to top

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

What is the List.Reverse function in Power Query?

The List.Reverse function is used to reverse the order of items in a list. It takes a list as its input and returns a new list with the items in reverse order. This function is commonly used to sort data in descending order or to reverse the order of columns in a table.

The M code behind the List.Reverse function

The M code behind the List.Reverse function is relatively simple. The function takes a list as its input and uses the List.Buffer function to create a new list with the same items. The List.Buffer function is used to improve performance by storing the list in memory as a buffer.

After creating the new list, the M code uses a for loop to iterate through each item in reverse order. The loop starts with the last item in the list and moves backwards to the first item. The loop uses the List.InsertRange function to insert each item into the new list in reverse order.

The M code for the List.Reverse function looks like this:

(List as list) =>

let

Source = List.Buffer(List),

Result = List.Generate(

() => List.Count(Source) – 1,

each _ >= 0,

each _ – 1,

each Source{_}

)

in

Result

The code creates a new list called "Source" using the List.Buffer function. The "Result" variable uses the List.Generate function to loop through each item in the source list in reverse order. The List.Count function is used to determine the number of items in the list, and the "each _ >= 0" expression is used to iterate through each item in reverse order.

Examples of using the List.Reverse function

Let's look at some examples of how the List.Reverse function can be used in Power Query.

Sorting data in descending order

One common use of the List.Reverse function is to sort data in descending order. For example, let's say we have a list of sales data that we want to sort by the total sales amount. We can use the List.Sort function to sort the data in ascending order and then use the List.Reverse function to reverse the order and get the data in descending order.

The M code for this operation looks like this:

let

Source = Table.FromRows({{1, “Product A”, 100}, {2, “Product B”, 200}, {3, “Product C”, 150}}, {“ID”, “Product”, “Sales”}),

Sorted = List.Sort(Table.Column(Source, “Sales”), Order.Ascending),

Reversed = List.Reverse(Sorted),

Result = Table.FromColumns({Sorted}, {“Sorted Sales”})

in

Result

The code creates a new table called "Source" with three columns: ID, Product, and Sales. The List.Sort function is used to sort the "Sales" column in ascending order. The List.Reverse function is then used to reverse the order of the sorted list. Finally, the sorted and reversed list is converted into a new table with a single column called "Sorted Sales".

Reversing the order of columns in a table

Another use of the List.Reverse function is to reverse the order of columns in a table. For example, let's say we have a table with three columns: Name, Age, and Gender. We can use the List.Reverse function to reverse the order of columns and get a new table with the columns in the order Gender, Age, Name.

The M code for this operation looks like this:

let

Source = Table.FromRows({{“John”, 25, “Male”}, {“Jane”, 30, “Female”}, {“Bob”, 40, “Male”}}, {“Name”, “Age”, “Gender”}),

Reversed = Table.FromColumns(List.Reverse(Table.ToColumns(Source)), {“Gender”, “Age”, “Name”})

in

Reversed

The code creates a new table called "Source" with three columns: Name, Age, and Gender. The List.Reverse function is used to reverse the order of the columns and create a new table called "Reversed".

The List.Reverse function is a powerful tool in Power Query that enables users to reverse the order of items in a list. The M code behind the function is relatively simple and uses functions like List.Buffer, List.Generate, and List.InsertRange to create a new list with the items in reverse order. The function can be used for a variety of tasks like sorting data in descending order and reversing the order of columns in a table.

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)