החלפת טקסט בכמות גדולה ב-Power Query עם פונקציית List.Accumulate

איך להחליף במהירות ובכמות גדולה את הטקסט לפי רשימת הפניות בנוסחאות - כבר סידרנו. עכשיו בואו ננסה לעשות זאת ב- Power Query.

כפי שקורה לעתים קרובות לבצע משימה זו היא הרבה יותר קלה מאשר להסביר למה זה עובד, אבל בואו ננסה לעשות את שניהם 🙂

אז יש לנו שני טבלאות דינמיות "חכמות" שנוצרו מטווחים רגילים עם קיצור מקלדת Ctrl+T או צוות בית - עיצוב כטבלה (בית - עיצוב כטבלה):

התקשרתי לשולחן הראשון נתונים, השולחן השני - מַדרִיךבאמצעות שדה שם טבלה (שם שולחן) כרטיסייה בנאי (לְעַצֵב).

משימה: החלף בכתובות בטבלה נתונים כל המופעים מעמודה למצוא מדריך לעמיתיהם הנכונים המקבילים מהטור תחליף. שאר הטקסט בתאים צריך להישאר ללא נגיעה.

שלב 1. טען את הספרייה לתוך Power Query והפוך אותה לרשימה

לאחר הגדרת התא הפעיל לכל מקום בטבלת ההפניות, לחץ על הכרטיסייה נתונים (תַאֲרִיך)או בכרטיסייה שאילתת כוח (אם יש לך גרסה ישנה של Excel והתקנת את Power Query כתוסף בלשונית נפרדת) על הכפתור משולחן/טווח (מטבלה/טווח).

טבלת ההפניה תיטען לתוך עורך השאילתות של Power Query:

כדי לא להפריע, שלב נוסף אוטומטית סוג שונה (סוג שונה) בחלונית הימנית, ניתן למחוק את השלבים שהוחלו בבטחה, ולהשאיר רק את השלב מָקוֹר (מָקוֹר):

כעת, כדי לבצע טרנספורמציות והחלפות נוספות, עלינו להפוך את הטבלה הזו לרשימה (רשימה).

סטייה לירית

• טבלתי הוא מערך דו מימדי המורכב ממספר שורות ועמודות.
• הקלט (הקלט) – מחרוזת מערך חד מימדית, המורכבת ממספר שדות-אלמנטים עם שמות, למשל [שם = "מאשה", מגדר = "ו", גיל = 25]
  
• רשימה – עמודת מערך חד מימדית, המורכבת ממספר אלמנטים, למשל {1, 2, 3, 10, 42} or { "אמונה תקווה אהבה" }
  

כדי לפתור את הבעיה שלנו, נתעניין בעיקר בסוג רשימה.

הטריק כאן הוא שפריטי רשימה ב-Power Query יכולים להיות לא רק מספרים בנאליים או טקסט, אלא גם רשימות או רשומות אחרות. ברשימה (רשימה) כל כך מסובכת, המורכבת מרשומות (רשומות) אנחנו צריכים להפוך את הספרייה שלנו. בסימון תחבירי Power Query (ערכים בסוגריים מרובעים, רשימות בסוגריים מסולסלים) זה ייראה כך:

{

    [ מצא = "St. פטרסבורג", החלף = "St. פטרבורג" ] ,

    [ מצא = "St. פטרסבורג", החלף = "St. פטרבורג" ] ,

    [ מצא = "פיטר", החלף = "St. פטרבורג" ] ,

וכו '

}

טרנספורמציה כזו מתבצעת באמצעות פונקציה מיוחדת של שפת M המובנית ב- Power Query - Table.ToRecords. כדי להחיל אותו ישירות בשורת הנוסחאות, הוסף את הפונקציה הזו לקוד הצעד שם מָקוֹר.

זה היה:

אחרי:

לאחר הוספת הפונקציה Table.ToRecords, המראה של הטבלה שלנו ישתנה - היא תהפוך לרשימת רשומות. ניתן לראות את התוכן של רשומות בודדות בתחתית חלונית התצוגה על ידי לחיצה ברקע התא ליד כל מילה שיא (אבל לא במילה אחת!)

בנוסף לאמור לעיל, הגיוני להוסיף עוד קו אחד - כדי לשמור במטמון (לחצץ) את הרשימה שנוצרה שלנו. פעולה זו תאלץ את Power Query לטעון את רשימת החיפוש שלנו פעם אחת לזיכרון ולא לחשב אותה מחדש כשנגיע אליה מאוחר יותר כדי להחליף אותה. כדי לעשות זאת, עטפו את הנוסחה שלנו בפונקציה אחרת - רשימה. מאגר:

מטמון כזה ייתן עלייה ניכרת מאוד במהירות (פי כמה!) עם כמות גדולה של נתונים ראשוניים שיש לנקות.

זה משלים את הכנת המדריך.

נשאר ללחוץ עליו בית - סגור וטען - סגור וטען ל... (בית — סגור&טען — סגור&טען ל..), בחר אפשרות פשוט צור קשר (יצירת חיבור בלבד) ולחזור לאקסל.

שלב 2. טעינת טבלת הנתונים

הכל נדוש כאן. כמו בעבר עם ספר העיון, אנחנו מגיעים לכל מקום בטבלה, לחץ על הלשונית נתונים לַחְצָן מתוך טבלה/טווח והשולחן שלנו נתונים נכנס ל- Power Query. שלב נוסף אוטומטית סוג שונה (סוג שונה) אתה יכול גם להסיר:

לא נדרשות לעשות איתו פעולות הכנה מיוחדות, ואנחנו עוברים לדבר החשוב ביותר.

שלב 3. בצע החלפות באמצעות הפונקציה List.Accumulate

בואו נוסיף עמודה מחושבת לטבלת הנתונים שלנו באמצעות הפקודה הוספת עמודה - עמודה מותאמת אישית (הוסף עמודה - עמודה מותאמת אישית): והזן את שם העמודה שנוספה בחלון שנפתח (לדוגמה, כתובת מתוקנת) ותפקוד הקסם שלנו רשימה.צבור:

נשאר ללחוץ עליו OK – ואנו מקבלים עמודה עם ההחלפות שבוצעו:

שים לב ש:

• מכיוון ש-Power Query הוא תלוי רישיות, לא הייתה תחליף בשורה הלפני אחרונה, כי בספרייה יש לנו "SPb", לא "SPb".
• אם יש כמה מחרוזות משנה להחלפה בבת אחת בנתוני המקור (לדוגמה, בשורה השביעית צריך להחליף גם "S-Pb" וגם "תשקיף"), אז זה לא יוצר בעיות (בניגוד להחלפה בנוסחאות מ- השיטה הקודמת).
• אם אין מה להחליף בטקסט המקור (שורה 9), אז לא מתרחשות שגיאות (בניגוד, שוב, מהחלפה בנוסחאות).

המהירות של בקשה כזו היא מאוד מאוד הגונה. לדוגמה, עבור טבלה של נתונים ראשוניים בגודל של 5000 שורות, שאילתה זו עודכנה תוך פחות משנייה (ללא חציצה, אגב, כ-3 שניות!)

כיצד פועלת הפונקציה List.Accumulate

באופן עקרוני, זה יכול להיות הסוף (מבחינתי לכתוב, וכדי שתקראו) את המאמר הזה. אם אתה רוצה לא רק להיות מסוגל, אלא גם להבין איך זה עובד "מתחת למכסה המנוע", אז תצטרך לצלול קצת יותר עמוק לתוך חור הארנב ולהתמודד עם הפונקציה List.Accumulate, שעשתה את כל ההחלפה בתפזורת עבוד בשבילנו.

התחביר עבור פונקציה זו הוא:

=List.Accumulate(רשימה, זרע, מַצבֵּר)

איפה

• רשימה היא הרשימה שעליה אנו חוזרים על האלמנטים שלה. 
• זרע - מצב התחלתי
• מַצבֵּר – פונקציה שמבצעת פעולה כלשהי (מתמטית, טקסט וכו') באלמנט הבא ברשימה וצוברת את תוצאת העיבוד במשתנה מיוחד.
  

באופן כללי, התחביר לכתיבת פונקציות ב- Power Query נראה כך:

(argument1, argument2, … argumentN) => כמה פעולות עם ארגומנטים

לדוגמה, פונקציית הסיכום יכולה להיות מיוצגת כך:

(א, ב) => a + b

עבור List.Accumulate, לפונקציית מצבר זו יש שני ארגומנטים נדרשים (ניתן לקרוא להם כל דבר, אבל השמות הרגילים הם היו и נוֹכְחִי, כמו בעזרה הרשמית עבור פונקציה זו, כאשר:

• היו – משתנה שבו התוצאה מצטברת (הערך ההתחלתי שלו הוא זה שהוזכר לעיל זרע)
  
• נוֹכְחִי – הערך החוזר הבא מהרשימה רשימה
  

לדוגמה, בואו נסתכל על שלבי ההיגיון של הבנייה הבאה:

=List.Accumulate({3, 2, 5}, 10, (מצב, זרם) => מצב + זרם)

זה המצטבר האחרון היו הערך הוא List.Accumulate פונקציה ותפוקות כתוצאה מכך:

אם אתה מפנטז קצת, אז באמצעות הפונקציה List.Accumulate, אתה יכול לדמות, למשל, את הפונקציה של Excel CONCATENATE (ב-Power Query, האנלוגי שלה נקרא טקסט.שילוב) באמצעות הביטוי:

או אפילו חפש את הערך המקסימלי (חיקוי של פונקציית ה-MAX של Excel, שנקראת ב-Power Query רשימה.מקסימום):

עם זאת, המאפיין העיקרי של List.Accumulate הוא היכולת לעבד לא רק טקסט פשוט או רשימות מספריות כארגומנטים, אלא אובייקטים מורכבים יותר - למשל, רשימות-מ-רשימות או רשימות-מת-רשומות (שלום, Directory!)

בואו נסתכל שוב על הקונסטרוקציה שביצעה את ההחלפה בבעיה שלנו:

List.Accumulate(מַדרִיך, [כתובת], (מצב,נוכחי) => טקסט.החלף(מצב, נוכחי[מצא], נוכחי[החלף]) )

מה באמת קורה פה?

1. כערך התחלתי (זרע) אנחנו לוקחים את הטקסט המגושם הראשון מהעמודה [כתובת] השולחן שלנו: 199034, St. Petersburg, str. ברינגה, ד. 1
2. ואז List.Accumulate חוזר על רכיבי הרשימה אחד אחד - מדריך. כל רכיב ברשימה זו הוא רשומה המורכבת מזוג שדות "מה למצוא - במה להחליף" או, במילים אחרות, השורה הבאה בספרייה.
3. פונקציית המצבר מכניסה למשתנה היו ערך התחלתי (כתובת ראשונה 199034, St. Petersburg, str. ברינגה, ד. 1) ומבצע עליו פונקציית מצבר – פעולת ההחלפה באמצעות פונקציית M-רגילה טקסט.החלף (אנלוגי לפונקציית SUBSTITUTE של Excel). התחביר שלו הוא:

   Text.Replace( טקסט מקורי, מה אנחנו מחפשים, במה אנחנו מחליפים)

   והנה יש לנו:

• היו היא הכתובת המלוכלכת שלנו, שנמצאת ב היו (מגיעים לשם זרע)
  
• נוכחי [חיפוש] – ערך שדה למצוא מהערך החוזר הבא ברשימה מַדרִיך, שנמצא במשתנה נוֹכְחִי
• נוכחי [החלף] – ערך שדה תחליף מהערך החוזר הבא ברשימה מַדרִיךשוכב פנימה נוֹכְחִי

לפיכך, עבור כל כתובת, מופעל בכל פעם מחזור מלא של ספירה של כל השורות בספרייה, תוך החלפת הטקסט מהשדה [Find] בערך מהשדה [Replace].

מקווה שהבנתם את הרעיון 🙂

• החלפה בכמות גדולה של טקסט ברשימה באמצעות נוסחאות
  
• ביטויים רגולריים (RegExp) ב- Power Query