Power Query: Fix DataFormat.Error with NA, US Dates (M/D/YYYY) with Locale, Decimal Sales and Duplicate Rows vs Keys

By Ravindra Bagale · Last updated: 16 October 2026

Practice files: quick-commerce-sales-2025-raw.csv (the messy export) and power-query-sales-2025-clean-answer.xlsx (the clean table, the list of applied steps with row counts, and check formulas).

Made-up data: every order, product, price and store is made up for practice. The file imitates a quick-commerce export such as Blinkit or Amazon Now in Pune, Nashik, Solapur, Kolhapur, Nagpur and Sambhaji Nagar.

1. Where Get Data can read from

Data > Get Data lists the sources:

1. From File: Workbook, Text/CSV, XML, JSON, PDF, Folder.
2. From Database: SQL Server, Access, Oracle, MySQL and others.
3. From Azure and From Online Services.
4. From Other Sources: web pages, ODBC and more.

A database connection asks for more than a file: the server address, the database name, and a username and password (your company's IT team gives you these). After that, the editor works the same way for every source.

When is data used straight in Excel, without Power Query? Only when it is already clean. This export is not.

2. The messy file

Open the CSV with Data > Get Data > From File > From Text/CSV, choose quick-commerce-sales-2025-raw.csv, click Transform Data. The preview shows:

1. Two junk lines on top (a title and "Exported on 1 July 2025"), then the header line, then a blank line.
2. 52 data lines, with a blank order_id, an order_id of NA and one line that appears twice (ORD5020).
3. ORD5012 on two lines with different products: one order, two items.
4. Dates like 1/13/2025: month first.
5. sales like "1.5 sales": a number stuck to a word.
6. NA in price and revenue for two orders.
7. An unnamed column that is almost empty, three promo columns and two copies of the delivery date.

3. The first steps, and the Changed Type step

Do the top of the file as in the first Power Query chapter:

1. Home > Remove Rows > Remove Top Rows, 2.
2. Home > Use First Row as Headers.
3. Home > Remove Rows > Remove Top Rows, 1 (the blank line).

After Use First Row as Headers, Power Query adds a Changed Type step on its own, guessing a type for every column. On a messy file the guess is wrong: dates become errors, "1.5 sales" stays text. Click the × next to Changed Type in Applied Steps to delete it, and set the types yourself after cleaning.

1. Filter order_id: untick (blank) and NA. If NA is not in the list, click Load more at the bottom of the filter list. 50 rows remain.

4. Duplicate rows vs a repeated key

ORD5020 appears twice with every value the same: a true duplicate, the export wrote it twice. ORD5012 also appears twice, but with two different products: one order, two items. Both lines are real sales.

Right: remove only rows where every column is the same.

1. Click any column header, then press Ctrl+A to select all columns.
2. Home > Remove Rows > Remove Duplicates. 49 rows remain: only the copy of ORD5020 goes.

Wrong for this file: Remove Duplicates on order_id alone gives 48 rows. It deletes the copy of ORD5020 but also ORD5012's second product line, with its ₹80 of revenue.

Remove Duplicates on a key column is right only when the file has one row per key, like the orders file of the first Power Query chapter. In a file of order lines, check first.

5. Columns that carry nothing

Turn on View > Column Quality and View > Column Distribution:

1. Column10 (no name in the file): 98% Empty, one stray cell. Remove.
2. promo_bin_1: 92% Empty, values on only 4 orders. Too little to use: remove.
3. promo_type_2: one value, PR14, in every row (Column Distribution shows 1 distinct). It tells nothing: remove.
4. promo_discount_2: NA in every row: remove.
5. delivery_date_format2: the same dates as delivery_date_format1, written as DD/MM/YYYY: remove.

To remove several at once, Ctrl+click their headers, then right-click > Remove Columns (or Home > Remove Columns). Then double-click delivery_date_format1 and rename it delivery_date. Names with underscores are fine; spaces are a readability choice, not a rule.

6. Dates written month first

Change order_date to Date the normal way on an Indian computer and 28 of the 49 dates show Error: 1/13/2025 has no 13th month. The others are worse: 1/8/2025 quietly becomes 1 August when it is really 8 January.

1. Delete that Changed Type step (×).
2. Click the type icon of order_date > Using Locale...
3. Data Type: Date. Locale: English (United States). OK.

Every date is now right: 1/13/2025 is 13 January, 1/8/2025 is 8 January. The first Power Query chapter explains locales in more detail.

7. sales with decimals

Onion, rice and tomatoes are sold by weight, so sales has values like "1.5 sales" and "4.25 sales".

1. Select sales, Transform > Extract > Text Before Delimiter, delimiter: one space, OK.
2. Type icon > Decimal Number (not Whole Number as in the cleaning chapter, or 1.5 would become 2).
3. stock also has decimals (12.5, 6.75: loose items out of a box): Decimal Number.

8. "We couldn't convert to Number. Details: NA"

Set price to Currency. Two cells turn into Error. Click next to one of them and the details read: DataFormat.Error: We couldn't convert to Number. Details: NA. The text "NA" cannot become a number.

The two orders are ORD5015 and ORD5033, and both have NA in price and in revenue: the export has no money value for them at all.

1. Delete the Changed Type step that made the errors (×).
2. Filter price: untick NA. 47 rows remain.
3. Now set price and revenue to Currency. No errors.

Removing the rows is right here because the orders have no revenue to add. When only the price is missing and the revenue is known, keep the order with Replace Values NA → 0, as in the cleaning chapter. The rule is the same both times: remove the NA before changing the type.

9. Close & Load

Home > Close & Load. Excel adds a sheet with an Excel Table of 47 rows and 10 columns: order_id, order_date, city, product_id, sales, price, revenue, stock, store_id, delivery_date. Total revenue is ₹5,641.50. These Power Query types (Decimal Number, Currency, Date, Text) are the same in Excel and in Power BI, so the steps carry over.

10. More examples

Example 1: count real orders. 47 rows but 46 different order_ids, because ORD5012 has two lines. Count rows for "lines sold" and distinct order_ids for "orders".

Example 2: revenue of one city. On the loaded table, =SUMIFS(Sales2025[revenue],Sales2025[city],"Solapur") → ₹825.50 (after you name the table Sales2025 in Table Design).

Example 3: a date with a day of 12 or less. 3/1/2025 (ORD5014) is 1 March. With the Indian locale it would read 3 January without any error: the reason to check one date per column after every date conversion.

11. The whole chapter on one page

1. Delete the automatic Changed Type step on messy files.
2. Remove duplicate rows on all columns unless the key must be unique.
3. Column Quality and Distribution show columns worth removing.
4. Using Locale reads month-first dates correctly.
5. Clear NA before any number type change.

12. Try at home

Recap

Quick revision cards

Englishमराठीहिंदी

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.

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