Difference Between Load and Transform Data in Excel

Looking for the difference between Load and Transform Data in Excel? Then, this is the right place for you. First, we will briefly discuss the two topics separately and then we will find out the differences between the two in this article.

What Is Load Data in Excel?

Simply put, Load Data means importing data in Excel. Now, there are several ways of Loading Data into our Workbook. We will briefly tell you about each of these options.

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

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

Let’s 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 – We can import data from the “.xml” file types.
• From JSON – This option is to import data from JavaScript files.
• From PDF – We can import data from PDF files using this option.
• From Folder – This will allow us to import data from a specific Folder.

Here, we will quickly go through the less popular menus.

• Then, we have From Database option inside the Get Data feature. There are 4 sub-menus inside that. This sub-menus will allow us to import data from Microsoft Access and SQL database.
• After that, we have From Azure. By using this option we can Load Data from the Microsoft Azure Date Explorer.
• Next, we have the From Power Platform menu. We 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 – We can import from an online source.
• From Microsoft Query – This allows us to Load Data using Microsoft Query.
• From OData Feed – We 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. Moreover, let us see how we can Load Data from the Web.

Read More: How to Get and Transform Data in Excel

Load Data from Different Sources

In this section, we will Load Data from 2 sources. First, we will load it from Google Sheets and then from a text file. Moreover, we 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

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

Steps:

• We have published this to Web using File >>>> Share >>> Publish to Web.

• Then we copy the published link.

Now we will Load Data into Excel.

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

A dialog box will appear.

• Then, Paste the link and press OK.

The Navigator dialog box will appear.

• After that, select your Table. In our case, it was Table 0.
• Then, press Load.

Afterward, Table 0 will Load into a new Sheet. Thus we Load Data in Excel.

b) Loading Data from Text/CSV File

Now, let us see another way to Load Data in Excel from a text file.

Steps:

• First, from the Data tab >>> select From Text/CSV.

Then the Import Data dialog box will appear.

• Then, select the file and select Import.

• After that, press Load.

This is what the imported Data will look like.

We can see that the dollar sign ($) is missing from the Price column. However, if we open the text file, we can see there are dollar signs. Hence, we should edit this data after importing and that will be the Transform Data, which we will discuss next.

What Is Transform Data in Excel?

After importing data we have seen the formats break for the text file and From Web option. Additionally, we have noticed that there are a few extra rows and columns for the Web version. We can remove these using Transform Data.

Using Power Query Feature to Transform Data

Now, we will Transform Data in Excel. When we loaded our file there was another option called Transform. We will use that button in this section. Thus, we will learn about how Transform Data works in Excel.

Steps:

• First, as shown before, bring up the Navigator window.
• Next, press Transform Data.

• After this, the Power Query Editor window will appear.

• Then, we will Transform Data using various commands.
• Next, select the first two columns.
• Then, from the Remove Columns >>> select Remove Columns.

• By doing so, we will get rid of the columns.

• After that, we will remove the top row from the data.
• To do that, select Use First Row as Headers.

• Therefore, we will get rid of the first row from the Table.

• Afterward, we want 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. Hence, we will need to do it from the Number Format menu.

We can see all our changes in the Applied Steps under the Query Settings.

• Afterward, from File >>> select Close & Load.

This will Load Data in a new Sheet.

• Next, select the cell range D2:D7.
• Then, from the Home tab >>> Number Format >>> select Currency.

Therefore, we have added the currency symbol. Thus, we Transform 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 in Excel. However, when we edit the data, we use the Transform Data feature.
• Original data may be altered, whenever we use the Load Data in Excel. Transform Data in Excel can be used to 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. Whereas, we Transform Data using the Power Query Editor.

Download Practice Workbook

Conclusion

We have shown you the difference between Load and Transform Data in Excel. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Thanks for reading, keep excelling!

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