Forum Discussion
Solved
Want to filter based on condition
I have a table with ISSUE_KEY appearing in multiple sprints (Multiple Rows). The requirement is to know in which sprint this Issue_Key was created. To do that we should use the condition (Created Date of that issue should fall eaual to or between Start_date and End Date.
CREATED >=START_DATE and <=COMPLETE_DATE
Rest of the ISSUE_KEY duplicates should be discarded.
The Table look like
| ISSUE_KEY | ISSUE_TYPE_NAME | ISSUE_STATUS_NAME | PRIORITY | RESOLUTION | PROJECT_KEY | CREATED | RESOLUTION_DATE | Detailed_Affected_Version | Story_Points | Task_Type | SPRINT_NAME | AFFECTS_VERSION | FIX_VERSION | Labels_Name | STATE | START_DATE | END_DATE | COMPLETE_DATE |
| ABC-56727 | Sub-task | Resolved | Major | Fixed | ABC | 09-06-2022 11:27:53 +00:00 | 22-06-2022 07:10:41 +00:00 | null | 0 | In-sprint Bug | Sprint 173 | null | null | team-filterkaapi | closed | 25-04-2022 04:09:00 +00:00 | 13-05-2022 16:00:00 +00:00 | 13-05-2022 19:28:04 +00:00 |
| ABC-56739 | Sub-task | Resolved | Major | Fixed | ABC | 09-06-2022 22:10:05 +00:00 | 17-06-2022 06:45:07 +00:00 | null | 0 | In-sprint Bug | Sprint 173 | null | null | team-filterkaapi | closed | 25-04-2022 04:09:00 +00:00 | 13-05-2022 16:00:00 +00:00 | 13-05-2022 19:28:04 +00:00 |
| ABC-56809 | Sub-task | Resolved | Major | Fixed | ABC | 14-06-2022 14:40:48 +00:00 | 22-06-2022 05:34:10 +00:00 | null | 0 | In-sprint Bug | Sprint 173 | null | null | team-filterkaapi | closed | 25-04-2022 04:09:00 +00:00 | 13-05-2022 16:00:00 +00:00 | 13-05-2022 19:28:04 +00:00 |
| ABC-56727 | Sub-task | Resolved | Major | Fixed | ABC | 09-06-2022 11:27:53 +00:00 | 22-06-2022 07:10:41 +00:00 | null | 0 | In-sprint Bug | Sprint 174 | null | null | team-filterkaapi | closed | 16-05-2022 04:00:00 +00:00 | 03-06-2022 16:00:00 +00:00 | 05-06-2022 13:26:54 +00:00 |
| ABC-56739 | Sub-task | Resolved | Major | Fixed | ABC | 09-06-2022 22:10:05 +00:00 | 17-06-2022 06:45:07 +00:00 | null | 0 | In-sprint Bug | Sprint 174 | null | null | team-filterkaapi | closed | 16-05-2022 04:00:00 +00:00 | 03-06-2022 16:00:00 +00:00 | 05-06-2022 13:26:54 +00:00 |
| ABC-56809 | Sub-task | Resolved | Major | Fixed | ABC | 14-06-2022 14:40:48 +00:00 | 22-06-2022 05:34:10 +00:00 | null | 0 | In-sprint Bug | Sprint 174 | null | null | team-filterkaapi | closed | 16-05-2022 04:00:00 +00:00 | 03-06-2022 16:00:00 +00:00 | 05-06-2022 13:26:54 +00:00 |
| ABC-56727 | Sub-task | Resolved | Major | Fixed | ABC | 09-06-2022 11:27:53 +00:00 | 22-06-2022 07:10:41 +00:00 | null | 0 | In-sprint Bug | Sprint 175 | null | null | team-filterkaapi | closed | 06-06-2022 04:00:00 +00:00 | 24-06-2022 16:00:00 +00:00 | 25-06-2022 13:38:18 +00:00 |
| ABC-56739 | Sub-task | Resolved | Major | Fixed | ABC | 09-06-2022 22:10:05 +00:00 | 17-06-2022 06:45:07 +00:00 | null | 0 | In-sprint Bug | Sprint 175 | null | null | team-filterkaapi | closed | 06-06-2022 04:00:00 +00:00 | 24-06-2022 16:00:00 +00:00 | 25-06-2022 13:38:18 +00:00 |
| ABC-56809 | Sub-task | Resolved | Major | Fixed | ABC | 14-06-2022 14:40:48 +00:00 | 22-06-2022 05:34:10 +00:00 | null | 0 | In-sprint Bug | Sprint 175 | null | null | team-filterkaapi | closed | 06-06-2022 04:00:00 +00:00 | 24-06-2022 16:00:00 +00:00 | 25-06-2022 13:38:18 +00:00 |
| ABC-56727 | Sub-task | Resolved | Major | Fixed | ABC | 09-06-2022 11:27:53 +00:00 | 22-06-2022 07:10:41 +00:00 | null | 0 | In-sprint Bug | Sprint 176 | null | null | team-filterkaapi | closed | 27-06-2022 06:00:00 +00:00 | 15-07-2022 18:00:00 +00:00 | 16-07-2022 13:04:35 +00:00 |
| ABC-56739 | Sub-task | Resolved | Major | Fixed | ABC | 09-06-2022 22:10:05 +00:00 | 17-06-2022 06:45:07 +00:00 | null | 0 | In-sprint Bug | Sprint 176 | null | null | team-filterkaapi | closed | 27-06-2022 06:00:00 +00:00 | 15-07-2022 18:00:00 +00:00 | 16-07-2022 13:04:35 +00:00 |
| ABC-56809 | Sub-task | Resolved | Major | Fixed | ABC | 14-06-2022 14:40:48 +00:00 | 22-06-2022 05:34:10 +00:00 | null | 0 | In-sprint Bug | Sprint 165 | null | null | team-filterkaapi | closed | 01-11-2021 05:00:00 +00:00 | 19-11-2021 17:00:00 +00:00 | 19-11-2021 17:30:50 +00:00 |
| ABC-56809 | Sub-task | Resolved | Major | Fixed | ABC | 14-06-2022 14:40:48 +00:00 | 22-06-2022 05:34:10 +00:00 | null | 0 | In-sprint Bug | Sprint 166 | null | null | team-filterkaapi | closed | 22-11-2021 05:00:00 +00:00 | 10-12-2021 17:00:00 +00:00 | 10-12-2021 18:45:24 +00:00 |
| ABC-56809 | Sub-task | Resolved | Major | Fixed | ABC | 14-06-2022 14:40:48 +00:00 | 22-06-2022 05:34:10 +00:00 | null | 0 | In-sprint Bug | Sprint 172 | null | null | team-filterkaapi | closed | 04-04-2022 04:00:00 +00:00 | 22-04-2022 16:00:00 +00:00 | 23-04-2022 17:51:21 +00:00 |
The Expected Results would be
| ISSUE_KEY | ISSUE_TYPE_NAME | ISSUE_STATUS_NAME | PRIORITY | RESOLUTION | PROJECT_KEY | CREATED | RESOLUTION_DATE | Detailed_Affected_Version | Story_Points | Task_Type | SPRINT_NAME | AFFECTS_VERSION | FIX_VERSION | Labels_Name | STATE | START_DATE | END_DATE | COMPLETE_DATE |
| ABC-56727 | Sub-task | Resolved | Major | Fixed | ABC | 09-06-2022 11:27:53 +00:00 | 22-06-2022 07:10:41 +00:00 | null | 0 | In-sprint Bug | Sprint 175 | null | null | team-filterkaapi | closed | 06-06-2022 04:00:00 +00:00 | 24-06-2022 16:00:00 +00:00 | 25-06-2022 13:38:18 +00:00 |
| ABC-56739 | Sub-task | Resolved | Major | Fixed | ABC | 09-06-2022 22:10:05 +00:00 | 17-06-2022 06:45:07 +00:00 | null | 0 | In-sprint Bug | Sprint 175 | null | null | team-filterkaapi | closed | 06-06-2022 04:00:00 +00:00 | 24-06-2022 16:00:00 +00:00 | 25-06-2022 13:38:18 +00:00 |
| ABC-56809 | Sub-task | Resolved | Major | Fixed | ABC | 14-06-2022 14:40:48 +00:00 | 22-06-2022 05:34:10 +00:00 | null | 0 | In-sprint Bug | Sprint 175 | null | null | team-filterkaapi | closed | 06-06-2022 04:00:00 +00:00 | 24-06-2022 16:00:00 +00:00 | 25-06-2022 13:38:18 +00:00 |
you can add a colunn in PQ
=if [CREATED]>=[START_DATE] and [CREATED]<=[COMPLETE_DATE] then true else false
then filter the new column
pls see the attachement below
2 Replies
you can add a colunn in PQ
=if [CREATED]>=[START_DATE] and [CREATED]<=[COMPLETE_DATE] then true else false
then filter the new column
pls see the attachement below
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"ISSUE_KEY", type text}, {"ISSUE_TYPE_NAME", type text}, {"ISSUE_STATUS_NAME", type text}, {"PRIORITY", type text}, {"RESOLUTION", type text}, {"PROJECT_KEY", type text}, {"CREATED", type datetime}, {"RESOLUTION_DATE", type datetime}, {"Detailed_Affected_Version", type any}, {"Story_Points", Int64.Type}, {"Task_Type", type text}, {"SPRINT_NAME", type text}, {"AFFECTS_VERSION", type any}, {"FIX_VERSION", type any}, {"Labels_Name", type text}, {"STATE", type text}, {"START_DATE", type datetime}, {"END_DATE", type datetime}, {"COMPLETE_DATE", type datetime}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [CREATED]>=[START_DATE] and [CREATED]<=[END_DATE]), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}) in #"Removed Columns"Hope this helps.
