Add a conditional column to your imported data set

 

by Kara Hiltz

Application:

Microsoft Excel 2010/2013/2016

Operating System:

Microsoft Windows

Download:

http://download.elijournals.com/excel/201610/customcolumn.zip

When you’re importing external data to use in Excel, you may feel like you’re stuck with whatever columns are available in the data. But you can add custom columns to your data with Power Query to create columns of text, calculations, or conditional data using formulas.

To create a conditional column in our imported data, we’ll:

  • Import data from a .CSV file into Excel.
  • Use the Query Editor to add a new conditional column to the existing data.
  • Save and load the amended query data so that it displays in our Excel workbook.

 

In Excel 2010 and 2013, the Power Query add-in allowed users to take more control over the imported external data that they use in Excel workbooks. Now, in Excel 2016, the Power Query feature is built in and there’s no need for an add-in.

In this article, we’ll show you an often overlooked feature of Power Query: the ability to create conditional columns that were not part of the original data, as shown in Figure A. A conditional column works much the same way as an IF formula by allowing you to populate the column with data only when it meets a condition that you define.

Note: This article includes steps for Excel 2016. While these features are available in Excel 2010 and 2013’s Power Query add-in, some steps may differ from the ones provided.

A:

IME18101A

The original data did not include the Late column; we’ll add that conditional column using the Query Editor.

Create a new query to import data

When you want to use data in Excel that already exists in a different file or database, you can create a query to import that data and save yourself time.

In our example, we have information about product shipments, their estimated shipment dates, the actual dates orders were shipped, and the order amounts, as shown in Figure B. We want to use Power Query to import this existing data, which is in a .CSV file into our Excel workbook. Then, we’ll add a custom column that will display the text “Late” if a shipment’s actual ship date was later than its estimated ship date.

Download: You can follow along with our example by downloading and extracting the file ShipmentInfo.csv from the URL given at the beginning of this article.

B:

IME18101B

Our .CSV file contains shipment data that we want to open and use in Excel.

To create a query that imports external data:

  1. Open a new blank workbook in Excel.
  2. Switch to the Data tab and, in the Get & Transform area, click the New Query button to open a dropdown list.
  3. Hover over From File to view a sub-menu and select From CSV, as shown in Figure C. Note that, depending on your data, you may also import data from other workbooks, databases like Microsoft Access, and online sources.
  4. In the Import Data dialog box, navigate to the file you want to import. In our example, we’ll find ShipmentInfo.csv, select the file, and click the Import button.

Once you connect with your external data, which can sometimes take Excel a few moments, you’ll see a preview of the data, as shown in Figure D. Now we’ll e[...]

 
Join NowClose
Return to the ExcelSkillsSociety's homepage