Way to duplicate query from SQL Server without update?

[Anonymous]

Hi,

I have a very large query that is importing data from SQL server. I want to create a duplicate of this query that preserves the original data and does not update the query based on the most recent data in SQL server. My goal in doing this is to have a copy of the original file to preserve that information and then update the original file (and corresponding visualizations) as needed. There are too many rows to simply copy/paste the data into a new file. Any suggestions?

Thank you,

K

[v-jingzhang · Accepted solution]

Hi @Anonymous 

The easiest way is to duplicate the table into a new table in the same SQL Server data source. Then in Power Query Editor, duplicate the original query and modify its Source step to navigate to the new duplicated table in DB. When you refresh data, this query will always get data from the static duplicated table in DB which will not be updated. See Copy Columns from One Table to Another (Database Engine) - SQL Server | Microsoft Docs.

Similarly you can copy or export data to other data sources or files. For example, you can refer to this blog: Exporting Data from Power BI Desktop to Excel and CSV – Part 1: Copy & Paste and DAX Studio Methods - BI Insight

If you don't want to preserve data in other files or data sources, here is another workaround you can try. First you can duplicate the query in Power Query Editor. Then in the right-click context menu, uncheck "Include in report refresh" option. In future refreshes, this query will not be updated. But you need to apply the first refresh on it before you uncheck this option. Its data needs to be imported into the model at the first refresh. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[BA_Pete]

Hi @Anonymous ,

Power BI isn't a data warehouse, therefore there's no certified method to prevent data from refreshing once a report is deployed to the PBI Service.

The best I can think of would be to either do a one-off write of your source SQL query to a new table on the server/db, or maybe run an export from SSMS to Excel/Access or similar to have a static base for your PBI report to draw from.

Pete