Power BI: Query Editor: Copy/duplicate query
- Thread starter govi
- Start date
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.
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.
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
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.
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.