I have loaded some .xlsx from a folder location in such a single source "master" table. I thought it might be smart to reference this table then in other tables as I need it multiple times. My motivation was that due to that the loading times might be improved as I just load it in the beginning. Well, it turns out that the same files are still loaded multiple times and this takes actually quite some time as the .xlsx files contain lots of data. So why is it atill laoding multiple containtimes and how can I prevent this?
Unfortunately with flat files there's no caching or optimization that happens. It does query it each time. Having the source query in this case just allows you the single point to make a change to the source location, or source type if needed.
When you will refresh, wouldn't it still load from your main access database for each query like 7 times queries access db. You have brought convenience by defining to change connection only in main query but still the refresh issue and slowness will be there, what is solution to that?
This was designed to allow for quick and easy source query updates from the developer. Not something specifically designed to speed up the refreshes in any way. One other option is to use a "source" parameter in each query as well, if you'd prefer not to use the reference feature. But that also wouldn't speed up the queries at all. The only thing that changes the speed of query refreshes is the number of applied steps that would be query folded. But that's a topic not in the scope of this particular video. I have other videos and streams that do discuss implications of that though. Hope this helps!
Thank you for this, but then what? How do you change the data source for this one query? Once all the queries point at this data source query, what do you point that data source query at?
That depends on what your data source is. There's over 100 connectors in Power Query. If the settings for the data source needs to change, or if the source needs to change entirely. E.g. SharePoint to SQL, etc. then you'd replace that "source query" with whatever new query and applied steps are needed for all the dependencies to fetch the same data at the same starting point from the new source.
That’s interesting approach but 2 questions: 1. If I want to change the access file - i can do it in Data Source and it will change it for all connections at once 2. What about performance? Is referencing give us better or worse performance? Thanks!
Hi Michael, by referencing and creating a single "source query" there is only one query pointing to the file location, allowing just one update if it needs to be changed. Otherwise if you had five queries all connected to the same location, but without a referenced source query. You'd need to update five source steps. Performance wise, it depends. Typically it should not have a performance impact. But depending on connection, transformations, etc... It might. Here's a great article that discusses that. blog.crossjoin.co.uk/2016/11/20/referenced-queries-and-caching-in-power-bi-and-power-query/
Hi there. Not sure I follow, you can right-click any query and select reference to reference that specific query. You can also toggle enable load to disable any queries that you don't want to load into the data model.
No difference for referencing queries since the underlying query being processed doesn't change. This is designed for query management, and easier updates in the future, and leveraging more repeatable logic between queries. :)
Thank you, great demonstration.
Just amazing!
Simple yet awesome thanks!
I have loaded some .xlsx from a folder location in such a single source "master" table. I thought it might be smart to reference this table then in other tables as I need it multiple times. My motivation was that due to that the loading times might be improved as I just load it in the beginning.
Well, it turns out that the same files are still loaded multiple times and this takes actually quite some time as the .xlsx files contain lots of data. So why is it atill laoding multiple containtimes and how can I prevent this?
Unfortunately with flat files there's no caching or optimization that happens. It does query it each time. Having the source query in this case just allows you the single point to make a change to the source location, or source type if needed.
When you will refresh, wouldn't it still load from your main access database for each query like 7 times queries access db.
You have brought convenience by defining to change connection only in main query but still the refresh issue and slowness will be there, what is solution to that?
This was designed to allow for quick and easy source query updates from the developer. Not something specifically designed to speed up the refreshes in any way. One other option is to use a "source" parameter in each query as well, if you'd prefer not to use the reference feature. But that also wouldn't speed up the queries at all. The only thing that changes the speed of query refreshes is the number of applied steps that would be query folded. But that's a topic not in the scope of this particular video. I have other videos and streams that do discuss implications of that though. Hope this helps!
Thank you for this, but then what? How do you change the data source for this one query? Once all the queries point at this data source query, what do you point that data source query at?
That depends on what your data source is. There's over 100 connectors in Power Query. If the settings for the data source needs to change, or if the source needs to change entirely. E.g. SharePoint to SQL, etc. then you'd replace that "source query" with whatever new query and applied steps are needed for all the dependencies to fetch the same data at the same starting point from the new source.
That’s interesting approach but 2 questions:
1. If I want to change the access file - i can do it in Data Source and it will change it for all connections at once
2. What about performance? Is referencing give us better or worse performance?
Thanks!
Hi Michael, by referencing and creating a single "source query" there is only one query pointing to the file location, allowing just one update if it needs to be changed. Otherwise if you had five queries all connected to the same location, but without a referenced source query. You'd need to update five source steps. Performance wise, it depends. Typically it should not have a performance impact. But depending on connection, transformations, etc... It might. Here's a great article that discusses that. blog.crossjoin.co.uk/2016/11/20/referenced-queries-and-caching-in-power-bi-and-power-query/
Thanks!
Can I select which tables use in the reference query?? in order to use only necessary tables for my model
Hi there. Not sure I follow, you can right-click any query and select reference to reference that specific query. You can also toggle enable load to disable any queries that you don't want to load into the data model.
How about performance? Does it reduce refresh time
No difference for referencing queries since the underlying query being processed doesn't change. This is designed for query management, and easier updates in the future, and leveraging more repeatable logic between queries. :)
Is it possible to refer to a specific step of another query?
Good question, not using the reference option. That will only reference the last applied step of the other query
Very Very useful! Thanks again @Reid
This video has enhanced my wisdom about Power BI,
Also, I request Reid to please be a bit slow while speaking... U are the fastest of all ;)
Just put a playback speed to something smaller than 1.0.
But that assumes all data are coming from the same source
Hi John, that’s correct. This is designed to show you how to reduce the dependencies when obtaining multiple tables from a single data source.
@@HavensConsulting Thanks Reid
nice
nice!
Great!!!!!!!