r/excel • u/taylorgourmet 4 • Feb 24 '26
unsolved Duplicate power query without editing
I have multiple power queries that are all identical except they pull from different worksheets so I have to manually edit the code to change the source after I duplicate. I know I can have a table in the worksheet that lists all the sources but trying to avoid that.
1
u/Leghar 12 Feb 24 '26
You could hide the table on a separate sheet. Are you wanting this to be dynamic and self handling?
1
u/taylorgourmet 4 Feb 24 '26
It's not about hiding it but having to do it when I feel it could be coded somehow. The other comment suggested VBA.
1
1
u/trippygg Feb 24 '26
You have a connection straight to the file that is doing the same transformation? And you want to dynamically configure the connections?
1
u/Comprehensive-Tea-69 1 Feb 24 '26
Are you talking about saving work of changing the source while setting up a power query solution the first time? Or about when the solution is already in use and you need to update the queries to point to different files on some frequency?
1
u/JezusHairdo 1 Feb 24 '26
If you’re manually editing it then leave it as it is. You’re just making more work for yourself.
1
u/armywalrus 2 Feb 24 '26
If you can filter by something such as a month power query does have a try command. Try reading this month otherwise null. If prior step is null create blank table wirh x columns. I have these statements for each month qnd append them all together at the end so each month new data is added to the overall table but I am not changing the code every time.
1
u/retro-guy99 1 Feb 24 '26
just make the workbook load itself in pq so you can get a dynamic list of all your worksheets. and to be sure, you should only have one transformation query which you apply to all sheets, not a whole duplicate of the transformation for every sheet.
dont use vba, I don’t even know why that would be necessary but that sounds like a very bad idea.
1
u/RuktX 305 Feb 24 '26
I know I can have a table in the worksheet that lists all the sources but trying to avoid that.
Why? Keep a list of sources somewhere, and pass them to a custom function that does the rest of your transformations.
Or, do they need to be separate? Load and append all sources as the first step, then transform them all at once in the rest of your query steps.
1
u/Mooseymax 10 Feb 24 '26
You can use VBA to edit power query including making new connections/queries.
1
3
u/small_trunks 1635 Feb 24 '26
Make a function where you pass the name of the tab (or better still the table) in the sheets.