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?

 

[GraH]

Excel.currentworkbook() will retrieve all tables and named ranges.
Then build your logic around that.

 

[Alex Blakenburg]

You are going to need a query per Tab.
If they are just different filters applied to the same query I would imagine it would look like this:

1. Main Query - Unfiltered
2. A separate "Reference" Query based on Main Query for each Tab
3. Each Reference Query would need to filter using the parameter that resides on that sheet.
4. If you want to use the same RangeName on each sheet they are going to have to have the RangeName qualified with the sheet they are on ie
   1. Workbook Scoped RangeName
      = Excel.CurrentWorkbook(){[Name="Test_Rname"]}[Content]{0}[Column1]
   2. WorkSheet Scoped RangeName
      = Excel.CurrentWorkbook(){[Name="Sheet2!Test_Rname"]}[Content]{0}[Column1]

 

[happyhungarian]

You are going to need a query per Tab.
If they are just different filters applied to the same query I would imagine it would look like this:

1. Main Query - Unfiltered
2. A separate "Reference" Query based on Main Query for each Tab
3. Each Reference Query would need to filter using the parameter that resides on that sheet.
4. If you want to use the same RangeName on each sheet they are going to have to have the RangeName qualified with the sheet they are on ie
   1. Workbook Scoped RangeName
      = Excel.CurrentWorkbook(){[Name="Test_Rname"]}[Content]{0}[Column1]
   2. WorkSheet Scoped RangeName
      = Excel.CurrentWorkbook(){[Name="Sheet2!Test_Rname"]}[Content]{0}[Column1]

Thank you for this. I think this would work but it is a bit of a brute-force method. My tab has about 5 Power Queries on it where I have this RangeName qualifier on it. Then I need to copy this tab about 15-20 times. So I would need to go in and manually type in the cell reference for about 100 Power Queries. No ideas on how to make it dynamic to look at the named range on the specific tab that the Power Query is residing on?

 

[happyhungarian]

Instead of using a Named Range is it possible to use a dynamic cell reference? For example, instead of:

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

If would read something like:

ProjectCode = Excel.CurrentSheet(){[Cell=C4]}[Content]{0}[Column1],

I know this syntax wouldn't work but hopefully it gets my idea through...

 

[GraH]

How would PQ know current sheet?
The only thing I come up with is what I suggested in #2.
However, fine people around here for sure are able to provide a VBA that sends current sheet as a parameter to PQ. Unfortunately I don't know how VBA syntax for PQ works.
Maybe this tutorial by Jon can give you a start.

 

[Alex Blakenburg]

I am in a similar position to GraH, in terms of not having used VBA to create Power Query queries and how to access the "Load To" sheet name, on which the parameter is being stored.

"100 Power Queries" in one workbook, is ringing warning bells to me. This sounds like an maintenance nightmare, not to mention the performance implications.

100 Power Queries.

 

[GraH]

"100 Power Queries" in one workbook, is ringing warning bells to me. This sounds like an maintenance nightmare, not to mention the performance implications.

If the refresh is only one at the time it might not harm on performance. Replicating the Query 100 times is indeed not smart for maintenance. That's why I'm thinking of passing a parameter to one PQ.
VBA might store active sheet in a named range. Then the PQ reads this name to make the connection string. It's in the movie I shared I believe. Did not watch it in great detail.

 

[Alex Blakenburg]

If the refresh is only one at the time it might not harm on performance. Replicating the Query 100 times is indeed not smart for maintenance. That's why I'm thinking of passing a parameter to one PQ.
VBA might store active sheet in a named range. Then the PQ reads this name to make the connection string. It's in the movie I shared I believe. Did not watch it in great detail.

I did watch it in full and did like it.
It won't resolve this issue as it stands. It assumes that you have the (notional) 100 tables already in the workbook and that you are planning to combine them into a single table with an append query. You therefore want to pull all those tables into PQ as a connection only query.
The macro creates the connection only query to each of those tables with the option of also pulling them into the data model.

These tables are input to the combine tables step.
What we are trying to do here is create multiple output queries linked to a parameter that resides on the output sheet. And it is the Output sheet we don't now how to access from within PQ.

I don't really think the design makes sense. The whole idea of PQ & Data Model / Power Pivot is to have a single output which you filter as required, not create a separate view for each of the filter option.
That sounds more like a VBA distribution macro.

PS: If you wanted to have a look at the code you will find the workbook here, the page has a totally different title.
How to Combine Tables with Power Query - Jon Acampora

 

[GraH]

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