با استفاده از پاور بی آی دسکتاپ (POWER BI DESKTOP) شما میتوانید به انواع مختلفی از منابع داده متصل شوید . در مورد نحوه اتصال به داده ها در مطلب قبل صحبت شد. پس از اتصال به داده ها میتوانید داده ها خود را اصلاح کنید و به نحو مورد نظر خود به شکل دهی آنها بپردازید و با دیگران به اشتراک بگذارید.در این مطلب مبحث شکل دهی داده ها در پاور بی آی را بررسی خواهیم کرد.  شکل دهی داده ها شامل تبدیل داده ها، تغییر نام ستون یا جدول، تبدیل متن به عدد، حذف ردیف ها، قرار دادن اولین ردیف بعنوان سرفصل و … است. ترکیب داده ها به معنی اتصال به دو یا چند منبع داده، شکل دهی به داده ها به شکل مورد نظر و یکپارچه سازی داده های هر دو منبع در یک کوئری واحد است.

این مطلب یکی از قسمت های سلسله مطالب در زمینه آموزش پاور بی آی (POWER BI) است و بهتر است در صورتی که مطالب قبل در زمینه آموزش پاور بی آی را مطالعه نکرده اید، به صفحات زیر مراجعه کنید:

معرفی ابزار پاور بی آی (POWER BI)

مقایسه ساخت داشبورد پاور بی آی و داشبورد اکسل

پاور بی آی دسکتاپ (power BI desktop)

مفاهیم اولیه در زمینه power BI service

نحوه نصب پاور بی آی دسکتاپ (power bi desktop)

نحوه اتصال به داده در پاور بی آی (POWER BI)

شکل دهی داده ها در پاور بی آی

زمانی که داده ها را در Query editor  شکل دهی میکنید در واقع یک دستورالعمل گام به گام ایجاد میکنید که query editor برای شما طی میکند تا داده ها شما به نحو مورد نیاز اصلاح شوند. منبع اصلی داده تحت تاثیر قرار نمی گیرد و فقط همین نمای داده تغییر داده میشود.

گام هایی که شما مشخص میکنید، توسط query editor ضبط میشود و هر بار که این کوئری اجرا شود همین گام ها طی خواهند شد. این فرایند هربار که شما از power bi desktop استفاده کنید یا برای هرکسی که شما کوئری را با استفاده از یک ابزار مثل power bi service با او به اشتراک گذاشته باشید، اتفاق می افتد. این مراحل در قاب Query Settings در قسمت Applied Steps نمایش داده میشود.

برای اتصال به داده ها مراحل زیر را به ترتیب انجام دهید:

1- باید منابع داده را از یک صفحه وب وارد پاور بی آی کنید، پس از گزینه get data اتصال web را انتخاب کنید.

2- لینک ریز را در قسمت URL پیست کنید و بر روی OK کلیک کنید.

https://www.fool.com/research/best-states-to-retire

3- از پنجره Nabigator جدول table 1 را انتخاب کنیدو بر روی دکمه transform data کلیک کنید.

4- پنجره پاور کوئری باز خواهد شد. به صورت پیش فرض چندین مرحله در پین Query Setting در بخش APPLIED STEPS انجام شده است.

• Source: متصل شدن به سایت
• Extracted Table from Html: استخراج جدول از وب سایت
• Promoted Headers: تبدیل اولین سطح داده ها به عنوان عنوان ستون ها
• Changed Type: تبدیل نوع داده های ستون ها براساس داده های موجود در هر ستون. (مثلا متن، عدد و …)

5- نام کوئری را از Table 1 به Retirement Data تغییر دهید.

6- همانطور که در صفحه وب منبع توضیح داده شده است، داده های موجود براساس یک امتیاز وزنی مرتب شده اند. بیایید یک ستون سفارشی برای محاسبه امتیاز متفاوت اضافه کنیم. سپس جدول را در این ستون مرتب می کنیم تا رتبه بندی امتیاز سفارشی که حساب کرده ایم را با رتبه موجود مقایسه کنیم.

7- از تب add column گزینه Custom column را انتخاب کنید.

در قسمت custom column ، در کادر new column name عبارت new score را تایپ کنید و در قسمت custom column formula فرمول زیر را وارد کنید:

( [Quality of life] + [Housing cost] + [Healthcare cost and quality] + [Crime rate rate] + [#"Public health/COVID-19 response"] + [Sales taxes] + [#"Non-housing costs"] + [Weather] ) / 8

9-دقت داشته باشید که در کادر پایین فرمول عبارت «No syntax errors have been detected» به معنی عدم وجود خطا در فرمول نمایش داده شود و سپس OK را انتخاب کنید.

10- همان طور که در Query Setting مشاهده می کنید در قسمت APPLIED STEPS مرحله Added Custom اضافه شده است.

11- برای ثبات داده های ستون، مقادیر ستون جدید را به اعداد کامل تغییر دهید. برای این کار، بر روی عنوان ستون، راست کلیک کنید و گزینه Change Type > Whole Number را انتخاب کنید.

شما همچنین میتوانید از تب transform نوع داده ها را نیز تغییر دهید. با استفاده از دکمه data type که در شکل زیر نیز نشان داده شده است، میتوانید نوع داده ها را تغییر دهید.

توجه داشته باشید که در query setting، گزینه applied steps تمام گام های مربوط به شکل دهی داده ها را نمایش میدهد. در صورتی که میخواهید هرکدام از گام ها را از فرایند شکل دهی حذف کنید، کافیست علامت x را در سمت چپ مرحله انتخاب کنید . شما می بایست با انجام تغییرات زیر بر روی کوئری داده ها، آن را به شکل مورد مورد نظر تبدیل کنید.  

• با حذف یک ستون، رتبه بندی را اصلاح کنید. ما بر این عقیده ایم که cost of living( هزینه زندگی( در محاسبات ما تاثیرگذار نیست. بنابراین هیچ نیازی به این ستون از داده ها نخواهیم داشت و آن را حذف میکنیم.
• چند خطا را رفع کنید. از آنجایی که یکی از ستون ها را حذف کردیم، می بایست محاسبات خود را در ستون new rank اصلاح کنیم. برای این کار باید در فرمول خود تغییراتی را ایجاد کنیم .
• داده ها را رتبه بندی کنید. بر اساس ستون های rank و new rank داده های خود را رتبه بندی کنید.
• داده ها را جایگزین کنید. در مورد جایگزینی مقادیر خاص و نیاز به تعریف مراحل جدید در قسمت  Applied step ، صحبت خواهیم کرد.
• نام جدول را تغییر دهید. Table 0 نام مناسبی برای جدول نیست. تغییر نام جدول بسیار ساده است و شما میتوانید به سادگی آن را تغییر دهید.

برای حذف ستون cost of living، کافیست آن را انتخاب کنید و گزینه remove column را از تب home انتخاب گنید.

به یاد داشته باشید که مقادیر new rank تحت تاثیر قرار نمی گیرد. این موضوع به دلیل ترتیب مراحل است. از آنجایی که query editor مراحل را به صورت پشت سر هم اجرا میکند، شما میتوانید هرکدام از مراحل را به صورت مستقل به بالا یا پایین لیست منتقل کنید. با راست کلیک بر روی هرکدام از مراحل، گزینه های Rename( تغییر نام)، Delete( حذف)، Delete Until End( مرحله انتخاب شده و تمام مراحل بعد از آن را حذف میکند)، Move Up( انتقال به بالا)، Move Down( انتقال به پایین)  ظاهر میشود. شما می بایست مرحله نهایی یعنی removed columns را به بالای مرحله added custom column  منتقل کنید.

سپس، مرحله Added Custom را انتخاب کنید، توجه داشته باشید که در حال حاضر مقادیر  error نمایش داده میشود که می بایست رفع خطا شوند.

چند راه برای بدست آوردن اطلاعات بیشتر در مورد هر خطا وجود دارد. شما میتوانید سلول را انتخاب کنید( بدون اینکه بر روی کلمه error کلیک کنید)، یا به صورت مستقیم بر روی کلمه Error کلیک کنید. اگر راه اول را انتخاب کنید، query editor در پایین صفحه اطلاعات مربوطه را نمایش میدهد.

در صورتی که مستقیما بر روی کلمه error کلیک کنید، کوئری یک applied step در قاب Query Settings ایجاد میکند و اطلاعات مربوط به خطا را نمایش میدهد. ما نمیخواهیم این مسیر را طی کنیم در نتیجه cancel را انتخاب کنید.

برای رفع خطا، ستون new rank را انتخاب کنید، سپس با انتخاب چک باکس formula bar در تب view، فرمول داده های ستون را نمایش دهید.

حال میتوانید پارامتر cost of living را از فرمول حذف کنید و فرمول را به شکل زیر تبدیل کنید:

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

چک مارک سمت چپ فرمول را انتخاب کنید یا دکمه enter را فشار دهید تا داده ها با فرمول جدید محاسبه شوند. همچنین مرحله Added Custom می بایست با no errors تکمیل شود.

نکته:

شما میتوانید با استفاده از ریبون یا منوی راست کلیک نیز خطاها را رفع کنید. در این صورت هر ردیفی که دارای خطاست، حذف میشود. در صورت استفاده از این شیوه، تمام ردیف های داده ما حذف خواهد شد و ما نمیخواهیم این اتفاق رخ دهد. ما می بایست از شیوه ای استفاده میکردیم که داده ها خود را از دست ندهیم.

حال می بایست داده های خود را بر اساس ستون new rank رتبه بندی کنیم. ابتدا گام آخر applied step یعنی Changed Type1 را انتخاب کنید تا آخرین داده ها را بدست آورید. سپس لیست کشویی موجود در کنار عنوان ستون new rank را انتخاب کنید و Sort Ascending را کلیک کنید.

حال داده ها بر اساس ستون new rank رتبه بندی شده اند. با اینحال، اگر به ستون rank نگاهی بیاندازید متوجه خواهید شد داده ها به صورت مناسبی رتبه بندی نشده اند. برای حل این مشکل، ستون new rank را انتخاب کنید و فرمول موجود در Formula bar را به شکل زیر تغییر دهید:

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

چک مارک سمت چپ باکس formula را انتخاب کنید یا کلید enter را فشار دهید. حال می بایست تمام ردیف ها بر اساس هر دو ستون  Rank و new rank رتبه بندی شده باشند.

علاوه بر این شما میتوانید در هر جایی از لیست applied steps یکی از مراحل را انتخاب کنید و شکل دهی داده را در آن مرحله از لیست انجام دهید. در این صورت query editor به صورت اتوماتیک یک مرحله پس از مرحله انتخاب شده اضافه میکند. برای روشن تر شدن موضوع یکبار این این کار را انجام میدهیم :

ابتدا مرحله قبل از اضافه کردن ستون ( removed column) را انتخاب کنید. در اینجا میخواهیم مقدار رتبه بندی weather( آب و هوا) را برای ایالت Arizona تغییر دهیم . بر روی سلولی که شامل رتبه بندی آب و هوای آریزونا است، راست کلیک کنید و گزینه replace values را انتخاب کنید. مرحله ای را که انتخاب کرده و تغییر داده اید( مرحله قبل از added custom) در نظر داشته باشید.

از آنجایی که میخواهیم یک مرحله اضافه کنیم، query editor  هشدار میدهد که این اقدام ممکن است بر مراحل بعد تاثیر گذار باشد و باعث آسیب دیدن کوئری شود. به همین دلیل میبایست به دقت و آگاهانه عمل شود. در اینجا همه چیز تحت کنترل است و جای هیچ نگرانی نیست. پس دکمه insert را فشار دهید.

مقدار را به 51 تغییر دهید. حال داده مربوط به ARIZONA تغییر داده شده است. زمانی که یک APPLIED STEP جدید ایجاد کنید، QUERY EDITOR آن را بر اساس نوع اقدام انجام شده نامگذاری میکند( در این مورد REPLACED VALUE) . زمانی که بیش از یک مرحله با نام های مشابه در کوئری خود داشته باشید، یک عدد به انتهای هرکدام اضافه میشود تا تشخیص آنها تسهیل شود.

حال آخرین Applied Step یعنی Sorted Rows را انتخاب کنید و توجه داشته باشید که داده ها با توجه به تغییر رتبه ARIZONA تغییر یافته اند. این موضوع به این دلیل است که ما مرحله REPLACED VALUE را در محل مناسب یعنی قبل از مرحله Added Custom قرار دادیم.

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

تغییر نام جدول ساده است: در قاب Query Settings در قسمت Properties نام جدید جدول را تایپ کنید و ENTER را فشار دهید.در این مورد از نام RetirementStats برای جدول استفاده میکنیم.

ما داده های خود را به نحو مورد نیاز خود شکل دهی کردیم. در مطلب بعد در زمینه آموزش پاور بی آی، با اتصال به یک منبع داده دیگر و ترکیب داده های مربوط به چندین منبع داده، بحث ترکیب داده ها در پاور بی آی را بررسی خواهیم کرد.