Difference Between Load and Transform Data in Excel

What Is Load Data in Excel?

Load Data means importing data in Excel. There are several ways of Loading Data into our Workbook. We will briefly explain each of these options.

• From the Data tab >>> select Get Data.

This will bring up all options for importing data into our Workbook.

Go through the options of the Get Data feature.

Under the From File section, we have the following options –

• From Excel Workbook – The first option will allow us to import data from all Workbook file types (for example – “.xlsx”, “.xlsm”, “.xls”, etc.) in our current file.
• From Text/CSV – This will allow us to import data from “.txt”, “.csv”, and “.prn” files.
• From XML – You can import data from the “.xml” file types.
• From JSON – This option is to import data from JavaScript files.
• From PDF -You can import data from PDF files using this option.
• From Folder – This will allow us to import data from a specific Folder.

We will quickly go through the less popular menus.

• From Database option inside the Get Data feature. There are 4 sub-menus inside that. This sub-menus will allow you to import data from Microsoft Access and SQL database.
• From Azure. Using this option, you can Load Data from the Microsoft Azure Data Explorer.
• From the Power Platform menu. You can Load Data from the Dataverse using this option.

Now, we will look at the From Other Sources menu.

• From Table / Range – This will Load existing Data into Power Query.
• From Web – Import from an online source.
• From Microsoft Query – This allows us to Load Data using Microsoft Query.
• From OData Feed – Use this to import data from OData Feed.
• From ODBC – Import data from ODBC.
• From OLEDB – Import data from OLEDB.
• Blank Query – This will open the Power Query Editor with no data.

This concludes our feature exploration of the Get Data feature. See how we can Load Data from the Web.

Load Data from Different Sources

Load Data from 2 sources. Load it from Google Sheets and then from a text file. You need to learn about the Load Data in Excel to understand the difference between Load and Transform Data in Excel.

a) Loading Data from Web

You have this online dataset on Google Sheets. Load Data from this in our Excel file.

Steps:

• You published this to Web using File >>>> Share >>> Publish to Web.

• Copy the published link.

Load Data into Excel.

• To begin with, from the Data tab >>> select From Web.

A dialog box will appear.

• Paste the link and press OK.

The Navigator dialog box will appear.

• Select your Table. It was Table 0.
• Press Load.

Table 0 will Load into a new Sheet. Load Data in Excel.

b) Loading Data from Text/CSV File

See another way to Load Data in Excel from a text file.

Steps:

• From the Data tab >>> select From Text/CSV.

The Import Data dialog box will appear.

• Select the file and select Import.

• Press Load.

This is what the imported Data will look like.

We can see that the dollar sign ($) is missing from the Price column. If we open the text file, we can see dollar signs. You should edit this data after importing it, which will be the Transform Data, which we will discuss next.

What Is Transform Data in Excel?

Using Power Query Feature to Transform Data

When we loaded our file, another option called Transform appeared. We will use that button in this section and learn how Transform Data works in Excel.

Steps:

• Bring up the Navigator window.
• Press Transform Data.

• The Power Query Editor window will appear.

• Transform Data using various commands.
• Select the first two columns.
• From the Remove Columns >>> select Remove Columns.

• Get rid of the columns.

• Remove the top row from the data.
• Select Use First Row as Headers.

• Get rid of the first row from the Table.

• To format the Price column in the Table. But we will Transform Data into Numbers using this.
• From the Transform tab >>> Data Type >>> select Currency.

Notice that there is no currency symbol on this. Excel does not allow this to change. You will need to do it from the Number Format menu.

See all our changes in the Applied Steps under the Query Settings.

• From File >>> select Close & Load.

This will Load Data in a new Sheet.

• Select the cell range D2:D7.
• From the Home tab >>> Number Format >>> select Currency.

You added the currency symbol. You Transformed Data in Excel.

Key Differences Between Load and Transform Data

As we have seen how these two features work, we can state the differences between them.

• When we import data to our Excel file, we use the Load Data feature in Excel. Use the Transform Data feature when we edit the data.
• Original data may be altered whenever we use the Load Data in Excel. Transform Data in Excel can bring back the original data structure and formatting.
• There are several ways to Load Data in Excel, such as – text files, from the Web, etc. We also transform data using the Power Query Editor.

Download Practice Workbook

<< Go Back to Transform Data in Power Query | Power Query Excel | Learn Excel