Tricks in Table.Group and Table.Replacevalue Function: M-Code

List. Transform inside Table.Group Function:

Today, we will discuss how to add multiple columns utilizing the List.Transform function inside Table.Group function:

Our goal is to transform the table so that month names appear in the row field and weekday names in the column field, while also calculating the total sales.

We can achieve this using the Table.Pivot function.

However, today's focus is to explore this task using the Table.Group function with the help of List.Transform function.

let

 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

 Weekdays = {"Mon","Tue","Wed","Thu","Fri","Sat"},

 Result = Table.Group(Source, "Month", List.Transform(Weekdays, (f) =>

 {f, each List.Sum(Table.SelectRows(_, (x) => x[Day] = f)[Sales])}))

in

 Result

Using 3 variables in the Replacer Argument of the Table.ReplaceValue Function in Power Query:

The source data comprises the names of Team 1, Team 2, and their respective scores in the Result column, which are separated by a "-".

Our goal is to identify the winner. There are several approaches to achieve this, but I will demonstrate a method that utilizes the Table.ReplaceValue function.

let

 Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],

 Result = Table.RenameColumns(Table.ReplaceValue(Source, each [Team 1], each [Team 2], (Current,Old,New) => [num = Expression.Evaluate(Current), fin = if num < 0 then New else if num > 0 then Old else null][fin],{"Result"}),{"Result","Winner"})

in

 Result