Power Query Assistance

[Fcoatis]

Hello community,

I need help with this:

 Table 1 registers tasks of people for a specific job, but it registers only the current team:

Table 2 keeps track of historical allocated teams for a specific job:

I would like Power Query to change Table 1 base on table 2: In this example case June 30th handlig1 task for that job would replace mary Jones by John Smith. Is it possible?

Thanks in advance.

Link for the model: https://www.dropbox.com/s/i0iju0r60g322bs/Historical.pbix?dl=0

[TomMartens]

Hey,

in the QueryEditor

1. Mark your first table
2. On the Home ribbon select "Merge Queries" (at the far right)
3. Select the second table
4. Mark the columns in both tables that can be considered as key columns
5. Select the join type
6. Expand the table, just select initial team
7. create a new custom column, get the value from initial team
8. remove all unwanted columns

Hope this gets your started

Regards

[Fcoatis]

Thank you @TomMartens,

That was my initial approach. But the table expansion gets records duplicated. After that result I dont know how to replace the task team and delete the duplicates I dont need. Check the link of the model if you will.

Best Regards

[TomMartens]

Hey

what columns did you use for the join, were you expecting duplicate rows

yes: why

no: maybe a column is missing during the join 

I will have a closer look at your model tomorrow.

Cheers

[Fcoatis]

Hey you,

I used fullJOB for the join and I didnt expect duplicates.

Thank you for your time.

[TomMartens · Accepted solution]

Hey,

I'm not sure if I understood your data model 100% but I used two columns for the Left Outer Join: fullJOB and Task

The changed file is here

After the I expanded the table and just selected the column 

Rows in the base Table "Jobs" have not been duplicated ...

After that I created a custom column Team", but as far as I understood your requirement it would also been sufficient to take Initial Team column

Here is the result in the report

Hope this helps

[Fcoatis]

Thank you @TomMartens,

My rookie knowledge wasnt aware of the possibility to link 2 columns in the merge step. That was the ace in the hole. next step: Go Live.

Best regards.