Forum Discussion
Post PatronPower Query Assistance
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
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
6 Replies
- TomMartens
Super User
Hey,
in the QueryEditor
- Mark your first table
- On the Home ribbon select "Merge Queries" (at the far right)
- Select the second table
- Mark the columns in both tables that can be considered as key columns
- Select the join type
- Expand the table, just select initial team
- create a new custom column, get the value from initial team
- 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
