Forum Discussion
Dynamic Column Update
The purpose of Power Query is to transform existing source data. If you are trying to include historical forecast values then those should be present in the source data somewhere. It sounds like they are not. The best option I think would be to either create a file that contains historical forecast amounts with their applicable dates (this could easily be done with Power Automate), or use Power Query to read all of the weekly files and aggregate the weekly amounts.
My new values are comming from another table's column = 'Project'[forecast]
There is no date column, the forecast keeps changing, so I am trying to get a snapshot summary of forecast for each week. That is, save the forecast for week 20, As the week ends, the Summary value of forecast for week 20 is locked, and we move on to update week 21 forecast.
Please let me know if you need any other info.
Thank you for your help!
Super UserThe purpose of Power Query is to transform existing source data. If you are trying to include historical forecast values then those should be present in the source data somewhere. It sounds like they are not. The best option I think would be to either create a file that contains historical forecast amounts with their applicable dates (this could easily be done with Power Automate), or use Power Query to read all of the weekly files and aggregate the weekly amounts.
jennratten
How if we add a new column every week?
As the week change from 20 to 21, we add an new column of 21 and update its value. Will this help in restoring column 20's updated values?
Please let me know.
Thank you!