Results 1 to 2 of 2

Thread: Handling Dynamic Table Naming (Financial Month)

  1. #1
    Neophyte wsquare's Avatar
    Join Date
    Jun 2020
    Posts
    3
    Articles
    0
    Excel Version
    Office 365 ProPlus

    Lightbulb Handling Dynamic Table Naming (Financial Month)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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,

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,834
    Articles
    0
    Excel Version
    O365
    Have you got a sample file of the input, and a step by step of what you would do with it?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •