Forum Discussion
Using Source of one query for another
Yes, absolutely.
- Do your initial import of the view and any transformations you want. You don't have to do any, but you might want everything to be filtered for the last 2 years for example. Do that here so you don't have do that 4 times in the next step.
- Right-click on that query and select REFERENCE. That will make query 1 the source of query 2. Any changes to 1 will affect 2 (and 3, 4, 5.)
Do not select DUPLICATE. That will copy the first query and changes made in 1 will no longer affect 2. You might want that for other scenarios, but it doesn't sound like you do for this question.
Yes, absolutely.
- Do your initial import of the view and any transformations you want. You don't have to do any, but you might want everything to be filtered for the last 2 years for example. Do that here so you don't have do that 4 times in the next step.
- Right-click on that query and select REFERENCE. That will make query 1 the source of query 2. Any changes to 1 will affect 2 (and 3, 4, 5.)
Do not select DUPLICATE. That will copy the first query and changes made in 1 will no longer affect 2. You might want that for other scenarios, but it doesn't sound like you do for this question.
Hi edhans
Thanks for your response. yes I tried with Reference already. When I refresh the refrenced query, there is always 2 sessions coming to DB. One from Actual query and other from referenced query. So I was confused, if it's being effective. Also my .pbix file is 1.8Gb and with this reference(common View Queries), my .pbix grew to 2.4 GB. Thats again a problem for me.
It is effective.
A few things:
- Make sure your first query is set to NOT LOAD. Right-click and tell it not to load. That will solve the size issue. You only want the tables you need in the DAX model to load. the others will still be used in calculations, but will not load their results to DAX, merely hand them off to referenced queries.
- It will make multiple requests to the DB. Power Query works bottom up, not top down. So if you have 3 queries referencing 1, and 1 is the databse, it will do the 3 end queries and go to the top, hitting your DB 3 times.
Thanks edhans . I will check this and will let you know. Referencing is a very good option.
