Copy new Column value from one Table to another when there is exact match with all other column valu

[JeevanMallya]

I have a Table called SPRINT_MASTER where ISSUE_KEY appear multiple times in different sprints

PROJECT_KEY

BOARD_NAME

ISSUE_KEY

SPRINT_NAME

SPRINT_NUMBER

INITIAL_ESTIMATE

CURRENT_ESTIMATE

SPRINT_REPORT_STATUS

IS_ADDED_DURING_SPRINT

STORY_POINTS_COMMITTED

STORY_POINTS_EXTRA_PULLED

STORY_POINTS_COMPLETED

STORY_POINTS_EXTRA_COMPLETED

STORY_POINTS_NOT_COMPLETED

STORY_POINTS_REMOVED

ISSUE_TYPE_NAME

ISSUE_STATUS_NAME

PRIORITY

RESOLUTION

CREATED

Detailed_Affected_Version

Story_Points

Task_Type

STATE

START_DATE

END_DATE

COMPLETE_DATE

STORY_COMITTED

STORY_POINTS_COMMITED_START

ABC

ABC Scrum Board

ABC-473

ABC Sprint 31

31

3

3

INCOMPLETED

FALSE

3

0

null

null

3

0

Story

Closed

Major

Fixed

22-09-2022 11:14:16 +00:00

null

3

null

closed

06-05-2024 13:00:50 +00:00

24-05-2024 17:00:00 +00:00

27-05-2024 06:32:08 +00:00

1

3

ABC

ABC Scrum Board

ABC-473

ABC Sprint 32

32

3

3

INCOMPLETED

FALSE

3

0

null

null

3

0

Story

Closed

Major

Fixed

22-09-2022 11:14:16 +00:00

null

3

null

closed

27-05-2024 13:58:34 +00:00

14-06-2024 18:00:00 +00:00

15-06-2024 10:09:34 +00:00

1

3

ABC

ABC Scrum Board

ABC-473

ABC Sprint 33

33

3

3

COMPLETED

FALSE

3

0

3

0

0

0

Story

Closed

Major

Fixed

22-09-2022 11:14:16 +00:00

null

3

null

closed

17-06-2024 13:01:04 +00:00

05-07-2024 19:00:00 +00:00

08-07-2024 07:08:23 +00:00

1

3

I have another Table called UNIQUE_STORY_POINTS where i have considered only ISSUE_KEY once.In this Table there is an new column called TOTAL_UNIQUE_STORY_POINTS. I want to copy the new Column to SPRINT_MASTER Table on condition that all Column Values in Specific Rows should match and where there is no match add zero. Something like below

PROJECT_KEY

BOARD_NAME

ISSUE_KEY

SPRINT_NAME

SPRINT_NUMBER

INITIAL_ESTIMATE

CURRENT_ESTIMATE

SPRINT_REPORT_STATUS

IS_ADDED_DURING_SPRINT

STORY_POINTS_COMMITTED

STORY_POINTS_EXTRA_PULLED

STORY_POINTS_COMPLETED

STORY_POINTS_EXTRA_COMPLETED

STORY_POINTS_NOT_COMPLETED

STORY_POINTS_REMOVED

ISSUE_TYPE_NAME

ISSUE_STATUS_NAME

PRIORITY

RESOLUTION

CREATED

Detailed_Affected_Version

Story_Points

Task_Type

STATE

START_DATE

END_DATE

COMPLETE_DATE

STORY_COMITTED

STORY_POINTS_COMMITED_START

TOTAL_UNIQUE_STORY_POINTS

ABC

ABC Scrum Board

ABC-473

ABC Sprint 31

31

3

3

INCOMPLETED

FALSE

3

0

null

null

3

0

Story

Closed

Major

Fixed

22-09-2022 11:14:16 +00:00

null

3

null

closed

06-05-2024 13:00:50 +00:00

24-05-2024 17:00:00 +00:00

27-05-2024 06:32:08 +00:00

1

3

0

ABC

ABC Scrum Board

ABC-473

ABC Sprint 32

32

3

3

INCOMPLETED

FALSE

3

0

null

null

3

0

Story

Closed

Major

Fixed

22-09-2022 11:14:16 +00:00

null

3

null

closed

27-05-2024 13:58:34 +00:00

14-06-2024 18:00:00 +00:00

15-06-2024 10:09:34 +00:00

1

3

0

ABC

ABC Scrum Board

ABC-473

ABC Sprint 33

33

3

3

COMPLETED

FALSE

3

0

3

0

0

0

Story

Closed

Major

Fixed

22-09-2022 11:14:16 +00:00

null

3

null

closed

17-06-2024 13:01:04 +00:00

05-07-2024 19:00:00 +00:00

08-07-2024 07:08:23 +00:00

1

3

3

Can you please help

[JeevanMallya · Accepted solution]

Thank you very much.It worked

[JeevanMallya]

I had followed the step mentioned earlier as well but didnt get the results since TOTAL_UNIQUE_STORY_POINTS values get added to other columns as well instead of null or zero. I wanted it to copy only to specific Column where all Row value matches specifically sprint number.

In Table SPRINT_MASTER ISSUE_KEY appear more than once but in UNIQUE_STORY_POINTS Table i have built logic to consider it once and built a new column TOTAL_UNIQUE_STORY_POINTS column to calculate story points. So ideally when i merge or add this column to Sprint Master, i want this value to be added to specific row rather than duplicating it to other colums of issue_key.

• Highlisht the SPRINT_MASTER table
• Go to the ribbon > Home > Merge Queries
• Select the Issue Key in Both tables
• Expand the UNIQUE_STORY_POINTS column only
• Highlight the expaned column and in the ribbon choose Replace Values
• Enter null in the first field and then 0 in the below field
• Load the table

PROJECT_KEY

BOARD_NAME

ISSUE_KEY

SPRINT_NAME

SPRINT_NUMBER

INITIAL_ESTIMATE

CURRENT_ESTIMATE

SPRINT_REPORT_STATUS

IS_ADDED_DURING_SPRINT

STORY_POINTS_COMMITTED

STORY_POINTS_EXTRA_PULLED

STORY_POINTS_COMPLETED

STORY_POINTS_EXTRA_COMPLETED

STORY_POINTS_NOT_COMPLETED

STORY_POINTS_REMOVED

ISSUE_TYPE_NAME

ISSUE_STATUS_NAME

PRIORITY

RESOLUTION

CREATED

Detailed_Affected_Version

Story_Points

Task_Type

STATE

START_DATE

END_DATE

COMPLETE_DATE

STORY_COMITTED

STORY_POINTS_COMMITED_START

UNIQUE_STORY_POINTS.TOTAL_UNIQUE_STORY_POINTS

ABC

ABC Scrum Board

ABC-473

ABC Sprint 31

31

3

3

INCOMPLETED

FALSE

3

0

null

null

3

0

Story

Closed

Major

Fixed

22-09-2022 11:14:16 +00:00

null

3

null

closed

06-05-2024 13:00:50 +00:00

24-05-2024 17:00:00 +00:00

27-05-2024 06:32:08 +00:00

1

3

3

ABC

ABC Scrum Board

ABC-473

ABC Sprint 32

32

3

3

INCOMPLETED

FALSE

3

0

null

null

3

0

Story

Closed

Major

Fixed

22-09-2022 11:14:16 +00:00

null

3

null

closed

27-05-2024 13:58:34 +00:00

14-06-2024 18:00:00 +00:00

15-06-2024 10:09:34 +00:00

1

3

3

ABC

ABC Scrum Board

ABC-473

ABC Sprint 33

33

3

3

COMPLETED

FALSE

3

0

3

0

0

0

Story

Closed

Major

Fixed

22-09-2022 11:14:16 +00:00

null

3

null

closed

17-06-2024 13:01:04 +00:00

05-07-2024 19:00:00 +00:00

08-07-2024 07:08:23 +00:00

1

3

3

Can you please help.

[Joe_Barry]

If you are using the Power Query solution, you can merge on multiple columns. If the same columns are in each table.

This is important, highlight the columns you want to merge on in the same order! Example:

• Highlight SPRINT_MASTER
• Merge Queries
• In the SPRINT_MASTER highlight the ISSUE_KEY, hold CTRL on your keyboard and choose the other columns
• In UNIQUE_STORY_POINTS highlight the same columns in the same order

DAX Solution

Unique Story Points =
IF(
CALCULATE(
    MAX(UNIQUE_STORY_POINTS[TOTAL_UNIQUE_STORY_POINTS])
    ,UNIQUE_STORY_POINTS[ISSUE_KEY] = [ISSUE_KEY]
   && UNIQUE_STORY_POINTS[SPRINT_NUMBER] = [SPRINT_NUMBER]) ) 
= BLANK(), 0 ,
CALCULATE(
    MAX(UNIQUE_STORY_POINTS[TOTAL_UNIQUE_STORY_POINTS])
    ,UNIQUE_STORY_POINTS[ISSUE_KEY] = [ISSUE_KEY]
     && UNIQUE_STORY_POINTS[SPRINT_NUMBER] = [SPRINT_NUMBER]) 
)

[Joe_Barry]

Hi @JeevanMallya 

I hope I understood you questions correctly.

Why don't you create a one to many relationship between from UNIQUE_STORY_POINTS  table  ISSUE_KEY column  to the SPRINT_MASTER ISSUE_KEY column?

If you create a Matrix visual add all the columns from SPRINT_MASTER and then add the TOTAL_UNIQUE_STORY_POINTS column from UNIQUE_STORY_POINTS, you will get the result you need.

You can do measures if you want to use the column for calculations.

Otherwise, if you need to have it in the table you can use DAX

Unique Story Points =
IF(
CALCULATE(
    MAX(UNIQUE_STORY_POINTS[TOTAL_UNIQUE_STORY_POINTS])
    ,UNIQUE_STORY_POINTS[ISSUE_KEY] = [ISSUE_KEY]) 
= BLANK(), 0 ,
CALCULATE(
    MAX(UNIQUE_STORY_POINTS[TOTAL_UNIQUE_STORY_POINTS])
    ,UNIQUE_STORY_POINTS[ISSUE_KEY] = [ISSUE_KEY]) 
)

You can also merge the tables in Power Query

• Highlisht the SPRINT_MASTER table
• Go to the ribbon > Home > Merge Queries
• Select the Issue Key in Both tables
• Expand the UNIQUE_STORY_POINTS column only
• Highlight the expaned column and in the ribbon choose Replace Values
• Enter null in the first field and then 0 in the below field
• Load the table

Hope this helps

Joe