Power Query؛ تمیزکاری و آماده‌سازی حرفه‌ای داده‌ها

کتاب جامع Power BI — آموزش پروژه‌محور و کاربردی

مقدمه؛ داده واقعی معمولاً تمیز نیست

در فصل قبل اولین گزارش را ساختیم. اما در پروژه واقعی معمولاً فایل یا دیتابیسی تحویل نمی‌گیریم که همه‌چیزش مرتب و آماده باشد. یک جا تاریخ خراب است، یک جا مقدار Null داریم، اسم شعبه با چند شکل نوشته شده و گاهی هم چند فایل باید با هم ترکیب شوند.

اینجا Power Query تبدیل می‌شود به یکی از مهم‌ترین ابزارهای ما. قبل از اینکه سراغ مدل‌سازی، DAX و داشبورد برویم، باید داده را به شکلی قابل اعتماد آماده کنیم.

۳.۱. Power Query دقیقاً چه کار می‌کند؟

Power Query موتور دریافت و تبدیل داده در Power BI است. با آن می‌توانیم داده را از منابع مختلف بگیریم، تمیز کنیم، شکلش را تغییر دهیم و بعد وارد مدل کنیم.

Source → Clean → Transform → Combine → Load

نکته مهم این است که عملیات ما به صورت مرحله‌ای ثبت می‌شوند. یعنی هنگام Refresh همان مراحل دوباره روی داده جدید اجرا می‌شوند.

۳.۲. ورود به Power Query Editor

از Transform Data وارد Power Query Editor می‌شویم. سمت چپ Queryها را می‌بینیم، وسط پیش‌نمایش داده است و در بخش Applied Steps مراحلی را می‌بینیم که روی داده انجام داده‌ایم.

Applied Steps را جدی بگیر. اگر بعداً نتیجه اشتباه شد، همین مراحل مثل تاریخچه عملیات به ما کمک می‌کنند بفهمیم مشکل از کجا شروع شده است.

۳.۳. سناریوی شرکت هانا؛ داده کثیف

فرض کنیم فایل فروش شرکت هانا مشکلات زیر را دارد:

هدف ما این است که قبل از تحلیل، این مشکلات را کنترل کنیم.

۳.۴. Rename کردن ستون‌ها

اسم ستون باید برای آدم بعدی هم قابل‌فهم باشد. Column1 و Field7 در پروژه سازمانی اسم‌های خوبی نیستند.

Order_Date  → OrderDate
Sales_Man   → SalesPerson
Qty         → Quantity

نام‌گذاری منظم باعث می‌شود DAX، مدل و مستندسازی بعدی خواناتر شوند.

۳.۵. Data Type؛ یک موضوع ساده ولی حیاتی

Power BI باید بداند هر ستون چه نوع داده‌ای دارد. تاریخ، متن، عدد صحیح و عدد اعشاری رفتار متفاوتی دارند.

اگر Quantity به شکل Text وارد شود، ممکن است محاسبات عددی به مشکل بخورند. همیشه نوع داده را آگاهانه بررسی کن.

۳.۶. حذف ستون‌های اضافی

هر ستونی که وارد مدل می‌کنیم هزینه دارد. اگر ستونی هیچ کاربرد تحلیلی، ارتباطی یا محاسباتی ندارد، نگه‌داشتنش فقط مدل را شلوغ‌تر می‌کند.

مثلاً اگر TemporaryNote فقط یادداشت داخلی فایل اولیه است و در گزارش استفاده نمی‌شود، می‌توانیم آن را حذف کنیم.

نکته: ستون را فقط چون «شاید یک روز لازم شود» وارد مدل نکن. اول کاربردش را مشخص کن.

۳.۷. مدیریت Nullها

Null یعنی مقدار وجود ندارد؛ اما دلیلش مهم است. ممکن است شعبه ثبت نشده باشد، قیمت هنوز مشخص نشده باشد یا واقعاً آن فیلد برای رکورد کاربرد نداشته باشد.

نباید همه Nullها را کورکورانه صفر کنیم. صفر خودش یک مقدار واقعی است و ممکن است معنی تحلیل را عوض کند.

Null ≠ 0
Null ≠ Empty String

برای هر ستون باید تصمیم کسب‌وکاری مشخص داشته باشیم.

۳.۸. Trim و Clean؛ فاصله‌های دردسرساز

گاهی «تهران» و « تهران » برای چشم ما یکی هستند، ولی در داده می‌توانند دو مقدار متفاوت محسوب شوند. Trim فاصله‌های ابتدا و انتهای متن را حذف می‌کند و Clean برای حذف بعضی کاراکترهای کنترلی مفید است.

این تمیزکاری مخصوصاً برای ستون‌هایی مثل Branch، CustomerName و ProductCode مهم است.

۳.۹. Replace Values

گاهی لازم است چند مقدار را استاندارد کنیم. مثلاً در فایل قدیمی نام شعبه به انگلیسی ثبت شده و در فایل جدید فارسی است.

TEHRAN → تهران
Tehran → تهران

اما مراقب باش Replace Values می‌تواند داده را تغییر دهد. قبل از جایگزینی گسترده، منطق کسب‌وکار را تأیید کن.

۳.۱۰. Split Column

فرض کن ستونی داریم که کد و نام محصول را کنار هم نگه داشته است:

P-1001 | موتور الکتریکی

می‌توانیم با Split Column آن را به ProductCode و ProductName تبدیل کنیم. جداکردن داده‌های مرکب معمولاً مدل و تحلیل را تمیزتر می‌کند.

۳.۱۱. Merge Columns

برعکس Split، گاهی می‌خواهیم چند ستون را برای یک خروجی نمایشی ترکیب کنیم. مثلاً نام و نام خانوادگی فروشنده.

FirstName + " " + LastName → FullName

ولی اگر دو ستون اصلی برای تحلیل لازم‌اند، بهتر است صرفاً به خاطر نمایش آن‌ها را حذف نکنیم.

۳.۱۲. Conditional Column

Conditional Column به ما اجازه می‌دهد بر اساس شرط، ستون جدید بسازیم. مثلاً سفارش‌ها را بر اساس مبلغ دسته‌بندی کنیم.

اگر مبلغ >= 100,000,000 → سفارش بزرگ
اگر مبلغ >= 30,000,000  → سفارش متوسط
در غیر این صورت          → سفارش کوچک

این قابلیت برای منطق‌های ساده بسیار کاربردی است.

۳.۱۳. Custom Column

وقتی منطق پیچیده‌تر شود می‌توانیم Custom Column بسازیم. اینجا کم‌کم با زبان M روبه‌رو می‌شویم.

[Quantity] * [UnitPrice]

قرار نیست در این فصل M را کامل یاد بگیریم؛ فعلاً هدف این است که بدانیم پشت بسیاری از مراحل Power Query کد M وجود دارد.

۳.۱۴. Remove Duplicates؛ با احتیاط

حذف رکورد تکراری جذاب است، اما باید بدانیم «تکراری» یعنی چه. اگر دو ردیف همه مقادیر یکسان داشته باشند، شاید واقعاً دو فروش جداگانه باشند.

مثلاً بهتر است ابتدا کلید کسب‌وکاری را مشخص کنیم؛ شاید OrderID و ProductID با هم هویت ردیف را تعیین کنند.

نکته: Remove Duplicates بدون شناخت کلید داده می‌تواند اطلاعات واقعی را حذف کند.

۳.۱۵. Filter Rows

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

Status <> "Test"

فیلتر باید مستند و قابل دفاع باشد. اگر رکوردی حذف می‌شود، باید بدانیم چرا.

۳.۱۶. Group By

Group By می‌تواند داده را بر اساس یک یا چند ستون خلاصه کند. مثلاً فروش کل هر شعبه.

Branch → Sum(TotalAmount)

اما در مدل‌های تحلیلی همیشه لازم نیست داده را از قبل خلاصه کنیم. گاهی بهتر است جزئیات را نگه داریم و محاسبه را در مدل انجام دهیم. تصمیم به سناریو بستگی دارد.

۳.۱۷. Append Queries؛ چسباندن فایل‌ها زیر هم

فرض کن برای هر ماه یک فایل فروش داریم: فروردین، اردیبهشت و خرداد. ساختار ستون‌ها یکسان است و می‌خواهیم همه رکوردها زیر هم قرار بگیرند.

Sales-Farvardin
      +
Sales-Ordibehesht
      +
Sales-Khordad
      ↓
Sales-All

اینجا Append ابزار مناسبی است.

۳.۱۸. Merge Queries؛ وصل‌کردن جدول‌ها کنار هم

Merge با Append فرق دارد. Merge معمولاً بر اساس یک کلید، اطلاعات دو Query را کنار هم قرار می‌دهد.

Sales.ProductID = Products.ProductID

مثلاً می‌توانیم اطلاعات گروه محصول را از جدول Products به داده فروش متصل کنیم. البته در مدل‌سازی حرفه‌ای همیشه لازم نیست جدول‌ها را در Power Query یکی کنیم؛ گاهی Relationship انتخاب درست‌تری است.

۳.۱۹. فرق Append و Merge

اگر دو فایل ماهانه ساختار مشابه دارند معمولاً Append مطرح است. اگر جدول فروش را با جدول محصول بر اساس ProductID مرتبط می‌کنیم، Merge مطرح می‌شود.

۳.۲۰. Pivot و Unpivot

Unpivot یکی از قابلیت‌هایی است که در پروژه‌های Excelمحور خیلی نجات‌دهنده است. فرض کن فایل بودجه به این شکل آمده باشد:

Product | Farvardin | Ordibehesht | Khordad
A       | 100       | 120         | 150

برای تحلیل بهتر معمولاً می‌خواهیم ماه‌ها به ردیف تبدیل شوند:

Product | Month       | Amount
A       | Farvardin   | 100
A       | Ordibehesht | 120
A       | Khordad     | 150

این همان جایی است که Unpivot خیلی کاربردی می‌شود.

۳.۲۱. کار با Folder؛ سناریوی چند فایل

یکی از سناریوهای واقعی این است که واحد فروش هر ماه یک Excel داخل پوشه مشخص قرار می‌دهد. به جای اتصال جداگانه به هر فایل، می‌توانیم از Folder به عنوان منبع استفاده کنیم و فایل‌های هم‌ساختار را ترکیب کنیم.

این روش وقتی ارزشش را نشان می‌دهد که ماه بعد فایل جدید فقط داخل پوشه قرار بگیرد و Refresh، فرایند تعریف‌شده را دوباره اجرا کند.

۳.۲۲. Applied Steps را تمیز نگه دار

در پروژه بزرگ ممکن است ده‌ها مرحله تبدیل داشته باشیم. نام مرحله‌ها و ترتیبشان باید قابل‌فهم باشد.

Source
→ Promoted Headers
→ Changed Types
→ Removed Empty Rows
→ Trimmed Branch
→ Standardized Branch Names

وقتی شش ماه بعد خودت پروژه را باز می‌کنی، همین نظم باعث می‌شود از خودت تشکر کنی!

۳.۲۳. آشنایی اولیه با زبان M

هر عملیاتی که در Power Query انجام می‌دهیم معمولاً به کد M تبدیل می‌شود. برای مثال یک مرحله تغییر نوع داده می‌تواند در M نمایش داده شود.

Table.TransformColumnTypes(Source, {{"Quantity", Int64.Type}})

در شروع لازم نیست همه کدها را دستی بنویسی. ولی خواندن M به مرور کمک می‌کند تبدیل‌های حرفه‌ای‌تر و قابل نگهداری‌تری بسازی.

۳.۲۴. خطاهای رایج Power Query

خطای اول: بدون بررسی داده، Nullها را صفر می‌کنیم.

خطای دوم: Remove Duplicates را روی کل جدول اجرا می‌کنیم بدون اینکه کلید واقعی را بشناسیم.

خطای سوم: همه جدول‌ها را با Merge تبدیل به یک جدول غول‌پیکر می‌کنیم.

خطای چهارم: نوع داده را به Auto Detection می‌سپاریم و بررسی نمی‌کنیم.

خطای پنجم: مسیر فایل‌ها را طوری طراحی می‌کنیم که با کوچک‌ترین جابه‌جایی پروژه Refresh خراب شود.

خطای ششم: مراحل Power Query آن‌قدر نامنظم هستند که هیچ‌کس نمی‌فهمد چه اتفاقی افتاده است.

۳.۲۵. Best Practiceهای Power Query

تبدیل‌ها را مرحله‌ای و قابل‌فهم انجام بده.

تا جای ممکن ستون‌های غیرضروری را زودتر حذف کن.

Data Type را صریح بررسی کن.

قواعد تمیزکاری مهم را مستند کن.

قبل از Merge یا Remove Duplicates، کلید و Grain داده را بفهم.

Queryهای کمکی را با نام مشخص نگه دار.

منطق کسب‌وکاری را با تمیزکاری فنی قاطی نکن؛ هر تغییر باید دلیل مشخص داشته باشد.

۳.۲۶. تمرین پایه

یک فایل Excel با ستون‌های Branch، Product، Quantity و UnitPrice بساز. در Branch عمداً فاصله اضافی ایجاد کن و Quantity را در چند ردیف به شکل Text ذخیره کن.

فایل را وارد Power Query کن، Trim انجام بده و Data Typeها را اصلاح کن.

۳.۲۷. تمرین متوسط

سه فایل ماهانه با ساختار یکسان بساز و با Append آن‌ها را به یک Query تبدیل کن. سپس یک ستون Month اضافه کن تا منبع زمانی هر رکورد مشخص باشد.

۳.۲۸. تمرین کاربردی

یک جدول Sales و یک جدول Products بساز. هر دو ProductID داشته باشند. با Merge نام Category را به داده فروش اضافه کن و نتیجه را بررسی کن.

بعد فکر کن آیا در پروژه واقعی بهتر است Merge کنیم یا دو جدول را جدا نگه داریم و در مدل Relationship بسازیم. دلیل انتخابت را بنویس.

۳.۲۹. تمرین چالشی

یک فایل بودجه بساز که ماه‌ها در ستون‌های جدا باشند. با Unpivot آن را به ساختار ردیفی تبدیل کن.

سپس Nullها، نوع داده و نام ستون‌ها را اصلاح کن و خروجی را برای ورود به مدل آماده کن.

۳.۳۰. پروژه نهایی فصل؛ کارخانه تمیزکاری داده شرکت هانا

برای شرکت هانا سه فایل فروش ماهانه، یک فایل محصول و یک فایل شعبه داریم. داده‌ها عمداً شامل فاصله اضافی، Null، نام‌های ناسازگار و چند نوع داده اشتباه هستند.

در پایان باید بتوانی توضیح بدهی هر مرحله چرا وجود دارد. اگر نتوانی دلیل یک Step را توضیح بدهی، احتمالاً باید دوباره درباره آن فکر کنی.

جمع‌بندی فصل سوم

Power Query جایی است که داده خام را برای تحلیل قابل اعتماد آماده می‌کنیم. این بخش شاید به اندازه داشبورد نهایی پرزرق‌وبرق نباشد، ولی کیفیت گزارش به شدت به همین مرحله وابسته است.

Raw Data
   ↓
Power Query
   ↓
Clean + Transform + Combine
   ↓
Reliable Data
   ↓
Data Model

در فصل بعد وارد قلب معماری Power BI می‌شویم: مدل‌سازی داده، Fact و Dimension، Relationship و Star Schema. همان‌جا می‌بینیم چرا یک مدل خوب می‌تواند DAX را ساده‌تر و گزارش را سریع‌تر کند.