vba to update advanced editor in power query

[TaskMaster]

Good morning,

I am have begun using power query to format my data which is working rather well. The issue is that the data source seems to change and im wondering if there would be to automate updating this using vba. E.g. "Framework_F7_Daily_Base" might become "Framework_F8_Daily_Base" when the source data is refreshed. My initial thought was to use an input box & find & replace to update the number that would change but I can't seem to get it working. Does any one have any suggestions?

Sub UpdateLinks()

'***********************************************************************************
      'Purpose: Update power query advanced editor with new source data
      'How:     Find and replace source data with new link
      'Outputs: Using InputBox to manually enter new data link
'***********************************************************************************

    Dim oQ As WorkbookQuery
    Dim xy As Variant
    Dim xx As Variant
    
    'Manually enter the updated DB Link
    
    xx = InputBox("Enter updated DB Link")
    xy = ThisWorkbook.Worksheets("xxxx").Range("xxxx")
    
    'Find queries using this table
    
    For Each oQ In ActiveWorkbook.Queries
        oQ.Formula = Replace(oQ.Formula,"Framework_" & xy"", xx"")
    Next

End Sub

    Source = AnalysisServices.Databases("SQLDB", [TypedMeasureColumns=true, Implementation="2.0"]),
    Framework_F7_Daily_Base = Source{[Name="Framework_F7_Daily_Base"]}

 

[RoryA]

Wouldn't it be easier to set up a parameter for your query that you can simply populate from either a named range or a table?

 

[TaskMaster]

Hi Rory,

I can give that a try, how would I link this up to the advanced editor as im not familiar with the M code?

 

[RoryA]

Have a look at Ken's article here as he does a better job of explaining it than I will: Creating Dynamic Parameters in Power Query

Post back if you get stuck.

 

[jdellasala]

By coincidence, I ran across this four year old video that uses a table to allow calling for parameters using a function! It's very cool.
Create A Parameter Table For Your Power Queries