BASE_DATA_1 - QUERY_1A - QUERY_1B refresh

[UK_GER]

Hi,

i'm new here. Located in germany - and try to come into PQ.

At the moment I have a small problem, as described in the title above.

I have BASE_DATA_1 and transform it by using PQ. Result is stored in the file QUERY_1A.
As I have to build different reports out of the BASE_DATA_1, I have build other Querys that take only parts out of QUERY_1A that is named QUERY_1B, sometimes merge them with other data (BASE_DATA_2 / QUERY_2A / Query_2B).
If this datamodel is clever - I don' know.

My problem is, that the refresh of QUERY_1B ony refreshes data out of QUERY_1A, but not out of BASE_DATA_1.

Try it again in the short form:
Is it possible to refresh all querys that are picking data from each other, when opening the last query?


All my tests failed up to now.


If someone kowns how to solve this problem - please help.

Thanks
Uwe

 

[Ken Puls]

Hi Uwe, and welcome to the forum!

So a question here... you said:

I have BASE_DATA_1 and transform it by using PQ. Result is stored in the file QUERY_1A.

Do you mean that it's in the same workbook as all the other queries, or is it really in a different file? If it's in a different file, I can see where this would be an issue. If it's in the same workbook (with all queries building from each other), then the queries should refresh both themselves and any precedent queries correctly....

 

[UK_GER]

Hi Ken,

thanks for answering.

Let me try to explain it better - hope i can do it.

I download app. 5 different files from SAP.

Each of them I import in PQ. So get 5 Excel files.
These 1st import is only to transform everthing into the correct formats.

One of these files contains a lot of different data. So I split it by loading it in 3 different excel files and delete always the data I don't need for the next step.
This helps me in calculating in these files and also brings the amount of data down.

In the next step I merge data out of all (4 + 1 splitted to 3) different files.

So if I read your post correct, the data is not in the same file, it will be "transfered" from file to file.

Hope this explaination brings some light into the dark.

Thanks
Uwe

 

[Ken Puls]

Right okay. So here's the thing...

Power Query can read from the data source and refresh the data. If that source is a database, a workbook, or a text file, it's all fine. But what it can't do is reach back and refresh any previous data sources. So if your stuff is set up like this:

SAP --> Workbook 1 --> Workbook 2 --> Workbook 3

Then you're going to have a problem. In order to get this to refresh properly, you'll need to open Workbook 1, refresh, save, close. Open Workbook 2, refresh, save, close and so on.

I think you might be missing a major piece of how Power Query works though. I would set it up like this:
-Export your 5 data sets into the files from SAP
-In ONE Excel workbook, create 5 queries - 1 to load each data set, and filter out the stuff you don't need. The important thing here is to set them to load to Connection Only when you are finished. (This makes them actu like pointers to your formatted datat, but won't load any data into your file at this point.)
-Create your merge queries against these connection only queries. Only load the ones to worksheet that you actually need.

Doing this will allow you to keep the entire solution in one place, will let it update faster and more consistently, and will probably allow you to keep a smaller hard drive space usage overall than they way you seem to be trying to do things right now.

 

[UK_GER]

Dear Ken,

exactly this was my problem. I was worried about a not ending manual refreshing scenario.
As I will end up with app. 30 different reports in the upcoming year I have to find a manageable solution.

I will follow your advise and set "the whole thing" up in that way.

And - I had a lock into Chapter 9 "Merging tables and Queries".
The hardest problem was to find the Merge-Button. As this is not in the Editor I normally open.
For people that search for it: It is directly in the Power Query menu, one step right from the "load from table-button" (2010 /Add-In).


Ken, Thank you very much for the time you spend for solving my problems and helping me to come into Power Query.
Hope I can give something back to others in the future.

Uwe