Forum Discussion
JeevanMallya
Resolver II
Resolver IIFilter rows in query editor up to current Week
I have a Table that contains Columns "Project_Key", "YEAR", "WEEK_NUM", "REPORTED_CUMMULATIVE", "CLOSED_CUMMULATIVE", "INDEX".
We have the Year and Week_NUM that has Future Weeks till 53. Reported and Closed Cummulative fills down in case of Null values.
So here the requirement is to Filter Rows untill Current Week so that we stop showing data all Future Weeks in Table and Ultimately on Dashboard. I know it can be done manually by doing Filtering but looking for Power Queries to do that automatically.
| PROJECT_KEY | YEAR | WEEK_NUM | REPORTED_CUMMULATIVE | CLOSED_CUMMULATIVE | Index |
| ABC | 2022 | 1 | 0 | 0 | 1 |
| ABC | 2022 | 2 | 0 | 0 | 2 |
| ABC | 2022 | 3 | 0 | 0 | 3 |
| ABC | 2022 | 4 | 0 | 0 | 4 |
| ABC | 2022 | 5 | 0 | 0 | 5 |
| ABC | 2022 | 6 | 0 | 0 | 6 |
| ABC | 2022 | 7 | 0 | 0 | 7 |
| ABC | 2022 | 8 | 0 | 0 | 8 |
| ABC | 2022 | 9 | 0 | 0 | 9 |
| ABC | 2022 | 10 | 0 | 0 | 10 |
| ABC | 2022 | 11 | 0 | 0 | 11 |
| ABC | 2022 | 12 | 0 | 0 | 12 |
| ABC | 2022 | 13 | 0 | 0 | 13 |
| ABC | 2022 | 14 | 0 | 0 | 14 |
| ABC | 2022 | 15 | 0 | 0 | 15 |
| ABC | 2022 | 16 | 0 | 0 | 16 |
| ABC | 2022 | 17 | 0 | 0 | 17 |
| ABC | 2022 | 18 | 0 | 0 | 18 |
| ABC | 2022 | 19 | 0 | 0 | 19 |
| ABC | 2022 | 20 | 0 | 0 | 20 |
| ABC | 2022 | 21 | 0 | 0 | 21 |
| ABC | 2022 | 22 | 0 | 0 | 22 |
| ABC | 2022 | 23 | 0 | 0 | 23 |
| ABC | 2022 | 24 | 0 | 0 | 24 |
| ABC | 2022 | 25 | 0 | 0 | 25 |
| ABC | 2022 | 26 | 0 | 0 | 26 |
| ABC | 2022 | 27 | 0 | 0 | 27 |
| ABC | 2022 | 28 | 0 | 0 | 28 |
| ABC | 2022 | 29 | 0 | 0 | 29 |
| ABC | 2022 | 30 | 0 | 0 | 30 |
| ABC | 2022 | 31 | 0 | 0 | 31 |
| ABC | 2022 | 32 | 0 | 0 | 32 |
| ABC | 2022 | 33 | 0 | 0 | 33 |
| ABC | 2022 | 34 | 0 | 0 | 34 |
| ABC | 2022 | 35 | 0 | 0 | 35 |
| ABC | 2022 | 36 | 0 | 0 | 36 |
| ABC | 2022 | 37 | 0 | 0 | 37 |
| ABC | 2022 | 38 | 0 | 0 | 38 |
| ABC | 2022 | 39 | 0 | 0 | 39 |
| ABC | 2022 | 40 | 0 | 0 | 40 |
| ABC | 2022 | 41 | 0 | 0 | 41 |
| ABC | 2022 | 42 | 0 | 0 | 42 |
| ABC | 2022 | 43 | 0 | 0 | 43 |
| ABC | 2022 | 44 | 0 | 0 | 44 |
| ABC | 2022 | 45 | 1 | 0 | 45 |
| ABC | 2022 | 46 | 4 | 0 | 46 |
| ABC | 2022 | 47 | 9 | 0 | 47 |
| ABC | 2022 | 48 | 20 | 0 | 48 |
| ABC | 2022 | 49 | 34 | 0 | 49 |
| ABC | 2022 | 50 | 38 | 1 | 50 |
| ABC | 2022 | 51 | 46 | 1 | 51 |
| ABC | 2022 | 52 | 51 | 2 | 52 |
| ABC | 2022 | 53 | 52 | 2 | 53 |
| ABC | 2023 | 1 | 52 | 2 | 54 |
| ABC | 2023 | 2 | 54 | 3 | 55 |
| ABC | 2023 | 3 | 58 | 7 | 56 |
| ABC | 2023 | 4 | 63 | 8 | 57 |
| ABC | 2023 | 5 | 63 | 8 | 58 |
| ABC | 2023 | 6 | 65 | 8 | 59 |
| ABC | 2023 | 7 | 69 | 14 | 60 |
| ABC | 2023 | 8 | 71 | 16 | 61 |
| ABC | 2023 | 9 | 72 | 18 | 62 |
| ABC | 2023 | 10 | 76 | 22 | 63 |
| ABC | 2023 | 11 | 77 | 23 | 64 |
| ABC | 2023 | 12 | 82 | 26 | 65 |
| ABC | 2023 | 13 | 83 | 28 | 66 |
| ABC | 2023 | 14 | 87 | 29 | 67 |
| ABC | 2023 | 15 | 89 | 32 | 68 |
| ABC | 2023 | 16 | 96 | 33 | 69 |
| ABC | 2023 | 17 | 103 | 38 | 70 |
| ABC | 2023 | 18 | 103 | 40 | 71 |
| ABC | 2023 | 19 | 109 | 45 | 72 |
| ABC | 2023 | 20 | 112 | 49 | 73 |
| ABC | 2023 | 21 | 116 | 51 | 74 |
| ABC | 2023 | 22 | 119 | 56 | 75 |
| ABC | 2023 | 23 | 120 | 57 | 76 |
| ABC | 2023 | 24 | 121 | 63 | 77 |
| ABC | 2023 | 25 | 125 | 68 | 78 |
| ABC | 2023 | 26 | 126 | 69 | 79 |
| ABC | 2023 | 27 | 126 | 74 | 80 |
| ABC | 2023 | 28 | 126 | 76 | 81 |
| ABC | 2023 | 29 | 128 | 78 | 82 |
| ABC | 2023 | 30 | 128 | 83 | 83 |
| ABC | 2023 | 31 | 128 | 84 | 84 |
| ABC | 2023 | 32 | 129 | 85 | 85 |
| ABC | 2023 | 33 | 132 | 88 | 86 |
| ABC | 2023 | 34 | 133 | 89 | 87 |
| ABC | 2023 | 35 | 133 | 91 | 88 |
| ABC | 2023 | 36 | 133 | 93 | 89 |
| ABC | 2023 | 37 | 134 | 95 | 90 |
| ABC | 2023 | 38 | 134 | 96 | 91 |
| ABC | 2023 | 39 | 140 | 96 | 92 |
| ABC | 2023 | 40 | 146 | 100 | 93 |
| ABC | 2023 | 41 | 146 | 103 | 94 |
| ABC | 2023 | 42 | 147 | 105 | 95 |
| ABC | 2023 | 43 | 147 | 106 | 96 |
| ABC | 2023 | 44 | 148 | 109 | 97 |
| ABC | 2023 | 45 | 148 | 109 | 98 |
| ABC | 2023 | 46 | 149 | 110 | 99 |
| ABC | 2023 | 47 | 152 | 113 | 100 |
| ABC | 2023 | 48 | 153 | 117 | 101 |
| ABC | 2023 | 49 | 155 | 122 | 102 |
| ABC | 2023 | 50 | 158 | 123 | 103 |
| ABC | 2023 | 51 | 160 | 124 | 104 |
| ABC | 2023 | 52 | 161 | 126 | 105 |
| ABC | 2023 | 53 | 161 | 126 | 106 |
| ABC | 2024 | 1 | 161 | 127 | 107 |
| ABC | 2024 | 2 | 161 | 127 | 108 |
| ABC | 2024 | 3 | 161 | 128 | 109 |
| ABC | 2024 | 4 | 163 | 128 | 110 |
| ABC | 2024 | 5 | 164 | 129 | 111 |
| ABC | 2024 | 6 | 164 | 129 | 112 |
| ABC | 2024 | 7 | 164 | 129 | 113 |
| ABC | 2024 | 8 | 164 | 129 | 114 |
| ABC | 2024 | 9 | 164 | 129 | 115 |
| ABC | 2024 | 10 | 164 | 129 | 116 |
| ABC | 2024 | 11 | 164 | 129 | 117 |
| ABC | 2024 | 12 | 164 | 129 | 118 |
| ABC | 2024 | 13 | 164 | 129 | 119 |
| ABC | 2024 | 14 | 164 | 129 | 120 |
| ABC | 2024 | 15 | 164 | 129 | 121 |
| ABC | 2024 | 16 | 164 | 129 | 122 |
| ABC | 2024 | 17 | 164 | 129 | 123 |
| ABC | 2024 | 18 | 164 | 129 | 124 |
| ABC | 2024 | 19 | 164 | 129 | 125 |
| ABC | 2024 | 20 | 164 | 129 | 126 |
| ABC | 2024 | 21 | 164 | 129 | 127 |
| ABC | 2024 | 22 | 164 | 129 | 128 |
| ABC | 2024 | 23 | 164 | 129 | 129 |
| ABC | 2024 | 24 | 164 | 129 | 130 |
| ABC | 2024 | 25 | 164 | 129 | 131 |
| ABC | 2024 | 26 | 164 | 129 | 132 |
| ABC | 2024 | 27 | 164 | 129 | 133 |
| ABC | 2024 | 28 | 164 | 129 | 134 |
| ABC | 2024 | 29 | 164 | 129 | 135 |
| ABC | 2024 | 30 | 164 | 129 | 136 |
| ABC | 2024 | 31 | 164 | 129 | 137 |
| ABC | 2024 | 32 | 164 | 129 | 138 |
| ABC | 2024 | 33 | 164 | 129 | 139 |
| ABC | 2024 | 34 | 164 | 129 | 140 |
| ABC | 2024 | 35 | 164 | 129 | 141 |
| ABC | 2024 | 36 | 164 | 129 | 142 |
| ABC | 2024 | 37 | 164 | 129 | 143 |
| ABC | 2024 | 38 | 164 | 129 | 144 |
| ABC | 2024 | 39 | 164 | 129 | 145 |
| ABC | 2024 | 40 | 164 | 129 | 146 |
| ABC | 2024 | 41 | 164 | 129 | 147 |
| ABC | 2024 | 42 | 164 | 129 | 148 |
| ABC | 2024 | 43 | 164 | 129 | 149 |
| ABC | 2024 | 44 | 164 | 129 | 150 |
| ABC | 2024 | 45 | 164 | 129 | 151 |
| ABC | 2024 | 46 | 164 | 129 | 152 |
| ABC | 2024 | 47 | 164 | 129 | 153 |
| ABC | 2024 | 48 | 164 | 129 | 154 |
| ABC | 2024 | 49 | 164 | 129 | 155 |
| ABC | 2024 | 50 | 164 | 129 | 156 |
| ABC | 2024 | 51 | 164 | 129 | 157 |
| ABC | 2024 | 52 | 164 | 129 | 158 |
| ABC | 2024 | 53 | 164 | 129 | 159 |
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
1 Reply
- Ahmedx
Super User
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.