Dynamic Variable in Calculated Columns

Step 1) Merge the weights table into your main table

In Power Query, select your main query → Home → Merge Queries → select your weights table → match on the Year column → Join Kind: Left Outer → OK.

Step 2) Expand the merged columns

Click the expand icon on the new merged column → select only the three weight columns (Wage weight, Job Postings weight, Change weight) → uncheck Use original column name as prefix → OK.

Step 3) Replace your custom column formula

Instead of hardcoded values, reference the looked-up weight columns:

= ( [Relative Wage] * [Wage Weight] )
+ ( [Relative Job Postings] * [Job Postings Weight] )
+ ( [Relative Change] * [Change Weight] )

Step 4) Remove the weight columns from the final table if not needed for display

Select the three weight columns → right-click → Remove Columns to keep the output clean.

But keep in mind that: