Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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.
Thanks but I got this:
replace
each (x,y,z) =>
by
(x,y,z) =>
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.
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
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.
Many thanks and sorry to have bothered you.
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) => .
Check out the July 2025 Power BI update to learn about new features.