برای دیدن تمام آموزش های پاور بی آی روی لینک زیر کلیک کنید:
فرض کنید که یک سری دیتای خام فروش را از دو منبع وارد مدل پاور بی آی کرده باشید. برخی از داده ها از فایل .csv بدست آمده اند که به صورت فیزیکی توسط تیم فروش در اکسل ذخیره شده است. سایر داده ها از طریق اتصال به برنامه ریزی منابع سازمانی (ERP مخفف Enterprise Resource Planning) شرکت بارگزاری شده اند. زمانیکه دیتای موجود در Power BI Desktop را بررسی می کنید، متوجه یک سری بی نظمی هایی در داده ها می شوید؛ برخی از داده ها را نیاز ندارید و برخی از داده های مورد نیازتان دارای فرمت اشتباه می باشند.
شما قبل از شروع مراحل مربوط به ایجاد گزارش باید با استفاده از Power Query Editor این داده ها را پاکسازی کنید و به آنها شکل دهید.
کار با Power Query Editor را شروع کنید
به منظور شکل دادن به داده ها، با انتخاب گزینه Transform data در زبانه Home Power BI Desktop، گزینه Power Query Editor را باز کنید.
در Power Query Editor دیتای انتخاب شده در کوئری در وسط صفحه نمایش داده می شود و پنجره Queries در سمت چپ لیست مربوط به کوئری های (جداول) در دسترس را نشان می دهد.
در حین کار با Power Query Editor، تمامی مراحل مربوط به شکل دهی داده ثبت می شود. بعد از این مرحله هر زمانیکه کوئری به منبع داده ها متصل شود، مراحل سپری شده به صورت اتوماتیک وار اعمال می شود و به همین دلیل داده های شما بر اساس روشی که خودتان مشخص کرده اید، شکل پیدا می کنند. Power Query Editor فقط در یک نما از داده های شما تغییر ایجاد می کند و شما می توانید نسبت به اعمال تغییرات در منبع داده های اصلی خودتان مطمئن شوید. همچنین می توانید یک لیست از مراحل خودتان را در سمت راست صفحه نمایش و در صفحه Query Settings و همراه با خصوصیات کوئری مشاهده کنید.
ریبون Power Query Editor دارای یک سری دکمه هایی است که می توانید برای انتخاب، مشاهده و شکل دادن به داده هایتان از آنها استفاده کنید.
به منظور کسب اطلاعات بیشر در مورد ویژگیها و توابع میتوانید بر روی این قسمت کلیک کنید.
نکته
در Power Query Editor، منوهایی که در راست کلیک نمایش داده می شود و همچنین تب Transform در ریبون بسیاری از گزینه های مشابه را نمایش می دهند.
مشخص کردن سرتیترها و نام ستون ها
اولین قدم در شکل دهی داده های اولیه عبارت از مشخص کردن سرتیترها و نام های ستون در دیتا و بعد از آن ارزیابی محل قرارگیری آنها می باشد که به منظور کسب اطمینان در مورد قرار گرفتن آنها در جای مناسب صورت می گیرد.
در تصویر زیر داده های منبعی در فایل csv برای SalesTarget (نمونه ارائه نشده است) دارای یک هدف طبقه بندی شده بر اساس محصولات و یک زیرمجموعه بر حسب ماه است که هر دو در ستون ها سازماندهی شده اند.
با این وجود متوجه میشوید که دیتا مطابق انتظار وارد نشده است.
در حال حاضر خواندن دیتا به سختی امکانپذیر می باشد. یک مشکلی در این وضعیت از داده ها ایجاد می شود چرا که سرتیتر ستون ها در سطرهای متفاوت (که دور آن خط قرمز کشیده شده است) قرار دارند و برخی از ستون ها دارای عناوین غیرتوصیفی (همانند Column1، Column2 و سایر موارد) می باشند.
شما بعد از مشخص کردن محل قرارگیری سرتیترها و اسم های ستون ها می توانید یک سری تغییراتی را به منظور سازماندهی مجدد داده ها انجام دهید.
بالاتر آوردن سرتیترها
زمانیکه در Power BI Desktop یک جدولی ایجاد می شود، Power Query فرض می کند که تمامی دیتاها مربوط به سطرهای جدول می باشند. با این وجود یک منبع داده ای می تواند دارای یک سطر اولیه باشد که شامل نام ستون ها است و مربوط به موارد اتفاق افتاده در مثال SalesTarget قبلی می باشد. شما بمنظور رفع این بی دقتی باید سطر اول جدول را بالاتر بیاورید و در سرتیتر ستون ها قرار دهید.
روش های بالا آوردن سر تیترها
روش اول
از طریق انتخاب کردن گزینه Use First as Headers در تب Home
روش دوم
از طریق انتخاب دکمه کشویی موجود در کنار Column1 و انتخاب Use First Row as Headers.
تصویر زیر نحوه تاثیر ویژگی Use First Row as Headers را بر روی دیتا نشان می دهد.
نامگذاری مجدد ستون ها
مرحله بعدی در شکل دادن به داده ها عبارت از بررسی کردن سرتیتر ستون ها می باشد. ممکن است متوجه شوید که یک یا چند سطر دارای سرتیترهای اشتباهی هستند، یک سرتیتر دارای غلط املایی می باشد و یا اصول مربوط به نامگذاری سرتیتر سازگار یا مطلوب کاربران نیستند.
با مراجعه به تصویر قبل می توانید تاثیر ویژگی Use First Row as Headers را مشاهده کنید. توجه داشته باشید که ستون حاوی زیر گروه دیتای Name در حال حاضر دارای سرتیتیر ستونی به اسم Month میباشد. این سرتیتر ستون نادرست است، به همین دلیل اسم آن باید تغییر کند.
شما به دو روش می توانید سرتیتر ستون ها را تغییر دهید. روش اول عبارت از راست کلیک کردن برروی سرتیتر، انتخاب Rename، ویرایش اسم و بعد از آن زدن دکمه Enter می باشد. روش دوم عبارت از دبل کلیک کردن بر روی سرتیتر ستون و نوشتن اسم صحیح می باشد.
همچنین شما از طریق حذف کردن (رد شدن) از دو ردیف اول و سپس نامگذاری صحیح ستون ها می توانید این کار را انجام دهید.
حذف سطرهای بالا
ممکن است در حین شکل دادن به دیتای خودتان نیاز باشد برخی از سطرهای بالا را حذف کنید؛ برای مثال در صورت خالی بودن سطرهای بالا یا در صورتیکه این سطرها داده هایی داشته باشند که در گزارشات به آنها نیاز وجود ندارد، نیاز خواهید داشت که سطرهای بالا را حذف کنید.
در صورتیکه مثال SalesTarget را ادامه دهید متوجه می شوید که سطر اول خالی است (هیچ نوع داده ای ندارد) و دیتای موجود در سطر دوم مورد نیاز نمی باشد.
به منظور حذف کردن سطرهای اضافی، در تب Home، Remove Row و بعد از آن Remove Top Rows را انتخاب کنید.
حذف ستون ها
یکی از مراحل مهم مربوط به فرآیند شکل دهی به داده ها عبارت از حذف کردن ستون های غیرضروری میباشد. هر چقدر که سریعتر ستون ها را حذف کنید، بهتر است. یک روش برای حذف ستون ها عبارت از محدود کردن ستون ها در حین دریافت دیتا از منبع داده ای می باشد. برای مثال در صورتیکه شما با استفاده از SQL دیتا را از یک دیتا بیس رابطه ای دریافت کنید، با استفاده از یک لیست ستونی در عبارت SELECT میتوانید ستون ها را محدود کنید.
حذف کردن ستون ها در مراحل اول بهتر از حذف کردن آنها در مراحل بعدی می باشد، بخصوص در مواردی که شما یک رابطه ای را مابین جداول ایجاد می کنید. حذف ستون های غیرضروری به شما کمک خواهد کرد تا بر روی دیتایی متمرکز شوید که به آن نیاز دارید و منجر به بهبود عملکرد مجموعه دیتا و گزارشات Power BI Desktop شما می شوند.
هر یک از ستون ها را بررسی کنید و از خودتان این سوال را بپرسید که آیا واقعا به دیتای موجود در این ستون نیاز دارید. در صورتیکه هیچ برنامه ای برای استفاده از این دیتا در گزارش نداشته باشید، این ستون هیچ ارزش اضافه ای برای مدل دیتای شما نخواهد داشت. بنابراین این ستون باید حذف شود. در صورتیکه نیازهای شما در مراحل بعدی تغییر کند، هر موقعی که بخواهید می توانید ستون های حذف شده را اضافه کنید.
دو روش حذف ستون ها
روش اول
در روش اول ابتدا باید ستونهایی که میخواهید حذف کنید را انتخاب نمایید و بعد از آن در تب Home، گزینه Remove Columns را انتخاب کنید.
روش دوم
در روش دوم باید سطرهایی را انتخاب کنید که می خواهید نگه دارید و بعد از آن از تب Home گزینه Remove Columns و سپس گزینه Remove Other Columns را انتخاب کنید.
Unpivot کردن ستون ها
Unpivot کردن دیتا یکی از ویژگی های مفید پاور بی آی می باشد. صرف نظر از هر نوع منبع دیتایی که دارید، می توانید از این روش استفاده کنید ولی این روش بیشتر در حین وارد کردن دیتا از اکسل مورد استفاده قرار داده می شود. مثال زیر یک نمونه از سند اکسل می باشد که مربوط به دیتای فروش است.
هر چند که ممکن است دیتا در ابتدا معنادار باشد، ولی بدست آوردن فروش کل ترکیبی از سال های 2018 و 2019 به راحتی امکانپذیر نباشد. بنابراین هدف شما عبارت از استفاده کردن از این دیتا در سه ستون از پاور بی آی می باشد که این ستون ها عبارت از Month، Year و SalesAmount می باشند.
زمانیکه شما دیتا وارد Power Query می کنید، تصویری مشابه مورد پایین را خواهید دید.
بعد از آن باید اسم اولین ستون را Month قرار دهید. عنوان این ستون نامناسب بوده است چرا که این سرتیتر در فایل اکسل عبارت از ستون های 2018 و 2019 بوده است. ستون ها 2018 و 2019 را مشخص کنید، تب Transform را در Power Query و بعد از آن Unpivot را انتخاب کنید.
شما می توانید عنوان ستون Attribute را به Year و عنوان ستون Value را به SalesAmount تغییر دهید.
Unpivot در مرحله بعد منجر به ساده سازی فرآیند ایجاد سنجش های DAX در دیتا خواهد شد. شما با کامل کردن این پروسه یک روش ساده ای را برای برش دادن داده ها به ستون های Year و Month ایجاد کرده اید.
Pivot کردن ستون ها
در صورتیکه داده هایی که می خواهید شکل بدهید، عبارت از داده های بی معنا و گسترده باشند (به عبارت دیگر جزئیات زیادی داشته باشند، ولی سازماندهی شده یا گروه بندی شده نباشند)، نبود ساختار می تواند توانایی شما را برای شناسایی الگوهای موجود در داده ها کاهش دهد.
شما برای تبدیل کردن این داده ها به یک جدول دارای مقدار تجمیعی از هر یک از مقادیر منحصر بفرد در یک ستون می توانید از ویژگی Pivot Column استفاده کنید. برای مثال با استفاده از این ویژگی و همچنین یک سری از توابع ریاضی موجود همانند Count، Minimum، Maximum، Median، Average یا Sum می توانید داده ها را به صورت خلاصه بیان کنید.
در مثال مربوط به SalesTarget می توانید ستون ها را Pivot کنید تا تعداد زیرگروه های محصولاتی را در هر یک از گروه های محصولاتی مشخص کنید.
در تب Transform، گزینه Transform و بعد از آن Pivot Columns را انتخاب کنید.
در پنجره Pivot Column نمایش داده شده، یک ستونی را از لیست Values Column (برای مثال Subcategory name) انتخاب کنید. گزینه های پیشرفته را باز کنید و یک گزینه ای را از لیست Aggregate Value Function (همانند Count (All)) انتخاب کنید و بعد از آن بر روی Ok کلیک کنید.
شکل زیر نحوه تاثیر ویژگی Pivot Column را بر روی روش سازماندهی داده ها نشان می دهد.
Power Query Editor تمامی کارهایی که برای شکل دادن به داده ها انجام داده اید را نشان می دهد و همچنین لیست مربوط به تمامی مراحل در صفحه Query Settings نشان داده شده است. در صورتیکه تمامی تغییرات مورد نیاز را اعمال کرده باشید، به منظور بستن Power Query Editor گزینه Close & Apply را انتخاب کنید و تغییرات خودتان را بر روی مدل دیتا اعمال نمایید. با این وجود قبل از انتخاب Close & Apply، می توانید اقدامات بعدی را برای پاک کردن و تبدیل دیتا در Power Query Editor انجام دهید. این مراحل اضافی در بخش های بعدی توضیح داده خواهند شد.
ممنون که تا اینجا با ما همراه بودید.
در جلسه بعدی به ساده سازی ساختار دیتا را خواهیم آموخت.