locked
Power Query; How do I reference a Power Pivot table from a Power Query query RRS feed

  • Question

  • Hi,

    It's pretty awesome how you can define Extract Transform and Load processes within Power Query without having to type in a single line of code. However how do I reference a Power Pivot table from a Power Query query to avoid me repeatedly accessing the same data source (CSV) file with a view to increasing performance?

    We are aware of the reference sub menu option with Power Query. However the new query created by the "reference" option still seems to refresh data from the data source (CSV) rather than just referencing the base query. Is this understanding correct? There does seem to be a lot of hard disk activity when re-running the new query which is based on a base query rather than a data source.  So we were hoping the new query would just need to reference the base query in memory rather than rescanning the hard disk. Is there any way to ensure that the reference query just rescans the base query in memory?

    Kind Regards,

    Kieran.


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/






    Monday, April 13, 2015 11:26 AM

Answers

  • Hi Kieran,

    This sounds like something to be suggested for a future release. At the present time, Power Query will always re-run the entire Power Query query when refreshed. The Reference feature is analogous to a SQL view whereby the underlying query is always re-executed when it's queried, or in this case refreshed. Even something like using the Power Query cache to minimise the amount of data re-read from the disk would be helpful for performance but the cache is only used for the preview data and stored locally.

    It would be a good idea to suggest this feature to the Power BI team via the feedback smiley face.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Tuesday, April 28, 2015 11:47 PM
  • Will put a smile request in on that as well. These “loop-backs” are really annoying, they even seem to happen within 1 query. Had a complex recursive calculation that took almost an hour for 500k lines if done within 1 query. Simply splitting up the existing code into 3 separate xls-files that need to be processed after another brought down the total time to under 5 minutes!

    Sometimes a List.Buffer or Table.Buffer helps to stop it, but not in every case apparently.


    Imke

    Saturday, May 2, 2015 8:43 AM
    Answerer

All replies

  • This blog post is about sharepoint but maybe it helps

    Monday, April 13, 2015 11:47 AM
  • Any suggestions, or could this be a recommendation for a future release of PowerQuery?


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/

    Tuesday, April 28, 2015 12:51 PM
  • Hi Kieran,

    This sounds like something to be suggested for a future release. At the present time, Power Query will always re-run the entire Power Query query when refreshed. The Reference feature is analogous to a SQL view whereby the underlying query is always re-executed when it's queried, or in this case refreshed. Even something like using the Power Query cache to minimise the amount of data re-read from the disk would be helpful for performance but the cache is only used for the preview data and stored locally.

    It would be a good idea to suggest this feature to the Power BI team via the feedback smiley face.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Tuesday, April 28, 2015 11:47 PM
  • Hi Kieran,

    This sounds like something to be suggested for a future release. At the present time, Power Query will always re-run the entire Power Query query when refreshed. The Reference feature is analogous to a SQL view whereby the underlying query is always re-executed when it's queried, or in this case refreshed. Even something like using the Power Query cache to minimise the amount of data re-read from the disk would be helpful for performance but the cache is only used for the preview data and stored locally.

    It would be a good idea to suggest this feature to the Power BI team via the feedback smiley face.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Hi Michael, 

    Glad to hear from you about this.  And thanks to Kieran for bringing a very good valid point to debate. Will be glad to see this in future release. 


    - please mark correct answers

    Thursday, April 30, 2015 1:56 PM
  • Will put a smile request in on that as well. These “loop-backs” are really annoying, they even seem to happen within 1 query. Had a complex recursive calculation that took almost an hour for 500k lines if done within 1 query. Simply splitting up the existing code into 3 separate xls-files that need to be processed after another brought down the total time to under 5 minutes!

    Sometimes a List.Buffer or Table.Buffer helps to stop it, but not in every case apparently.


    Imke

    Saturday, May 2, 2015 8:43 AM
    Answerer