As we all know Excel is a very powerful tool, however sometimes where your data is spread across various spreadsheets and other files trying to summarize it all can be tricky. You can add various lookups to pull in data but when you have to do this every month it can become very time consuming.
Built into Excel you have two very powerful tools that not everyone is aware of, Power Query and Power Pivot.
When working with data to report on, we use the principle of ETL, Extract, Transform and Load.
- Extract – is where you select the data from your required source to work with
- Transform – gets the data into the correct format to perform analysis
- Load – loads the data into Excel ready to analyse
Power Query enables you to pull in data from different data sources and transform it. You can pull in different Excel spreadsheets, CSV files, SQL or Access databases, data from websites and many other formats. Once in Power Query you can then organise and tidy up your data to ensure it is in a transactional format. You can make sure it has a proper header row, no blank rows or rows or columns of data you do not need. You can even add additional columns for formulas or conditional data. You can append records from other tables onto your data and where applicable merge data into columns from other tables. When you do this, it links to your original data source and creates a series of steps. What this means is that if the original data source changes, it will go through your steps to transform and update the data without you having to do it manually each time. A real time saver and it reduces the risk of error as once you have it right you know it will be right each time.
Power Pivot lets you then report on the data. Once loaded into Excel from Power Query the first thing you need to do in Power Pivot is to create relationships in your data so you know which table is connected to which and by what. For example you might have an table with a list of employees and another containing training data for everyone. So, you would use something like Employee ID to connect the two tables together. Once you have built the relationships you can now build Pivot Tables, Pivot Charts and use the powerful features such as Slicers and Timeline filters to report on information from multiple tables.
Our new one day Excel Power Query and Power Pivot course introduces you to the powerful features of these tools. This can be run virtually over a number of sessions or face to face.