• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power BI: Query Editor: Copy/duplicate query

govi

Member
Hi,

Is it possible in the query editor to make a copy/duplicate of a query?
This "mother" query must feed the copy/duplicate with new data when it is refreshed. But it is important that the copy/duplicate doesn't refresh (connect to the datasource) itself.

Thank you!
govi
 

Chihiro

Excel Ninja
Right click on the query and choose "Reference". That will create dependent query, which uses original query as source.
 

govi

Member
Hi Chihiro, thank you.
I tested that already. When I refresh the data it runs the original query and the reference query, and that what I don't want. I want it to run only once.

govi
 

Chihiro

Excel Ninja
When you tell query to refresh. It must refresh from source.

If you want original query to run just once? That kind of defeats the purpose of live connection to data source.

In that case, depending on size of the query result, load it to worksheet and kill the connection. Then use that as source of your subsequent query.

There are other alternatives, but I'm not clear on your desired set up and how you'll be using the data.
 

govi

Member
Hi Chihiro, thank you.

I will try to explain. I have made an embedded Power BI report for a customers site.

We used to pay Microsoft(Power BI Premium) 600 euro every month for running this report. It has a refresh schedule (it is refresh every hour) and the customer can manually refresh.
But now the data that is pulled in every refresh is getting more and more and we had to upgrade Power BI Premium and now we have to pay 1200 euro every month.

I have one query that pulls in 700.000+ records every time the report is refreshed.

I need to model the data from this query in 5 different configurations to build the report. So I now use the same query 5 times, that means that those 700.000+ records are pulled in 5 times on every refresh.

What I like to have is that the data is pulled in once and that the data is written to (4) other tables in the Query Editor so I can model these 4 for my report.

I know it is possible with DAX but with DAX I have to little tools to model the data further, it need the Query Editor for that.

Do you think there is a solution for this?

Thanks!!!!
govi
 

Chihiro

Excel Ninja
Hmm? If you had each query independent and querying from source, that payload is 700k * # of query. By building referenced query off of single query, payload is reduced to 700k rows.

Also, DAX can perform pretty much any transformation that PQ can do (as long as source data is in flat table structure). It just is more convenient to do it in PQ.

Alternative, is to use incremental load of data, this can dramatically reduce your payload per refresh. Since you have PowerBI Premium, you have that option. Have a read of link below.
https://docs.microsoft.com/en-us/power-bi/service-premium-incremental-refresh

In my experience, for reports with frequent update/refresh, you almost never need raw data with 700k rows. You can usually set up View, on-premise stating server etc to perform data aggregation and reduce payload before hitting BI Service.
 
Top