+ Reply to Thread
Results 1 to 6 of 6

Code to change Power Query source and refresh

  1. #1
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Code to change Power Query source and refresh

    I have a simple Power Query setup that I currently get the users to open Power Query change the source and refresh.

    I would like to have a button that runs a macro to do the function of opening a dialog box to select the new source and apply it to the PQ and refresh the data model. Is this possible?

    I tried recording a macro but nothing appears in the code.

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Code to change Power Query source and refresh

    You could set up a parameter table with the source data location in it, then have a simple macro that updates that table's record before refreshing data.
    Rory

  3. #3
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Code to change Power Query source and refresh

    It's possible and there are several ways to do this. Easiest method is to set up single cell named range in the workbook.

    Set up data validation at the location.

    Pass on the selected value to query as variable. Use that as path. Then either have user refresh query table, or let the code do it.

    However, depending on table structure and what transformation should be applied. You'll need to build additional custom functions and/or steps in advanced editor.

    Ex: Sheet1!$A$2 set up as named range and given name "vPath".
    In Advanced query editor, you can query the value using...
    Please Login or Register  to view this content.
    Then use fPath to construct path to the file like...
    Please Login or Register  to view this content.
    Or set up vPath to include file names as well and simply replace the string with fPath.

    There is way to do this via VBA using ActiveWorkbook.Queries object. But this method is supported on Excel 2016 or later only.
    And since .Formula property of WorkbookQuery is read-only. You'll need to delete the original and add new query.
    You can find sample code in link below.
    https://gallery.technet.microsoft.co...Power-956a52d1

    As you can see, for a simple task, it requires quite a few steps.
    I prefer to use named range method to pass variable to PowerQuery.
    Last edited by CK76; 12-10-2018 at 09:53 AM. Reason: Added link to sample VBA.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  4. #4
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Code to change Power Query source and refresh

    Hi CK76,

    I only have Excel 2013 for this process, so I don't think the VBA works for me.

    Are you able to attach a small demo showing where to put the elements of the Power Query code you suggested as your "easiest" method. I'm not able to follow the instructions.

  5. #5
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Code to change Power Query source and refresh

    See attached.

    I've set up path to where the workbook is stored in A2 and file name (csv files) drop down in B2.
    fPath and fName is used as variable.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    02-23-2021
    Location
    Eindhoven
    MS-Off Ver
    365 pro plus
    Posts
    1

    Re: Code to change Power Query source and refresh

    Just wanted to say tnx! This helped me out

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 06-29-2018, 05:35 AM
  2. [SOLVED] Refresh data source (excel table) in power pivot
    By maboho93 in forum Excel Charting & Pivots
    Replies: 11
    Last Post: 06-08-2018, 04:13 AM
  3. Power Query Source vs Different PCs
    By flpneves in forum Excel General
    Replies: 1
    Last Post: 12-05-2017, 01:07 PM
  4. Does My VBA Code To Refresh CSV Power Query Connections Need To Change for MySQL Queries?
    By beaglesBuddy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-11-2017, 02:57 PM
  5. code to refresh power query not working ..
    By Hassan1977 in forum Excel General
    Replies: 0
    Last Post: 08-31-2016, 12:31 PM
  6. Automatically refresh source data in Power Pivot Window
    By rv02 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-15-2015, 11:49 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1