How to fill a column based on other values in PowerBI?

by

Problem statement:
“I’m working on an HR Power BI report and need to fill a column with values based on another column. For example, I want to automatically assign a department to each employee based on their job title. How can I accomplish this using Power BI and Power Query without manually entering each value?”

Add a column based on other column values in Power Query


In Power BI, filling a column with values based on another column is a common task, especially in HR reporting. This process, known as data transformation, can be accomplished using either Power Query or DAX formulas. We’ll explore both methods to automate department assignment based on job titles.

Data Model

Table Name: EmployeeData
Columns: EmployeeID (Number),FullName (Text) ,JobTitle (Text), Department (Text, to be filled), HireDate (Date), Salary (Number)

Filling columns in Power Query

  • Open Power Query Editor:
  • Click “Edit Queries” in the Home tab.
  • Add a custom column:
  • Select “Add Column”, then select “Custom Column”.
  • Name the new column “Department”.
  • Enter the following if-then statement written as M code:
   if [JobTitle] = "Software Engineer" or [JobTitle] = "Data Analyst" then "IT"
   else if [JobTitle] = "Accountant" or [JobTitle] = "Financial Analyst" then "Finance"
   else if [JobTitle] = "HR Specialist" or [JobTitle] = "Recruiter" then "Human Resources"
   else if [JobTitle] = "Sales Representative" or [JobTitle] = "Account Manager" then "Sales"
   else "Other"
  • Click “OK” to apply the changes.
  • Close & Apply to update the model.

Fill a calculated column in Power BI

  • In the Data view, select the EmployeeData table.
  • Click “New Column” in the Table tools tab.
  • Then go ahead and enter the following DAX formula:
       Department = 
   SWITCH(
       TRUE(),
       OR(EmployeeData[JobTitle] = "Software Engineer", EmployeeData[JobTitle] = "Data Analyst"), "IT",
       OR(EmployeeData[JobTitle] = "Accountant", EmployeeData[JobTitle] = "Financial Analyst"), "Finance",
       OR(EmployeeData[JobTitle] = "HR Specialist", EmployeeData[JobTitle] = "Recruiter"), "Human Resources",
       OR(EmployeeData[JobTitle] = "Sales Representative", EmployeeData[JobTitle] = "Account Manager"), "Sales",
       "Other"
   )


    Both methods outlined above use conditional logic to assign departments based on job titles. The Power Query method is preferable for data preparation as it’s more efficient and doesn’t increase the model size. The Power BI / DAX method is useful for dynamic calculations or when you need to reference other tables.

    Troubleshooting

    1. If your new column isn’t populating:
    • Check for typos in job titles or inconsistent capitalization.
    1. If you get errors in Power Query:
    • Verify that column names match exactly in your M code.
    • Check for syntax errors like missing quotation marks.
    1. For DAX errors:
    • Confirm that column references are correct (table[column]).
    • Ensure parentheses are balanced in the SWITCH statement.
    1. If performance is slow:
    • Consider using Power Query for large datasets.
    • Optimize your data model by removing unnecessary columns.