Forum Discussion
Helper IDuplicating a Table based on an Activities Moving Date.
Hello-
I need to make a duplicate of the table below based on the Activity ID and Forecast Finish. ACT-Con-016 is the end of 60% Design. The issue is the Forecast dates can change with weekly data upates. Is there a way to duplicate the table below based on the Forecast Field where Activity ID is ACT-Con-016 and before what ever it has in its Forecast Finish>?
If not possible is there a way I can duplicate table and keep any Activity where Forecast Finish is before 11/1/2024?
Duplicate this table:
| Activity ID | Actual Finish | Forecast Finish | BL Finish | |
| ACT-Con-001 | 3/13/2022 | 3/13/2022 | 3/8/2022 | 60% Complete |
| ACT-Con-002 | 3/14/2022 | 3/14/2022 | 3/11/2022 | |
| ACT-Con-003 | 7/15/2022 | 7/15/2022 | 7/14/2022 | |
| ACT-Con-004 | 11/15/2022 | 11/15/2022 | 11/16/2022 | |
| ACT-Con-005 | 11/14/2022 | 11/1/2022 | 11/19/2022 | |
| ACT-Con-006 | 1/19/2023 | 1/17/2023 | 1/22/2023 | |
| ACT-Con-007 | 3/15/2023 | 3/20/2023 | 3/27/2023 | |
| ACT-Con-008 | 5/18/2023 | 5/21/2023 | 5/30/2023 | |
| ACT-Con-009 | 5/22/2023 | 5/22/2023 | 8/2/2023 | |
| ACT-Con-010 | 9/28/2023 | 9/22/2023 | 10/5/2023 | |
| ACT-Con-011 | 11/23/2023 | 11/23/2023 | 12/8/2023 | |
| ACT-Con-012 | 1/24/2024 | 1/24/2024 | 2/10/2024 | |
| ACT-Con-013 | 3/29/2024 | 3/26/2024 | 4/14/2024 | |
| ACT-Con-014 | 6/1/2024 | 5/27/2024 | 6/17/2024 | |
| ACT-Con-015 | 7/28/2024 | 8/20/2024 | ||
| ACT-Con-016 | 9/28/2024 | 10/23/2024 | ||
| ACT-Con-017 | 11/29/2024 | 12/26/2024 | ||
| ACT-Con-018 | 1/30/2025 | 2/28/2025 | ||
| ACT-Con-019 | 4/2/2025 | 5/3/2025 | ||
| ACT-Con-020 | 6/3/2025 | 7/6/2025 |
To this table:
| Activity ID | Actual Finish | Forecast Finish | BL Finish | |
| ACT-Con-001 | 3/13/2022 | 3/13/2022 | 3/8/2022 | 60% Complete |
| ACT-Con-002 | 3/14/2022 | 3/14/2022 | 3/11/2022 | |
| ACT-Con-003 | 7/15/2022 | 7/15/2022 | 7/14/2022 | |
| ACT-Con-004 | 11/15/2022 | 11/15/2022 | 11/16/2022 | |
| ACT-Con-005 | 11/14/2022 | 11/1/2022 | 11/19/2022 | |
| ACT-Con-006 | 1/19/2023 | 1/17/2023 | 1/22/2023 | |
| ACT-Con-007 | 3/15/2023 | 3/20/2023 | 3/27/2023 | |
| ACT-Con-008 | 5/18/2023 | 5/21/2023 | 5/30/2023 | |
| ACT-Con-009 | 5/22/2023 | 5/22/2023 | 8/2/2023 | |
| ACT-Con-010 | 9/28/2023 | 9/22/2023 | 10/5/2023 | |
| ACT-Con-011 | 11/23/2023 | 11/23/2023 | 12/8/2023 | |
| ACT-Con-012 | 1/24/2024 | 1/24/2024 | 2/10/2024 | |
| ACT-Con-013 | 3/29/2024 | 3/26/2024 | 4/14/2024 | |
| ACT-Con-014 | 6/1/2024 | 5/27/2024 | 6/17/2024 | |
| ACT-Con-015 | 7/28/2024 | 8/20/2024 | ||
| ACT-Con-016 | 9/28/2024 | 10/23/2024 |
Hi PeteyG
You can first duplicate the query,
In the new query, filter rows by [Activity ID] column and select Equals... and set it to equal to ACT-Con-016. This will give you the formula like below in the formula bar and remain only one row in the outcome.
= Table.SelectRows(#"Changed Type", each [Activity ID] = "ACT-Con-016")
Don't worry, you just need to change the equal sign "=" into "<=", then you will have the expected outcome like below.
Reference: Filter by values in a column feature description - Power Query | Microsoft Learn
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
1 Reply
Hi PeteyG
You can first duplicate the query,
In the new query, filter rows by [Activity ID] column and select Equals... and set it to equal to ACT-Con-016. This will give you the formula like below in the formula bar and remain only one row in the outcome.
= Table.SelectRows(#"Changed Type", each [Activity ID] = "ACT-Con-016")
Don't worry, you just need to change the equal sign "=" into "<=", then you will have the expected outcome like below.
Reference: Filter by values in a column feature description - Power Query | Microsoft Learn
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!