Power Query with dependent queries

P

Paul_Christie

New member
Joined
Mar 23, 2016
Messages
153
Reaction score
0
Points
0
Location
Nottingham, UK
Website
www.instantpages.ltd.uk
Excel Version(s)
Office 365 Monthly update
I'm trying to create a system for monitoring KPI data across an entire organisation that is split on two levels. I'm successfully using fnGetParameter from Ken's book to get the data for the location that I'm interested in. I'm then using that data to pick data for a given location and match it with rate cost data from another table. This all works fine until I change the location. At this point the downstream queries hold on to the data from the original location. I think I want to drive the refresh in a particular order and I don't want certain queries to run until a user has made a further selection of a department within a location but the fact that the downstream queries don't refresh is causing me a proble even I force a refresh.

So far I've succeeded in doing all of this without writing any vba because it's a prototype solution that eventually will be written in C# and SQL. The reason I'm using Excel is because once we've got a given set of data we're using the Solver to optimise it. Posting an example is an issue because of an internal security obsession that I'm having to work around.
 
Paul, that's odd.

I can see this happening in the Preview window, as previews are cached. Even in the PQ Editor though, clicking "refresh preview" should drive an update through the preview windows.

The bigger concern though, is when you do a refresh all from the Excel UI, the location is not getting updated? That should never happen, as the full PQ chain should get refreshed. (The flyover previews may still show old data, as the preview and real data are not always in sync, but the data model should be good.)

Can you confirm that this problem is being exhibited in the data model (or worksheet) on a full refresh?
 
Ken,

Reading your reply focused my attention on what I was seeing. What's happening is that the 'connection only' queries that are doing some of the linking don't refresh in the query pane so anything downstream of that doesn't refresh correctly.

I was using 'connection only' to save resources because in the short term the model will be used on Excel 2010 32 bit on a 4GB system. Am I being the victim of my own search for efficiency? Should I make them all load the data?

Paul
 
No, not at all. Honestly, I use more connection only queries than I do "load" queries, and never have this issue.

During a Data --> Refresh All, the queries are refreshed in the order shown in your Data --> Connections panlel. Having said that, any parent calls the refresh of each (and every) child query... a process that cascades down to refresh their children and so on as needed. In fact, this causes other perf issues as that connection only query you use to serve up 5 data model tables actually gets refreshed 5 times.

Again, inside the PQ editor and the query pane I can see that you'd be seeing cached query values. But when you hit Excel's Data Refresh All, that should kick off the full calc of ALL queries.
 
Ken,

My mistake, I've just discovered I left a parameter in the Access query so the Power Query parameter was trying to do a selection that had already been done and failing. Don't know if you understand UK humour but I'll just say 'Stupid Boy'.

Hopefully I'll now solve my pwn problem but thanks for your insight. I'll make sure I document the way this lot cascade if only to help me find this sort of thing quicker.

Paul
 
LOL! Hate it when that happens. Funny enough, it's impossible to see those kind of issues until AFTER you ask someone else for help. (Been there myself!)

:)
 
You must log in or register to reply here.
Back
Top