Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
prabhatnath
Advocate III
Advocate III

Append rows from 2 tables into a new table based on Matching column value

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.

WorItemIdTitleWorkIemTypeStatePriorityParentWorkItemIdProgramAssignedToStory PointsOriginalEstimateCompletedWorkRemainingWorkIsCurrent
40001Task1TaskNew 30001Project1Prabhat 505TRUE
40002Task2TaskActive 30001Project1Prabhat 624TRUE
40003Task3TaskClosed 30001Project1Rupak 770TRUE
40004Task4TaskActive 30002Project1Alok 211TRUE
40005Task5TaskNew 30002Project1Prabhat 606TRUE
40006Task6TaskClosed 30003Project1Prabhat 440TRUE
40007Task7TaskActive 30003Project1Prabhat 826TRUE
40008Task8TaskActive 30003Project1Prabhat 312TRUE
40009Task9BugNew 30004Project1Prabhat1606TRUE
40010Task10TaskActive 30007Project2Lokesh 312TRUE
40011Task11TaskActive 30007Project2Hari 532TRUE
40012Task12TaskActive 30007Project2Srinivas 817TRUE
40013Task13TaskActive 30008Project2Rajesh 303TRUE
40014Task14TaskActive 30008Project2Suma 330TRUE

 

Table-2: Revisions. This table has History records for all workitems from ALL projects where isCurrent = FALSE for all records. 

 

WorItemIdRevRevDateIsCurrentTitleWorkIemTypeStatePriorityParentWorkItemIdProgramAssignedToStory PointsOriginalEstimateCompletedWorkRemainingWork
4000111-Jan-24FALSETask1TaskNew 30001Project1Prabhat 505
4000122-Jan-24FALSETask1TaskNew 30001Project1Prabhat 514
4000133-Jan-24FALSETask1TaskNew 30001Project1Prabhat 514
4000144-Jan-24FALSETask1TaskNew 30001Project1Prabhat 523
4000511-Jan-24FALSETask5TaskNew 30002Project1Prabhat 606
4000524-Jan-24FALSETask5TaskNew 30002Project1Prabhat 615
4000535-Jan-24FALSETask5TaskNew 30002Project1Prabhat 615
4000546-Jan-24FALSETask5TaskNew 30002Project1Prabhat 633
4000557-Jan-24FALSETask5TaskNew 30002Project1Prabhat 633
4000568-Jan-24FALSETask5TaskNew 30002Project1Prabhat 642
4001117-Jan-24FALSETask11TaskActive 30007Project2Hari 532
4001218-Jan-24FALSETask12TaskActive 30007Project2Srinivas 817
4001229-Jan-24FALSETask12TaskActive 30007Project2Srinivas 826
40012310-Jan-24FALSETask12TaskActive 30007Project2Srinivas 826
4001517-Jan-24FALSETask100TaskActive 30089Project20Srinivas 826
40016110-Jan-24FALSETask101TaskActive 30078Project20Srinivas 826

 

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

1 ACCEPTED 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

View solution in original post

3 REPLIES 3
prabhatnath
Advocate III
Advocate III

Thank you @selimovd . That is exactly what I was looking for.

- Prabhat

selimovd
Super User
Super User

Hey @prabhatnath ,

 

where are you struggling?

And what did you try already?

 

Best regards

Denis

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.