CODEWITHSUNDEEP
excelvbapowerquery
Fedo
Fedo

Reputation: 35

Update a line "Advanced editor" in power query with Macro

Quick question. Got a data link to a site that is already designed (without macro)in excel. I want to just change a small line in the advanced editor in power query editor with macro.

The code: 

let
 Source = Web.Page(Web.Contents("http://xxx.xxxxxx.xx.xx/xxx/XXX/user_id=111")),
 Data0 = Source{0}[Data],
 #"Removed Columns1" = Table.RemoveColumns(Data0,{"name"}),
 #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Columns1",1),
 #"Changed Type" = Table.TransformColumnTypes(#"Removed Bottom Rows",{{"number", type number}})
in
 #"Changed Type"

I want to update the user_id from 111 to 112 using macro(will make user input the date) I got lots of tables like these and redesigning it will take more time that I can spare.

Best regards. Niko

Upvotes: 0

Views: 290

Answers (1)

jkpieterse
jkpieterse

Reputation: 2986

You could use code similar to this:

Sub UpdateQueries()
    Dim oQ As WorkbookQuery
    'Find queries using this table
    For Each oQ In ActiveWorkbook.Queries
        oQ.Formula = Replace(oQ.Formula,"user_id=111""","user_id=112""")
    Next
End Sub

Upvotes: 1

Related Questions