How to Fix US Dates with Locale, Keep ID Columns as Text and Use Fixed Decimal Number in Power Query

By Ravindra Bagale · Last updated: 16 October 2026

Practice files: blinkit-orders-us-small.csv (the 8 rows on this page), blinkit-orders-us.csv (1,001 rows, for the practice task) and blinkit-store-cities.csv. The basic cleaning steps (headers, blank and duplicate IDs, NA to null) are covered in How to Clean Messy Sales Data in Power BI Power Query; this chapter adds what that one does not.

Made-up data: fictional Blinkit-style orders, exported in US date format, for practice only. Amounts are in ₹.

1. The problem

The file blinkit-orders-us-small.csv:

On a laptop set to English (India) (day/month/year), changing order_date to Date gives:

• Error for 8/18/2024 and 12/24/2024: there is no month 18 or 24.
• Wrong dates with no error for 9/3, 10/12, 11/5 and 12/1: they become 9 March, 10 December, 11 May and 12 January.

And: store_id 0101 turns into 101, BLK8004 appears twice, one order has no product_id, and sales is text like "2 sales".

2. The idea in simple words

• A date has a locale. The file was written in the US (month/day/year). Tell Power Query that with Change Type › Using Locale… › Date › English (United States). It then reads 9/3/2024 as 3 September, whatever your laptop's setting is.
• IDs are names, not amounts. order_id, store_id and product_id may contain digits, but you never add them up. Keep them as Text: leading zeros survive (0101 stays 0101) and Power BI will not offer "Sum of store_id".
• Unique means unique. Column profile shows, for a column, Count (values), Distinct (different values) and Unique (values that appear exactly once). For a primary ID all three should be equal.
• Do not lose money to a blank ID. If you delete the BLK8003 row because product_id is blank, its ₹270 disappears from revenue. Writing an UNKNOWN product ID keeps the sale and shows clearly that the product is missing.
• Money needs a fixed scale. Fixed decimal number stores exactly 4 digits after the decimal point. Sums of money then add up exactly, without the tiny floating-point remainders an ordinary Decimal Number can carry.

3. The data

What each order date really is, and what the India locale makes of it:

Column profile of order_id before cleaning: Count 8, Distinct 7, Unique 6 (BLK8004 twice).

After removing the duplicate there are 7 orders, 16 units and revenue ₹2,639. Deleting the blank-product row would leave 6 orders and ₹2,369: ₹270 less.

4. Step by step

Load and look

1. Home › Get data › Text/CSV › blinkit-orders-us-small.csv › Transform Data.
2. If Power Query added a Changed Type step, click its ✕ to delete it: it may have turned store_id into numbers and dates into errors.
3. View › tick Column quality, Column distribution and Column profile. Click the order_id header: Count 8, Distinct 7, Unique 6.

IDs as Text and unique

4. Ctrl+click order_id, store_id and product_id › Transform › Data Type › Text. store_id keeps 0101.
5. Right-click order_id › Remove Duplicates. Column profile now reads Count 7, Distinct 7, Unique 7.
6. Turn Column profile off again (View › untick) to keep the editor fast.

Unknown product ID

7. Click the BLK8003 product_id cell. If it is empty text, right-click product_id › Replace Values › leave Value To Find empty › Replace With UNKNOWN › OK. (If it shows null instead, type null in Value To Find.)

US dates

8. Right-click order_date › Change Type › Using Locale… › Data Type Date, Locale English (United States) › OK.
9. Check: BLK8001 shows 18-08-2024 and BLK8002 shows 03-09-2024 (your laptop shows them in its own date format).

Numbers and money

10. Select sales › Transform › Extract › Text Before Delimiter, type a single space, OK. "2 sales" becomes 2. Set the type to Whole Number.
11. Ctrl+click price and revenue › Data Type › Fixed decimal number (in Power Query it may show as Currency, with a $ icon; it does not change the ₹ meaning of your numbers).
12. Home › Close & Apply. In Table view, check 7 rows.

5. Your result should look like this

Checks: 7 rows, sales 16, revenue ₹2,639, no Error in order_date, store_id still 4 characters long. Revenue by store: 0205 1,479, 0102 530, 0101 510, 0303 120.

6. What happened

• Deleting the automatic Changed Type step stopped Power Query from guessing before you decided.
• Text type kept the IDs exactly as written, so 0101 can still match the store table later.
• Remove Duplicates on order_id kept the first BLK8004 and dropped the copy.
• UNKNOWN kept BLK8003's ₹270 in every total and made the missing product easy to find.
• Using Locale read every date as month/day/year; the stored value is a real date, shown in your own format.
• Fixed decimal number rounded 26.66667 to 26.6667 and stores every price with exactly 4 decimals.

7. Explore further

• Why the error came first. Power BI only shows Error for impossible dates. The quiet ones (9/3 read as 9 March) are worse, because nothing warns you. Always check one date you know.
• Error? Delete the step first. If a Change Type step produced errors, delete that step and redo it with Using Locale; do not stack another type change on top. The same rule is explained in How to Fix DataFormat.Error We Couldn't Convert to Number in Power Query.
• Leading zeros and lookups. If store_id becomes 101 in sales but stays 0101 in blinkit-store-cities.csv, a merge or relationship on store_id finds nothing.
• Unknown ID or delete? The clean messy sales data chapter removes rows with a blank product ID to keep practice simple. When the money must match finance's total, keep the row with an UNKNOWN ID instead, and report how many there are. The same idea for blank product names is in How to Clean Text Columns in Power Query with Split, Trim, Clean and Lowercase.
• How exact is Fixed decimal? It keeps 4 decimal places. Values with more digits are rounded (26.66667 → 26.6667). A rounding difference of just 1 paisa per row on 1 crore rows would be ₹1,00,000, which is why money columns deserve a fixed scale.
• Column profile reads the first 1,000 rows by default; switch to Column profiling based on entire data set at the bottom of the editor for the practice file, which has 1,001 rows.

8. Common mistakes

9. Practise on your own

10. Think like a professional

• Business view. A month-wise report built on wrongly read dates puts September sales into March. Date checks are cheap; wrong monthly numbers are expensive.
• Agree on IDs. Keys must have the same type and format in every table (all Text, same leading zeros), or merges and relationships fail quietly.
• Reconcile money. Compare your total with the source system's total. UNKNOWN IDs and Fixed decimal number help them match.
• Interviewers love dates. Be ready to explain locale, M/D/Y vs D/M/Y, and why some dates error while others are silently wrong.

Interview questions

1. How do you convert US dates on an Indian-locale laptop? Change Type › Using Locale › Date › English (United States).
2. Why keep IDs as Text? They are labels: no sums, leading zeros kept, keys match across tables.
3. What do Count, Distinct and Unique mean in Column profile? Values, different values, values appearing exactly once.
4. Fixed decimal number vs Decimal Number? Fixed decimal keeps exactly 4 decimal places, good for money; Decimal Number is floating point.

Recap

Ravi Sir's Cards for this lesson

Revision cardComparison card

मराठी cards