תוכן
אם אתה לפחות קצת מכיר את הביטויים הרגולריים, אז אתה לא צריך לפרסם אותם. אם אתה לא ממש בנושא, אז ביטויים רגולריים (ביטויים רגולריים = RegExp = "regexps" = "רגילים") היא שפה שבה, באמצעות תווים וכללים מיוחדים, מחפשים את המחרוזות הדרושות בטקסט, הן נשלפות או הוחלף בטקסט אחר. זהו כלי חזק ויפה מאוד, בסדר גודל עדיף על כל שאר דרכי העבודה עם טקסט.
כבר תיארתי בפירוט ועם שלל דוגמאות מהחיים כיצד ניתן להוסיף תמיכה בביטויים רגולריים לאקסל באמצעות פקודות מאקרו פשוטות – אם לא קראת את המאמר הזה, אני ממליץ בחום לקרוא אותו לפני שתמשיך. תגלו הרבה דברים חדשים, אני מבטיח 🙂
עם זאת, השאלה נותרה פתוחה - כיצד להוסיף את היכולת להשתמש בביטויים רגולריים ב-Power Query? Power Query, כמובן, טוב בפני עצמו ויכול לעשות הרבה עם טקסט (חיתוך, הדבקה, ניקוי וכו'), אבל אם היית יכול לחצות את זה עם כוחם של ביטויים רגולריים, זה היה פשוט פצצה.
למרבה הצער, אין פונקציות מובנות לעבודה עם RegExps ב- Power Query, והעזרה הרשמית של Microsoft ותמיכה טכנית עונים על שאלה זו בשלילה. עם זאת, יש דרך לעקוף את המגבלה הזו 🙂
מהות השיטה
הרעיון המרכזי הוא פשוט לחרפה.
ברשימת יכולות Power Query המובנות, יש פונקציה עמוד אינטרנט. התיאור של פונקציה זו באתר העזרה הרשמי של Microsoft הוא תמציתי ביותר:
בתרגום, זה יהיה: "מחזיר את התוכן של מסמך ה-HTML בחלוקה למבנים המרכיבים שלו, כמו גם ייצוג של המסמך המלא והגוף שלו לאחר הסרת התגים." כך-כך תיאור, בכנות.
בדרך כלל פונקציה זו משמשת בעת ייבוא נתונים מהאינטרנט ומוחלפת אוטומטית, למשל, כאשר אנו בוחרים בכרטיסייה נתונים פיקוד מהאינטרנט (נתונים - מהאינטרנט). אנו נותנים לפונקציה דף אינטרנט כארגומנט, והיא מחזירה לנו את התוכן שלה בצורה של טבלאות, לאחר שניקה בעבר את כל התגים.
מה שהעזרה לא אומרת זה בנוסף לשפת הסימון HTML פונקציה עמוד אינטרנט תומך בסקריפטים של JavaScript, שכיום נמצא בכל מקום באתרים באינטרנט. ו-JavaScript, בתורו, תמיד הצליח לעבוד עם ביטויים רגולריים ויש לו פונקציות מובנות עבור RegExps! אז כדי ליישם ביטויים רגולריים ב-Power Query, נצטרך להזין את פונקציות ה-Web.Page כארגומנט לתוכנית JavaScript קטנה שתעשה את כל העבודה עבור Power Query.
איך זה נראה ב-JavaScript טהור
יש הרבה מדריכים מפורטים לעבודה עם ביטויים רגולריים ב-JavaScript באינטרנט (לדוגמה, אחד, שניים).
בקיצור ובפשטות, קוד JavaScript ייראה כך:
כאן:
- var str = 'שלם חשבונות 123 ו-789 עבור נקניק'; - ליצור משתנה str ונקצה לו את טקסט המקור שננתח.
- var pattern = /d+/gi; – ליצור ביטוי רגולרי ולשים אותו במשתנה דפוס.
הביטוי מתחיל בקו נטוי (/).
הביטוי עצמו כאן, למשל, הוא d+ מייצג כל רצף של ספרות.
דרך השבר שאחרי הביטוי, ישנם פרמטרים נוספים לחיפוש (משנים) - ניתן לציין אותם בכל סדר:
- g – פירושו חיפוש גלובלי, כלומר לאחר מציאת התאמה, אין להפסיק, אלא להמשיך בחיפוש עד סוף הטקסט. אם השינוי הזה לא מוגדר, הסקריפט שלנו יחזיר רק את ההתאמה הראשונה (123)
- i – חיפוש ללא קשר לאותיות גדולות
- m - חיפוש מרובה שורות (משמש כאשר טקסט המקור מחולק למספר שורות)
- var result = str.match(pattern).join(';'); - בצע חיפוש בטקסט המקור (str) על ידי הביטוי הרגולרי הנתון (דפוס) ושם את התוצאות במשתנה תוצאה, שרשור אותם עם נקודה-פסיק באמצעות הפקודה להצטרף
- document.write(result); - הצג את התוכן של משתנה התוצאה
כמו כן, שים לב שמחרוזות טקסט (למעט ביטויים רגולריים) ב-JavaScript מוקפות באפוסתרופים, לא במירכאות כפי שהן ב-Power Query או VBA.
בפלט, סקריפט זה ייתן לנו כתוצאה מכך את כל המספרים שנמצאו בטקסט המקור:
123, 789
הקורס הקצר של JavaScript הסתיים, תודה לכולם. מקווה שהבנת את ההיגיון 🙂
נותר להעביר את הבנייה הזו ל-Power Query.
חיפוש וחילוץ פונקציית טקסט לפי ביטוי רגולרי ב-Power Query
אנו עושים את הפעולות הבאות:
1. פתח את Excel וצור שאילתת Power ריקה חדשה בכרטיסייה נתונים - קבל נתונים / צור בקשה - ממקורות אחרים - בקשה ריקה (נתונים - קבל נתונים / שאילתה חדשה - ממקורות אחרים - שאילתה ריקה). אם יש לך גרסה ישנה של Excel 2010-2013 ושל Power Query אין לך מובנה, אבל הותקנה כתוסף נפרד, כל זה יהיה בכרטיסייה שאילתת כוחולא נתונים.
2. בחלון הריק של עורך השאילתות שנפתח, בחלונית הימנית, הזן מיד את שם הפונקציה העתידית שלנו (לדוגמה, fxRegExpExtract)
3. בוא נלך ללשונית תצוגה - עורך מתקדם (תצוגה - עורך מתקדם), אנו מוחקים את כל קוד M של הבקשה הריקה ומדביקים שם את הקוד של פונקציית העל שלנו:
שימו לב לידיים שלכם:
בשורה הראשונה, אנו אומרים שלפונקציה שלנו יהיו שלושה ארגומנטים של טקסט: txt - הטקסט המקורי המנותח, regex - דפוס ביטוי רגיל, תוחם - תו מפריד להצגת תוצאות.
לאחר מכן אנו קוראים לפונקציה עמוד אינטרנט, ויוצרים את קוד ה-JavaScript שתואר לעיל בארגומנט שלו. אנו מדביקים ומחליפים את הארגומנטים המשתנים שלנו בקוד.
רסיס:
[נתונים]{0}[ילדים]{0}[ילדים]{1}[טקסט]{0}
... נדרש כדי "ליפול" לטבלה עם התוצאות שאנו צריכים. הנקודה היא שהפונקציה עמוד אינטרנט כתוצאה מכך, הוא מייצר מספר טבלאות מקוננות שחוזרות על המבנה של דף אינטרנט. ללא חתיכת קוד M זה, הפונקציה שלנו תוציא את זה:
... והיינו צריכים ללחוץ על המילה מספר פעמים טבלתי, ברציפות "נופלים" לטבלאות מקוננות של ילדים בעמודות ילדים:
במקום כל הציטוט הזה, אנו מציינים מיד בקוד של הפונקציה שלנו איזו טבלה ועמודה מקוננות (טקסט) אנחנו צריכים.
כאן, למעשה, כל הסודות. נותר ללחוץ על הכפתור סיום בחלון עורך מתקדם, שבו הכנסנו את הקוד שלנו, ותוכלו להמשיך לטעים ביותר - נסו את הפונקציה שלנו בעבודה.
הנה כמה דוגמאות זרעים.
דוגמה 1. שליפת מספר החשבון והתאריך מתיאור התשלום
יש לנו דף חשבון בנק עם תיאור (מטרה) של תשלומים, שבו אתה צריך לשלוף את המספרים והתאריכים של חשבוניות ששולמו לעמודות נפרדות:
אנו טוענים את הטבלה ל-Power Query בדרך הסטנדרטית נתונים - מטבלה/טווח (נתונים - מ-Tמסוגל/רמַלְאָך).
לאחר מכן נוסיף עמודה מחושבת עם הפונקציה שלנו via הוסף עמודה - התקשר לפונקציה מותאמת אישית (הוסף עמודה - הפעל פונקציה מותאמת אישית) והזן את הטיעונים שלו:
כביטוי רגיל (טיעון regex) תבנית בה אנו משתמשים:
(ד{3,5}|d{2}.d{2}.d{4})
... מתורגם לשפה אנושית כלומר:
מספרים מ-3 עד 5 ספרות (מספרי חשבון)
or
קטעים מהצורה "מספר 2 סיביות - נקודה - מספר 2 סיביות - נקודה - מספר 4 סיביות", כלומר, תאריכים בצורת DD.MM.YYYY.
בתור תו מפריד (טיעון תוחם) הזן נקודה-פסיק.
לאחר לחיצה על OK פונקציית הקסם שלנו מנתחת את כל הנתונים הראשוניים לפי הביטוי הרגולרי שלנו ויוצרת עבורנו עמודה עם המספרים שנמצאו והתאריכים של החשבוניות:
נותר להפריד אותו באמצעות נקודה-פסיק באמצעות הפקודה בית - עמודה מפוצלת - לפי מפריד (בית - עמודה מפוצלת - לפי מפריד) ונקבל את מה שרצינו:
יופי!
דוגמה 2: חלץ כתובות אימייל מטקסט
נניח שיש לנו את הטבלה הבאה בתור נתונים ראשוניים:
... מהמקום בו אנחנו צריכים לשלוף את כתובות האימייל שנמצאו שם (לצורך הבהירות, הדגשתי אותן באדום בטקסט).
כמו בדוגמה הקודמת, אנו טוענים את הטבלה ל-Power Query בדרך הסטנדרטית באמצעות נתונים - מטבלה/טווח (נתונים - מ-Tמסוגל/רמַלְאָך).
לאחר מכן נוסיף עמודה מחושבת עם הפונקציה שלנו via הוסף עמודה - התקשר לפונקציה מותאמת אישית (הוסף עמודה - הפעל פונקציה מותאמת אישית) והזן את הטיעונים שלו:
ניתוח כתובות דוא"ל הוא משימה קשה יותר ויש חבורה של ביטויים קבועים בדרגות שונות של סיוט כדי לפתור אותה. השתמשתי באחת האפשרויות הפשוטות - לא אידיאלי, אבל די עובד ברוב המקרים:
[w|.|-]*@w*.[w|.]*
כמפריד (תוחם) אתה יכול להזין נקודה-פסיק ורווח.
לחץ על OK ואנו מקבלים עמודה עם כתובות דואר אלקטרוני המופקות מהטקסט המקורי "דייסה":
קֶסֶם!
PS
כמו שאומר הפתגם: "אין דבר טוב כזה שאי אפשר היה לעשות אפילו טוב יותר." Power Query מגניב בפני עצמו, ובשילוב עם ביטויים רגולריים, הוא נותן לנו כוח וגמישות לא מציאותיים לחלוטין בעיבוד נתוני טקסט. אני מקווה שמיקרוסופט תוסיף מתישהו תמיכה ב-RegExp בעדכוני Power Query ו-Power BI וכל הריקודים לעיל עם טמבורין יהפכו לנחלת העבר. ובכן, לעת עתה, כן.
אני גם רוצה להוסיף שנוח לשחק עם ביטויים רגולריים באתר https://regexr.com/ – ממש בעורך המקוון. שם במדור דפוסי קהילה יש מספר עצום של עונות רגילות מוכנות לכל אירוע. ניסוי - כל הכוח של ביטויים רגולריים עומד כעת לשירותך ב-Power Query!
- מהם ביטויים רגולריים (RegExp) וכיצד להשתמש בהם באקסל
- חיפוש טקסט מעורפל ב-Power Query
- הרכבת טבלאות מקבצים שונים באמצעות Power Query