Hi Bax,

There's not currently an official way to do this in Power Query. Your could try changing the Query Properties (right-click the query in the query pane > Properties) to not 'Refresh this connection on Refresh All'. I've never tested this so it may or may not result in some efficiency gains.

The good news is Microsoft are working on incremental data loads so this wont be an issue in future.

Mynda

Thanks Mynda. I will try that. Glad to see Microsoft are looking at a solution.

Hi,

I was just looking at a similar issue again where I have already created a query that doesn't change but still refreshes every time I update other queries that use the data. Has Power Query moved on since 2018 when I first posted this? Is it at all possible to either convert the original query to some kind of static table or flag it somehow not to be included in the refresh?

Any other options been developed in the last 5 years? If so I am all ears 🙂

Thanks

Bax

Hi Bax,

You could convert the data to a static data table as explained here. If you have a lot of data, then you might like to use Power BI to paste the data into a table and then get the JSON code from the advanced editor for the static table.

Mynda

Hi Mynda,

Thanks for getting back to me. There is lots of data, hundreds of thousands of rows so creating a static table using the method prescribed in Phil's post is probably not viable in this case. If I took the Power BI option how would this work? I can re-create the query in Power BI but how do I then get that back to the Excel Power Query using the JSON code? This is not something I am familiar with.

Thanks

Bax

Hi Bax,

Just copy the query from the Advanced Editor in Power BI and paste it into a new query via the Advanced Editor in Excel.

Mynda

Hi Mynda,

I am not understanding the bit about pasting the data into a table in Power BI. I can re-create the query in power BI but I would have to reference the same source as I use in Excel PQ. You say all I need to do is copy the query in the advanced editor from BI to Excel but without doing something else to the query in BI how does it make it a static query.

Apologies for being a bit thick but I am am sure I am missing something simple but not sure what it is!

Thanks

Bax

Apologies, Bax. I haven't been clear.

In Power BI you can copy and paste your data into a table. Doing this creates JSON code for your dataset in the Advanced Editor. You can then copy the code from the advanced editor back to Excel's advanced editor. However, I think it's limited to 3000 records, so like you say, it won't work for you.

If you're using Excel, then you can import the data direct from an Excel table to the Power Pivot model from the Power Pivot ribbon tab > Add Data to Model. Then make sure you don't use Refresh All to update the model. Instead, refresh the queries and as this table is not loaded via a query, then it doesn't update. However, if you need to reference it in other queries, this won't work.

Otherwise, I don't have any other suggestions for Excel. However, in Power BI, you can set certain queries to not refresh (right-click query > uncheck 'include in report refresh'). Therefore, you could use Power BI to get and model your data, then connect to the Power BI model in Excel (Insert tab > PivotTable > From Power BI).

This would require refreshing queries in Power BI and then refreshing PivotTables in Excel to get the latest data. In Power BI, you can schedule refreshes so the data is always up to date.

Mynda