In the last lesson we took a break from the logistics file that we’d been using until that point. Instead we looked at some headcount data and learned about the Transpose function. This function allows us to swap around the headers and the values in the first column. Very handy.
So far, we’ve looked a lot at how to clean and transform a table of information in PowerQuery. However, we’ve not really looked at what you can do with your information once you’ve got it structured in a nice, neat columnar fashion. That’s what we’ll do in this lesson; we’ll understand into what format you can load the data back into Excel.
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…
PowerPivot – Oh go on then, a little more about PowerPivot, since you asked so nicely! PowerPivot is a free add in to Excel which allows data from multiple tables to be entered into the same Pivot Table and interact with each other seamlessly. You can write custom calculations, create PivotTables over millions of lines of data, and it’s all blisteringly quick. It’s a truly brilliant tool, and to be honest, we only scratch the surface of it in the next lesson.