Mission M10 · Excel · 15 min

Turn text numbers into real numbers

Request from your manager

Finance tried to total the January export and got a number that was too low. Some net_sales values are stored as text like '$5.03', and some quantities say '3 units'. Fix both columns so the totals are trustworthy.

Data

Download the practice file(s):

⬇ sales_dirty.xlsx⬇ sales_dirty.csv

Steps

  1. In Excel go to Data > From Text/CSV, pick sales_dirty.csv and click Transform Data. In the Power Query Editor delete the 'Changed Type' step under Applied Steps so every column stays as raw text, then Home > Close & Load to a table named Dirty.
  2. Add a column net_sales_num: =VALUE(SUBSTITUTE([@net_sales],"$","")). SUBSTITUTE removes the dollar sign and VALUE converts the text to a number.
  3. Add a column qty_num: =VALUE(SUBSTITUTE([@quantity]," units","")).
  4. Use =SUMPRODUCT(--ISTEXT(Dirty[net_sales_num])) to confirm that no values are still text (should be 0).
  5. Total the clean column with =SUM(Dirty[net_sales_num]). Keep all 627 rows; duplicates are handled in mission 12.
  6. Check the count of converted values with =COUNT(Dirty[net_sales_num]); it should be 627.

Check your answer

What is the total net sales of all 627 rows after converting the text values to numbers?

Show solution

29 net_sales values carried a '$', and SUM skips any value stored as text, which is why Finance's total came out too low. After SUBSTITUTE + VALUE the full total is $5,212.36. The same trick fixes the 15 quantities that say 'units'; total quantity becomes 1,201.

net_sales_num: =VALUE(SUBSTITUTE([@net_sales],"$",""))
qty_num:       =VALUE(SUBSTITUTE([@quantity]," units",""))
Text check:    =SUMPRODUCT(--ISTEXT(Dirty[net_sales_num]))
Total:         =SUM(Dirty[net_sales_num])

Answer: 5212.36 $

← Fix messy store names with TRIM and PROPERFix mixed date formats in Power Query →