چگونه چندین زیررشته (Substring) را در Power Query M جایگزین کنیم
Power Query دارای توابعی برای جایگزینی زیررشته‌ها است. می‌توانید از تابع Text.Replace در یک ستون سفارشی استفاده کنید، یا از Table.ReplaceValue برای جایگزینی مستقیم مقادیر در یک جدول بهره بگیرید. با این حال، این توابع معمولاً فقط قادر به انجام یک جایگزینی در هر بار هستند.
اما اگر بخواهید چندین زیررشته متفاوت را در یک مجموعه داده جایگزین کنید چه؟ این یک سناریوی رایج هنگام پاک‌سازی داده‌ها است. در این مقاله، تکنیک‌های مختلفی را برای جایگزینی چندین زیررشته در Power Query بررسی خواهیم کرد.
روش اول: جایگزینی دستی (Manual Replacements)
ساده‌ترین روش برای جایگزینی چندین زیررشته (Substring) در Power Query، استفاده چندباره از عملیات Replace Values است. هر بار جایگزینی، یک گام (Step) جدید در Query Editor ایجاد می‌کند، بنابراین تعداد مراحل با تعداد جایگزینی‌های مورد نیاز برابر خواهد بود. در ادامه یک مثال آورده شده است:
let
Source = MyData,
#"Replaced Value1" = Table.ReplaceValue(Source," (GDR)","",Replacer.ReplaceText,{"country_txt"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1"," (FRG)","",Replacer.ReplaceText,{"country_txt"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","People's Republic of the Congo","Congo",Replacer.ReplaceText,{"country_txt"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","Republic of the Congo","Congo",Replacer.ReplaceText,{"country_txt"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","-"," ",Replacer.ReplaceText,{"country_txt"})
in
#"Replaced Value5"
در این مثال، هر خط یک جایگزینی واحد را روی ستون country_txt انجام می‌دهد. هر مرحله‌ی جایگزینی به‌طور واضح تعریف شده است، اما این رویکرد یک نقطه‌ضعف دارد: اگر به تعداد زیادی جایگزینی نیاز داشته باشید، خیلی سریع تکراری و دست‌وپاگیر می‌شود. افزودن یا تغییر جایگزینی‌ها نیز به این معناست که باید چندین مرحله را تنظیم و ویرایش کنید، که زمان‌بر است.
در حالی که این روش برای مجموعه داده‌های کوچک یا زمانی که فقط به چند جایگزینی نیاز است مناسب است، اما قابلیت گسترش‌پذیری (Scalability) ندارد. زمانی که جایگزینی‌های گسترده‌ای لازم باشد، ایجاد مراحل جداگانه برای هر مورد هم زمان‌بر است و هم باعث شلوغی در کد می‌شود.
اگر به دنبال روشی مقیاس‌پذیرتر برای انجام چندین جایگزینی در مراحل کمتر هستید، بیایید یک روش جایگزین را بررسی کنیم که نگهداری آن ساده‌تر باشد.
روش دوم: جایگزینی‌ها با استفاده از List.Accumulate
برای رویکردی قابل نگهداری‌تر جهت انجام چندین جایگزینی، می‌توانید از List.Accumulate استفاده کنید. اگرچه این تابع پیچیده است، اما به شما اجازه می‌دهد عملیاتی را به صورت مکرر با تکرار روی یک لیست انجام دهید.
برای استفاده از این روش، ابتدا یک تابع سفارشی به نام fxReplaceValues تعریف کنید که جایگزینی‌ها را مدیریت می‌کند:
( Table as table, listOfReplacements as list, listOfColumns as list ) =>
List.Accumulate(
listOfReplacements,
Table,
(state, current) =>
Table.ReplaceValue(
state,
current{0},
current{1},
Replacer.ReplaceText,
listOfColumns
)
)
تابع fxReplaceValues سه آرگومان دریافت می‌کند:
Table: جدولی که قرار است جایگزینی‌ها در آن انجام شود.
listOfReplacements: لیستی از لیست‌ها که هر کدام شامل یک مقدار قدیمی و یک مقدار جدید هستند. برای مثال:
{ {" (GDR)", ""}, {" (FRG)", ""} }.listOfColumns: لیستی از نام ستون‌هایی که در آن‌ها جایگزینی‌ها انجام می‌شود. اگر فقط با یک ستون کار می‌کنید، باز هم باید به صورت یک لیست مشخص شود، مثلاً:
{ "country_txt" }.
پس از تعریف این تابع، می‌توانید آن را با استفاده از یک عبارت let روی جدول خود اعمال کنید. در اینجا یک مثال از کاربرد fxReplaceValues روی ستون country_txt آورده شده است:
let
// --------------------Function Definition ------------------------------
fxReplaceValues =
( Table as table, listOfReplacements as list, listOfColumns as list ) =>
let
myFunction =
List.Accumulate (
listOfReplacements,
Table,
( state, current ) =>
Table.ReplaceValue (
state,
current{0},
current{1},
Replacer.ReplaceText, listOfColumns )
)
in
myFunction,
// -----------------------Query Start -----------------------------------
Source = MyData,
ReplaceValues =
fxReplaceValues(
Source,
{
{" (GDR)",""},
{ " (FRG)","" },
{ "People's Republic of the Congo","Congo" },
{ "Republic of the Congo","Congo" },
{ "-"," " }
},
{"country_txt"}
)
in
ReplaceValues
در این مثال:
Source جدول داده‌های اصلی شما است.
مرحله ReplaceValues تابع
fxReplaceValuesرا فراخوانی می‌کند و موارد زیر را به آن می‌فرستد:جدول اصلی (Source).
لیستی از جفت‌های جایگزینی، مانند
{" (GDR)", ""}که مقدار ” (GDR)” را با رشته خالی جایگزین می‌کند.لیستی که مشخص می‌کند این جایگزینی‌ها روی کدام ستون‌ها اعمال شوند (در اینجا،
{"country_txt"}).
روش List.Accumulate نتیجه‌ای تمیز و یکپارچه ایجاد می‌کند که همه جایگزینی‌ها را به صورت همزمان انجام می‌دهد، که باعث می‌شود مراحل کوئری شما حداقل و مدیریت آن ساده‌تر باشد. هر زمان که نیاز به جایگزینی‌های جدید داشتید، کافی است یک جفت {old, new} جدید به لیست listOfReplacements اضافه کنید. اگر بخواهید این جایگزینی‌ها را روی چند ستون اعمال کنید، فقط کافی است لیست موجود در آرگومان listOfColumns را گسترش دهید.
و اگر بخواهید این تابع را در چندین کوئری مختلف استفاده کنید، می‌توانید به سادگی تابع را در Query Pane خود ذخیره کرده و در همه جا اعمال کنید. این کار نگهداری مقادیر جایگزینی را بسیار آسان‌تر می‌کند.
در ادامه، روشی دیگر را بررسی خواهیم کرد که مزایای مشابهی دارد اما با رویکردی کمی متفاوت در پیاده‌سازی.
روش سوم: جایگزینی‌ها با استفاده از List.Generate
تابع List.Generate معمولاً عملکرد بهتری نسبت به List.Accumulate دارد و می‌تواند نتیجه‌ای مشابه به دست دهد، اما تنظیم آن کمی پیچیده‌تر است.
برای استفاده از این روش، تابعی مشابه مثال قبلی تعریف خواهیم کرد، اما این بار با استفاده از List.Generate. برای وضوح بیشتر، هر یک از آرگومان‌های List.Generate را با یک کامنت جدا کرده‌ام.
ساختار کد به شکل زیر است:
( Table as table, listOfReplacements as list, listOfColumns as list ) =>
let
myFunction =
List.Generate(
() =>
[
myTable = Table,
Index = 0,
Replacements = listOfReplacements
],
// ---------------------------------------------------------------------
each [Index] <= List.Count([Replacements]),
// ---------------------------------------------------------------------
each [
myTable = Table.ReplaceValue(
[myTable],
Replacements{[Index]}{0},
Replacements{[Index]}{1},
Replacer.ReplaceText,
listOfColumns
),
Index = [Index] + 1,
Replacements = [Replacements]
],
// ---------------------------------------------------------------------
each [myTable]
),
Result = List.Last(myFunction)
in
Result
آرگومان‌های این تابع همانند تابع List.Accumulate که قبلاً ساخته شده بود، یکسان هستند؛ اما بدنه تابع کمی پیچیده‌تر است. بر خلاف List.Accumulate، در List.Generate لازم است به صورت دستی یک شمارنده (Index) برای پیگیری جایگزینی جاری افزایش یابد.
این تابع به صورت زیر عمل می‌کند:
با یک حالت اولیه شروع می‌کند:
[ myTable = Table, Index = 0, Replacements = listOfReplacements ].تا زمانی که مقدار
[Index]کمتر از تعداد[Replacements]باشد، تکرار انجام می‌دهد.هر جایگزینی را با استفاده از
Table.ReplaceValueبرای مقدار شاخص جاری اعمال می‌کند، سپس حالت را با افزایشIndexبه‌روزرسانی می‌کند.پس از کامل شدن همه جایگزینی‌ها، جدول نهایی را با استفاده از
List.Last(myFunction)بازمی‌گرداند.
این ساختار به List.Generate اجازه می‌دهد تا جایگزینی‌ها را تا زمانی که همه موارد مشخص شده روی ستون‌های مورد نظر اعمال شوند، ادامه دهد.
وقتی این تابع را روی یک کوئری اعمال می‌کنید، شکل آن به صورت زیر خواهد بود:
let
// --------------------Function Definition ------------------------------
fxReplaceValues =
( Table as table, listOfReplacements as list, listOfColumns as list ) =>
let
myFunction =
List.Generate(
() =>
[
myTable = Table,
Index = 0,
Replacements = listOfReplacements
],
each [Index] <= List.Count( [Replacements] ),
each
[
myTable =
Table.ReplaceValue(
[myTable],
Replacements{ [Index] }{0},
Replacements{ [Index] }{1},
Replacer.ReplaceText,
listOfColumns
),
Index = [Index] + 1,
Replacements = [Replacements]
],
each [myTable]
),
Result = List.Last( myFunction )
in
Result,
// -----------------------Query Start---------------------------------
Source = MyData,
ReplaceValues =
fxReplaceValues(
Source,
{
{" (GDR)",""},
{ " (FRG)","" },
{ "People's Republic of the Congo","Congo" },
{ "Republic of the Congo","Congo" },
{ "-"," " }
},
{"country_txt"}
)
in
ReplaceValues
همچنین در این ساختار، می‌توانید به سادگی لیست جایگزینی‌ها و لیست ستون‌هایی که جایگزینی باید روی آن‌ها انجام شود را گسترش دهید.
اگرچه List.Generate ممکن است کمی پیچیده‌تر از List.Accumulate به نظر برسد، اما یک روش قدرتمند و انعطاف‌پذیر برای انجام چندین جایگزینی با عملکرد بهتر، به ویژه برای تبدیل‌های داده‌ای بزرگ، ارائه می‌دهد.
پس از آن، می‌توانید جدول را با استفاده از تابع Table.ToList به یک لیست از لیست‌ها تبدیل کنید.
نتیجه‌گیری
جایگزینی مقادیر در Power Query کار ساده‌ای است. رابط کاربری دکمه‌ای راحت فراهم می‌کند که برای هر جایگزینی یک مرحله جداگانه تولید می‌کند. اگر قابلیت نگهداری (maintainability) برای شما اهمیت دارد، بهتر است از روش‌های جایگزینی استفاده کنید که همه جایگزینی‌ها را در یک لیست منسجم قرار می‌دهند، مانند List.Accumulate و List.Generate.
دیدگاهتان را بنویسید