המלכודת הקלאסית: למה אסור להוסיף נתונים ידנית לטבלה של Power Query

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

הדוגמה הבאה תעזור להמחיש את הבעיה

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

הטעות הנפוצה: הוספת נתונים ידנית בגיליון

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

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

הרגע שבו הכל משתבש

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

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

למה זה קורה?

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

המידע הזה לא כולל את עמודת ההערות, שנכתבה על גבי טבלת הפלט (ולא בטבלה שמהווה מקור נתונים).
לאחר הרענון, נמחק המידע הישן ובמקומו מוצג הפלט החדש.
ומה קורה לעמודת הערות הידנית? שום דבר. היא נשארת בדיוק באותם תאים פיזיים שבהם הקלדנו אותה, במקרה שלנו – בתא D4.

הפתרון הנכון: הפכו את ההזנה הידנית לחלק מהתהליך

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

שלב ראשון: יצירת טבלת הזנת הנתונים (הטבלה הידנית)

בגיליון נפרד, נקים טבלה חדשה, שבה נזין את הנתונים הידניים שלנו: המק"ט וההערות.
נהפוך אותה לטבלת חכמה (Ctrl+T) וניתן לה שם משמעותי, למשל tbl_Notes.

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

שלב שני: טעינת הטבלה הידנית ל-Power Query

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

שלב שלישי: מיזוג השאילתות

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

נסמן את השדה המשותף (מק"ט), נאשר ובטבלה שתתקבל נרחיב את המידע הרצוי:

וזו התוצאה שהתקבלה:

וכמה מילים לסיכום

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

שאלות ותשובות על קיבוע הערות ב Power Query

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

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