Duplicate vs. Reference Queries

[WillBeeSEA]

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.

[v-yanjiang-msft]

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 · Accepted solution]

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.