شکل دادن داده‌ ها در Power BI

 Power BI این امکان را برای شما فراهم می‌کند تا به انواع متفاوتی از منابع داده متصل شوید و همچنین شکل دادن داده ها را برای شما میسر میسازد که درنتیجه ، میتوانید نمودارهایی تهیه کنید و با دیگر همکاران خود به اشتراک بگذارید. شکل‏ دادن داده‏‌ها به معنای تبدیل داده‌ها، تغییر نام ستون یا جدول، تبدیل متن به عدد، حذف سطرها، قرار دادن اولین سطر به عنوان Header و غیره می‏باشد.

در این آموزش شما یاد می‏گیرید که چگونه :

• با استفاده از Query Editor داده را شکل دهید،
• به یک منبع داده متصل شوید،
• به منبع داده‌ی دیگری متصل شوید،
• منابع داده را با یکدیگر ترکیب کرده و مدل داده‌ای (DataModel) را بسازید، تا در گزارش‌های خود از آن استفاده کنید.

 کوئری استفاده شده در این آموزش بعلاوه نحوه ایجاد کوئری، در اینجا  با جزئیات توضیح داده شده است. بیشتر آیتم‌هایی که شما میتوانید در تب (Transform) انتخاب کنید، توسط راست کلیک کردن روی یک آیتم ( مثل یک ستون) و انتخاب از منویی که ظاهر می‌شود در دسترس است .

شکل دادن به داده:

زمانی که شما یک داده را درQuery Editor شکل میدهید، دستورالعمل‌های گام‌‏به‏‌گامی (که Query Editor برای شما انجام می‏دهد) فراهم می‏کنید. تا زمانی‏که Query Editor بارگذاری (Load) می‏شود، داده را تنظیم کند. منبع اصلی داده تحت تاثیر این عمل قرار می‏گیرد و تنها این نمای داده است که تغییر داده میشود.

تمامی گام‌هایی که شما طی می‏کنید (ازجمله تغییر نام جدول، تغییر نوع داده، یا حذف ستون) توسط Query Editor  ثبت می‏شوند. و زمانی‏که این Query به منابع داده وصل شود گام‌ها انجام می‏شوند تا داده همواره به شیوه‌ای که شما مشخص کرده‌اید شکل داده شود. این فرایند زمانی که از ویژگی Query Editor در Power BI استفاده کنید یا برای هر کسی که از Query به اشتراک گذاشته شما مانند سرویس Power BI استفاده میکند، اتفاق می‌افتد. این گام‌ها به ترتیب در قاب  Query Settings در زیر  Applied Steps قرار داده میشوند.

تصویر زیر قاب Query Settings را نمایش می‏دهد.

برای شروع ابتدا یک ستون را Add  می‏کنیم تا رتبه‌بندی را بر اساس آن محاسبه کنیم و سپس این ستون را با ستون فعلی(Rank) مقایسه می‌کنیم. برای این کار ابتدا Add Column را انتخاب کرده و سپس Custom Column را بزنید تا اینکه به شما اجازه دهد ستونی جدید ایجاد کنید.

در قسمت  Custom Column، در کادر New Column Name عبارت New Rank  را وارد کنید و در قسمت Custom column formula  فرمول زیر را وارد کنید:

([Cost of living] + [Weather] + [Health care quality] + [Crime] + [Tax] + [Culture] + [Senior] + [#”Well-being”]) / 8

توجه کنید که پیامی (‘No syntax errors have been detected.’) در گوشه سمت چپ صفحه‏‌ای که ظاهر می‏شود، وجود دارد . بعد از بررسی پیام، OK را بزنید.

برای حفظ کردن دائمی داده‏‌ها، مقادیر ستون جدید را به اعداد کامل تبدیل کنید. برای این کار کافی است بر روی عنوان ستون، کلیک راست کرده و سپس طبق مراحل زیر عمل کنید.

Change Type > Whole Number

حال اگر بخواهید بیش از یک ستون انتخاب کنید، ابتدا باید ستون مورد نظر را انتخاب کرده و سپس کلید SHIFT را فشرده و ستون‏‌های مجاور را نیز انتخاب کنید. سپس بر روی عنوان Column کلیک راست کنید تا تمامی ستون‏‌ها یکجا تغییر کنند. همچنین می‏توانید از طریق دکمه CTRL ستون‏‏‌هایی را که مجاور نیستند انتخاب کنید.

شما همچنین میتوانید نوع داده‌های موجود در ستون‏‌ها را به صورت دیگری نیز تغییر دهید. در نوارTransform، همان‏ طور که در تصویر مشاهده می‏کنید، با توجه به قسمتی که فلش به آن اشاره کرده است، می‏توانید نوع داده خود را تغییر دهید.

توجه کنید که در پنجره Query Settings ، در قسمت پایین Applied Steps ، گام‏‌های مربوط به شکل دادن داده را نشان می‏دهد. اگر بخواهیم گامی را در فرآیند شکل دادن به داده حذف کنیم، باید از علامت X که در کنار هر یک از گام‏‏‌ها قرار دارد استفاده کنیم.

همان‏طور که در تصویر زیر مشاهده می‏کنید، Applied Steps شامل موارد زیر می‏باشد:

Source:  اتصال به وبسایت

Navigation: انتخاب جدول

Change Type : زمانی که جدول بارگذاری می‏شود، Query Editor به صورت اتوماتیک اعداد ستون را از Text به اعداد کامل تبدیل می‏کند. دو گام نهایی یعنی Add Custom و Change Type1 عملیات قبلی را نمایش میدهند.

قبل از اینکه بخواهیم با این Query کار کنیم نیازداریم تغییراتی را بر آن اعمال کنیم.

• با حذف یک ستون رتبه‏‌بندی را تنظیم کنید: ما براین عقیده هستیم که Cost of living در نتایج ما تاثیرگذار نیستند. اما بعد از اینکه این ستون را حذف کنید متوجه این موضوع میشوید که داده‏‌های باقیمانده تغییری نکرده‌‏اند.
• خطا را باید تصحیح کنید: زمانی که ستونی را حذف میکنید نیاز به سازگاری مجدد محاسبات خود در New Rank می‌باشید که این کار را از طریق تغییر در فرمول باید انجام دهید.
• داده‏‌ها رامرتب کنید: این مرتب سازی براساس ستونهای New Rank و Rank می‏باشد.
• داده‌‏ها راجایگزین کنید: نشان خواهیم داد که چگونه مقادیر خاص را جایگزین کنید و همچنین در اینجا نیاز به افزودن یک Applied Step میباشد.
• نام جدول را تغییر دهید: Table0، توصیف‏‌گر خوبی برای یک جدول نیست و شما میتوانید به راحتی این تغییرنام را انجام دهید.

برای حذف ستون Cost of living ، ابتدا ستون را ابتدا انتخاب کرده و سپس Remove Columns که در تب Home قرار دارد را انتخاب کنید.

توجه داشته باشید که به دلیل ترتیب مراحل، مقادیر New Rank تحت تاثیر این تغییرات قرار نمی‏گیرند. از آنجایی که Query Editors گام‏‌ها را به صورت پی‏‌درپی ثبت میکند، میتوانید هرکدام ازاین گام‏‌ها را بدون اینکه هیچ‏گونه وابستگی به یکدیگر داشته باشند به بالا یا پایین این دنباله منتقل کنید.

باکلیک راست برروی هرکدام ازاین گام‏‌ها Query Editor این امکان را فراهم می‏کند تا شما بتوانید، delete, Rename, Delete Until End, Move Up or Move Down  را انجام دهید. و باید گام آخر یعنی Removed Columns را به بالای گام Added Custom Step منتقل کنید.

و سپس گام Added Custom را انتخاب کنید. توجه داشته باشید که ستون New Rank مقدار  Error را نشان می‏دهد.

که ما می‏توانیم اطلاعاتی را در مورد این خطاها بدست آوریم. بدون کلیک بر روی Error، سلول مورد نظر را انتخاب کنید. شما میتوانید Error ها را در پایین صفحه ببینید.

اگر روی کلمه Error به طور مستقیم کلیک کنید، کوئری، در قاب Query Settings یک Applied Step ایجاد می‏کند.و اطلاعاتی را درمورد خطا نمایش می‏دهدکه ما قصد نداریم این مسیر را طی کنیم. پس Cancel را انتخاب کنید.

برای رفع این خطاها، ستون New Rank را انتخاب کنید سپس همانطور که در شکل نشان داده شده است چک باکس Formula bar را فعال کنید. فرمول، داده‏‌های این ستون را نمایش دهید.

اکنون شما میتوانید پارامتر  Cost of living را از فرمول حذف کنید و عملیات تقسیم را کاهش داده و فرمول را به صورت زیر بنویسید.

Table.AddColumn(#”Removed Columns”, “New Rank”, each ([Weather] + [Health care quality] + [Crime] + [Tax] + [Culture] + [Senior] + [#”Well-being”]) / 7)

سپس Enter را بفشارید تا داده‌ها با مقداری که اصلاح شده است جایگزین شوند. همچنین باید گام Added Custom بدون هیچ خطایی تکمیل شود.

اکنون ما نیاز داریم داده‏‌های خود را بر اساس ستون New Row، مرتب کنیم. برای این کار ابتدا در قسمت Steps Applied، Change Type1 را انتخاب کنید تا اغلب داده‌های اخیر را بدست آورید.سپس روی عنوان New Rank کلیک کرده و Sort Ascending  را انتخاب کنید.

توجه داشته باشید که الان داده‏‌ها بر اساس New Rank مرتب شده‏‌اند. با این حال اگر نگاهی به ستون  Rank بیاندازید می‏بینید که داده‌‏ها به درستی مرتب نشده‏‌اند. برای رفع این مشکل ستون New Rank  را انتخاب کنید و فرمول زیر را در Formula Bar  وارد کنید

= Table.Sort(#”Changed Type1″,{{“New Rank”, Order.Ascending},{“Rank”, Order.Ascending}})

و سپس enter  را فشار دهید. حال باید تمامی سطرها بر اساس ستون Rank و New Rank رتبه‌بندی شده باشند. علاوه بر این شما میتوانید در هرجایی از لیست، Applied Step را انتخاب کنید و شکل دادن به داده را در این دنباله(sequence) انجام دهید.

Query Editor، به صورت اتوماتیک یک گام جدید را بعد از مرحله انتخاب شده اضافه می‏کند. برای انجام این کار باید به روش زیر عمل کنید:

ابتدا قبل از اضافه کردن ستون Custom ،Applied Step را انتخاب کنی.د که این گام Remove Column  می‌باشد.

در اینجا ما می‏خواهیم مقدار رتبه‏‌بندی weather را برای ایالت  Arizona تغییر دهیم. حال در ستون  weather بر روی سلول مربوط به Arizona کلیک راست کرده و  از منویی که ظاهر شده است، Replace Value را انتخاب می‏کنیم.

از آنجایی که ما می‏خواهیم یک گام را اضافه کنیم، Query Editors هشدار میدهد که ممکن است این اقدام بر روی مراحل بعدی اثر بگذارد. به همین دلیل باید با دقت بالایی عمل کرد. و Insert را میفشاریم.

مقدار این سلول را به 51 تغییر دهید. با این کار داده مربوط به Arizona به 51 تغییر می‏یابد.

زمانی که یک Applied Step جدیدی را ایجاد می‏کنید Query editor بر اساس اقدام انجام شده برای این گام نامی را انتخاب می‏کند( در این مورد  Replace Valued ). حال زمانی که بیش از یک مرحله با نام‏‌های مشابه در Query خود داشته باشید، یک عدد به انتهای هرکدام از نام‏‌ها اضافه می‏شود تا به راحتی بتوان آنها را از یکدیگر تشخیص داد.

حال آخرین بخش از Applied Step یعنی همان Stored Row را انتخاب کنید. توجه کنید که بعد از این تغییرات، داده‏‌ها با توجه به تغییر مقدار رتبه Arizona، تغییر یافته‏‌اند. این بخاطر این است که گام replaced values درست قبل از custom steps  اضافه شده است.

درنهایت، می‏خواهیم نام جدول را به یک نامی که توصیف‏‌گر مناسبی برای آن باشد تغییر دهیم.

تغییر نام بسیار آسان می‏باشد: در Query Setting، در قسمت پایین Properties، در باکس مربوط به Name نام جدید جدول را وارد ‏کنید. و سپس  Enter را  بفشارید. تصویر زیر گویای این گام ها می‏باشد.( نام جدول را RetirementState قرار دادیم)

تا اینجا ما داده‏‌های خود را شکل داده‌‏ایم. حال باید به منبع داده دیگری متصل شویم تا عملیات مربوط به ترکیب داده را بررسی کنیم. اما در اینجا مشکلی وجود دارد . بسیاری از داده‏‌ها به اختصار (دو کلمه‏‌ا‌ی) نوشته می‏شوند. برای حل این مشکل گام های زیر را میتوانید طی کنید.

Query Editor> Home> New Source> Web

حال این آدرس را وارد کرده و Connect  را انتخاب کنید.

ما Codes and abbreviations  را انتخاب می‏کنیم، اما قبل از اینکه بخواهیم از آن استفاده کنیم باید اندکی به شکل‌‏دادن داده‌‏ها بپردازیم تا جدول داده‏‌ها قابل استفاده شود.

برای شکل‏‌دادن به داده به صورت زیر عمل کنید:

از آنجایی که نیازی به سطر بالایی نداریم باید آن را حذف کنیم. برای این کار وارد تب Home شده و مراحل زیر را طی کنید.

Reduce Rows> Remove Rows> Remove Top Rows

حال پنجره Remove Top Rows  ظاهر می‏شود و به شما اجازه می‏دهد تعداد سطرهایی را که می‏خواهید حذف کنید در قسمت مربوطه وارد کنید.

نکته:

اگر power BI  به صورت تصادفی عنوان جدول را به عنوان یک سطر در جدول داده شما وارد کرده، میتوانید use first را انتخاب کنید و به صورت زیر عمل کنید.

Home> Transform

26 سطر پایین را حذف کنید، زیرا تمام آنها محدوده‏‌های جغرافیایی هستند که نیازی به آنها نداریم.

Home> Reduce Rows> Remove Rows> Remove Bottom Rows

از آنجایی که جدول  RetirementStats اطلاعاتی برای واشنگتن ندارد. پس باید آن را از لیست فیلتر کنیم. در کنار ستون Region Status، فلش کشویی را انتخاب کنید و سپس در چک‌‏باکس Federal district، تیک آن را بردارید.

ستون‌‏های غیر ضروری را حذف کنید: ما تنها به نقشه ایالت‌ها با حرف اختصاری دو کلمه ای آن‌ها نیاز داریم . پس ما میتوانیم ستون 1،3،4  و6 تا 11 را حذف کنیم.

ابتدا ستون 1 را انتخاب کنید و با نگه داشتن CTRL بقیه ستون‏‌ها را نیز انتخاب کنید.  سپس وارد تب Home شده و مراحل زیر را طی کنید.

Home> Remove Columns> Remove Columns

نکته:

جا دارد اینجا اشاره کنیم که ترتیب مراحل انجام شده در Query Editors بسیار مهم می‏باشد و روی شکل دادن به داده‏ها اثر می‏گذارد . و هم‏چنین این مهم است که هر مرحله چه تاثیری بر مراحل دیگر دارد. اگر شما یکی از گام‏‌های Applied Steps را حذف کنید. این امکان وجود دارد که گام‏‌های پس از آن به درستی عمل نکنند.

نکته:

زمانی که سایز پنجره Query Editors را تغییر داده و کوچکتر می‏کنید بعضی از آیتم‌‏ها فشرده می‏شوند. اما زمانی که عرض پنجره Query Editors را افزایش دهید آیتم‌ها به حالت قبلی برمیگردند.

-حال باید نام جدول و ستون‌‏ها را تغییر دهید. همان‏طور که در تصویر زیر می‏بینید برای این کار میتوانید ستون را انتخاب کنید و سپس در تب Transform، Rename  را انتخاب کنید. و یا اینکه روی ستون راست کلیک کرده و Rename را انتخاب کنید.

نام ستون‏‌ها را به state name و State Code تغییر دهید. برای تغییر نام جدول کافی است در باکس  Name در قسمت Query Setting ، StateCodes را تایپ کنید.

بعد از شکل دادن به داده، حال وقت آن است که آنها را با یکدیگر ترکیب کرده و به یک Query تبدیل کنید. 2 روش عمده برای این کار وجود دارد:

1)ادغام کردن

2)الحاق کردن

وقتی یک یا چندین ستون دارید که میخواهید یکی را به دیگری اضافه کنید از ادغام (merge) استفاده کنید. و زمانی که چندین سطر دارید و میخواهید آن را به کوئری دیگری اضافه کنید باید ازالحاق(append ) استفاده کنید.

دراین مورد (case)، ما میخواهیم کوئری‌‏ها را با هم ادغام کنیم .پس باید به صورت زیرعمل کرد:

ابتدا در قسمت Query ، آن کوئری را که میخواهید درکوئری فعلی ادغام کنید، انتخاب میکنید که دراین مورد RetirementStats انتخاب شده است. سپس combine > Merge Queries را از تب home  انتخاب کنید.

بعد پنجره Merge ظاهرمیشود، پیامی ظاهر میشود که نشان میدهد باید جدولی را که میخواهید با جدول فعلی ادغام شود را انتخاب کنید.

ستون State  را ازجدول Retirement انتخاب کرده وسپس کوئری StateCodes را انتخاب کنید.(دراین مثال ساده از آنجایی که فقط یک کوئری وجود دارد انتخاب آن ساده می‌باشد. اما اگر به چند منبع داده متصل شوید میتوانید چندین کوئری را انتخاب کنید. زمانی که ما ستون‏‌های تطبیقی را انتخاب میکنیم (ستون State  ازRetirement و State Name ازStateCodes)  باید پنجره merge  به صورت زیر باشد. و همچنین دکمه ok فعال باشد.

یک New Column درستون انتهایی کوئری، که محتوای آن شامل ادغام کوئری باکوئری موجود می‏باشد، ساخته شده است. تمام ستون‏‌های کوئری ادغام شده در New Column ذخیره شده است. اما شما میتوانید جدول را گسترش دهید.

برای گسترش جدول ادغام شده و انتخاب ستون‏‌های موردنظر، می‌توانید همانطور که فلش در تصویر زیر نشان می‌دهد، آیکن آن را انتخاب کرده تا پنجره expand ظاهر شود.

در این مورد ما فقط ستون State Code را می‏خواهیم. پس فقط همین ستون را انتخاب کرده و OK می‏کنیم. شایان ذکر است که ما باید در اینجا تیک مربوط به Use original column name as prefix را برداریم زیرا در غیر اینصورت نام ستون ادغام شده NewColumn.State Code  خواهد شد.

برای اینکه تغییراتی که انجام داده‏‌اید اجرا شوند فقط کافی است در تب Home ،Close & Apply  را بزنید.

حال مجموعه‌‏ای از داده‏‌های شکل داده شده آماده هستند تا ما در ساخت گزارش‏‌های خود از آنها استفاده کنیم.