Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Here is the cenario that I need help:
I have 2 tables.
Table-1: WorkItems. This table has all latest Workitems from specific project with isCurrent = TRUE for all records.
WorItemId | Title | WorkIemType | State | Priority | ParentWorkItemId | Program | AssignedTo | Story Points | OriginalEstimate | CompletedWork | RemainingWork | IsCurrent |
40001 | Task1 | Task | New | 30001 | Project1 | Prabhat | 5 | 0 | 5 | TRUE | ||
40002 | Task2 | Task | Active | 30001 | Project1 | Prabhat | 6 | 2 | 4 | TRUE | ||
40003 | Task3 | Task | Closed | 30001 | Project1 | Rupak | 7 | 7 | 0 | TRUE | ||
40004 | Task4 | Task | Active | 30002 | Project1 | Alok | 2 | 1 | 1 | TRUE | ||
40005 | Task5 | Task | New | 30002 | Project1 | Prabhat | 6 | 0 | 6 | TRUE | ||
40006 | Task6 | Task | Closed | 30003 | Project1 | Prabhat | 4 | 4 | 0 | TRUE | ||
40007 | Task7 | Task | Active | 30003 | Project1 | Prabhat | 8 | 2 | 6 | TRUE | ||
40008 | Task8 | Task | Active | 30003 | Project1 | Prabhat | 3 | 1 | 2 | TRUE | ||
40009 | Task9 | Bug | New | 30004 | Project1 | Prabhat | 1 | 6 | 0 | 6 | TRUE | |
40010 | Task10 | Task | Active | 30007 | Project2 | Lokesh | 3 | 1 | 2 | TRUE | ||
40011 | Task11 | Task | Active | 30007 | Project2 | Hari | 5 | 3 | 2 | TRUE | ||
40012 | Task12 | Task | Active | 30007 | Project2 | Srinivas | 8 | 1 | 7 | TRUE | ||
40013 | Task13 | Task | Active | 30008 | Project2 | Rajesh | 3 | 0 | 3 | TRUE | ||
40014 | Task14 | Task | Active | 30008 | Project2 | Suma | 3 | 3 | 0 | TRUE |
Table-2: Revisions. This table has History records for all workitems from ALL projects where isCurrent = FALSE for all records.
WorItemId | Rev | RevDate | IsCurrent | Title | WorkIemType | State | Priority | ParentWorkItemId | Program | AssignedTo | Story Points | OriginalEstimate | CompletedWork | RemainingWork |
40001 | 1 | 1-Jan-24 | FALSE | Task1 | Task | New | 30001 | Project1 | Prabhat | 5 | 0 | 5 | ||
40001 | 2 | 2-Jan-24 | FALSE | Task1 | Task | New | 30001 | Project1 | Prabhat | 5 | 1 | 4 | ||
40001 | 3 | 3-Jan-24 | FALSE | Task1 | Task | New | 30001 | Project1 | Prabhat | 5 | 1 | 4 | ||
40001 | 4 | 4-Jan-24 | FALSE | Task1 | Task | New | 30001 | Project1 | Prabhat | 5 | 2 | 3 | ||
40005 | 1 | 1-Jan-24 | FALSE | Task5 | Task | New | 30002 | Project1 | Prabhat | 6 | 0 | 6 | ||
40005 | 2 | 4-Jan-24 | FALSE | Task5 | Task | New | 30002 | Project1 | Prabhat | 6 | 1 | 5 | ||
40005 | 3 | 5-Jan-24 | FALSE | Task5 | Task | New | 30002 | Project1 | Prabhat | 6 | 1 | 5 | ||
40005 | 4 | 6-Jan-24 | FALSE | Task5 | Task | New | 30002 | Project1 | Prabhat | 6 | 3 | 3 | ||
40005 | 5 | 7-Jan-24 | FALSE | Task5 | Task | New | 30002 | Project1 | Prabhat | 6 | 3 | 3 | ||
40005 | 6 | 8-Jan-24 | FALSE | Task5 | Task | New | 30002 | Project1 | Prabhat | 6 | 4 | 2 | ||
40011 | 1 | 7-Jan-24 | FALSE | Task11 | Task | Active | 30007 | Project2 | Hari | 5 | 3 | 2 | ||
40012 | 1 | 8-Jan-24 | FALSE | Task12 | Task | Active | 30007 | Project2 | Srinivas | 8 | 1 | 7 | ||
40012 | 2 | 9-Jan-24 | FALSE | Task12 | Task | Active | 30007 | Project2 | Srinivas | 8 | 2 | 6 | ||
40012 | 3 | 10-Jan-24 | FALSE | Task12 | Task | Active | 30007 | Project2 | Srinivas | 8 | 2 | 6 | ||
40015 | 1 | 7-Jan-24 | FALSE | Task100 | Task | Active | 30089 | Project20 | Srinivas | 8 | 2 | 6 | ||
40016 | 1 | 10-Jan-24 | FALSE | Task101 | Task | Active | 30078 | Project20 | Srinivas | 8 | 2 | 6 |
I want to have a new table that will store ALL records from WorkItems and ONLY Matching records (Based on WorkItemId) from Revisions table. The Rev and RevDate columns can be NULL in the new table for the record that is coming from WorkItems.
Thanks,
Prabhat
Solved! Go to Solution.
Hey @prabhatnath ,
I think I got your question.
You can first do an inner of the second table with the first, then only the IDs will be left that exist in the first table.
Afterwards you can do an append.
Please see the attached file for details.
If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
Best regards
Denis
Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
Hey @prabhatnath ,
I think I got your question.
You can first do an inner of the second table with the first, then only the IDs will be left that exist in the first table.
Afterwards you can do an append.
Please see the attached file for details.
If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
Best regards
Denis
Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
71 | |
70 | |
38 | |
28 | |
26 |
User | Count |
---|---|
98 | |
96 | |
59 | |
44 | |
40 |