כשPower Query מתעצל
תקציר המאמר:המאמר מסביר כיצד מנגנון החישוב העצל (Lazy Evaluation) ב-Power Query עלול לגרום לתוצאות שגויות כאשר מסתמכים על מיון והסרת כפילויות לשליפת הנתון העדכני ביותר.
הפתרון המומלץ דרך הממשק הוא יצירת שאילתת עזר המבצעת קיבוץ (Group By) למציאת התאריך המקסימלי, ומיזוגה חזרה לטבלה המקורית. |
לפני כמה זמן בניתי ללקוח קובץ, שדרש לקחת את שער המטבע האחרון מתוך טבלת מטבעות שמתוחזקת בקובץ אחר.
מבנה הטבלה היה: שמות שערי המטבע בעמודה אחת, התאריך בעמודה השנייה והשער בעמודה השלישית (כך שגם התאריכים וגם המטבעות חוזרים על עצמם).
על פניו, הפתרון היה פשוט –
טענתי את נתוני הטבלה אל השאילתה,
מיינתי על פי מטבע ותאריך בסדר יורד, כך שהתאריך האחרון יהיה למעלה,
הסרתי כפילויות כך שעבור כל מטבע יתקבל השער האחרון
ואז החזרתי את הטבלה לשלב שלפני הסרת הכפילויות.
כעת כל מה שהייתי צריכה לעשות זה לקחת את ערך המטבע הנכון, שכבר חושב בשלבים הקודמים.
אבל זה לא מה שקרה:
למרות שאחרי הסרת הכפילויות קיבלתי טבלה מסודרת עם שורה אחת לכל מטבע, שבה השער האחרון הוא זה שנשאר, בפועל – השערים שהתקבלו היו השערים הלא נכונים.
למה זה קרה
אנחנו רגילים לחשוב על חלונית 'שלבים שהוחלו' כעל סדרת פעולות: קודם בצע את הפעולה הראשונה, אחריה את השנייה, וכן הלאה.
אבל בפועל, Power Query משתמש בתלות בין השלבים כדי לחשב את התוצאה, ובמסגרת האופטימיזציה הוא עשוי לשנות את אופן הביצוע ולעתים לדלג על פעולות שאינן נחוצות לטעמו.
מבחינתו, סדר המיון אינו שלב שצריך להתחשב בו אחרי פעולות כמו קיבוץ, מיזוג והסרת כפילויות.
כלומר, הסרת כפילויות לפי מטבע מבקשת להשאיר רשומה אחת לכל מטבע, אבל פעולת המיזוג לא מגדירה שהרשומה שתילקח תהיה מהשלב שבו נשאר רק התאריך המאוחר ביותר.
אנחנו הסקנו את הדרישה הזאת מעצם המיון וסדר השלבים.
מבחינת PQ עצמה, אין התחייבות לבחור דווקא ברשומה הראשונה שמוצגת לפנינו.
Lazy Evaluation
למה זה קורה? בין השאר בגלל מנגנון שנקרא Lazy Evaluation, ובעברית 'חישוב עצל'.
החישוב העצל הוא עיקרון שלפיו חישובים מסוימים מתבצעים רק כאשר יש צורך בתוצאה שלהם.
על פניו, זה עיקרון מועיל והוא חלק מהאופטימיזציה של PQ: למה לחשב משהו שאף אחד לא צריך?
עם זאת, במקרה שלי, החסכון הזה פעל לרעתי.
תחשבו על מחלקת כספים שצריכה לקבל את שערי המטבע הנכונים כדי לעשות את ההמרות, אבל מקבלת הפרשים לא מוסברים בדוחות הכספיים,
רק בגלל ש PQ החליט שהשלב שמבחינתנו הוא הכרחי, לא באמת חשוב!
אז איך בכל זאת מקבלים את השער האחרון?
אחד הפתרונות הפשוטים לעבודה דרך הממשק, הוא שימוש בשאילתת עזר:
ניקח את טבלת שערי המטבע המלאה, נקבץ אותה באמצעות Group By לפי מטבע, ונחשב Max על התאריך.
מכיוון שכל מטבע מופיע פעם אחת בכל תאריך, התאריך המקסימלי מזהה באופן חד‑משמעי את השער המבוקש,
נוכל למזג את טבלת הקיבוץ חזרה עם הטבלה המקורית לפי מטבע ותאריך, כדי לקבל את השער..
בשאילתה הראשית נפנה אל טבלת העזר הזאת, במקום לבצע את הפעולות שלה בתוך אותה השאילתה.
ולסיום, ההערה של Microsoft:
לפי התיעוד של Microsoft, מיון ואחריו הסרת כפילויות עשויים להחזיר את התוצאה הרצויה, אבל אין הבטחה שתישמר דווקא הרשומה הראשונה אחרי המיון.
כלומר, הבעיה שתיארתי לא תופיע בהכרח בכל מקרה. ייתכן שהשיטה עבדה אצלכם היטב, אבל כשחשוב לבחור רשומה מסוימת, כדאי להגדיר את תנאי הבחירה שלה במפורש.