Forum Discussion
Copy new Column value from one Table to another when there is exact match with all other column valu
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
Thank you very much.It worked
4 Replies
Thank you very much.It worked
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
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.
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]) )
