הוספת הערות או מידע לטבלאות שמתקבלות מ Power Query

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

אז מה יש לנו?

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

מצד ימין הטבלה המקורית, ומצד שמאל העותק שלה, אחרי שעבר דרך ה Power Query.

תוספות לעותק שנוצר ב Power Query

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

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

שינוי בנתונים

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

אז עכשיו כל מה שנשאר לנו לעשות זה לרענן את ה PQ, וזו התוצאה שהתקבלה:

יוסטון, ווי הב א פרובלם:

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

אז מה בעצם קרה פה?

הוספת עמודות לטבלה חכמה, שהמקור שלה הוא Power Query לא קושרת את העמודות החדשות אל הרשומות, ולא קשורה בשום צורה שהיא ל Power Query
שמעדכן, במקרה שלנו, רק את עמודות E:G
ולכן, בעוד שטבלת המקור עדכנה את עמודות G:E, עמודת ההערות שהוספנו לא הושפעה, וכעת אין התאמה בין הטקסטים שמופיעים בה, לבין ה PQ.

ומה הפתרון?

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

כעת נטען את טבלת ההערות ל PQ ונמזג את השאילתות:

נבחר את סוף הצירוף, במקרה שלנו – חיצוני שמאלי, נרחיב את העמודה החדשה

ונטען לגיליון:

בשלב הזה נתקן את הנתונים בטבלת ההערות, בהתאם לטבלת המקור המעודכנת:

נרענן את השאילתה

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

ולסיום, מתי כן נוכל להוסיף עמודות ישירות לטבלה, בלי להעביר אותן דרך העורך?

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