If you are having to collate information from different sources into one place, learn about Get & Transform. (It used to be called Power Query and is still often referred to as this.) It will change your life.
The Basics
You can find Get & Transform on the Data menu. Basically, Get & Transform is designed to get information from sources and transform it into the form that you want.
Sources
The amazing thing about Get & Transform is the different sources of data that information can be collected from:
- PDFs (Not all PDFs are created equally, so it does depend on the type)
- Other Excel Workbooks
- Text Files, or csv files
- A folder
- Databases (SQL, Access)
- Websites (Not all website work well)
- A table within the current workbook
- Plus more
Transform
Changing the data from the state that it comes in, to the state that you want, can be done as a series of steps. Each step is recorded and then when new data is collected the same steps will be done to this new data to include it in your output. Creating the steps is usually quite easy, but you need to be careful that you understand your data and the recording is being faithful to what you expect. It can get quite complex, and you can edit the code directly if required.
An Example
My client had twenty spreadsheets used by twenty different people. Each workbook had some columns in common, but inconsistent column headings. One person needed to be able to see the information from all twenty spreadsheets. The current solution was to open each workbook, copy and paste the data into a new workbook, bringing all the information together in one place. This was slow, and the most recent information was not available unless the copy and paste had just been done.
Thank Excel for Get & Transform! I created connections to each of the twenty workbooks. The columns could then be transformed so there is a consistency between all the information. This clean data was then joined together (Append in Get & Transform language) and presented. Each time the workbook is opened the data is refreshed automatically so the latest information is always available. Only one spreadsheet needs to be opened, and no copy and paste required. One happy client!