Consulting

Results 1 to 4 of 4

Thread: Modifying Datasource Query using VBA in Power Pivot

  1. #1
    VBAX Newbie
    Joined
    Jun 2019
    Posts
    2
    Location

    Modifying Datasource Query using VBA in Power Pivot

    Hello there! I have been days looking for a solution to my problem.
    Y have a complex tool in excel that uses information from 20 different datasources loaded in Power Pivot.

    Thing is, that queries bringing iformation from DataBase have date ranges. And it is very annoying edit every connection to edit the query and update the data range every time. It takes for ever.

    Solution I am needing is to have a simple table in the main excel file, where I have the dates for each data source.
    What I need is, load all the dates in this excel table, and then using VBA modify all the queries from the datasources working in Power Pivot.

    I dont know if I was clear enough. If not, let me know.

    Thank you for your help!

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,112
    Location
    You could do that easily if you load the data through Power Query and then access that datamodel. You can build a parameter table in Excel that you can pull into PQ and use that to filter the data. If you want to try this, provide more details and we can help you get there.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Newbie
    Joined
    Jun 2019
    Posts
    2
    Location
    Hello xld, all the datasoruces impact on external records. Excels and SQL server.
    At this point, and since this tool has not been developed by me, if there is a chance to modify the queries into the datasets that impact SQL Server it would be great.
    Not sure how adding Power Query would help and not a pro user
    Thanks

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,112
    Location
    If you were to use Power Query, you would need to recreate the connections, not sure if that is possible if you didn't develop it.

    The general thrust of what I am saying is:
    - build an Excel parameter table of all the variables that you want
    - build queries for all of the datasources (complete data)
    - in each query read in the relevant parameter(s) and filter the data
    - load the queries into the datamodel.

    Power Query helps because you can read the datasources as you can with PowerPivot, manipulate/transform that data, and then load into the datamodel. PowerPivot, or regular pivot tables, can then access that datamodel.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

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