Mutiply two columns using Lists and header name

[Mic1979]

Dear all

I have this table. Of course this is an abstract of my dataset:

I need to multiply the columns having "Volumes" and "Distribution" in the header, and I would like to use the lists, as my dataset is much bigger than what I am showing here.

So I created this function:

Change_Volume_Header = Table.TransformColumnNames (RemoveServerHelper, each if Text.Contains (_,"Volumes") then _& " OLD" else _),
SelectVolumeColumn = List.Select (Table.ColumnNames (Change_Volume_Header), each Text.Contains (_, "Volumes")),
SelectDistributionColumn = List.Select (Table.ColumnNames (Change_Volume_Header), each Text.Contains (_, "Distribution"))

I changed the header adding the "OLD" text because at the end the columns having "OLD" in the header will be cancelled. 

Sorry but now I don't know how to proceed. 

I tried this:

Volumes_Year3New = Table.AddColumn (
Change_Volume_Header,
"Volumes_Year3New",
each Record.Field (_,SelectVolumeColumn) * Record.Field (_,SelectDistributionColumn)
)

But I got this:

An error occurred in the ‘’ query. Expression.Error: We cannot convert a value of type List to type Text.

Any support?

Thanks.

[SundarRaj · Accepted solution]

Hi @Mic1979 ,
This error is because you are multiplying Records with Records when you say Record.Field (_,SelectVolumeColumn) * Record.Field (_,SelectDistributionColumn).

You need to convert Record into a value in order to multiply them together. See the images below. I'll leave the code below. Thanks

Solution:

Code:

let
Source =
#table(
{"Region (DB Key)", "Project_Step (DB Key)", "Function_Description (DB Key)", "Volumes Year3", "Product_Series_Description (EXPAND)", "Distribution (EXPAND)"},
{
{"AM.", "Step 2.2", "ON/OFF", 715.4, "2 way", 1},
{"AM.", "Step 2.2", "Prop.", 306.6, "2 way", 1},
{"APAC", "Step 2.2", "ON/OFF", 444.5, "2 way", 1},
{"EMEA", "Step 2.2", "ON/OFF", 136.6666667, "2 way", 1},
{"APAC", "Step 2.2", "Prop.", 190.5, "2 way", 1},
{"EMEA", "Step 2.2", "Prop.", 273.3333333, "2 way", 1}
}
),
Change_Volume_Header = Table.TransformColumnNames (Source, each if Text.Contains (_,"Volumes") then _& " OLD" else _),
SelectVolumeColumn = List.Select (Table.ColumnNames (Change_Volume_Header), each Text.Contains (_, "Volumes")),
SelectDistributionColumn = List.Select (Table.ColumnNames (Change_Volume_Header), each Text.Contains (_, "Distribution")),
Custom1 = SelectDistributionColumn,
Custom2 = Table.AddColumn ( Change_Volume_Header , "Volumes_Year3New" , each Record.ToList ( Record.SelectFields ( _ , SelectVolumeColumn ) ){0} * Record.ToList ( Record.SelectFields ( _ , SelectDistributionColumn ) ){0} )
in
Custom2

[Mic1979]

Working..thanks