Unpivot and creating duplicate queries

[dapster105]

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 I currently fetch as select * from vw....

I need to create a number of 'unpivoted' tables from this core view.

The process I know and understand involves:

1. Duplicate the core query/table into a new query/table

2. Remove all columns from the new query/table except the PK and the columns I want to unpivot

3. Rename the columns to pivot so they're as I want them to show as table values

4. Do the unpivot on this new query/table

5. Make sure PKs link properly between core query/table and this new unpivoted query/table

Repeat above 30 times to create other unpivots on groups of columns.

I'm concerned about potential performance issues from this approach.

Does duplicating the query/table mean that powerbi will run the view select query multiple times or is it smart enough just to do the query itself once and do the unpivoting 'internally'? Is there a better way to fetch the data once and do multiple unpivots on it?

Presumably if multiple queries are unavoidable I'd be better off creating multiple queries each with only the narrow select fields I need and want to unpivot? Is there any guarantee that in selecting from the same view multiple times I will get consistent data (e.g. I won't have extra 'just-inserted' rows from one of my view selects vs others)?

Thanks!

Tim

[v-yuezhe-msft · Accepted solution]

@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]

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!