7. Data Cleaning A–Z in Power Query
7.7 Wrong Data Types and Indian Date Formats (Using Locale)
Lakshat theva, indian systems write dates as dd-mm-yyyy (14-03-2025). If your Windows region is English (United States), Power Query reads "03-04-2025" as 4 March (month first) instead of 3 April, and "14-03-2025" becomes an Error karan there is no month 14.
Before (Text, read as US)
| Order Date (text) | Result with US locale |
|---|---|
| 03-04-2025 | 04-Mar-2025 ✗ |
| 14-03-2025 | Error ✗ |
| 01-11-2025 | 11-Jan-2025 ✗ |
After (Using Locale en-IN)
| Order Date (Date) |
|---|
| 03-Apr-2025 |
| 14-Mar-2025 |
| 01-Nov-2025 |
Steps in Power BI
- Delete the automatic Changed Type step if it already converted the column wrongly (in Applied Steps, click the ✕ next to it).
- Right-click the Order Date header › Change Type › Using Locale…
- Data Type: Date (or Date/Time if there is a time part); Locale: English (India) › OK.
- Check the column quality bar: errors should now be 0%.
- To fix it for the whole file instead: File › Options and settings › Options › Current File › Regional Settings › Locale for import = English (India).
Typed = Table.TransformColumnTypes(Source, {{"Order Date", type date}}, "en-IN")
Choosing the right type for each column in our data:
| Column | Correct type | Why |
|---|---|---|
| Order ID, Product ID, Store ID, Pincode, Phone | Text | Identifiers are never added up; keeps leading zeros |
| Quantity | Whole Number | Counts of items |
| Amount, Discount, Delivery Fee | Fixed Decimal Number (currency) or Decimal Number | Money; Fixed Decimal stores 4 decimal places precisely |
| Delivery Time Mins | Whole Number (or Decimal) | Minutes |
| Order DateTime | Date/Time (or Date/Time/Timezone for UTC data) | Needs date and hour |
| Order Date | Date | Relates to the Date table |
| Is Repeat Customer | True/False | Yes/No flags |
Dates cha problem Maharashtra madhe sagalyat common aahe, karan aapan dd-mm-yyyy lihito. Locale lakshat theva.
Ravindra Bagale's Tip
The biggest date mistake is changing the type with the default locale, so 03-04-2026 becomes 4 March instead of 3 April. Use Change Type › Using Locale › English (India) for dd-mm-yyyy data. Also never let type detection turn Pincode or Phone into numbers, and use Fixed Decimal for ₹ amounts. Don't worry – after doing it two or three times, it becomes a habit.
Ravindra Bagale's Tip – मराठी
सगळ्यात मोठी date ची चूक म्हणजे default locale ने type बदलणं, त्यामुळे 03-04-2026 चा 3 April ऐवजी 4 March होतो. dd-mm-yyyy data साठी Change Type › Using Locale › English (India) वापरा. तसंच type detection ला Pincode किंवा Phone चे numbers कधीच करू देऊ नका, आणि ₹ amounts साठी Fixed Decimal वापरा. घाबरू नका, दोन-तीन वेळा केलं की सवय होते.
Ravindra Bagale's Tip – हिंदी
Date की सबसे बड़ी गलती है default locale से type बदलना, जिससे 03-04-2026 बन जाता है 3 April की जगह 4 March. dd-mm-yyyy data के लिए Change Type › Using Locale › English (India) इस्तेमाल करो. साथ ही type detection को कभी Pincode या Phone को numbers मत बनाने दो, और ₹ amounts के लिए Fixed Decimal इस्तेमाल करो. घबराओ मत, दो-तीन बार करने पर आदत हो जाती है.
Practice task
An Amazon Now export for Kolhapur has Delivered On as text like "31-10-2025 23:52". Convert it to Date/Time using the English (India) locale (भाषा/देशानुसार तारीख-अंक लिहिण्याची पद्धत), then add a Date-only column.