From function to calculation

[Mic1979]

Dear all,

I have two tables: 

Table_to_Change

and 

Start_Table (this table as other columns that the ones represented here...)

Finally with your precious help I created a custom function to merge them:

(
Start_Table,
Table_to_Change,
Start_Table_Col_1 as text, Start_Table_Col_2 as text, Start_Table_Col_3 as text,
Change_Column_1 as text, Change_Column_2 as text, Change_Column_3 as text,
Euro_Value as text) =>

[
Start_Table_Helper = Table.AddColumn (Start_Table, "Start_Table_Helper", each Text.Combine({
Record.Field(_, Start_Table_Col_1),
Record.Field(_, Start_Table_Col_2),
Record.Field(_, Start_Table_Col_3)}, " "), type text),

Clean_Start_Table = Table.RemoveColumns (
Start_Table_Helper,
List.Select(Table.ColumnNames(Start_Table_Helper), each not (Text.Contains(_, "Helper") or Text.Contains(_, "€")))
),

Table_to_Change_Helper = Table.AddColumn (Table_to_Change, "Table_to_Change_Helper", each Text.Combine({
Record.Field(_, Change_Column_1),
Record.Field(_, Change_Column_2),
Record.Field(_, Change_Column_3)}, " "), type text),

Clean_Table_to_Change = Table.RemoveColumns (
Table_to_Change_Helper,
{Change_Column_1, Change_Column_2, Change_Column_3}
),

Changed_Type_Table_to_Change = Table.TransformColumnTypes(
Clean_Table_to_Change,
List.Transform(List.Select(Table.ColumnNames(Clean_Table_to_Change), each Text.Contains(_, "%")), each {_, Percentage.Type})
),

Merge_Start_Table = Table.NestedJoin(
Changed_Type_Table_to_Change,
{"Table_to_Change_Helper"},
Clean_Start_Table,
{"Start_Table_Helper"},
"Value",
JoinKind.Inner
),

Expanded_Merged_Table = Table.ExpandTableColumn (
Merge_Start_Table,
Euro_Value,
List.Select(Table.ColumnNames(Clean_Start_Table), each Text.Contains(_, "€")),
List.Select(Table.ColumnNames(Clean_Start_Table), each Text.Contains(_, "€"))
),

Result = Table.AddColumn (
Expanded_Merged_Table,
"Result",
each (x,y,z) =>
each if Table.ColumnNames(y, each Text.Contains(_, "%"))< 0

then z = Table.ColumnNames(x, each Text.Contains(_, "€"))*(1+Table.ColumnNames(y, each Text.Contains(_, "%")))

else if Table.ColumnNames(y, each Text.Contains(_, "%"))> 0

then z = Table.ColumnNames(x, each Text.Contains(_, "€"))*(1-Table.ColumnNames(y, each Text.Contains(_, "%")))
else "")

]

[Result]

and I got the following:

I need now to apply the percentage in the column Pipe_Size_List_Price_Adder_% to the column Base_List_Price_€

applying the function:

Result = Table.AddColumn (
Expanded_Merged_Table,
"Result",
each (x,y,z) =>
each if Table.ColumnNames(y, each Text.Contains(_, "%"))< 0

then z = Table.ColumnNames(x, each Text.Contains(_, "€"))*(1+Table.ColumnNames(y, each Text.Contains(_, "%")))

else if Table.ColumnNames(y, each Text.Contains(_, "%"))> 0

then z = Table.ColumnNames(x, each Text.Contains(_, "€"))*(1-Table.ColumnNames(y, each Text.Contains(_, "%")))
else "")

]

However in the column Function I don't get the result, but a field named Function.

Could you help me to understand where I am wrong?

Thanks.

[Mic1979]

Thanks but I got this:

[PwerQueryKees]

replace 

each (x,y,z) =>

by

(x,y,z) =>

[Mic1979]

Hello,

thanks. Based on the suggestion I got this:

Function is invoked in this way:

let
Source = ConsumptionReview_3(Base_Price_EURO, Pipe_Size_Adder_PERC, "Region", "Type", "Pipe_Size", "Region", "Type", "Pipe_Size", "Value"),
Result = Source{0}[Result]
in
Result

So I did not get any calculation done.

Any more inputs?

Thanks a lot.

[AlienSx]

TBH, I don't want to dive into details of your code. I just pointed out that Table.AddColumn works with a row of table. Function that calculates a value for any particular row of the new column MUST BE a function of SINGLE agrument - a current row in for form of RECORD. Attempt made by @PwerQueryKees (message 4th in this topic) and the error you've got is just a confirmation of the fact I described above. I don't know what you've done so far keeping this information in mind. Do you still receive a function in new column? Well, it means you changed nothing "based on the suggestion". Sorry, I am totally lost. 

[Mic1979]

Hello AlienSx

I changed my code based on you suggestion:

Result = Table.AddColumn (
Expanded_Merged_Table,
"Result",
(_) => (x,y,z) =>
each if Table.ColumnNames(y, each Text.Contains(_, "%"))< 0
then z = Table.ColumnNames(x, each Text.Contains(_, "€"))*(1+Table.ColumnNames(y, each Text.Contains(_, "%")))
else if Table.ColumnNames(y, each Text.Contains(_, "%"))> 0
then z = Table.ColumnNames(x, each Text.Contains(_, "€"))*(1-Table.ColumnNames(y, each Text.Contains(_, "%")))
else "")
]

[Result]

what I am getting now is not a column with fuction, but the following

Anyway, many thanks.

[AlienSx · Accepted solution]

but IT IS a column with function! 

(_) => (x,y,z) => ...

you've changed nothing! If you did not now that each is in fact a syntax sugar (a shortcut if you like) for ( _ ) =>  then I can't help you much.

In order for your (x, y, z) function to do something you must call it with arguments like any other function in M. Example: 

(x, y, z) => x + y - z // here is your function, no identifier assigned 
( (x, y, z) => x + y - z ) (3, 2, 1) // call function right after we defined it by providing arguments in brackets. Result is 4
my_function = (x, y, z) => x + y - z // here we assigned an identifier my_function
call = my_function(2, 3, 1) // and call it by providing arguments in brackets. Result is 4.

M language spec - Functions

[Mic1979]

Many thanks and sorry to have bothered you.

[AlienSx]

each (x,y,z) =>

resolves to 

(_) => (x,y,z) =>

so that when Table.AddColumn passes a record to it's 3rd argument (it is doing so by design!) the result is ... function (x, y, z) => .