Forum Discussion
Helper VAdd values into a different table
Dear members hello,
I have a really difficult problem that I have not yet been able to solve.
I have 2 unrelated fact tables. The one table has salary data and the second table expenses data.
Salary Table:
Employee ID Salary Period ID Country ID
1000 1000 20210101 1
1001 900 20210101 2
1002 800 20210101 3
1003 1100 20210101 4
1004 1200 20210101 5
1000 1000 20210201 1
1001 900 20210201 2
1002 800 20210201 3
1003 1100 20210201 4
1004 1200 20210201 5
Expenses Table:
Employee ID Expenses Category Period ID Country ID
1000 100 Tickets 20210101 1
1001 200 Food 20210101 2
1002 100 Taxi 20210101 3
1003 200 Car 20210101 4
1004 50 Tickets 20210101 5
1000 20 Food 20210201 1
1001 35 Taxi 20210201 2
1002 60 Car 20210201 3
1003 80 Food 20210201 4
1004 100 Taxi 20210201 5
I would like to find a way to add the salary data of employee with id: 1001 to the expenses table as expenses to the employee with id 1002 at the same period ID and Country ID in the category "Car" if it exist otherwise to be filled in as a car category.
Is that possible?? I can't find a way to achive that 😞
You can use Power Query to do this as you need to add new rows when employee 1002 doesn't have car category with some period and country IDs. Click "Transform data" to open Power Query Editor to transform tables.
Here are my steps to transform both tables:
Salary Table:
1. Filter "Employee ID" column to only have rows that Employee ID = 1001;
2. Replace "1001" with "1002" in "Employee ID" column;
3. Add a custom column whose value is "Car", column name is "Category";
4. Rename "Salary" column to "Expenses".
Now you will have below table, which has five columns whose names are totally consistent with those in Expenses Table. This is Employee 1002's Car expenses in every period and country.
Expenses Table:
5. Append above transformed Salary Table to Expenses Table;
6. Group rows by four columns and perform Sum on "Expenses" column;
7. Sort Rows.
Then you will have the following result. ( * In your sample data, Employee 1001 has country ID 2 and 1002 has country ID 3, which cannot be matched. So I changed both to country ID 2 to test it. )
In the following image, I have highlighted most features used to transform tables in above steps. Sample pbix is at bottom.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
3 Replies
v-jingzhangCommunity Support
You can use Power Query to do this as you need to add new rows when employee 1002 doesn't have car category with some period and country IDs. Click "Transform data" to open Power Query Editor to transform tables.
Here are my steps to transform both tables:
Salary Table:
1. Filter "Employee ID" column to only have rows that Employee ID = 1001;
2. Replace "1001" with "1002" in "Employee ID" column;
3. Add a custom column whose value is "Car", column name is "Category";
4. Rename "Salary" column to "Expenses".
Now you will have below table, which has five columns whose names are totally consistent with those in Expenses Table. This is Employee 1002's Car expenses in every period and country.
Expenses Table:
5. Append above transformed Salary Table to Expenses Table;
6. Group rows by four columns and perform Sum on "Expenses" column;
7. Sort Rows.
Then you will have the following result. ( * In your sample data, Employee 1001 has country ID 2 and 1002 has country ID 3, which cannot be matched. So I changed both to country ID 2 to test it. )
In the following image, I have highlighted most features used to transform tables in above steps. Sample pbix is at bottom.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Dimitris_KatsThank you very very much.
This is really complex. I need to test it and come back to you.
So sorry for the late reply. I didn't get a notice for your post.
Thank you very much again!
- amitchandak
Super User
Dimitris_Kats , can explain the car part. May be expected output can help
You can create a new column like
sumx(filter(salary, salary[Employee ID] = expense[Employee ID] && salary[Period ID] = expense[Period ID]
&& salary[Country ID] = expense[Country ID]),Salary [Salary])
or
sumx(filter(salary, salary[Employee ID] = expense[Employee ID] && salary[Period ID] = expense[Period ID]
&& salary[Country ID] = expense[Country ID] && expense[Category] = "Car"),Salary [Salary])
