Calculation column

[sbott]

I have a spreadsheet where I use the formula =IF(S2="Lead Functional",R2*118,IF(S2="Lead Technical",R2*118,IF(S2="Analyst",R2*67.5,IF(S2="Technical",R2*112.5,IF(S2="Functional",R2*112.5,0)))))

It looks at what is in S2, then depending on the content it multiples R2 by a specific value. What is is doing is looking to see what the role type is (S2), then multiplying the hourly rate by the number of hours (R2). 

I want to create a column (assume custom column?) but not sure how to do it.  The columns I would use are Role, Hours and then I want a column called Sales Price with the multiplication. 

Can anyone advise please?

[slorin · Accepted solution]

Hi

Custom column formula = 

= [Hours] * 
Record.FieldOrDefault(
   [Lead Functional = 118, Lead Technical = 118, Analyst = 67.5, Technical = 112.5, Functional = 112.5],
   [Role],
   0)

Stéphane 

[sbott]

Many thanks, that works a treat.

[slorin]

Thanks,  but I just adapted AlienSx's solution

Stéphane 

[AlienSx]

rec = 
 [Lead Functional = 118,
 Lead Technical = 118,
 Analyst = 67.5,
 Technical = 112.5,
 Functional = 112.5],
price = Table.AddColumn(your_table, "Sales Price", each [Hours] * Record.FieldOrDefault(rec, [Role], 0))

[sbott]

Many thanks for taking time to reply, you can probably tell I am a newbie to Power Query. Where would I go from here?