Data Types in Power Query
Data types tell Power Query whether a column is text, a whole number, a decimal, or a date. Wrong types silently break sums, sorts, relationships and leading zeros on PIN codes and phone numbers.
Friends! PIN 411001 becomes 411001 as a number and looks fine — until a code with a leading zero arrives. Why care? Because type is the quiet bug behind “SUM does not work”. How? We fix Amount, PIN, phone and an Indian date on a FreshBasket practice sheet. Icons first, then Using Locale.
मित्रांनो! PIN 411001 number म्हणून ठीक दिसतो — जोपर्यंत leading zero असलेला कोड येत नाही. का महत्त्वाचे? Type हा शांत बग असतो ज्यामुळे “SUM काम करत नाही”. कसे? FreshBasket practice sheet वर Amount, PIN, phone आणि Indian date ठीक करायचे. आधी icons, मग Using Locale.
मित्रों! PIN 411001 number के रूप में ठीक लगता है — जब तक leading zero वाला कोड न आए. क्यों ज़रूरी? Type वो शांत बग है जिससे “SUM काम नहीं करता”. कैसे? FreshBasket practice sheet पर Amount, PIN, phone और Indian date ठीक करें. पहले icons, फिर Using Locale.
Quick answer
Vertical path:
- Read column header icons: ABC text, 123 whole number, calendar date.
- Amount → Decimal (or Fixed Decimal).
- PIN and phone → Text so zeros stay.
- Ambiguous dates → Data Type → Using Locale → English (India).
- Delete a wrong automatic Changed Type if it guessed badly.
- Close & Apply; test SUM and a relationship.
Real example: shop sheet types
| PIN | Phone | Amount | OrderDate |
|---|---|---|---|
| 411001 | 9876543210 | 100 | 15-01-2025 |
| 400001 | 9123456780 | 40 | 02-03-2025 |
What wrong types do:
- PIN as Whole Number — fine for these rows, but
"040001"would lose the zero. - Amount as Text — SUM fails or concatenates nonsense; card will not show 140.
- OrderDate as Text — no proper date hierarchy; time intelligence stays blank.
- After fixing types: Amount sums to 140, dates sort in calendar order, PIN stays text.
Why a type step is needed: Power Query’s automatic Changed Type samples rows and guesses. Guesses are not business rules.
What do I need before this guide?
- Applied Steps.
- Course: Data types.
Before and after (look at the tables first)
Before
आधी (Before)
पहले (Before)
After
नंतर (After)
बाद में (After)
Icons and common choices
Column icons show the type: ABC text, 123 whole number, calendar date. Wrong type breaks sums and relationships.
कॉलम icons type दाखवतात: ABC text, 123 whole number, calendar date. चुकीचा type sums आणि relationships मोडतो.
कॉलम icons type दिखाते हैं: ABC text, 123 whole number, calendar date. गलत type sums और relationships तोड़ता है.
PIN codes and phone numbers stay Text so leading zeros do not disappear.
PIN codes आणि phone numbers Text राहतात — म्हणून leading zeros गळत नाहीत.
PIN codes और phone numbers Text रहते हैं — इसलिए leading zeros गायब नहीं होते.
- Text — IDs, PIN, phone, product codes with letters.
- Whole number — counts, qty when always integer.
- Decimal / Fixed decimal — money and averages.
- Date / Date/Time — order day for relationships to a Date table.
- True/False — flags.
Indian dates often need Using Locale (English India) when automatic detection guesses wrong.
Indian dates ला अनेकदा Using Locale (English India) लागते — जेव्हा automatic detection चुकीचे गृहीत धरते.
Indian dates को अक्सर Using Locale (English India) चाहिए — जब automatic detection गलत अनुमान लगाए.
Why Using Locale: 02-03-2025 might mean 2 March (India) or 3 February (US). Locale removes the coin flip.
Working with Changed Type
- Click the Changed Type step.
- If PIN became a number, set it back to Text (or delete Changed Type and set types yourself).
- Prefer explicit type steps you renamed:
Typed Amount decimal,Typed PIN text. - Errors after conversion mean dirty cells — fix or replace errors before you model.
Mistakes and calm fixes
| Symptom | Likely cause | Fix |
|---|---|---|
| SUM grey / wrong | Amount still text | Set decimal type |
| Leading zero gone | Typed as number | Text type |
| Date sort weird | Text dates | Date + locale |
| Relationship fails | Type mismatch on key | Match types on both columns |
Ravindra Bagale's Tip
Interview line: “I set types in Power Query, keep PIN and phone as Text, and use Using Locale for Indian dates.” Got it?
Ravindra Bagale's Tip – मराठी
Interview line: “मी types Power Query मध्ये set करतो, PIN आणि phone Text ठेवतो, आणि Indian dates साठी Using Locale वापरतो.” समजलं का?
Ravindra Bagale's Tip – हिंदी
Interview line: “मैं types Power Query में set करता हूँ, PIN और phone Text रखता हूँ, और Indian dates के लिए Using Locale इस्तेमाल करता हूँ.” समझ में आया?
Practice task
- Import a sheet with PIN, Amount, and dd-mm-yyyy dates.
- Fix all three types.
- Prove SUM = expected total on a card.
- Note which automatic Changed Type you replaced.
Got it? Types decide whether sums, dates and PIN codes behave. Set them in Power Query on purpose. Next: Duplicate vs Reference. Let us go ahead.
समजलं का? Types ठरवतात sums, dates आणि PIN codes कसे वागतील. Power Query मध्ये मुद्दाम set करा. पुढे: Duplicate vs Reference. आता पुढे जाऊया.
समझ में आया? Types तय करते हैं sums, dates और PIN codes कैसे व्यवहार करेंगे. Power Query में जानबूझकर set करें. आगे: Duplicate vs Reference. अब आगे बढ़ते हैं.
Frequently asked questions
Why did my PIN lose the leading zero?
The column was typed as a number. Numbers do not keep leading zeros. Use Text.
Whole number vs decimal?
Whole number is integers (order count). Decimal is money and averages.
What is Using Locale?
You tell Power Query which regional date and number style to use when converting text.
Types in Power Query vs model?
Set them in Power Query so every refresh stays clean. Do not rely on fixing types only in the model.
Error cells after type change?
Some values cannot convert (text in an amount column). Fix or replace errors before Close & Apply.
Course lesson?
Data types in Power Query essentials.