Table.TransformRows

Understanding Table.TransformRows

Before we dive into the M code behind Table.TransformRows, let’s first take a moment to understand what this function does. Essentially, Table.TransformRows allows you to apply a function to each row of a table. The function you apply can modify the data in each row, or perform some other action based on the data in that row.

For example, let’s say you have a table of customer orders, and you want to calculate the total cost of each order. With Table.TransformRows, you could apply a function to each row of the table that calculates the total cost based on the quantity and price of each item in the order.

The M Code Behind Table.TransformRows

So, what does the M code behind Table.TransformRows look like? Here’s an example:

At first glance, this code may look a bit intimidating. But let’s break it down step by step.

The first line defines the function signature. It takes two arguments – a table and a transform function – and returns a table.

Next, we use the List.Transform function to create a new list of columns for our transformed table. This list includes all of the existing columns from the original table, as well as a new “Transformed” column.

Then, we use the Table.AddColumn function to add a new column to the table, which is the result of applying the transform function to each row of the table.

We then use the Table.ToRows function to convert the table to a list of rows, and the Record.FieldValues function to extract the values from each row.

Next, we use the Table.FromColumns function to create a new table from the list of rows, using the new columns we defined earlier.

Finally, we remove the “Transformed” column from the new table, since we no longer need it.

Example: Calculating Order Totals

Let’s take a look at an example of how we could use Table.TransformRows to calculate the total cost of customer orders.

Suppose we have a table of customer orders that looks like this:

| OrderID | CustomerID | ProductID | Quantity | Price |

| ——- | ———- | ———| ——–| —– |

| 1 | A | Widget | 3 | 10.00 |

| 2 | B | Gadget | 2 | 15.00 |

| 3 | C | Widget | 1 | 12.50 |

To calculate the total cost of each order, we could define a transform function like this:

This function takes a row of data as input, and returns a new record that includes a “Total” field, which is calculated as the product of the Quantity and Price fields.

We can then apply this function to our order table using Table.TransformRows:

This will produce a new table with an additional “Total” column:

| OrderID | CustomerID | ProductID | Quantity | Price | Total |

| ——- | ———- | ———| ——–| —– | —– |

| 1 | A | Widget | 3 | 10.00 | 30.00 |

| 2 | B | Gadget | 2 | 15.00 | 30.00 |

| 3 | C | Widget | 1 | 12.50 | 12.50 |

In this article, we’ve explored the M code behind the Power Query M function Table.TransformRows. We’ve seen how this function allows you to apply a transformation to each row of a table, and we’ve looked at an example of how it can be used to calculate the total cost of customer orders. With this powerful function in your toolkit, you’ll be able to clean and reshape your data with ease.

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

Upcoming Courses

Contact Us