Power Query یک تغییر دهنده بازی از نظر میزان زمانی است که من ذخیره می کنم ، اطمینان از داده های وارداتی برای تجزیه و تحلیل در اکسل آماده است. اگر در این ابزار تازه کار هستید ، دانستن چند دستور که می توانند مهارت های تحول داده شما را به سطح بعدی برسانند ، مفید است. حتی اگر تا به حال از Power Query نشنیده اید ، هنوز هم مفید است که به این مثالها نگاه کنید تا ببینید آیا انگیزه ای برای یادگیری نحوه استفاده از پرس و جو از قدرت دارید.
Power Query یک تغییر دهنده بازی از نظر میزان زمانی است که من ذخیره می کنم ، اطمینان از داده های وارداتی برای تجزیه و تحلیل در اکسل آماده است. اگر در این ابزار تازه کار هستید ، دانستن چند دستور که می توانند مهارت های تحول داده شما را به سطح بعدی برسانند ، مفید است. حتی اگر تا به حال از Power Query نشنیده اید ، هنوز هم مفید است که به این مثالها نگاه کنید تا ببینید آیا انگیزه ای برای یادگیری نحوه استفاده از پرس و جو از قدرت دارید.
تمیز کردن داده ها در اکسل ، به ویژه برای چندین کتاب کار ، یکی از آزار دهنده ترین وظایفی است که هر کاربر اکسل باید با آن مقابله کند. اما با پرس و جو قدرت ، این کار حتی می تواند تا حدودی لذت بخش شود.
سلولهای تقسیم کننده را تقسیم می کنند
به راحتی داده ها را به ستون ها تقسیم کنید
یک مشکل رایج که هنگام تمیز کردن یک برگه اکسل به آن رسیدید ، زمانی است که داده هایی که باید به دو ستون تقسیم شوند به یک ترکیب می شوند. به تصویر زیر نگاهی بیندازید ، جایی که ستون سفارش ID نیز نام مشتری را دارد.
ما قصد داریم این ستون را با استفاده از تعیین کننده ، یک شخصیت یا نماد (مانند کاما ، فضا یا هیفن) که داده ها را در یک سلول جدا می کند ، در پرس و جو قدرت تقسیم کنیم. تعیین کننده Hyphen یا Dash (-) است که شناسه سفارش را از نام مشتری در مثال بالا جدا می کند.
بیایید فرض کنیم که این برگه را در جایی در رایانه خود ذخیره کرده اید. برای وارد کردن آن ، برگه Data را انتخاب کرده و روی دریافت داده -> از File -> From Excel در گروه فرمان Get & Transform Data در روبان کلیک کنید.
از آنجا ، به محل پرونده بروید ، آن را انتخاب کنید و روی واردات کلیک کنید. در زیر گزینه های نمایش در سمت چپ ، برگه را با داده ها انتخاب کرده و روی تبدیل داده ها کلیک کنید. به خاطر داشته باشید که این فقط یک راه برای وارد کردن داده ها به ویرایشگر است – بسته به اینکه داده ها را از کجا می گیرید ، گزینه ها متفاوت خواهد بود.
در ویرایشگر Power Query ، ستون را برجسته کنید (شناسه سفارش در مثال ما) و روی Split Column -> توسط Delimiter در گروه فرمان Transform از برگه Home کلیک کنید. در انتخاب یا Enter Delimiter Dropdown ، Custom را انتخاب کنید. به طور مستقیم در زیر آن ، تعیین کننده ای را که می خواهید استفاده کنید وارد کنید و OK را کلیک کنید.
پس از تقسیم ستون ، ستون های جدید را به چیزی تغییر دهید که معنی دارد. من به ترتیب به آنها تغییر نام دادم تا شناسه و نام مشتری را سفارش دهند. پس از آن ، روی Close & Load در گروه Close Command کلیک کنید.
تاریخ های تفریق
تفاوت های دقیق تاریخ را بدست آورید
ادامه با جدول مثال در بالا ، ما همچنین می توانیم محاسبات مختلفی را در ستون های ویرایشگر Power Query انجام دهیم. من معمولاً از این فرصت استفاده می کنم تا ستون های اضافی را برای محاسبات خاص اضافه کنم. رایج ترین موردی که من انجام می دهم این است که تفاوت بین خرما را پیدا کنید.
در اینجا ، من می خواهم دریابم که چند روز سفارشات تحویل داده شده است ، بنابراین می خواهم تاریخ سفارش را از تاریخ تحویل کم کنم. برای انجام این کار ، ستون تاریخ تحویل را انتخاب کنید ، CTRL را فشار داده و سپس تاریخ سفارش را انتخاب کنید (ترتیب انتخاب را در نظر داشته باشید). در مرحله بعد ، برگه Add Column را انتخاب کنید و Date را کلیک کنید -> روزهای SUFTRACT را در گروه فرمان Date & Time روبان.
ستون جدید دارای یک نام عمومی مانند تفریق خواهد بود. می توانید روی آن دو بار کلیک کرده و آن را به چیزی معنی دار تر تغییر دهید. از آنجا می توانید برای تجزیه و تحلیل داده های خود ، برگه را در اکسل ببندید و بارگذاری کنید.
فیلتر برای خطاها
اطمینان حاصل کنید که فقط ردیف های آسیب دیده را می بینید
Power Query راهی آسان برای فیلتر کردن ندارد به طوری که شما فقط ردیف هایی با خطا نشان می دهید. اگر در پرونده ای با صدها ردیف یا بیشتر باشند ، می توانند سردرد باشند.
فرض کنید می دانید که ستون تاریخ سفارش دارای خطا است و می خواهید قبل از بارگیری داده ها در اکسل ، آنها را به صورت دستی حل کنید. شما می توانید یک فیلتر ایجاد کنید تا فقط ردیف های آسیب دیده را نشان دهید.
برگه Add Column را انتخاب کرده و روی ستون سفارشی در گروه فرمان عمومی روبان کلیک کنید. در منطقه متن ستون سفارشی ، فرمول زیر را وارد کرده و بر روی OK کلیک کنید:
try [Order Date]
Power Query یک ستون جدید به نام سفارشی اضافه می کند ، و در صورت عدم خطا یا نادرست ، ردیف ها یا ارزش واقعی دارند. اکنون ، موارد زیر را انجام دهید:
- روی نماد فلش در سمت چپ ستون سفارشی کلیک کنید
- همه چیز را به جز خطایی که وجود دارد ، بردارید.
- OK را کلیک کنید
- دوباره روی نماد پیکان کنار ستون سفارشی کلیک کنید.
- کاذب را باز کنید.
- OK را کلیک کنید
اکنون فقط ردیف هایی را با خطاهای برای بازرسی مشاهده خواهید کرد. اگر ردیف های آسیب دیده مهم نیستند ، سریعترین راه برای مقابله با آنها فقط کلیک راست بر روی ستون و انتخاب خطاهای حذف در منو است. این ردیف ها را حذف می کند ، بنابراین با دقت از آن استفاده کنید.
اگر مهم هستند ، روی ستون کلیک راست کرده و خطاهای جایگزین را انتخاب کنید. اگر همه آنها نیاز به یک مقدار یکسان دارند ، آن را در کادر متن وارد کرده و روی OK کلیک کنید. اگر اینطور نیست ، و شما هنوز هم دوست دارید آنها را نگه دارید اما خطاها را از بین ببرید ، مقداری مانند تهی اختصاص دهید تا خطاها برطرف شوند.
همچنین می توانید به منبع برگردید ، خطاها را اصلاح کنید و پس از آن داده ها را دوباره وارد کنید.
پس از آن ، مراحل ایجاد فیلتر را در بخش مراحل کاربردی پانل تنظیمات پرس و جو در سمت راست حذف کنید. برای من ، مراحل به صورت سفارشی 1 ، گسترش سفارشی 1 و ردیف های فیلتر شده اضافه شد. فقط کافی است روی نماد X در کنار آنها کلیک کنید و روی حذف در پاپ آپ کلیک کنید.
داده های خود را از بین ببرید
تجزیه و تحلیل داده های کمک
داده های محوری همان چیزی است که در قالب گسترده ای ساخته شده است و مقادیر آن در ستون های مختلف پخش می شوند. این می تواند تجزیه و تحلیل داده ها را هنگام استفاده از چیزی مانند جدول محوری در اکسل دشوار کند.
نگاهی به تصویر جدول زیر بیندازید که داده های فروش را در سه ایالت نشان می دهد. از آنجا که ماه ها یک گروه هستند ، آنها واقعاً باید به جای ستون ها در ردیف باشند تا به تجزیه و تحلیل داده ها کمک کنند.
برای رفع این مشکل ، ما باید داده ها را از بین ببریم ، به این معنی که باید آن را به یک قالب بلند تغییر شکل دهد. در اصل ، ایالت ها ، ماه ها و فروش ها باید در ردیف های جداگانه باشند.
انجام این کار به صورت دستی شامل کپی و چسباندن زیادی ، تراز دستی ارزش ها و اطمینان از عدم خطا خواهد بود. اما پرس و جو قدرت می تواند این کار را آسانتر کند.
برای انجام این کار ، برگه Data را انتخاب کنید ، جدول را برجسته کنید و از جدول/دامنه در گروه فرمان Get & Transform Data از روبان کلیک کنید. در ویرایشگر Power Query ، ستون های مورد نظر خود را برای Unpivot با نگه داشتن CTRL و کلیک بر روی آنها (به عنوان مثال ، ژانویه ، فوریه و مارس) انتخاب کنید. روی آنها کلیک راست کرده و ستون های Unpivot را در منو انتخاب کنید.
داده های شما اکنون بدون توجه و آماده برای تجزیه و تحلیل داده های بیشتر است. حتماً این ویژگی ها را به عنوان توصیفی تر تغییر نام دهید. این ماه ها و فروش در مورد ما خواهد بود.
یک پرس و جو ساده مسیری طولانی را طی می کند
این اغراق آمیز نیست که بگوییم پرس و جو قدرت باعث می شود شما منتظر تمیز کردن داده های اکسل باشید. این امر به ویژه صادق است زیرا می توانید یک فرآیند خودکار برای تمیز کردن چندین ورق به همان روش ایجاد کنید. و همانطور که می بینید ، می توانید با چند دستور ساده کارهای زیادی انجام دهید.