Aggregate columns from one table into rows of another table

[abedkhooli]

I have dynamic data in a few columns in Table2 (see image). In Table 1 (created manually - enter data), each row represents one of the columns in Table 2. I need to replace the placeholders in the 3rd column of Table 1 with the average of the values in the indicated columns from Table 2 (the two tables are not related). Tried conditional column but it does not accept formulas in the value field. Thought about Table.FromRows and got an error (not sure if formula or reference related). Any ideas? Hopefully it is simple, not an expert!

[MarcelBeug · Accepted solution]

Easiest is to add the averages as a new column.

So, if you delete the column with placeholders from Table1, you can add it in your query on the tab "Add Column", option "Custom Column" with name AvNO2

and formula

= List.Average(Table.Column(Table2,[EggName]&"NO2"))

or, if you need to catch any errors:

= try List.Average(Table.Column(Table2,[EggName]&"NO2")) otherwise null

[abedkhooli]

Thank you @MarcelBeug - this works very well.

Although naming was not intended for calculation purposes, your formula made it elegant and simple. Will add my other columns in the same way.

[7storms]

Thank you, worked for me!