Load to options

So far, once we’ve got our data cleaned and transformed in the PowerWuery Edtor, we’ve only loaded the information back into Excel in the form of a table.

However, there’s plenty of other options of how to load your data and in this lesson we’re going to take a walk through them.

So first things first; how do I get to the list of options that show me in what format I can Load my data?

In the PowerQuery Editor

If you’re in the PowerQuery Editor, then go to the Home tab and then to the Close and Load dropdown on the left hand side of the ribbon. You’ll then have an option that says ‘Close and Load To’. Now if you’re editing a query you’ve previously loaded then this option will be greyed out. If you’re loading a query for the first time though, you’ll be able to select this option and the ‘Load To’ menu will appear.

In Standard Excel

If you’re in Standard Excel however, open the Queries and Connections pane (select the Data tab and then immediately below the tab button, select ‘Queries and Connections’) to show the available queries.

Right click any of the queries and a ‘Load To’ option pops up. Select this option and the ‘Load To’ menu will appear.

Load To Table

The first option on the Load To menu is the Load to Table option and this is the default option. This is what happens when we simply select Close and Load in the PowerQuery screen. There’s not too much more to add about this option as it’s how we’ve ended every lesson – it simply loads the data to a table.

Load To Pivot Table and Load to PivotChart

The next options are a PivotTable report and a PivotChart. PivotTables and PivotCharts are assumed knowledge on this course, but in summary PivotTables take tables of data and then summarise the data by any characteristics in the data . PivotCharts are then graphs that can be created off the back of those PivotTables.

The only additional things to note about these options (as long as you know PivotTables of course) is that if you select either of these options, having previuosly selected to load a query into a table of data, then you’ll remove the tables of data that you originally loaded.

Only Create Connection

The next option in the menu, ‘the Only Create Connection’ is an interesting one though; an option where we just create a connection to the dataset – no other output like a table or chart. Why would this be useful?

Well often we just need to create a single table of data, but lots of queries might be combined to make that single table of data. We’ve seen this kind of thing so far in our workbook; we started with our arrivals data showing the containers that have arrived into the country. We then merged in some cost data, then further merged in some invoice data.  That’s three tables of data we needed to create a single table of data showing the container arrivals, our estimation of how much we think those containers will cost, and how much we’re being charged to ship the containers. The chances are though that we only want the single table that combines together all of these tables. We don’t need the rest. If that’s the case then we can select to ‘Only Create Connection’ for all the constituent queries. If we do so, then we retain the queries in the PowerQuery Editor, but the results don’t flow through to Standard Excel.

Add to the Data Model

In the next lesson we start talking about this final option which is called the data model, also known as PowerPivot. This is a very exciting (and pretty much unheard of) part of Excel and you’ll get a bit of a sneak peak in the next lesson…