There’s no denying that the way we work with data has changed dramatically since Microsoft introduced Excel in 1987. Thirty-six years later, organizations now have the opportunity to work with data lakes and petabyte-scale databases. Yet despite cutting-edge advances in machine learning algorithms, Microsoft Excel continues to be one of the most frequently used mediums for data analysis. As much as users love Excel, most of their data analysis there is an overwhelmingly manual process. What’s more surprising is that this manual work is accepted as normal. 

Guess what? It doesn’t have to be. 

Excel is a far more powerful tool than many of us realize. Even if you’re a seasoned veteran of VLOOKUP and CONCATENATE, you still might not have known that Excel has a powerful feature that allows you to use the software as an extract, transform, load (ETL) tool. 

Allow us to introduce you to Power Query. 

Power Query and ETL

If you’re not already familiar with ETL, it sounds like a tech term that is far more complicated than it actually is. Database administrators and data engineers talk nonstop about “ETL pipelines” and “ETL tools” to the point where most might assume that ETL is something only a certified data expert can do. But in fact, ETL can be done easily right in Excel (yes, Excel!) using a little known tool called Power Query. 

Microsoft itself touts Power Query as a tool that makes Excel even more powerful. “Power Query lets you analyze your data right from within Excel, while seamlessly connecting to a wide range of external data sources,” the header on the Power Query webpage reads. “Easily reshape and transform your data along the way if needed.”

With the help of Power Query, you can extract data from different sources, transform it, and the load it to an Excel worksheet. Can this be done instead with various formulas and pivot tables? Sometimes. Typical data manipulations that one might perform manually in Excel include:

• Removing columns
• Filtering the data
• Grouping the data
• Pivoting / unpivoting the data
• Splitting strings into substrings
• Extracting keywords from strings
• Appending rows from another table
• Joining two dimension tables

But if you’re looking to streamline the data manipulation process—not to mention make it real time so it can refresh with any data changes—you’re going to want Power Query ETL. 

The Benefits of ETL

Data must be properly formatted and prepared for loading into data storage systems. Relying on an ETL instead of unprocessed or hand-processed data comes with multiple benefits. 

Manage Complex Data Efficiently
ETL helps you perform calculations, spring manipulation, data changes, and integrate multiple sets of data.

Improve Business Intelligence
ETL improves access to information that can directly affect both operational and strategic decisions based on data-driven facts. Business leaders have the ability to analyze information based on specific requirements in order to make the best decisions possible.

Timely Access to Data
With ETL, less time is spent on the actual data retrieval process. And, ETL functions to consolidate data from multiple sources and transform it into a usable format so it can be accessed from one source of truth.

Using Excel for ETL

You can probably agree with us that actual data migration between systems can be easy (and it’s even easier with ExtendInsights, but more on that later). It’s all the processes that have to happen before data migration that bog down the process: data discovery, cleaning data, managing the process at scale. ETL makes it an easy process, and with the fact that you can use Excel—the program you already know and love—as your ETL tool makes it even better. 

In older versions of Excel, you can easily install Power Query as an add-in. Excel 2016 and later includes more tightly integrated tools: Click on the Data tab, then select the Get & Transform Data section. 

Extract
The first step is to bring the data from an outside source into Excel. Power Query can use data sources such as text and CSV files, databases, SharePoint, XML, HTML, web data, and of course, Excel itself. Extraction not only pulls the data into Excel for processing, but keeps the raw data intact and untouched. Under the Data tab, click Get Data, and select the appropriate option (From Table / Range, From Web, From CSV, etc.). 

Transform
The second step makes the data usable. This could include:

• Sorting or filtering rows
• Adding, dropping, renaming, or calculating columns
• Grouping by and aggregating categories
• Merging multiple data sources

Most of the time spent in the ETL process will be here in data transformation. In the Power Query ribbon itself, simply select Transform and select the data you want to add.

Load
This is the final and easiest step: Click Home on the Power Query editor, then Close & Load. The transformed extract of your data then loads in Excel.

Once your data is extracted, transformed, and loaded, it’s ready for action. 

ETL, Excel, and ExtendInsights

At CloudExtend, we make it easy for users to extract disparate data, then cleanse and transform it with Power Query before visualizing it in Excel or Power BI. Users could even opt to upload the transformed data back into NetSuite. In fact, we’re the ultimate NetSuite integration tool to enhance productivity and maximize resources, and one of the ways we do that is by helping you automate the process of extracting data from NetSuite to analyze in Excel. We can also help you add information from Excel right into NetSuite. Check out ExtendInsights to see how your data can be even more powerfully transformed into clear, actionable insights.

Not convinced yet? We’ll prove it to you with a free two-week trial.