Copying and pasting data can be a quick and easy way to get data into Excel. However, not all data sources and data types will suit this method. If the data does not have a clearly defined structure that suits a spreadsheet, copy-pasting might be a tedious and needless task. The Excel Power Query module allows for an easier and cleaner way of getting and preparing your data for analysis. It allows you to clean and transform your data into a usable format before importing it into Excel. It also allows you to get data from different locations and file types. You can import data from an existing database, PDF files, CSV files, Text files, and other Excel workbooks.
There are four phases to using Power Query.
- Connect:- Make connections to data in the cloud, on a service, or in your local machine.
- Transform:- Shape data to meet your needs
- Combine:- Join data from different sources to have a unified dataset
- Load:- Complete your query and load it into a worksheet or Data model with periodic refreshes.
Cleaning and Transforming data with Excel Power Query
Power query has many features that allow you to transform and clean data. It considers the most common unstructured data types and provides the tools needed to transform the data. For example, you can use the “Unpivot Columns” button in the Transform tab to change horizontally spread data into a vertical table which is desirable for analysis. Power Query records each data manipulation/transformation step and stores it in its memory. Each time you refresh your data, the same steps get applied to maintain the format. That means you only need to transform the data once.
Note: Any transformation done with Power Query does not affect the original data.
You can also add columns and virtual tables from your data inside Power Query. This allows you to create summary columns like months and years from a dates column. You can also append different tables with the same information to only have one table or merge overlapping datasets into one.
When transforming your data, you want to organize it in a way that is easy to access, search, and analyze. A simple way to do this is by ensuring a single row provides all the information for a single record. For example, if you have purchase records for items over some dates arranged in rows and columns, one row should only show information about the purchase of a single item. That is, the item name, quantity of purchase, date of purchase, and purchase amount. Likewise, no two columns should be of the same data type. That is, instead of having 12 columns for each month showing the purchase amount, create a single column for all the months and a second column for the purchase amount. This is what is considered a good data structure.
Watch the video below to see how you can transform some of the most common unstructured data formats to a structured format for analysis.
Looking for project-driven supply chain management software?
Current SCM is the first of its kind – supply chain management software purpose-built to support the most complex procurement and materials management projects. With materials management and vendor document requirements uniquely integrated into the order, Current SCM provides a unified, collaborative platform to streamline the end-to-end process of project-driven procurement and materials management.
If you are engaged in any direct procurement, technical procurement, project procurement or third-party procurement, Current SCM will improve your procurement and materials management workflow. If you are engaged in all four, Current SCM will revolutionize the way you do business.
Contact our sales professionals at Current SCM today!