Results 1 to 3 of 3

Thread: Data model with Power query

  1. #1
    Seeker n.kehayova's Avatar
    Join Date
    Feb 2018
    Excel Version

    Data model with Power query

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


    I have a team of 10 people, over which a pipeline of 2000 accounts is distributed.
    Each account is loaded with Deadline for Check type A.
    After a check Type A is completed on status success, a check type B is scheduled 6 months after Check type A completion.
    After a check type B is completed on status success , another check type B is scheduled for the same account, 12 months after the last check type B's completion, and so on. We have unlimited checks type B.

    I want to create a model where from a master list of the 2000 accounts, I push with queries to the 10 individual pipelines account info, and schedule them for Check type A.
    Is it possible to push a list, then add a few columns to the query table, where the people are supposed to fill status, completion date and other relevant info, then pull it back to the master record, in order to get that generated data and use it for scheduling of next check?
    For example:
    Master file:
    Name Account Deadline
    Ann Bla 1/1/2019
    Beth Blabla 1/3/2019

    Ann gets in her file for check A:
    Name Account Deadline Completion Date Status
    Ann Bla 1/1/2019 [blank to fill completion date] [dropdown to fill status]

    I subsequently pull that record to an enhanced master file with actual data for Check A, and use it to generate Check Bs, which are pushed to Ann again.

    What kind of potential issues do you see with that? I know it's ugly, but the current setup is manual - 10 individual files are set, schedules given by me in the 10 files, and there is one master, which reads and consolidates them with power query. Currenlty once I get to moving accounts across people, including new accounts and removing such, I need to open each individual file and do it manually, and since they are on a shared folder, I need to ask the people to close them off while I work, it's a pain in the ass...
    Last edited by n.kehayova; 2019-02-05 at 07:03 PM.

  2. #2
    Conjurer Paul_Christie's Avatar
    Join Date
    Mar 2016
    Nottingham, UK
    Excel Version
    Office 365 Monthly update
    Since Power Query effectively creates Excel Tables you can simply add columns as required to the right of any existing Query and then add new Queries that will be based on the expanded Table.

    Paul Chrustie

  3. #3
    Acolyte NormS's Avatar
    Join Date
    Jul 2017
    Excel Version
    Excel 2016 ProPlus
    Easier said than done, this addresses some of the issues you may run into

Posting Permissions

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