Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Mic1979
Post Partisan
Post Partisan

From function to calculation

Dear all,

 

I have two tables: 

 

Table_to_Change

Mic1979_0-1737447673944.png

 

 

and 

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

Mic1979_1-1737447673943.png

 

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:

Mic1979_2-1737447786278.png

 

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.

 

 

1 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

View solution in original post

8 REPLIES 8
Mic1979
Post Partisan
Post Partisan

Thanks but I got this:

Mic1979_0-1737490739208.png

 

PwerQueryKees
Super User
Super User

replace 

each (x,y,z) =>

by

(x,y,z) =>
Mic1979
Post Partisan
Post Partisan

Hello,

 

thanks. Based on the suggestion I got this:

Mic1979_0-1737466537909.png

 

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.

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. 

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

Mic1979_0-1737528009864.png

 

Anyway, many thanks.

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

Many thanks and sorry to have bothered you.

AlienSx
Super User
Super User

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) => . 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.