پاور کوئری به عنوان یکی از ابزارهای نوین اکسل جهش قابل توجهی در کار با دیتای اولیه و آماده سازی آن برا تحلیل ها ایجاد کرد. حتی قابلیت های ساده و اولیه پاور کوئری نیز در بسیاری از مسائل راه گشا است. مثلا فرض کنید فایل های دیتا هر شهر به صورت مستقل برای شما ارسال شده است. حال می خواهید همه فایل ها را به صورت تجمیع شده داشته باشیم. یا فرض کنید همزمان نیاز به اتصال چندین پایگاه داده از جمله اکسل، اکسس، اسکیوال سرور و mySql داشته باشید. یا بخواهید دیتای خود را پاکسازی کنید و هر ماه نیاز به تکرار این فرایند داشته باشید. قطعا پاور کوئری ابزاری است که به شما کمک بسیاری خواهد کرد.
زمان یادگیری پاور کوئری نسبت به یادگیری فرمول نویسی و ماکرو نویسی کمتر ولی نتایجی که بدست خواهید آورد بسیار زیاد است. شاید کارهایی که نیاز به ساعت ها برنامه نویسی و فرمول نویسی داشت در پاور کوئری با چند کلیک قابل انجام است. به راحتی می توانید از پاورکوئری به جای فرمول نویسی برای مغایرت گیری و ایجاد لیست های مغایرت استفاده کنید.
پاور کوئری (power query) چیست؟
ابزار و افزونه پاور کوئری بخشی از ابزار سلف سرویس هوش تجاری (BI) در اکسل و سایر ابزار مانند پاور BI است. به زبان ساده افزونه پاور کوئری کار شما برای استفاده از داده ها را آسان خواهد کرد و هر نوع داده ای را برای شما آماده استفاده می کند!! ابزار پاور کوئری رابط کاربری بصری و سازگاری را برای شما فراهم می کند تا بدون دانش برنامه نویسی و استفاده از زبان های برنامه نویسی مانند SQL، به استخراج، ترکیب و بهبود داده های خود از منابع داده ای متنوع مانند منابع داده ای رابطه ای، ساختار یافته، نیمه ساختار ساخته، OData، وب، هدوپ (Hadoop) و سایر منابع دسترسی داشته باشید. یعنی با استفاده از پاور کوئری شما در دسترسی به داده ها در اکسل و کار بر روی داده ها هیچ محدودیتی نخواهید داشت!
با استفاده از این ابزار شما بدون دانش برنامه نویسی می توانید به راحتی مانند یک برنامه نویس و مهندس کامپیوتر با داده ها کار کنید. به راحتی می توانید کوئری های خودتان را بسازید و آن ها را با سایرین به اشتراک بگذارید. سایر کابران به راحتی می توانند به این کوئری ها دسترسی داشته باشند و از آن ها استفاده کنند.
با پاور کوئری چه کاری هایی می توانیم انجام دهیم؟
- به منابع داده ای مختلف و متنوعی متصل شوید. (از یک فایل متنی ساده، فایل های اکسل تا sql server و سایر دیتابیس ها)
- ادغام داده ها از منابع مختلف و آماده سازی آن ها جهت انجام تجزیه و تحلیل های متنوع و استفاده در سایر ابزار مانند پاور پیوت (Power Pivot)، پاور ویو (Power View) و پیوت تیبل یا جدول های محوری (Pivot Table).
- ایجاد نمایش های سفارشی بر روی داده ها
- فراخوانی داده ها در قابل JSON جهت مصورسازی داده های بزرگ
- پاک سازی و مرتب سازی داده ها
- آماده سازی داده ها از چندین فایل log (فایل های تاریخی یا داده های ثبت شده توسط سیستم ها)
- دریافت اطلاعات با استفاده از api ها از سیستم های مختلف مانند فیس بوک و …
- انتقال داده های موجود در چند پوشه و سایر منابع داده ای به مدل های داده ای و ابزار پاور پیوت
- اشتراک گذاری کوئری ها برای استفاده توسط سایرین
- و هزاران قابلیت دیگر
اما برای آشنایی کامل تر با ابزار جادویی پاور کوئری و آشنایی با نحوه استفاده از این ابزار و کارکردهای مهم آن، پیشنهاد میکنیم دو ویدئوی کوتاه و فوق العاده پیوست را نیز مشاهده کنید.
پاور کوئری به چه منابع داده ای می تواند متصل شود؟
- صفحات وب
- فایل های اکسل و CSV
- فایل های XML
- فایل های متنی (txt)
- پوشه ها
- پایگاه داده Access
- پایگاه داده SQL Server
- پایگاه داده Microsoft Azure SQL
- پایگاه داده Oracle
- پایگاه داده IBM DB2
- پایگاه داده MySQL
- پایگاه داده PostgreSQL
- پایگاه داده Sybase
- پایگاه داده Teradata
- SharePoint List
- OData feed
- Microsoft Azure Marketplace
- Hadoop File (HDFS)
- Microsoft Azure HDInsight
- Microsoft Azure Table Storage
- Active Directory
- Microsoft Exchange
پاور کوئری یک ابزار ETL است، ابزار ETL چیست؟
قبل از شروع کار با پاور کوئری و مطالعه آموزش های پاور کوئری در اکسل باید با این ابزار بیشتر آشنا شوید و کاربردها و اهداف آن را بدانید. بعد از آشنایی کار شما در فهم و آموزش این ابزار بهتر خواهد شد و با سرعت بیشتری پیش خواهید رفت. قبل از هر چیز ETL مخفف Extract Transform and Load است که به معنای استخراج (extract)، پالایش (transform) و بارگذاری (load) داده ها است. ETL فرایندی است که در آن اطلاعات از یک یا چند منبع مختلف جمع آوری، پالایش و در نهایت بارگذاری می‌شود.
با استفاده از ETL عملیات استخراج داده ها (extract) از یک یا چند منبع داده ای و تبدیل آن به داده مورد نیاز ما (transform) از طریق پاکسازی و تبدیل داده ها و ایجاد فیلدهای محاسباتی و سپس در اختیار بودن داده ها (Load) به ساده ترین شکل ممکن انجام می شود. در بسیاری از موارد به جای اینکه نیاز به نوشتن کوئری های پیچیده داشته باشید، می توانید با گزینه هایی که در اختیار دارید به ادغام و تبدیل داده های خود بپردازید.
پس از مدل سازی، تحلیل و طراحی داده، نوبت به بارگذاری داده‌ها در آن می شود.
استخراج (extract) داده ها در پاور کوئری
استخراج داده‌ها تابع قوانین خاصی است که در زیر به آنها اشاره می کنیم:
۱- شناسایی داده هایی که در مدل می خواهیم بارگذاری کنیم
۲- قرار دادن داده ها در یک محیط واسط برای انجام عملیات پاکسازی و تبدیل (بانک اطلاعاتی بین داده های اولیه و مدل داده ای جدید)
چرا باید داده را در یک محیط واسط قرار دهیم و مستقیم در مدل خود نمی آوریم؟ بسیار ساده است، چون در اکثر موارد داده های منابع ابتدایی نیاز به تغییر دارند و شما معمولا نمی توانید در داده های اولیه و منابع اصلی تغییر ایجاد کنید. به همین دلیل تمام داده های اولیه را در یک محیط واسط قرار می دهیم و تغییر مورد نظر خود را انجام میدیم. در اکسل این کار در ابزار پاور کوئری یا افزونه پاور کوئری (power query) انجام می شود.
پالایش (transform) کردن داده ها در پاور کوئری
مهم ترین مرحله در کار با پاور کوئری پالایش یا تبدیل داده ها برای استفاده در مدل های داده ای است. محیط پاور کوئری مشابه اکسل است و کاربران اکسل به راحتی می توانند یادگیری و کار با پاور کوئری را یاد بگیرند و در کارهای خود از این ابزار فوق العاده استفاده کنند. پالایش داده ها شامل مراحل زیر است:
بررسی کیفیت داده‌ها (Verify data quality) در پاور کوئری
کیفیت داده‌ها از طریق سوال های زیر تعیین می شود.
آیا داده‌های ما کامل هستند (نیازهای ما را پوشش می دهند.)؟
گفتیم که قبل از شروع کار با پاور کوئری نیازهای داده ای خود را شناسایی میکنیم و مدل های داده خود را طراحی می کنیم. در این مرحله باید تعیین کنیم که آیا می توانیم همه داده های مورد نیاز خود را اختیار داشته باشیم.
داده‌ها صحیح هستند یا دارای اشتباه هستند؟ دلیل اشتباه بودن داده ها چیست؟
فرض کنید در سیستم اسامی تکراری وجود دارد یا از حرف «ي» عربی استفاده شده باشد و در منبع دیگر از «ی» فارسی استفاده شده باشد. شما باید کارهای یکسان سازی داده ها را نیز انجام دهید. به طور مثال اگر در یک جدول نام دپارتمان «منابع انساني» با ي عربی نوشته شده است و در جدول دیگری با ی فارسی، نیاز است این اشتباهات را تصحیح کنیم. گاهی اوقات اشتباهات انسانی وجود دارد، مثلا در یک جدول کلمه «منا بع» با فاصله نوشته شده است. باید تمامی این موارد را بررسی کنیم تا درآینده با مشکل رو به رو نشویم. (البته بسیاری از مشکلات در مرحله اجرا نمایان می شوند و باید به مراحل قبل بازگشت و اصلاح شوند.)
آیا مقدارها و داده های گم شده وجود دارد؟
فرض کنید برای یک ماه داده های فروش شما در بین داده ها موجود نیست. در این صورت باید داده ها را تکمیل کنید. یا در پایگاه داده شما جنیست کارمندان مشخص نشده است و …
پاکسازی داده‌ها (Clean data) در پاور کوئری
تمیز کردن داده ها که به عنوان پاکسازی داده ها شناخته شده است، روندی است که طی آن خطاهای موجود در یک مجموعه رکورد، جدول یا پایگاه داده شناسایی می شود و برای بهبود کیفیت داده ها، اصلاحات مورد نیاز صورت می گیرد.
شکل دادن داده‌ها (Construct data) در پاور کوئری
گاهی اوقات نیاز است تغییراتی در دیتای اولیه صورت بگیرد. به صورت مثال اعداد به صورت ۱۷% ذخیره شده اند ولی این دیتا برای ما در محاسبات قابل استفاده نیست و باید به صورت ۰٫۱۷ تبدیل شود. این فرایند در پاور کوئری به سادگی انجام می شود. گاهی اوقات نیاز است ستون جدیدی به داده ها خود اضافه کنیم. مثلا تعداد کالا و قیمت واحد کالا را داریم. برای محاسبه درآمد باید فیلد جدیدی که فرمول (تعداد * قیمت واحد) را محاسبه می کند را ذخیره کنیم. اینها مثال های ساده ای از شکل داده داده بود.
ادغام داده‌ها (Integrate data) در پاور کوئری با استفاده از گزینه merge
شما به راحتی می توانید با استفاده از گزینه merge و استفاده از ستون های مشترک در جدول های مختلف دیتای خود را با هم ادغام کنید و سپس در تحلیل ها استفاده کنید. برای مثال جدول کارمندان که شامل کد پرسنلی، نام و نام خانوادگی و جدول دیگری که شامل کد پرسنلی و آدرس هست را دارید. حالا میخواهید آدرس ها در جدول کارمندان قرار بگیرد. با فیلد مشترک «کد پرسنلی» می توانید این دو جدول را با هم ادغام کنید.
قالب بندی داده‌ها (Format data) در پاور کوئری
تغییر فرمت یا قالب بندی داده ها در پاور کوئری مشابه کاری است که با گزینه cell formatting در اکسل انجام می دهیم. گاهی اوقات نیاز است که جنس داده های خود را مشخص کنیم. گاهی اوقات اعداد در منابع داده ای ما به صورت متن ذخیره شده اند که قابلیت انجام محاسبات در آنها نیست و نیاز است که فرمت این فیلد را تغییر دهیم. شما به راحتی در پاور کوئری می توانید قالب داده های خود را تغییر دهید.
بارگذاری (load) داده ها در پاور کوئری
آخرین کاری که در پاور کوئری یا سایر ابزار ETL انجام می دهیم بارگذاری داده‌های استخراج و پالایش شده از منابع مختلف داده ای است. معمولا پس از بارگذاری تغییراتی در داده ها ایجاد نمی شود و تغییرات در مرحله پالایش انجام می شود. در پاور کوئری به دو صورت دیتا بارگذاری می شود:
۱- بارگذاری دیتای پاور کوئری در شیت اکسل
اگر داده ها را در شیت اکسل بارگذاری کنید دقت داشته باشید که نمی توانید بیش از محدودیت تعداد سطرهای اکسل (حدود یک میلیون سطر) کار بارگذاری را انجام دهید. در این صورت دیتا شما به صورت کامل در فایل اکسل قابل مشاهده است و می توانید دیتای خود را بررسی کنید.
۲- بارگذاری دیتای پاور کوئری به صورت اتصال
در این صورت محدودیت تعداد سطرهای اکسل را نخواهید داشت و میتوانید چندین میلیون سطر اطلاعات را بارگذاری کنید. دقت داشته باشید در این روش دیتا در رم شما بارگذاری می شود و دیتا قابل مشاهده نیست و فقط می توانید در پنجره پاور کوئری آن ها را ببینید. ولی می توانید از این اتصال برای استفاده در پاور پیوت، پاور ویو، پیوت تیبل استفاده کنید.
ورود به سایت