داده‌های صفحه‌گسترده هرگز به‌صورت تمیز به‌دست نمی‌آیند. بین مقادیر گمشده، قالب‌بندی ناهماهنگ و سلول‌های ادغام‌شده، من ساعت‌ها را صرف کار با مجموعه‌داده‌های نامنظم می‌کردم. سپس به Power Query برخورد کردم و ناگهان آن وظایف خسته‌کنندهٔ پاکسازی کمتر شبیه مجازات و بیشتر شبیه روشن کردن سوئیچ‌ها به‌نظر می‌رسید.
داده‌های صفحه‑گسترده هیچ‌وقت به صورت تمیز وارد نمی‌شوند. بین مقادیر گمشده، فرمت‌های ناسازگار و سلول‌های ادغام‑شده، من ساعت‌ها را صرف دست و پنجه‌نرم کردن با مجموعه‌داده‌های شلوغ می‌کردم. سپس به پاور‌کوئری برخوردم و ناگهان آن کارهای خسته‌کنندهٔ پاکسازی، کمتر شبیه مجازات و بیشتر شبیه فشار یک دکمه احساس شد.
پاور‌کوئری پاکسازی داده‌ها را آسان می‌کند
پاور‌کوئری موتور تبدیل دادهٔ داخلی اکسل است که کارهای خسته‌کنندهٔ داده را خودکار می‌کند. به‌جای این‌که به‌صورت دستی هر سلول را پاکسازی کنید، یک بار به‌پاور‌کوئری می‌گویید چه می‌خواهید و این مراحل را روی هر مجموعه داده‌ای اعمال می‌کند. می‌توانید موارد تکراری را حذف کنید، ستون‌ها را تقسیم کنید، نوع داده‌ها را تغییر دهید و مقادیر گمشده را تنها با چند کلیک مدیریت کنید.
این ابزار رویکرد گام‌به‑گام و رابط بصری دارد که دنبال کردن آن را آسان می‌سازد. هر عملی که انجام می‌دهید به‌عنوان یک گام ثبت می‌شود که بعداً می‌توانید آن را ویرایش یا حذف کنید. به‌طور عمده، شما در حال دستکاری داده‌ها هستید، نه نوشتن کد—هرچند اگر کنجکاو باشید، کد M را در پس‌زمینه تولید می‌کند.
پاور‌کوئری به‌صورت عملی به تقریباً هر منبع داده‌ای متصل می‌شود، از جمله فایل‌های اکسل، اسناد CSV، پایگاه‌های داده، صفحات وب و حتی سرویس‌های ابری. به‌سادگی یک فایل را به همان پوشه اضافه کنید و پاور‌کوئری به‌صورت خودکار آن را بازیابی کرده و تمیز کردن آن صفحه‌گستردهٔ شلوغ را انجام می‌دهد.
نحوهٔ تنظیم PowerQuery در اکسل
پاور‌کوئری از نسخهٔ Excel 2016 به بعد به‌صورت پیش‌فرض درون‌ساخته است. بنابراین نیازی به نصب جداگانه ندارید.
به‌جدول فروش محصولات زیر نگاهی بیندازید — یک آشفتگی کامل است. شامل قالب‌های تاریخ مخلوط، نام‌های محصول ناهماهنگ، مقادیر گمشده و فضاهای اضافی در همه جای جدول است. این همان نوع مجموعه داده‌ای است که مرا وادار می‌کند از اکسل کاملاً دست بکشید.
اما با پاور‌کوئری راحت است آن را اصلاح کنید. در اینجا نحوهٔ تنظیم اولین تبدیل شما آورده شده است:
- محدودهٔ داده‌های خود را انتخاب کنید یا در هر نقطه‌ای از مجموعه داده کلیک کنید.
- به تب Data بروید، سپس روی From Table/Range کلیک کنید.
- اکسل مرزهای داده‌های شما را شناسایی کرده و یک جدول ایجاد می‌کند.
- روی OK کلیک کنید تا Power Query Editor باز شود.
- تبدیل‌های خود را با استفاده از ابزارهای نوار ربان انجام دهید.
- روی Close & Load کلیک کنید تا تغییرات به اکسل اعمال شود.
Power Query Editor در یک پنجره جداگانه باز می‌شود. این محیط جداگانه به شما اجازه می‌دهد بدون تأثیر بر داده‌های اصلی‌تان آزمایش کنید — به‌عنوان یک جعبهٔ شنی برای پاکسازی داده‌ها تصور کنید.
تبدیل‌های شما به‌عنوان پرس‌و‌جوهای (queries) در پنل Queries ذخیره می‌شوند. این پرس‌و‌جوها قابل‌استفاده مجدد و قابلیت به‌روزرسانی دارند. وقتی دادهٔ جدیدی می‌آید، کافی است روی آن کلیک راست کنید و Refresh را انتخاب کنید تا همان مراحل پاکسازی به‌صورت خودکار اعمال شود.
من این خودکارسازی را هنگام ساخت فرمول‌های جستجوی بسیار سریع مفید می‌دانم، زیرا داده‌های تمیز و سازگار باعث می‌شوند روابط جدول به‌خوبی کار کنند و به‌جای خراب شدن به‌دلیل تفاوت‌های جزئی فرمت، پایدار بمانند.
این مراحل پاکسازی بیشتر مشکلات داده را حل می‌کنند
پس از بارگذاری داده‌ها در Power Query Editor، گام بعدی پاکسازی آن‌هاست. بیایید فایل داده‌های فروش را با استفاده از تبدیل‌های پاور‌کوئری بررسی کنیم. مراحل زیر ۹۰٪ از مشکلات رایج داده‌ها را حل می‌کنند.
مرحله ۱: حذف سرعنوان‌های ادغام‌شده
سرعنوان‌های ادغام‌شده همه چیز را خراب می‌کنند. پاور‌کوئری نمی‌تواند به‌درستی نوع ستون‌ها را تشخیص دهد وقتی “SALES INFORMATION” بر چند ستون پخش شده باشد. برای رفع این مشکل مراحل زیر را دنبال کنید.
- سطر سرعنوان ادغام‌شده را در Power Query Editor انتخاب کنید.
- کلیک راست کرده و گزینه Remove Rows > Remove Top Rows را انتخاب کنید.
- عدد ۱ را وارد کنید تا سطر سرعنوان ادغام‌شده حذف شود.
- روی OK کلیک کنید. اکنون سرعنوان‌های واقعی ستون‌ها به‌درستی موقعیت‌گیری شده‌اند.
مرحله ۲: استانداردسازی قالب‌های تاریخ
جدول ما هشت قالب تاریخ مختلف دارد، از جمله ورودی‌های مشکل‌ساز مانند “16/01/2024”. پاور‌کوئری به‌صورت پیش‌فرض از قالب تاریخ ایالات متحده (MM/DD/YYYY) استفاده می‌کند، بنابراین “16/01/2024” خطا ایجاد می‌کند چون ۱۶ نمی‌تواند ماه معتبری باشد. متأسفانه باید این مقادیر را به‌صورت دستی جایگزین کنید یا از توابع تاریخ اکسل استفاده کنید.
برای استانداردسازی تاریخ‌ها:
- ستون Date را انتخاب کنید.
- به Transform > Data Type > Date بروید.
- پاور‌کوئری بیشتر قالب‌ها را به‌صورت خودکار شناسایی می‌کند — از جمله “January 15, 2024” و “15‑Jan‑24”.
- پیشنمایش را بررسی کنید تا تأیید شود همه تاریخ‌ها به‌درستی تبدیل شده‌اند.
هنگام کار با قالب‌های تاریخی بین‌المللی مخلوط، همیشه پیشنمایش تبدیلات را بررسی کنید. شناسایی خودکار پاور‌کوئری برای قالب‌های واضح خوب عمل می‌کند اما در قالب DD/MM/YYYY وقتی مقدار روز بیش از ۱۲ باشد، خطا می‌دهد.
برای مدیریت صحیح قالب‌های تاریخی بین‌المللی در پاور‌کوئری، باید نوع داده را با تنظیم واضح محل (locale) مناسب (مثلاً English (United Kingdom) برای DD/MM/YYYY) هنگام تغییر نوع ستون تعیین کنید.
می‌توانید این کار را با کلیک راست روی ستون، انتخاب Change Type > Using Locale، سپس انتخاب Date و محل موردنظر انجام دهید. این دقیق‌تر از مجرداً در هنگام “import داده‌های اولیه” است.
مرحله ۳: رفع ناسازگاری‌های متنی
نام‌های محصول مانند “surface laptop” نیاز به حروف بزرگ دارند و “iPhone 15″، “iphone15” و “iPhone‑15” باید استاندارد شوند. اینجاست که پاور‌کوئری واقعاً می‌درخشد:
- ستون Product Name را انتخاب کنید.
- به Transform > Format بروید و حالت مناسب (proper case) را برای اصلاح حروف بزرگ انتخاب کنید.
- سپس دوباره، روی Transform > Replace Values کلیک کنید تا انواع مختلف را استاندارد کنید.
مرحله ۴: حذف فضاهای اضافی
فاصله‌های پیشین و انتهای نام‌های نمایندگان فروش سبب شکست جستجو می‌شوند — یکی از اشتباهات اکسل که می‌تواند زمان تحلیل را افزایش دهد. برای رفع این مشکل:
- ستون Sales Rep را انتخاب کنید.
- به Transform > Format > Trim بروید.
فضاها از ورودی‌هایی مانند ” sarah johnson ” و ” Emma Taylor ” حذف می‌شوند. این کلیک واحد مشکلات فاصله‌ای را برطرف می‌کند که در غیر این صورت جدول‌های محوری و فرمول‌های VLOOKUP را خراب می‌کرد.
مرحله ۵: مدیریت هوشمند مقادیر گمشده
سلول‌های خالی در ستون‌های Category و Units Sold نیاز به توجه دارند. پاور‌کوئری چندین رویکرد ارائه می‌دهد.
برای Category:
- ستون Category را انتخاب کنید.
- سپس به Transform > Replace Values بروید.
- مقادیر null را با “Uncategorized” جایگزین کنید یا برای بازبینی دستی خالی بگذارید.
برای ستون‌های عددی مانند Units Sold، جایگزینی null با صفر ممکن است میانگین‌ها را تحت تأثیر قرار دهد. به‌جای آن می‌توانید آن‌ها را به‌عنوان “Data Missing” علامت‌گذاری کنید.
مرحله ۶: استانداردسازی قالب‌های ارز
مقادیر Revenue به صورت “$1,200″، “1200” و “$1.2K” ظاهر می‌شوند — هر کدام نیاز به نحوهٔ متفاوتی دارند.
- ستون Revenue را انتخاب کنید.
- از Replace Values استفاده کنید تا “$1.2K” را به “1200” تبدیل کنید.
- سپس به Transform > Data Type > Currency بروید، که به‌صورت خودکار علامت دلار و کاماها را مدیریت می‌کند.
- پیشنمایش نتایج را بررسی کنید تا از تبدیل صحیح اطمینان حاصل شود.
اختصار “K” نیاز به جایگزینی دستی دارد زیرا پاور‌کوئری به‌صورت خودکار این نماد را تشخیص نمی‌دهد.
مرحله ۷: ایجاد دسته‌بندی‌های سازگار
“Electronics”، “electronics” و “ELECTRONICS” باید یکسان باشند. توابع متنی پاور‌کوئری این مسئله را به‌سرعت حل می‌کنند.
- ستون Category را انتخاب کنید.
- روی Transform > Format کلیک کنید و حالت مناسب (proper case) را برای اصلاح حروف بزرگ انتخاب کنید.
این هفت مرحله بیشتر سناریوهای پاکسازی داده‌ها را پوشش می‌دهند. این تبدیلات تبدیل به گام‌های قابل تکرار می‌شوند. شما فقط کافی است همان فرآیند را برای داده‌های فروش شلوغ ماه بعد با یک بار به‌روزرسانی اعمال کنید.
هر تبدیل در پنل Applied Steps نشان داده می‌شود. می‌توانید گام‌ها را بدون شروع دوباره ویرایش، حذف یا ترتیب‑دوباره کنید. این انعطاف‌پذیری پاور‌کوئری را زمانی که نیاز به تنظیم فرآیند پاکسازی دارید، بخشنده می‌کند.
پاور‌کوئری تمامی مشکلات داده‌ای که با آن مواجه می‌شوید را حل نمی‌کند، اما بیشتر وظایف تکراری پاکسازی که ساعت‌ها کار دستی می‌طلبند را مدیریت می‌کند. البته منحنی یادگیری وجود دارد اگر با صفحات کاری پیچیده کار کنید، اما صرفه‌جویی زمانی به‌سرعت مشهود می‌شود. چه با گزارش‌های ماهانه و چه با مجموعه داده‌های یک‌باره سروکار داشته باشید، این تکنیک‌های تبدیل پایهٔ محکمی برای تحلیل داده‌های تمیزتر و قابل‌اعتمادتر فراهم می‌کنند.