Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dapster105
Advocate III
Advocate III

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

 

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

@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.
1.JPG

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


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yuezhe-msft
Employee
Employee

@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.
1.JPG

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


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.