Merge Queries - Expanded Table uses a formula

[ryan_b_fiting]

Hello Community - 

I am in need of some assistance.  I have a step in Power query where I am merging queries.  Once merged, I am expanding a single column from that merged query which is a 'Bonus Rate'.  The end value needs to be a Bonus $ Amount.  Instead of having to create another step that would create a Custom Column to simply take the Salary ($) * Bonus Rate (which is the expanded column from the merged query), is there a way to nest the custom column step into the 'Expand Merge Queries' step? 

Currently My query step is this:

= Table.ExpandTableColumn(#"Merge Bonus Data", "Bonus", {"Rate with Vacancy Hedge"}, {"Bonus Rate"})

My next step would be to create the custom column, but I want to know if there is a way that I can take the step from above and tweak it to hace the "Rate with Vacancy Hedge" * Salary($) in the same step?

I have several columns where I need to do this, so ideally rather than having to create a custom column for each one, I would like to consolidate those steps.

Thanks in advance for any insights!

Ryan F

[ImkeF · Accepted solution]

Hello @ryan_b_fiting ,
you cannot include the calculation in the Expand-step/formula.
But if you are sure that there will be always just one row in the merged column, then instead of expanding the table, you could reference the required field in the unexpanded table instead like so:

Table.AddColumn(#"Merge Bonus Data", "myCalculation", each  [Bonus]{0}[#"Rate with Vacancy Hedge"] * [#"Salary($)"])