Functional Equivalents in Power Query (Direct Argument vs. each _ vs. (x)=˃ x (Lambda Expression)

Описание: #powerquery #power_query
Power Query: Functional Equivalents
• Direct Argument
• each _
• (x)=˃ x (Lambda Expression Notation)

Functional Equivalents:

Many formulas can be expressed in different ways that provide semi-equivalent results using:
• Direct Argument
• Each _
• (x)=˃x (Lambda Expression Notation)

Pros and Cons of Each Formula Convention:

Direct Argument:
Pros:
• Retains data type in many cases
• Simple to read and write
• More efficient as it calls the function directly and applies it to the entire context simultaneously

Cons:
• Limited functionality as it can only be applied generally to the given default context

Each _:
Pros:
• Highly flexible, allowing multiple parameters and simultaneous functions
• Easy to use once syntax is understood

Cons:
• Loses existing data type
• Slower than direct argument, as the function is applied individually to each element

(x)=˃x (Lambda Expression Notation):
Pros:
• Very flexible, as it allows you to define custom functions that can be used for multiple columns or transformations.
• Can be combined with other functions to create complex transformations.
• Required as an initializer function for List.Generate (not shown in this tutorial)

Cons:
• Loses existing data type
• Slower than direct argument, as the function is applied individually to each element
• More complex to read and write than direct argument or each _

In general:
• Use direct argument notation when it will work, and
• Use each _ when more flexibility, or multiple parameters or functions are needed.

Table.TransformColumns()

• Operational Context: Values as Text
• Example with Direct Argument Notation:
= Table.TransformColumns(T1,{{"Col1", Text.Upper}})

• Example with Each :
= Table.TransformColumns(T1,{{"Col1", each Text.Upper()}})

• Example with (x)=˃x:
= Table.TransformColumns(T1,{{"Col1", (x)=˃ Text.Upper(x)}})

Table.AddColumn():

• Operational Context: Rows as Records
• Example with Direct Argument Notation:
= Table.AddColumn(T2, "Custom", Record.ToList)

• Example with Each :
= Table.AddColumn(T2, "Custom", each Record.ToList())

• Example with (x)=˃x:
= Table.AddColumn(T2, "Custom", (x)=˃ Record.ToList(x))

Table.Pivot():

• Operational Context: Columns as Lists
• Example with Direct Argument Notation:
= Table.Pivot(T4, List.Distinct(T4[Col1]), "Col1", "Col2", List.Sum)

• Example with Each :
= Table.Pivot(T4, List.Distinct(T4[Col1]), "Col1", "Col2", each List.Sum())

• Example with (x)=˃x:
= Table.Pivot(T4, List.Distinct(T4[Col1]), "Col1", "Col2", (x)=˃ List.Sum(x))

Eternal Hope by Kevin MacLeod is licensed under a Creative Commons Attribution 4.0 license. https://creativecommons.org/licenses/...

Source: http://incompetech.com/music/royalty-...

Artist: http://incompetech.com/