Power Query Question

[happyhungarian]

Hi, I have a table I run using Power Query where one of the filters in the query runs off of a Named Range called "ProjectCode". Whatever project code is entered into the designated cell it will update the query with that new filter. This works great with a singular tab. However, when I want to replicate that tab the queries that get create on the tab are still referencing the Named Range "ProjectCode" from the original tab rather than referencing the "ProjectCode" that's on the newly created tab. I think it has to do with the syntax on my Power Query where it says "CurrentWorkbook". Is there a simple fix like "Current Worksheet" or something similar that would fix this?

 

[Alex Blakenburg]

Haha the 100 queries was in video of Jon ? must have missed that ?.

LOL - OK so in his template he only had 9. ?

 

[GraH]

Hi again, happyhungarian,

This code (or an improved one, I hope some-one else can provide) with a correct trigger (on change of the named range, but I don't know how to do that) will create a "stable" named range reference "vPQ" as it is on the scope of the workbook.

Dim Rng As Range
  Set Rng = ActiveSheet.Range("PrjCode")
  ThisWorkbook.Names.Add Name:="vPQ", RefersTo:=Rng, Category:=Workbook

This PQ Query can retrieve that variable

 Excel.CurrentWorkbook(){[Name = "vPQ"]}[Content][Column1]{0}

Then any of your Queries via a copy sheet will work on the same variable.
One downside seems you need to confirm the name exists already. For sure fine people over here can find a solution for that.

 

[happyhungarian]

Thank you both for the thoughts on this! To give some additional background on the purpose for the Workbook and the intended logic behind it is I have an initial tab that I built out to be a complete repository of all information related to a singular project. This tab actually has 6 different queries on it that are each pulling from tables contained on different tabs in the same workbook. These tables have been turned into connections that the power queries are pulling from. Now that I have built out all the logic and structure that I wanted on this project tab I want to now replicate it several times; one tab for each of the projects I want information on. I was hoping that once I copied the tab all I would have to do is change the project code on the new second worksheet to that of the second project and voila! all the information for the second project would automatically populate.

One update I have is I tried to just brute force it by changing the named range on the new second tab to be something different and then editing one of the power queries on that second tab to use the new named range instead of the "ProjectCode" from the original tab and the query actually says that it can't locate that named range... very interesting...

 

[Alex Blakenburg]

I am not sure why you are getting the error "that it can't locate that named range", in post #3 I had syntax for both a Workbook & a Worksheet scoped Named Range and so it is just a matter of getting the syntax right.

I'm afraid I don't have a solution to automatically updating the query to use the Named Range located on the output sheet.

 

[happyhungarian]

Thanks. I couldn't quite get the = Excel.CurrentWorkbook(){[Name="Sheet2!Test_Rname"]}[Content]{0}[Column1], logic to work. Is there some element that's missing? Also, lLast thought... is there any Indirect-type logic in mcode that can be utilized in conjunction with the Worksheet logic above? Where I could have the sheet reference be the indirect logic combined with other logic that pulls the name of the active sheet?

 

[GraH]

Can't you work with what's in #12

 

[happyhungarian]

Can't you work with what's in #12

Couldn't get it to work. Should the VBA reside on the sheet or on a Module?

 

[GraH]

On sheet. When you copy the sheet it seems VBA is copied too.

 

[Alex Blakenburg]

I couldn't quite get the = Excel.CurrentWorkbook(){[Name="Sheet2!Test_Rname"]}[Content]{0}[Column1], logic to work. Is there some element that's missing?

I can talk you through this if this part is of value to you without the 2nd part.
It will probably make for a clearer example if you show me your Name Manager screen and highlight the Worksheet scoped range name that you are trying to use.

Also, lLast thought... is there any Indirect-type logic in mcode that can be utilized in conjunction with the Worksheet logic above? Where I could have the sheet reference be the indirect logic combined with other logic that pulls the name of the active sheet?

I don't know of any way to of accessing the Output Sheet name in PQ