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

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.

Choose Power Query or DAX based on when the value should change

A safe diagnosis changes one condition at a time and checks the result immediately. Define blank, null, error, and zero as separate business states. Record the account, device or client, and exact symptom so every later test uses the same starting point. Avoid combining resets, reinstalls, and permission changes because a successful result would not identify which action mattered.

Keep the first test reversible

Test conditions against a small table with known expected values. Use non-sensitive sample data and a standard user account whenever possible. If the sample succeeds, compare its location, ownership, policy, and data shape with the failing item rather than widening the repair to the whole application.

Keep the source column until the calculated result is validated. Preserve the original file, configuration, or identifier until the result is confirmed. A locked option or organization message is evidence of an administrative boundary, not an invitation to bypass policy.

Verify normal operation after the change

undefined. Close and reopen the relevant app, then repeat the ordinary workflow. Check a second client or observer only when it answers a specific question about synchronization, permissions, hardware, or service availability.

When results differ, note the app version, account type, time, network, and exact error. That information separates a device problem from account, data, permission, or service behavior and gives support a focused case. Remove personal names, addresses, document text, and tokens from screenshots before sharing them.

Have another authorized user repeat the shortest safe test if shared access is involved. Their result may expose owner-only privileges, stale state, or an assumption about storage. Keep the known-good configuration available until the change survives a restart, refresh, or normal work cycle, and reverse experimental settings that did not help. Check the outcome again after the next scheduled synchronization when timing can affect the feature.

Troubleshooting

1. If your new column isn’t populating:

• Check for typos in job titles or inconsistent capitalization.

2. 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.

3. For DAX errors:

• Confirm that column references are correct (table[column]).
• Ensure parentheses are balanced in the SWITCH statement.

4. If performance is slow:

• Consider using Power Query for large datasets.
• Optimize your data model by removing unnecessary columns.