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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
WillBeeSEA
Helper I
Helper I

Duplicate vs. Reference Queries

I understand how to create a duplicate / reference query from an original query.

WHAT ARE THE ADVANTAGES TO EACH OF THESE METHODS?

Can you give some specific reasons to use one over the other

Also, can you give some actual examples of when one is better than another?  

  • WHEN would you want an independent copy? (Duplicate)
  • WHEN would you want an dependent copy? (Reference)

Thank you.

1 ACCEPTED SOLUTION
speedramps
Super User
Super User

Hi WillBeeSEA

 

Some Power BI Novices think Power BI queries are tables with data.  They are not,

Queries are M scripts not data. You can view the M scripts by clicking the advanced editor.

Power BI displays the first 1000 rows of the query which can fool novices into thinking they are tables. But they are scripts!

 

So when you copy or reference a query you copy or reference the script and not the data.

A copy is bad because Power Bi will have to run the script twice to get the data from the original data source (eg an SQL server).

It is best practice to get data once and then reference the query.

These scripts can also be converted to Data Flows in the Power BI Service to reduce the number of hits on the database server.

If you source data has up to the minute updates then two copies a few seconds apart can cause discrepancies, whereas references will have identical date/time data.

You can view dependencies by clicking on view dependencies

You can’t have circular refences. They will display an error. So sometimes you need to create temporary staging queries.   

I recommend that you learnt about query folding.
Query folding delegates heavy processing to the multi-threading SQL server rather than the single thread PBI service.
Query folding ceases to function as soon as the first non-delegable command is encountered.
So it is important to keep those commands at the end of your dependencies rather than near the start.

Please click the thumbs up and accept as solution button.

View solution in original post

2 REPLIES 2
v-yanjiang-msft
Community Support
Community Support

Hi @WillBeeSEA ,

Is your problem solved by @speedramps 's solution? If so, Would you mind accept the helpful reply as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ kalyj

speedramps
Super User
Super User

Hi WillBeeSEA

 

Some Power BI Novices think Power BI queries are tables with data.  They are not,

Queries are M scripts not data. You can view the M scripts by clicking the advanced editor.

Power BI displays the first 1000 rows of the query which can fool novices into thinking they are tables. But they are scripts!

 

So when you copy or reference a query you copy or reference the script and not the data.

A copy is bad because Power Bi will have to run the script twice to get the data from the original data source (eg an SQL server).

It is best practice to get data once and then reference the query.

These scripts can also be converted to Data Flows in the Power BI Service to reduce the number of hits on the database server.

If you source data has up to the minute updates then two copies a few seconds apart can cause discrepancies, whereas references will have identical date/time data.

You can view dependencies by clicking on view dependencies

You can’t have circular refences. They will display an error. So sometimes you need to create temporary staging queries.   

I recommend that you learnt about query folding.
Query folding delegates heavy processing to the multi-threading SQL server rather than the single thread PBI service.
Query folding ceases to function as soon as the first non-delegable command is encountered.
So it is important to keep those commands at the end of your dependencies rather than near the start.

Please click the thumbs up and accept as solution button.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.