Once you've loaded a Power Query, how do you change your load options?
When you've created a Power Query in Excel, you can close it and load the resultant data. But how do you then change how the query loads? This blog explains how!

Posted by Andy Brown on 01 April 2015

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

How to change Close & Load options in Power Query

We're hoping to put a full tutorial on Power Query on this website soon, but in the meantime here's the answer to something which baffled me.  Suppose that you have created a Power Query, and used this to load data into a data model or workbook:

Close & Load option

The option to close the query and load the data it returns.


You then go back into the query to tweak it, and want to change how you load the data.  How can you do this?  The only answer I can find is shown below.

First, make sure that you have the Power Query Workbook Queries pane visible in Excel:

Show Pane tool on ribbon

Click on the Show Pane tool on the Power Query tab of the Excel ribbon.


Now click on a query, and a pop-up window will show the results of the query.  At the bottom left will be 3 little icons:

The three dots icon

Click on the three dots shown to edit load options.


When you click on the three dots on the right, you can change how your query loads:

Change the Load to... option

You can now change this option to load the data in a different way.


Here's the sort-of dialog box which appears:

You can change the load options

This dialog box is much clearer than the original Close & Load options!

Well, it took me a long time to find!  If there's an easier way, do let me know ...

This blog has 0 threads Add post