In Excel Power query I want to return a rate if the job code equals xxxx...

Question

In Excel Power query I want to add a column that returns a rate if the job code equals xxxx. There are multiple job codes. The Job codes are in their own column.

So if column C equals 4C03 then the added billing_rate column equals $2.50, if not then move on to the next criteria.

Can I add a custom column with an embedded if then statement?

Answer 1

Yes, you can add a custom column in Excel Power Query using an embedded if-then statement to return a rate based on the job code. You can do this by following these steps:

1. In Power Query, go to the Add Column tab.
2. Select Custom Column.
3. In the Custom Column dialog, you can write your if-then logic. For example:

      if [JobCode] = "4C03" then 2.50 else null
   

   This formula checks if the value in the JobCode column equals "4C03". If it does, it returns $2.50; otherwise, it returns null.
4. You can add more conditions by using else if statements to check for other job codes and their corresponding rates. For example:

      if [JobCode] = "4C03" then 2.50 
      else if [JobCode] = "5D04" then 3.00 
      else null
   

This way, you can create a custom column that evaluates multiple job codes and returns the appropriate billing rate based on your criteria.

Answer 2

Hello @Becky Fairchild,  

Thank you for reaching out to the Q&A forum! 

Based on your information, I created a sample and tested it in my environment. Here’s what I did and detail steps you can try: 

• You select the table, then you go to “Data” => choose “From Table/Range” to open Power Query. 
• In the Power Query Editor, you choose “Add Column” => “Custom Column”, you can rename the new column to billing_rate, and you enter the following formula: 

= if [JobCode] = "4C03" then 2.50 
else if [JobCode] = "ZX99" then 3.75 
else if [JobCode] = "AB12" then 4.25 
else null

 For explaining: 

• [JobCode] refers to the column name in your table. 
• “null” means if the value does not match any of the conditions, it will remain blank (you can change this to 0 or any default value). 

After applying this, the result looked like this: 

Once again, thank you again for raising this issue; it’s helped broaden my understanding. I hope you understand that I want to help as much as I can within the scope of this forum.

Look forward to hearing from you!     

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Answer 3

Hi,

You may also create a 2 column rate table and then join this with your existing table.