মার্চ মাসের শেষ। অফিসে তখন বছর শেষের হিসাব-নিকাশের ধুম। আমাদের accountant দাদা — সুমন দা — সকাল থেকে বসে আছেন। Tally থেকে সারা বছরের ledger export করেছেন। Excel-এ নিয়ে এসেছেন। SUM দিয়েছেন। দেখেন — ০।
আমি পাশ দিয়ে যাচ্ছিলাম। তিনি ডাকলেন — "দেখো তো ভাই, Excel-এ কী হয়েছে।"
File খুলে দেখি — এক ভয়ংকর দৃশ্য। Date গুলো text হয়ে আছে। Amount-এ comma। Ledger Name-এ আগে-পরে space। মাঝে মাঝে Tally-র নিজস্ব "Total" row। কোথাও কোথাও ফাঁকা row।
সুমন দা বললেন, "এগুলো manually ঠিক করতে হলে সারাদিন লাগবে।"
আমি বললাম, "১৫ মিনিট দাও।"
Power Query খুললাম। কয়েকটা click। সব clean। সুমন দা তাকিয়ে রইলেন।
আজকে সেই একই পদ্ধতি step-by-step তোমাদের শেখাব।
Tally Export করলে Excel-এ ঠিক কী সমস্যা হয়?
Tally থেকে data Excel-এ আনলে এই ৬টা সমস্যা প্রায় সবসময় থাকে:
| # | সমস্যা | ফলাফল |
|---|---|---|
| ১ | Date text format-এ (01-04-2025) | SUM, FILTER, Pivot কাজ করে না |
| ২ | Amount-এ comma (15,000.00) | Number হিসেবে কাজ করে না |
| ৩ | Ledger Name-এ extra space | VLOOKUP/MATCH মিলে না |
| ৪ | Blank rows মাঝে মাঝে | SUM ভেঙে যায় |
| ৫ | Tally-র "Total" / "Grand Total" row | Double counting হয় |
| ৬ | প্রথমে Tally-র title rows | Header ঠিক হয় না |
নিচের screenshot-এ দেখো — এটাই আমাদের raw Tally export। লাল রঙে highlight করা সমস্যাগুলো একটু খেয়াল করো।
📸 Tally_Raw_Export Sheet — blank rows, summary rows, text format amount সহ আসল Tally data
Power Query কেন? সাধারণ Formula দিয়ে হয় না?
হয়। কিন্তু সমস্যা হলো — প্রতি মাসে নতুন Tally export আসলে প্রতিবার সব formula আবার লাগাতে হবে। ভুলের সম্ভাবনা বাড়ে।
Power Query-র magic হলো — একবার setup করলে পরের মাসে শুধু "Refresh" click করলেই সব automatically clean হয়ে যাবে। এক click, ব্যস।
| পদ্ধতি | প্রথমবার | পরের মাসে |
|---|---|---|
| Manual Formula | ৩০-৪৫ মিনিট | আবার ৩০-৪৫ মিনিট |
| Power Query | ১৫ মিনিট | ১ click (Refresh) |
Practice File-এর Structure
আমি এই tutorial-এর জন্য একটা practice Excel file বানিয়েছি। এতে ৩টা sheet আছে:
- Tally_Raw_Export — আসল Tally export-এর মতো messy data (সমস্যাগুলো দেখার জন্য)
- After_Power_Query — Power Query দিয়ে clean করার পরের result
- Power_Query_Steps — সব ধাপের quick reference
ধাপে ধাপে Power Query Setup
Excel খোলো → উপরে Data tab → Get Data → From File → From Workbook → তোমার Tally export file select করো → Tally_Raw_Export sheet select → Transform Data click করো।
Power Query Editor খুলবে। এখানেই সব কাজ হবে।
Tally export-এর প্রথম ৩টি row company name, period — এগুলো data না। সরাতে হবে।
Home → Remove Rows → Remove Top Rows → 3 দাও → OK
এখন row ৪ (আসল header) উপরে এসে গেছে। সেটাকে column header করো।
Transform → Use First Row as Headers
Tally মাঝে মাঝে ফাঁকা row দেয়। এগুলো বাদ দাও।
Home → Remove Rows → Remove Blank Rows
Tally নিজে "Total" ও "Grand Total" row যোগ করে। এগুলো রাখলে double counting হবে।
Ledger Name column-এর dropdown → Filter → "Total" এবং "Grand Total" uncheck → OK
Date column টি text হিসেবে আছে (01-04-2025)। Excel date-এ বদলাও।
Date column select → Transform tab → Data Type → Date
যদি error আসে, তাহলে locale change করো: Using Locale → Bengali (India) বা English (India)
Dr Amount column-এ "15,000.00" আছে — এটা text। দুটো কাজ করতে হবে:
প্রথমে comma সরাও: Transform → Replace Values → "," কে "" দিয়ে replace
তারপর type বদলাও: Data Type → Decimal Number
Cr Amount column-এও একই কাজ করো।
" Electricity Bill " — আগে-পরে space থাকলে VLOOKUP কাজ করে না।
Ledger Name column select → Transform → Format → Trim
তারপর: Format → Clean (invisible character সরাবে)
Dr Amount থাকলে Receipt, Cr Amount থাকলে Payment — এই logic-এ নতুন column বানাও।
Add Column → Conditional Column → নাম দাও "Type"
Condition: Dr Amount is not null → "Receipt", else → "Payment"
সব ধাপ শেষ। এখন clean data Excel-এ load করো।
Home → Close & Load To → Table → Existing Worksheet → After_Power_Query sheet select → OK
📸 Power_Query_Steps Sheet — সব ধাপের quick reference একনজরে
এই হলো Result — Before vs After
সব ধাপ শেষে After_Power_Query sheet-এ এই সুন্দর clean data দেখতে পাবে:
📸 After_Power_Query Sheet — blank rows নেই, summary নেই, date সঠিক, amount number-এ, name clean
পরের মাসে মাত্র ১ Click!
এটাই Power Query-র আসল জাদু। পরের মাসে নতুন Tally export এলে:
- নতুন Tally data পুরনো file-এর Tally_Raw_Export sheet-এ paste করো
- After_Power_Query sheet-এ যাও
- যেকোনো জায়গায় right-click → Refresh
ব্যস। সব automatically clean হয়ে যাবে। সুমন দার মতো আর সারাদিন বসে থাকতে হবে না।
FAQ
প্রশ্ন ১: Power Query কি সব Excel-এ আছে?
Excel 2016 এবং তার পরের সব version-এ আছে। Microsoft 365-এ সবচেয়ে ভালো কাজ করে। Excel 2013-এও আছে, তবে আলাদা add-in হিসেবে।
প্রশ্ন ২: Date বদলাতে গিয়ে error আসছে — কী করব?
এটা common। Tally date format (DD-MM-YYYY) Excel সবসময় চেনে না। Date column-এ right-click → Change Type → Using Locale → English (India) বা Bengali (India) select করো।
প্রশ্ন ৩: Amount-এ শুধু comma না, ₹ sign বা অন্য character থাকলে?
Replace Values দিয়ে এক এক করে সরাও। অথবা Transform → Extract → Text Between Delimiters ব্যবহার করো।
প্রশ্ন ৪: আমার Tally export-এ আরও বেশি সমস্যা আছে — কী করব?
Power Query Editor-এর বাঁ দিকে "Applied Steps" panel দেখো। যেকোনো step-এ ক্লিক করে আগের state দেখতে পারবে। নতুন step যোগ করা যায়, মুছেও দেওয়া যায়।
প্রশ্ন ৫: Refresh দিলে আমার manually করা formatting মুছে যাচ্ছে?
হ্যাঁ, এটা Power Query-র limitation। Formatting আলাদা sheet-এ রাখো, বা Power Query output-এর পাশে Conditional Formatting use করো — সেটা Refresh-এ থাকে।
প্রশ্ন ৬: এই method কি Tally Prime-এও কাজ করবে?
হ্যাঁ। Tally ERP 9 এবং Tally Prime দুটোর export format প্রায় একই। একই Power Query steps কাজ করে।
শেষ কথা
সুমন দা এখন প্রতি মাসে নিজেই Refresh করেন। আমাকে ডাকেন না। একদিন বললেন — "এই একটা জিনিস শিখে মাসে ৩-৪ ঘণ্টা বাঁচছে।"
Power Query শেখা একটু সময় লাগে। কিন্তু একবার শিখলে Tally, bank statement, billing software — যেখান থেকেই data আসুক, ভয় নেই।
📥 Practice Excel File Download করো:
👉 excel.technicalrajesh.in
কোনো step-এ আটকে গেলে comment করো — আমি উত্তর দেব।