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.


Download Practice Workbook


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.

difference between load and transform data in excel

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.

difference between load and transform data in excel 2

This concludes our feature exploration of the Get Data feature. Moreover, let us see how we can Load Data from the Web.


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.

difference between load and transform data in excel 3

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.

difference between load and transform data in excel 5

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.

difference between load and transform data in excel 6

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.

difference between load and transform data in excel 7

  • After that, press Load.

This is what the imported Data will look like.

difference between load and transform data in excel 8

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 the Transform Data works in Excel.

Steps:

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

difference between load and transform data in excel 9

  • 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.

difference between load and transform data in excel 11

  • 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.

difference between load and transform data in excel 12

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

difference between load and transform data in excel 13

  • 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.

difference between load and transform data in excel 14

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.

difference between load and transform data in excel 15

  • 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.

difference between load and transform data in excel 16


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.

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. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I am passionate about all things related to data and MS Excel is my favorite application. I want to make people's life easier by writing easy-to-follow and in-depth guides here at Exceldemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo