Ravindra BagaleCourses & study guides मराठी Track your progress

Clean Data in Excel Power Query: Extract Text Before Delimiter, Replace NA with 0, Data Types, Remove Columns and Close & Load

By Last updated:

The previous chapter fixed the rows of the messy orders export: title lines, blank and NA IDs, duplicates and dates. Now the columns: a number stuck to a word ("3 sales"), prices that say NA, money columns that are still text, columns nobody needs and names that are hard to read. At the end, one click loads the clean table into Excel, and the same steps clean next month's file.

What you'll learn in this chapter

  • Remove a column that repeats another one
  • Extract > Text Before Delimiter: "3 sales" becomes 3
  • Replace Values: NA to 0, instead of filtering rows away
  • Data types: Whole Number, Currency, Date
  • Remove columns that carry no information, and rename the rest
  • Close & Load, Refresh, and how much data Excel can hold

Practice files: quick-commerce-orders-raw.csv (the messy export) and power-query-clean-orders-final-answer.xlsx (the final clean table, every applied step of both chapters, and check formulas).

Made-up data: the orders, product IDs, store IDs and amounts are made up for practice. They imitate an export from a quick-commerce app such as Blinkit or Amazon Now in Pune, Nashik, Solapur, Kolhapur, Nagpur and Sambhaji Nagar.

About the figures: they are simplified diagrams of the Power Query Editor, drawn for this chapter, not screenshots.

1. Where we are

After the previous chapter the query has 26 orders and these columns: order_id, order_date (a real date), city, product_id, sales, price, revenue, stock, store_id, promo_code and order_date_2. To reopen it: Data > Queries & Connections, double-click the query.

2. Remove the repeated date column

order_date_2 holds the same dates as order_date, only written as day/month/year text. Two columns with the same information confuse anyone who uses the table later.

  1. Right-click the order_date_2 header.
  2. Click Remove.

3. Extract the number from "3 sales"

The sales column says "1 sales", "3 sales" and so on: text, so it cannot be added.

  1. Select the sales column.
  2. Transform > Extract > Text Before Delimiter.
  3. Delimiter: type one space. Click OK. "3 sales" becomes "3".
  4. Click the type icon in the header > Whole Number.

The column now holds real numbers that add up to 76 units. (Transform changes the column itself; the same command on the Add Column tab would create a new column and keep the old one.)

Classroom line

What does extract mean? From that column, keep what you need and take out what you don't.

Transform > Extract > Text Before Delimiter (" ") (diagram) 1 sales 1 1 4 sales 4 4 2 sales 2 2 5 sales 5 5 3 sales 3 3 ABC sales (text) ABC after extract 123 Whole Number Delimiter: one space Keeps the text before it Total units: 76

Figure 1. Transform > Extract > Text Before Delimiter with a space: "1 sales" becomes 1, "3 sales" becomes 3. Then the type changes from Text (ABC) to Whole Number (123).

This is the same idea as TEXTBEFORE in the split quantity and unit chapter, done once in Power Query instead of in a formula column.

4. NA in price: replace, don't filter

Two orders, ORD3009 and ORD3020, have NA as their price. Their sales, revenue, city and date are all fine.

If you filter NA out of price, Power Query removes the whole row: two real orders and their revenue of 270 and 135 disappear from every total. Replace the value instead:

  1. Select the price column.
  2. Transform > Replace Values.
  3. Value To Find: NA. Replace With: 0. Click OK.

The two orders stay, with price 0. Their revenue column still holds the real amounts. (If 0 could be mistaken for a free item in your report, replace NA with null instead; it shows as an empty cell in Excel.)

NA in price: filter (loses rows) vs Replace Values (keeps rows) Filter: untick NA order_id price revenue ORD3008 45 90 ORD3010 60 180 (2 orders gone) −405 Replace Values: NA → 0 order_id price revenue ORD3008 45 90 ORD3009 0 270 ORD3010 60 180 ORD3020 0 135 Filtering deletes the whole row. Replace Values changes only the cell; the revenue of both orders stays.

Figure 2. Filtering NA out of price deletes the whole rows of ORD3009 and ORD3020 (revenue 270 and 135 lost). Replace Values NA → 0 keeps both orders; only the price cell changes.

5. Data types for the money and the counts

Click the type icon in each header:

Column Type Why
order_date Date (already set, with locale) real dates sort and group by month
sales Whole Number units are counted in whole numbers
price Currency money
revenue Currency money
stock Whole Number items on the shelf
order_id, product_id, store_id, city Text labels, never added

In Power Query, Currency is the type called Fixed decimal number (the $ icon). It keeps exactly four decimal places, so money adds up without rounding surprises. Excel shows it with your own currency format after loading.

Classroom line

Anything that is about money, keep its data type as currency.

6. Remove columns that carry no information

Before loading, ask of every column: will anyone use it?

  1. promo_code: 24 of the 26 orders say NA; only ORD3005 and ORD3018 have FEST10. Too little to analyse here: Remove.
  2. A column with the same value in every row tells nothing either, and a 100% empty column (like Column10 in the previous chapter) never does.
  3. A second copy of the same data in another format (like order_date_2) goes too.

To remove many at once, select the columns you keep and use Home > Remove Columns > Remove Other Columns.

7. Rename the columns

Double-click a header and type the new name:

Old New
order_id Order ID
order_date Order Date
city City
product_id Product ID
sales Units Sold
price Price
revenue Revenue
stock Stock
store_id Store ID

Readable names make the table and every pivot built on it easier to understand. Some people prefer names without spaces (Order_ID) because they are quicker to type in formulas; either works, as long as you are consistent.

All Applied Steps, then Home > Close & Load (diagram) APPLIED STEPS Source Removed Top Rows (2) Promoted Headers Removed Top Rows (1) Filtered Rows (order_id) Removed Duplicates Filtered Rows (product_id) Removed Columns (Column10) Changed Type with Locale Removed Columns (order_date_2) Extracted Text Before Delimiter Changed Type (sales) Replaced Value (price NA → 0) Changed Type (Currency, Whole Number) Removed Columns (promo_code) Renamed Columns Loaded as an Excel Table, 26 rows: Order ID Order Date City Product ID Units Sold Price Revenue Stock Store ID Steps 1-9: previous chapter (rows) Steps 10-16: this chapter (columns) Revenue ₹3,779 · Units 76 · Pune 5 orders Next file: Data > Refresh All

Figure 3. The full Applied Steps list of both chapters, from Source to Renamed Columns, and the final 9 columns that will be loaded.

Ravindra Bagale's Tip

Never edit the cleaned table by hand after loading. The next refresh will overwrite your edit. If something is wrong, fix it as a step in Power Query, so it is fixed for every future file too.

8. Close & Load

  1. Home > Close & Load.
  2. Power Query closes and Excel adds a new sheet with an Excel Table of 26 rows and 9 columns.

Check it with formulas on the table, as in the answer file: 26 rows, total revenue ₹3,779, 76 units sold, 2 prices of 0, 5 orders in Pune.

Next month: save the new export over the old CSV (same name, same folder) and click Data > Refresh All. Every step runs again on the new file. A query can also combine many files of the same layout with Get Data > From File > From Folder.

How much data? One worksheet holds at most 1,048,576 rows. Power Query itself is not limited to that: it can read bigger files and load them to the Data Model instead of a sheet (Close & Load To... > Only Create Connection and tick Add this data to the Data Model), where PivotTables can summarize them.

9. The whole chapter on one page

Problem Step
Same dates twice Remove order_date_2
"3 sales" Extract > Text Before Delimiter (space) > Whole Number
NA price Replace Values NA → 0 (not a filter)
Money as text Currency type
Mostly NA / single value / empty columns Remove
Hard-to-read names Double-click header > rename
Done Home > Close & Load
New file Data > Refresh All
  1. Extract keeps the part of the text you need.
  2. Filtering removes whole rows; Replace Values changes only the cell.
  3. Money is Currency, counts are Whole Number, IDs are Text.
  4. Remove columns that carry no information.
  5. Close & Load creates an Excel Table; Refresh replays every step.

10. Try at home

Try at home

Task 1

  1. Instead of Replace Values, filter NA out of price and load. How many rows and how much revenue do you get? (24 rows; ₹3,374.)

Task 2

  1. In the loaded table, what is the total revenue of the orders from Pune? (₹450, with =SUMIFS on the table.)

Recap

In short

  1. Remove columns that repeat others or carry nothing.
  2. Extract Text Before Delimiter turns "3 sales" into 3.
  3. Replace NA with 0 instead of filtering the row away.
  4. Currency for money, Whole Number for counts, Text for IDs.
  5. Close & Load, then Refresh All for next month's file.

Quick revision cards

Quick revision

Revise this chapter in two minutes. Save the cards on your phone or download them as PNG files. All names and numbers are made up for practice.

Revision card: Clean in Power Query. Covers “3 sales” to 3?; NA in price?; Repeated date column?; Finish?. With a photo of Ravindra Bagale and a short tip.
Card 1 of 2: Clean in Power Query · Download PNG
Revision card: Data types. Covers Money; Counts; IDs; Dates. With a photo of Ravindra Bagale and a short tip.
Card 2 of 2: Data types · Download PNG

More cards for every chapter: Ravi Sir's Cards.