Trick in Table.TransformColumns and Table.Group using List.Transform Function

Trick in Table. TransformColumns function using List. Transform:

We aim to perform a similar transformation across several columns.

The input data consists of multiple columns for names, each prefixed with an Emp Id and separated by an underscore (_).

Our goal is to extract just the names from all the name columns.

The code below is generated when we execute this in the User interface.

let

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

 ExtractedTextAfterDelimiter = Table.TransformColumns(Source, {{"Emp Name", each Text.AfterDelimiter(_, "_"), type text}, {"Manager Name", each Text.AfterDelimiter(_, "_"), type text}})

in

 ExtractedTextAfterDelimiter

The second argument of the Table.TransformColumns function is a list of Transform Operations. Each column name along with its corresponding transformation is enclosed in curly braces, forming a sub-list, and all of these sub-lists are collectively wrapped in curly braces to create a list within a list structure.

we can utilize the List.Transform function to achieve the same structure with a straightforward approach.

let

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

 ColNames = List.Select(Table.ColumnNames(Source), (f) => Text.Contains(f,"Name")),

 Result = Table.TransformColumns(Source, List.Transform(ColNames, each {_,each Text.AfterDelimiter(_,"_")}))

in

 Result

Trick in Table. Group function using List. Transform:

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