Add values into a different table

[Dimitris_Kats]

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 😞

[v-jingzhang · Accepted solution]

Hi @Dimitris_Kats 

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_Kats]

Thank 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]

@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])