Forum Discussion

dapster105's avatar
dapster105
Advocate III
8 years ago
Solved

Unpivot and creating duplicate queries

Hi,   I'm about to undertake a large report project I would really appreciate a little advice to make sure I start off on the right track.   I am working with one large and complex SQL view which...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

dapster105,

In your scenario, there are additional operations on the new query, which can cause the M engine to compute a different SQL query, resulting in the server being hit again. 

To  reduce the recalculations of your query, please use Reference to create new query, and use Table.Buffer to store query result into Memory.


Reference:
https://blog.crossjoin.co.uk/2016/11/20/referenced-queries-and-caching-in-power-bi-and-power-query/


Regards,
Lydia

  • dapster105's avatar
    dapster105
    Advocate III
    8 years ago

    Hi Lydia,

     

    Thanks for your input and for the link.

     

    I have created the additional tables using right-click 'Reference' and when I look at the Advanced Editor I can see they are defined based on the first table (which itself references the database).

     

    However I can see when I refresh that it's generating multiple requests to the database as you suggested it might so now I need to understand Table.Buffer.

     

    I'm afraid I'm struggling to translate the article into what I know about PowerBI (I've never used PowerQuery before). Can you give me a simple pointer or example about how to apply Table.Buffer into the query definition and does it go onto the original table or each of the derived (unpivoted) tables?

     

    Thanks!