Using Power Query in Excel to Load, Shape and Transform Data

Using Power Query in Excel to Load, Shape and Transform Data

Time: 13:30 – 14:30, Auditorium Two

Roger GovierRoger’s session will explore the use of Power Query for importing data into Excel for subsequent Analysis through Pivot Tables or Power Pivot. We will explore the ease with which data can be imported from CSV files or plain Text files and how easy it is to create a connection which can be used time after time to get the data into the correct format. The techniques will include consolidating all files from a folder into a single source for subsequent analysis.

We will then move on through importing data from Excel files, and show how data can be Un-pivoted with ease in order to get it into the first normal form for subsequent analysis. We will then examine the vast array of different sources that are already available for data import, and look at some simple examples of scraping data from the Web.

Within the time permitted, the main purpose will be to show how all of this can be achieved using the inbuilt GUI of Power Query, with only the briefest of overviews of the powerful “M” language code that is created behind the scenes to facilitate this process.


Roger Govier is an Excel MVP based in the UK, and has been an MVP since 2007. He undertakes assignments in Excel and VBA for clients worldwide from small businesses to those with multi-billion $ turnover. Roger claims to be intrinsically lazy, so he always looks for a fast and simple way to provide solid workable solutions. He is a keen advocate of all the newer Power utilities in Excel as well as the use of Pivot Tables and Power Pivot models. In addition, Roger carries out training in all aspects of Excel either at client’s premises or at his office.