نحوه استفاده از پاور کوئری (Power Query) برای ادغام ستونها
پاور کوئری (Power Query) یک موتور آمادهسازی و تبدیل دادهها با رابط کاربری گرافیکی (GUI) است. این موتور میتواند دادهها را از منابع مختلف دریافت کند و دارای یک رابط ویرایش قدرتمند است که امکان پردازش استخراج، تبدیل و بارگذاری (ETL) دادهها را فراهم میکند.
از آنجایی که این موتور در بسیاری از سرویسها و محصولات موجود است، مقصد ذخیرهسازی دادهها معمولاً به جایی که پاور کوئری استفاده شده است بستگی دارد. در این مقاله، نحوه استفاده از پاور کوئری برای ادغام ستونها در اکسل را یاد خواهیم گرفت.
ادغام ستونها
پاور کوئری به ما این امکان را میدهد که دو یا چند ستون از نوع داده متنی را در کوئری خود ادغام کنیم. میتوانیم ستونها را با هم ترکیب کنیم تا آنها را با یک ستون جایگزین کنیم یا یک ستون جدید در مجاورت ستونهای ادغام شده خود ایجاد کنیم. ایدهآل این است که یک ستون جدید ایجاد کنیم و ستونهای اصلی را نگه داریم تا از هرگونه خطای احتمالی جلوگیری شود.
کاربران معمولاً میپرسند: “چگونه ستونها را در پاور کوئری ترکیب کنم؟” این موضوع در این مقاله بیشتر مورد بحث قرار خواهد گرفت.
ادغام ستونها برای جایگزینی ستونهای موجود
در این مثال از ویژگی ادغام ستونها در پاور کوئری مایکروسافت اکسل استفاده خواهیم کرد. پس از ادغام ستونها، ستونهای انتخاب شده به یک ستون واحد به نام ادغام شده تبدیل میشوند. اگر دادههای ادغام نشده هنوز مورد نیاز باشند، میتوانید ستونهای اصلی را کپی کنید. ستونهای اصلی دیگر در دسترس نخواهند بود. به عنوان مثال، در جدول زیر، OrderID و CustomerID را ادغام خواهیم کرد.
- یک پرسوجوی بارگذاری شده قبلی را از پاور کوئری ادیتور پیدا کنید، یک سلول در دادهها انتخاب کنید و Query > Edit را انتخاب کنید. این لینک نحوه ایجاد، بارگذاری یا ویرایش یک پرسوجو در اکسل را آموزش میدهد.
- ستونها باید از نوع داده Text باشند. در غیر این صورت، ستون را انتخاب کنید و سپس روی Transform > Data Type > Text کلیک کنید.
- ستونها را با فشار دادن Shift+Click یا CTRL+Click روی هر ستون بعدی که نیاز به ادغام دارد، انتخاب کنید. ترتیب انتخاب، ترتیب مقادیر ادغام شده را تعیین میکند.
- روی Transform > Merge Columns کلیک کنید.
- در کادر محاورهای Merge Columns، یک جداکننده برای درج بین هر ستون ادغام شده مشخص کنید. همچنین میتوانیم از مقادیر جداکننده از پیش تعریفشده موجود انتخاب کنیم یا مقادیر جداکننده سفارشی را مشخص کنیم.
- روی تأیید کلیک کنید. برای یادگیری نحوه تغییر نام ستون، کلیک کنید. نتیجه به صورت زیر نشان داده شده است:
ادغام ستونها برای ایجاد ستونهای جدید
در این مثال، یک ستون سفارشی را در جدول پرسوجو وارد میکنیم و از یک ستون سفارشی برای الحاق ستونهای متنی استفاده میکنیم. ستونهای ادغامشده همچنان به همراه ستون ادغامشده جدید در جدول پرسوجو در دسترس خواهند بود.
بیایید ببینیم چگونه میتوانیم OrderID و CustomerID را که با یک کاراکتر فاصله از هم جدا شدهاند، ادغام کنیم:
- یک کوئری که قبلاً از Power Query Editor بارگذاری شده است را پیدا کنید، یک سلول را در دادهها انتخاب کنید و Query > Edit را انتخاب کنید. نحوه ایجاد، بارگذاری یا ویرایش یک کوئری در اکسل را در این لینک بیاموزید.
- ستونها باید از نوع داده Text باشند. در غیر این صورت، ستون را انتخاب کرده و روی Transform > Data Type > Text کلیک کنید.
- روی Add Column > Custom Column کلیک کنید. کادر محاورهای Custom Column ظاهر میشود.
- اولین ستون را در لیست Available Columns انتخاب کرده و روی Insert کلیک کنید. همچنین میتوانیم روی اولین ستون دوبار کلیک کنیم. ستون بلافاصله پس از علامت مساوی (=) به کادر Custom Column Formula اضافه میشود. در کادر Custom Column Formula کلیدهای Ctrl + Space را فشار دهید تا ستونها و سایر عناصر فرمول نمایش داده شوند.
- بعد از اولین ستون درج شده، یک علامت & (&) در کادر Custom Column Formula وارد کنید. علامت & (&) عملگر AND است که مقادیر را در Power Query به همان روشی که در Excel ترکیب میکند، ترکیب میکند. جداکنندههای اضافی را نیز میتوان مشخص کرد، به عنوان مثال، با وارد کردن ” ” بعد از علامت &، از یک کاراکتر فاصله استفاده کنید.
- یک علامت & دیگر بعد از کاراکتر فاصله در کادر فرمول ستون سفارشی وارد کنید.
- ستون دوم را در لیست ستونهای موجود انتخاب کنید و روی درج کلیک کنید. همچنین میتوانیم روی ستون دوم دوبار کلیک کنیم.
- روی OK کلیک کنید. نتیجه به صورت زیر خواهد بود:
تبدیل دادههای خود با Power Query
Power Query (که در اکسل با نام Get & Transform data نیز شناخته میشود) به ما امکان میدهد دادههای خارجی را وارد یا به آنها متصل کنیم و آنها را شکل دهیم. به عنوان مثال، میتوان نوع داده را تغییر داد، یک ستون را حذف کرد یا جداول داده را بر اساس نیاز ادغام کرد. همچنین میتوانیم کوئری خود را در اکسل بارگذاری کنیم تا نمودارها و گزارشها را ایجاد کنیم و دادهها را به صورت دورهای بهروزرسانی کنیم تا بهروزرسانی شوند. Power Query در اکسل برای ویندوز، مک و آنلاین در دسترس است.
پاور کوئری همچنین بخشی از Power BI است و برای انجام تبدیل ETL و بارگذاری دادهها استفاده میشود. Power Query همچنین ویرایشگر Query نامیده میشود. میتوانیم از ویرایشگر Power Query برای اتصال منابع داده، تغییر بر اساس نیاز و شکلدهی و تبدیل دادهها استفاده کنیم. میتوانید بعداً آن مدل را در Power BI Desktop (یا سرویس وب) بارگذاری کنید. این ویرایشگر همان ردیفهای ادغام Power Query را بر اساس ستون و جداول ادغام Power Query Excel را با ویژگیهای ستونی متفاوت ارائه میدهد.
برای خرید لایسنس نرم افزار Power BI ، میتوانید از خدمات ما استفاده نموده و درخواست خود را از طریق فرم زیر ثبت نمایید.
میتوانید پاور بی آی دسکتاپ رایگان را دانلود کنید : Power BI desktop download