Handling Dynamic Table Naming (Financial Month)

wsquare

New member
Joined
Jun 9, 2020
Messages
3
Reaction score
0
Points
0
Excel Version(s)
Office 365 ProPlus
Hi,

I've a regular pain-point wondering if any folks here have a cleaner solution to eliminate/reduce the efforts involved with regards to coping with dynamic table name ie. (month) changes?

Broadly - this relates to monthly financial reporting where:
1. Input data feed will contain annualized plan ie. monthly planned forecast and actuals ie. Jan actuals $......Jul actuals $, Aug planned $, added sum fields for YTD$ actuals, ROY FC$
2. This query feed will be cascaded downstream across multiple queries for other reporting needs.

Manual changes done monthly in input data query:
- Addressing missing prev month planned data column ie. Jul Planned $ would be replaced by Jul Actuals $
- Year-to-Date actuals(YTD$) - will be addressed to include previous month actuals. Off power query - it would be easily handled by Sumifs dynamically with filter condition "*actuals $"
- Rest-of-year Forecast (ROY$) - will be addressed to drop previous month planned $. Off power query -it would be easily handled by sumifs dynamically with filter condition "*planned $"
- This same manual changes will be cascaded across of downstream queries table names and same address has to be done to realign again

Is there any available dynamic solutioning approach in Power Query i could adopt for this monthly regular table name changes -
1. where the pattern change is known by month name changes cascading downstream?
2. Sum feature in PQ which can handles such filter conditions without dependency on the actual month naming fields?

Appreciate any help here.
Thanks much.
With regards,
 
Have you got a sample file of the input, and a step by step of what you would do with it?
 
Back
Top