Dynamic Expand Power Query Custom Function

[Mic1979]

Dear all,

I have two tables to merge.

Here two samples table:

Table_to_Change

and 

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

What I need is to merge Table_to_Change and Start_Table, in order to make a calculation betweencolumns Pipe_Size_List_Price_Adder_% and Base_List_Price_€ to get a value instead of &.

The code I am using is this one:

(
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
),

// A = List.Select(Table.ColumnNames(Clean_Start_Table), each Text.Contains(_, "€")),

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(_, "€"))
)

] [Expanded_Merged_Table]

invoked in this way:

let
Source = ConsumptionReview_2(Base_Price_EURO, Pipe_Size_Adder_PERC, "Region", "Type", "Pipe_Size", "Region", "Type", "Pipe_Size", "New")
in
Source

Everything is fine, the only problem I have is when I expand.

I get the following error message:

This surprised me a lot because I already used this type of codes, I just used the following part more different:

List.Select(Table.ColumnNames(Clean_Start_Table), each Text.Contains(_, "€"))

Could you help me?

Thanks.

[Mic1979]

Dear all,

any feedback on the last post?

Thanks a lot

[v-nmadadi-msft · Accepted solution]

Hi @Mic1979  ,
Thanks for reaching out to the Microsoft fabric community forum.

There may be a possibility that you are encountering data type mismatch, consider changing the data type from percentage to decimal number and then multiplying them and check if you are still encountering the issue.

Also consider using error handling  https://learn.microsoft.com/en-us/power-query/error-handling
to test and catch errors in your code.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS.
Thanks and Regards

[Mic1979]

Hello,

what I need now is to multiply the column with % in header and column with € in header .

I made the following function:

(
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]

I got the following:

How to get the calculation in the column Result instead of Function?

Thanks.

[wdx223_Daniel]

in your function code, the "Value" Column was created by NestedJoin in the step of Merge_Start_Table, and the next step refers Euro_Value, which is "New" come from the function's last arguement,  to expand, that is the error.

you can amend your code as this 

let
Source = ConsumptionReview_2(Base_Price_EURO, Pipe_Size_Adder_PERC, "Region", "Type", "Pipe_Size", "Region", "Type", "Pipe_Size", "Value")
in
Source

or just delete the last arguement and amend your function code of step "Expanded_Merged_Table" as this

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

[Mic1979]

Hello wdx223_Daniel

thanks.

This is the working solution:

let Source = ConsumptionReview_2(Base_Price_EURO, Pipe_Size_Adder_PERC, "Region", "Type", "Pipe_Size", "Region", "Type", "Pipe_Size", "Value") in Source

let Source = ConsumptionReview_2(Base_Price_EURO, Pipe_Size_Adder_PERC, "Region", "Type", "Pipe_Size", "Region", "Type", "Pipe_Size", "Value") in Source

Thanks again.