Merge Queries و انواع Join در Power Query؛ وصل کردن داده‌ها بدون چندبرابر کردن اعداد

مقدمه؛ اینجا یکی از جاهای خطرناک Power Query است

فرض کن FactSales داری و می‌خواهی نام محصول را از DimProduct بگیری. در ظاهر کار ساده است: دو جدول را با ProductKey به هم وصل می‌کنیم. اما اگر کلید طرف مقابل یکتا نباشد، ممکن است یک ردیف فروش به چند ردیف تبدیل شود و مبلغ فروش بی‌سروصدا چند برابر شود.

پس Merge فقط «وصل کردن دو جدول» نیست؛ باید Grain، Cardinality، کیفیت Key و نوع Join را بفهمیم.

۲۴.۱. Merge Queries چیست؟

Merge در Power Query شبیه مفهوم Join در SQL است. یک یا چند ستون را به عنوان Key انتخاب می‌کنیم و ردیف‌های دو Query را بر اساس آن‌ها تطبیق می‌دهیم.

۲۴.۲. Merge با Relationship فرق دارد

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

۲۴.۳. Left Outer Join

رایج‌ترین Join برای غنی‌سازی Fact است: همه ردیف‌های جدول چپ می‌مانند و اگر Match پیدا شود اطلاعات جدول راست اضافه می‌شود.

FactSales LEFT JOIN DimProduct ON ProductKey

۲۴.۴. Inner Join

فقط ردیف‌هایی را نگه می‌دارد که در هر دو طرف Match دارند. این Join ممکن است رکوردهای بدون Match را حذف کند؛ پس قبل از استفاده باید بدانیم حذف آن‌ها مجاز است یا نه.

۲۴.۵. Left Anti Join

یکی از بهترین ابزارهای Data Quality است. ردیف‌های جدول چپ را نشان می‌دهد که در جدول راست Match ندارند.

FactSales LEFT ANTI DimProduct
→ فروش‌هایی که ProductKey معتبر در Dimension ندارند

۲۴.۶. Right Anti و Full Outer

Right Anti برعکس Left Anti است. Full Outer همه ردیف‌های هر دو طرف را نگه می‌دارد و برای Reconciliation یا مقایسه دو منبع می‌تواند مفید باشد.

۲۴.۷. Join Key خوب چه ویژگی دارد؟

Key باید از نظر معنی، Data Type و سطح جزئیات در هر دو جدول سازگار باشد. ProductCode در یک جدول و ProductName در جدول دیگر Join مطمئنی نیست.

نکته: قبل از Merge، Data Type ستون‌های Key را حتماً بررسی کن.

۲۴.۸. Join روی چند ستون

گاهی یک ستون به تنهایی یکتا نیست. مثلاً Price ممکن است با ProductCode و EffectiveDate یا BranchCode و Period مشخص شود.

Merge keys:
BranchCode
PeriodKey

۲۴.۹. ترتیب انتخاب ستون‌ها

در Merge چندستونی، ترتیب انتخاب Keyها در دو طرف باید متناظر باشد. Key اول سمت چپ با Key اول سمت راست و همین‌طور ادامه.

۲۴.۱۰. Expand چیست؟

بعد از Merge یک ستون Table ایجاد می‌شود. با Expand ستون‌های موردنیاز جدول دوم را وارد خروجی می‌کنیم.

فقط ستون‌هایی را Expand کن که واقعاً لازم داری؛ وارد کردن ده‌ها ستون اضافی Query را شلوغ و نگهداری را سخت می‌کند.

۲۴.۱۱. خطر Duplicate در جدول راست

Fact:
Order 1 | Product A | 100

DimProduct:
A | Name X
A | Name X duplicate

After merge → 2 sales rows

اگر انتظار Many-to-One داشتی ولی طرف Dimension Duplicate دارد، Merge می‌تواند Fact را تکثیر کند.

۲۴.۱۲. قبل از Merge یکتایی را تست کن

روی Key جدول Lookup یک Group By و Count Rows انجام بده و کلیدهایی که Count بیشتر از 1 دارند پیدا کن.

Group By ProductKey
Count Rows
Filter Count > 1

۲۴.۱۳. Reconciliation بعد از Merge

۲۴.۱۴. Null Key

Null در Key معمولاً Match طبیعی ایجاد نمی‌کند و باید معنی آن را بفهمیم. آیا Product واقعاً نامشخص است یا ETL ناقص بوده؟

۲۴.۱۵. Fuzzy Matching

Power Query قابلیت Fuzzy Matching برای متن‌های مشابه دارد. مثلاً نام شرکت با غلط املایی جزئی.

این قابلیت برای Master Data Cleaning مفید است، اما برای کلیدهای مالی و حساس نباید بدون Review انسانی به آن اعتماد کرد.

۲۴.۱۶. مثال نام مشتری

شرکت هانا
شرکت هانا
Hana Company

قبل از Fuzzy Match شاید Normalize کردن حروف، فاصله‌ها و Mapping Table راه مطمئن‌تری باشد.

۲۴.۱۷. Merge یا Mapping Table

برای استانداردسازی Codeها می‌توان Mapping Table را Merge کرد. این روش از ده‌ها Replace جداگانه قابل نگهداری‌تر است.

۲۴.۱۸. Merge یا VLOOKUP؟

اگر از Excel می‌آیی، Merge را می‌توان شبیه VLOOKUP/XLOOKUP تصور کرد، ولی Join Type، چند Match و Grain را خیلی جدی‌تر باید در نظر بگیری.

۲۴.۱۹. مثال FactSales + DimCustomer

اگر فقط برای نمایش CustomerName لازم است، در مدل ستاره‌ای معمولاً بهتر است Dimension جدا بماند و Relationship ساخته شود؛ نه اینکه همه ویژگی‌ها را داخل Fact Merge کنیم.

۲۴.۲۰. چه زمانی Merge منطقی‌تر است؟

برای Staging، ساخت Dimension، Mapping، ترکیب منابع، Lookupهای ETL و بعضی Snapshotها Merge بسیار مفید است.

برای مدل نهایی باید تصمیم را با معماری Star Schema هماهنگ کنی.

۲۴.۲۱. Query Folding

Merge روی دیتابیس در شرایط مناسب ممکن است به Source Fold شود. برای داده حجیم بررسی کن عملیات کجا اجرا می‌شود و آیا Keyها در Source بهینه‌اند.

۲۴.۲۲. Merge روی Text بزرگ

Join روی Textهای طولانی معمولاً از Join روی Keyهای مناسب سنگین‌تر و شکننده‌تر است. اگر امکان دارد Surrogate/Business Key استاندارد داشته باش.

۲۴.۲۳. مثال شرکت هانا؛ کد شعبه

FactSales شامل BranchCode است و جدول BranchMaster نام منطقه و مدیر را دارد. ابتدا یکتایی BranchCode را در Master کنترل می‌کنیم، سپس Left Outer Merge می‌زنیم و فقط RegionKey را Expand می‌کنیم.

۲۴.۲۴. پیدا کردن شعبه‌های ناشناخته

یک Query جدا با Left Anti بساز تا BranchCodeهایی که در Master نیستند مشخص شوند. این Query می‌تواند بخشی از Data Quality Monitoring باشد.

۲۴.۲۵. Slowly Changing Data؛ هشدار

اگر ویژگی Dimension در طول زمان تغییر می‌کند، Merge ساده فقط با CustomerKey ممکن است تاریخچه را اشتباه کند. باید Effective Date و نوع SCD را در معماری در نظر بگیری.

۲۴.۲۶. Merge تاریخ‌محور

Joinهای بازه زمانی مثل «قیمت معتبر در تاریخ سفارش» از Merge ساده مساوی پیچیده‌ترند و ممکن است به طراحی ETL متفاوت نیاز داشته باشند.

در این فصل مفهوم را می‌شناسیم؛ در معماری‌های پیشرفته‌تر باید Range Matching را جدا طراحی کرد.

۲۴.۲۷. Privacy Levels

وقتی منابع مختلف را ترکیب می‌کنی، تنظیمات Privacy می‌تواند روی ترکیب داده و اجرای Query اثر داشته باشد. در پروژه واقعی تنظیمات را با سیاست امنیتی سازمان هماهنگ کن.

۲۴.۲۸. Merge و Performance

ستون‌های غیرضروری را قبل از مراحل سنگین حذف کن، Data Type Keyها را هماهنگ کن و از Join روی ستون‌های بی‌کیفیت دوری کن.

اما Optimization را با اندازه‌گیری انجام بده، نه صرفاً حدس.

۲۴.۲۹. الگوی امن Merge

Define target grain
→ Profile join keys
→ Check lookup uniqueness
→ Choose join type
→ Merge
→ Expand only needed columns
→ Check unmatched
→ Reconcile row count and totals

۲۴.۳۰. خطای رایج؛ Inner Join بی‌دلیل

Inner Join می‌تواند فروش‌های دارای Product ناشناخته را حذف کند و عدد فروش را کمتر نشان دهد. گاهی بهتر است رکورد بماند و Unknown Member مدیریت شود.

۲۴.۳۱. خطای رایج؛ Duplicate Lookup

اگر جدول Lookup یکتا نیست، قبل از Merge علت Duplicate را حل کن. Remove Duplicates کورکورانه هم ممکن است اطلاعات واقعی را دور بریزد.

۲۴.۳۲. خطای رایج؛ Expand همه ستون‌ها

این کار Fact را پهن، وابسته و شلوغ می‌کند. فقط چیزی را وارد کن که واقعاً در ETL لازم است.

۲۴.۳۳. خطای رایج؛ Join با نام

نام مشتری یا محصول می‌تواند تغییر کند، فاصله داشته باشد یا تکراری باشد. Key پایدار معمولاً انتخاب بهتری است.

۲۴.۳۴. Best Practiceها

Grain هر دو Query را قبل از Merge بنویس.

Uniqueness طرف Lookup را تست کن.

برای Data Quality از Anti Join استفاده کن.

بعد از Merge Total و Row Count را Reconcile کن.

فقط ستون‌های لازم را Expand کن.

Merge را جای Relationship مدل استفاده نکن مگر دلیل معماری داشته باشی.

Fuzzy Match را برای داده حساس با Review استفاده کن.

۲۴.۳۵. تمرین پایه

FactSales را با DimProduct بر اساس ProductKey Left Outer Merge کن. سپس ProductCategory را Expand کن و تعداد Row قبل و بعد را مقایسه کن.

۲۴.۳۶. تمرین Data Quality

با Left Anti همه ProductKeyهای Fact را که در DimProduct وجود ندارند پیدا کن و یک جدول Exception بساز.

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

در DimCustomer برای CustomerKey=100 دو ردیف وجود دارد. نشان بده Merge چه اثری روی SalesAmount می‌گذارد و قبل از اصلاح چه سؤال‌هایی درباره Duplicate باید پرسیده شود.

۲۴.۳۸. پروژه فصل؛ Customer Enrichment شرکت هانا

در پایان باید هم Query اصلی سالم داشته باشی و هم Query خطاهای Master Data تا تیم داده بتواند CustomerKeyهای ناشناخته را اصلاح کند.

جمع‌بندی فصل بیست‌وچهارم

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

Good joins preserve meaning, not just rows.

در فصل بیست‌وپنجم سراغ Append Queries می‌رویم: ترکیب فایل‌های ماهانه، چند شعبه، چند سال و ساخت یک Fact یکپارچه بدون به‌هم‌ریختن Schema.