Power Query and large data sets

Saving time

When working with some large data sets in Power Query it may be useful to default your Close and Load option to Connection Only rather than a Table. That way if you do strike an issue it won’t take ages to load to data.

This is a Global setting, so it effects all the Queries you create after you change the setting.

You may need to switch it back and forth depending on the type and size of data you are working with.

If you are creating staging queries (initial workings) these are often Connection Only.

To default the Close & Load icon to Connection Only you need to change a Power Query Option.

On the Data ribbon, click the Get Data drop down and at the bottom click Query Options.

In the Global Data Load section on the left Click the Specify custom default load settings and untick the two check boxes. This causes the Data Load to default to Connection Only.

Click OK.

Now when you click the Close & Load icon on the Home ribbon in the Power Query window it will default to Connection Only.

You can change the Query destination to a Table by right clicking the Query in the Queries and Connection Task Pane and choosing Load To.

Then Select Table and Click OK.

You may also want to tick the Add This data to the Data Model check box.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.