Hello, I am new to Power Query and need some help please. I have an excel spreadsheet Source and a second excel spreadsheet Output generated from a Power query. The power query takes the data from the Source, filters by column Change number (only displaying rows with Change number) and has additional column Comments for manual update. I did the self-referencing table query to make sure any manually entered comments would not shift during refresh.
The next thing I would like to do is upon demand/request to filter the Output table only to show me changes that are valid from next week. My end-goal is to have a button to filter by next week and another button to reset and go back to all records (initial output query to filter only data that has change number). The reason is that I want to keep record of the manually entered data in the additional columns (to simplify I only put 1 column Comments, but my actual table has several manual input tables), because different stakeholders will provide an input, which I want to remain as part of the Output table when refresh is done. I was able to create a second query using Date.IsInNextWeek which works great, but it is in a separate sheet and manually entered comment to the second query table disappears during refresh of the Output. I have been trying different solutions, and so far I was only able to create a button using a macro to refresh queries, not a button to run a query. Not sure if what I want to achieve is possible, any help would be greatly appreciated!
Additionally, I learnt I should have an identifier, so I added index column to be manually entered in the data source table (I saw I can add an index column in the power query, but did not understand if a new row is added to the Source, do we need to manually enter the next number).
Thanks for taking the time to look into it!
Best Regards,
Lubina
Below is a simplified example of what I would like to do:
Table 1 Source
Table 2.1 Output - filtered by Change number and sorted by family with ability to add a comment without shifting to another row.
Table 2.2 Output - on demand Filter by Valid from "Next week" (filtered by Change number and Next week)
The next thing I would like to do is upon demand/request to filter the Output table only to show me changes that are valid from next week. My end-goal is to have a button to filter by next week and another button to reset and go back to all records (initial output query to filter only data that has change number). The reason is that I want to keep record of the manually entered data in the additional columns (to simplify I only put 1 column Comments, but my actual table has several manual input tables), because different stakeholders will provide an input, which I want to remain as part of the Output table when refresh is done. I was able to create a second query using Date.IsInNextWeek which works great, but it is in a separate sheet and manually entered comment to the second query table disappears during refresh of the Output. I have been trying different solutions, and so far I was only able to create a button using a macro to refresh queries, not a button to run a query. Not sure if what I want to achieve is possible, any help would be greatly appreciated!
Additionally, I learnt I should have an identifier, so I added index column to be manually entered in the data source table (I saw I can add an index column in the power query, but did not understand if a new row is added to the Source, do we need to manually enter the next number).
Thanks for taking the time to look into it!
Best Regards,
Lubina
Below is a simplified example of what I would like to do:
Table 1 Source
| Index | Type of change | Product family | Change number | Valid from |
| 1 | Load new BOM | A | No | 3/2/2021 |
| 2 | BOM change | A | 001 | 3/2/2021 |
| 3 | BOM change | A | 002 | 3/30/2021 |
| 4 | Load new BOM | B | No | 3/30/2021 |
| 5 | BOM change | B | 003 | 3/13/2021 |
Table 2.1 Output - filtered by Change number and sorted by family with ability to add a comment without shifting to another row.
| Index | Type of change | Product family | Change number | Valid from | Comment |
| 2 | BOM change | A | 001 | 3/2/2021 | Implemented in production 2/18/2021 |
| 3 | BOM change | A | 002 | 3/30/2021 | |
| 5 | BOM change | B | 003 | 3/13/2021 |
Table 2.2 Output - on demand Filter by Valid from "Next week" (filtered by Change number and Next week)
| Index | Type of change | Product family | Change number | Valid from | Comment |
| 5 | BOM change | B | 003 | 3/13/2021 | In progress |
