תיקייה אחת, שנים עשר קבצים, לחיצה אחת. כך נבנה כל דוח חודשי שמאחד ייצוא מהמערכת.
אבל בחודש שבו אחד הקבצים חוזר עם עמודה נוספת, הטבלה המאוחדת תיראה בדיוק כמו בחודש שעבר. בלי שגיאה, בלי אזהרה, ובלי העמודה.
ההמלצה הרשמית של מיקרוסופט למצב הזה היא לסדר את המקור, וזו עצה שאי אפשר ליישם מול מערכת שכר או מול ספק. אז נדבר על מה שכן אפשר: תיקון של שלב אחד, שגורם לעמודה להגיע מלכתחילה.
רגע, מה זה בכלל Power Query
זהו מנוע שליפת הנתונים שיושב בתוך Excel בכרטיסיית הנתונים, ובתוך Power BI. אתם מגדירים פעם אחת מאיפה הנתונים מגיעים ומה עושים איתם, והוא חוזר על אותן פעולות בכל רענון.
החיבור לתיקייה הוא אחד השימושים הנפוצים ביותר שלו אצל אנשי כספים. במקום לפתוח שנים עשר קבצים ולהדביק אחד אחרי השני, מצביעים על התיקייה ומקבלים טבלה אחת.
הסיבה שהעמודה נעלמת
היא כתובה בתיעוד של מיקרוסופט, והיא לא סודית. פשוט אף אחד לא קורא את השורה הזו.
בעמוד התיעוד של מחבר התיקייה כתוב שכאשר מאחדים קבצים, כל הקבצים בתיקייה ובתת התיקיות מטופלים באותה דרך, ושהאופן שבו הם מטופלים נקבע לפי קובץ הדוגמה שבחרתם. ברירת המחדל היא הקובץ הראשון ברשימה.
זה חצי מהמנגנון. קובץ אחד מגדיר את הצורה, ואחד עשר האחרים נכנסים לתבנית הזו.
החצי השני נמצא בשלב האחרון של השאילתה, והוא החלק שכדאי להכיר, כי שם גם נמצא הפתרון.
כשמסיימים איחוד, מיקרוסופט מתעדת שהיא מפעילה את הפונקציה שנוצרה על כל קובץ בנפרד, ואז מרחיבה את התוצאה לעמודות עליונות. פעולת ההרחבה הזו מקבלת רשימת עמודות מפורשת, ככתוב בתיעוד של הפונקציה שמבצעת אותה, והרשימה נכתבת פעם אחת בלבד לפי מה שנמצא בקובץ הדוגמה ברגע ההקמה.
כלומר העמודה החדשה כן הגיעה. היא הגיעה עד השלב האחרון, ושם לא ביקשו אותה.
מיקרוסופט אפילו מפנה לשם בעצמה. בהערה בסוף העמוד על איחוד קבצים היא כותבת שאם שינוי כלשהו נוגע לשמות עמודות או לסוגי נתונים, כדאי לבדוק את השלב האחרון בשאילתת הפלט.
ובעמוד הכללי על איחוד קבצים מיקרוסופט מנסחת את התנאי במפורש. אפשר לאחד את כל הקבצים בתיקייה כל עוד יש להם אותו סוג קובץ ואותו מבנה, ובסוגריים היא מוסיפה, כולל אותן עמודות. בעמוד על איחוד קבצים מופרדים בפסיקים היא חוזרת על זה בניסוח חד יותר, ואומרת שכדי לאחד קבצים הכרחי שלכולם יהיו אותו מבנה ואותה סיומת.
התנאי הזה מנוסח כהמלצה. בפועל הוא לא בדיקה. שום דבר במערכת לא עוצר אתכם כשהוא מופר.
ההבדל בין שגיאה להשמטה
זו הנקודה שהופכת את הנושא הזה מטכני לבקרתי.
כשקובץ נכשל בפתיחה, מתקבלת שגיאה. אולי היא מעצבנת, אבל היא נראית, ומישהו בודק אותה.
כשקובץ מכיל עמודה שאינה קיימת בקובץ הדוגמה, לא מתקבל כלום. העמודה פשוט אינה מגיעה לטבלה המאוחדת. הדוח נחתם, נשלח, ונראה סביר.
בתיעוד הפונקציה שקוראת קובץ מופרד בפסיקים מיקרוסופט מנסחת את זה בשפה של המנוע עצמו. אם מספר העמודות שהוגדר נמוך מזה שנמצא בקלט, העמודות הנוספות יתעלמו. ואם הוא גבוה יותר, העמודות הנוספות יוחזרו ריקות.
יש לזה גם גרסה מתמשכת. בתיעוד של מחבר הטקסט כתוב שבייבוא קובץ מופרד בפסיקים נוצר שלב שמקבע את מספר העמודות שהיה בייבוא הראשון, ושרענון אינו כולל עמודות מעבר למספר הזה. כלומר גם אם המערכת שממנה אתם מייצאים הוסיפה שדה, השאילתה תמשיך להחזיר את מה שהיה נכון ביום שהקמתם אותה.
הפעולה השנייה, זו שמתנהגת הפוך
באותה קבוצה ברצועה יושבת פעולה נוספת, הוספת שאילתות. היא נראית כמו וריאציה על אותו רעיון, וההתנהגות שלה הפוכה לגמרי.
מיקרוסופט מתעדת שההוספה מרכיבה את הצורה של הטבלה החדשה מכל כותרות העמודות של כל הטבלאות יחד, מתאימה לפי שם העמודה ולא לפי מיקומה, ומציגה ריק כשלטבלה אחת חסרה עמודה שקיימת באחרת.
הריק הזה הוא לא תקלה. הוא הדיווח.
וכדאי להחזיק את ההבחנה הזו, כי היא המפתח לפתרון בהמשך. אותה התנהגות בדיוק זמינה גם בתוך איחוד קבצים מתיקייה, ברגע שיודעים איזה שלב להחליף.
סדר האבחון
שלוש בדיקות, וכולן לוקחות פחות מרבע שעה בהקמה.
ראשית, ספירת שורות. כמה שורות יש בטבלה המאוחדת, וכמה יש בסך הקבצים בתיקייה. אם המספרים נפרדו, יש קובץ שלא נכנס או שנכנס חלקית.
שנית, ספירת קבצים. מיקרוסופט מציעה לאמת את האיחוד דרך סינון עמודת שם הקובץ, ומזהירה באותו עמוד שרשימת הערכים עלולה להיות חלקית עד שלוחצים על טעינת ערכים נוספים. אל תסתפקו במבט על הרשימה, ספרו אותה.
שלישית, בדיקת האפשרות לדילוג. בחלון האיחוד יש אפשרות לדלג על קבצים עם שגיאות, שמוציאה מהתוצאה כל קובץ שנכשל. אם מישהו סימן אותה פעם, קובץ שלם יורד בשקט בכל חודש.
מה שמיקרוסופט ממליצה
ההמלצה הרשמית קצרה, והיא מופיעה כמעט באותו ניסוח בשלושה עמודי תיעוד שונים. אפשר לאחד את כל הקבצים בתיקייה כל עוד יש להם אותו סוג ואותו מבנה, כולל אותן עמודות.
כלומר תסדרו את המקור. אם המערכת מייצאת קבצים בעלי מבנה משתנה, ההמלצה היא לתקן את זה לפני שלב האיחוד.
זו עצה נכונה, והיא גם לא ישימה ברוב הארגונים. אף אחד מכם לא ישנה את פורמט הייצוא של מערכת השכר כי שאילתה באקסל מעדיפה מבנה קבוע.
ולכן השאלה האמיתית אינה מה ממליצים, אלא איך עוקפים.
ואיך עוקפים את זה בפועל
שלוש דרכים, לפי מידת הקושי של המקור. כולן נשענות על התנהגות מתועדת, ולכולן אותו רעיון: להוציא את רשימת העמודות מהיד ולתת למנוע לגלות אותה בכל רענון.
הדרך הראשונה, לאחד במקום להרחיב
זו הדרך הנקייה, והיא שינוי של שלב אחד.
במקום להשאיר את שלב ההרחבה שנוצר אוטומטית, מוחקים אותו ומאחדים את עמודת הטבלאות ישירות. הפונקציה שמבצעת איחוד כזה מתועדת בפירוש: היא מחזירה טבלה שמבנה השורות שלה הוא איחוד של כל סוגי הקלט. בדוגמה השנייה בעמוד התיעוד שלה מיקרוסופט מאחדת שלוש טבלאות עם מבנים שונים לגמרי, והתוצאה כוללת את כל העמודות מכולן, כשכל טבלה מקבלת ריק בעמודות שלא היו לה.
זו בדיוק ההתנהגות שרציתם. עמודה שקיימת רק בקובץ אחד מגיעה, ואחד עשר הקבצים האחרים מציגים ריק במקומה. הריק נראה לעין, ולכן הפער מדווח במקום להיעלם.
הדרך השנייה, רשימת עמודות שמחשבת את עצמה
לפעמים רוצים דווקא להשאיר את ההרחבה, כדי לשלוט בסדר העמודות או כדי לשמור עמודות עזר כמו שם הקובץ.
במקרה הזה משאירים את השלב האחרון ומחליפים רק את רשימת העמודות הכתובה בו ברשימה שמחושבת בזמן ריצה. יש פונקציה מתועדת שמחזירה את שמות העמודות של טבלה כרשימה, ויש פונקציה מתועדת שמאחדת רשימות ומסירה כפילויות. שתיהן יחד מייצרות את רשימת כל העמודות שקיימות בכל הקבצים, בכל רענון מחדש.
התוצאה זהה לדרך הראשונה, והשליטה גדולה יותר. המחיר הוא שורה אחת בעורך המתקדם.
הדרך השלישית, כשגם השמות שונים
שתי הדרכים הקודמות פותרות עמודה חסרה. הן לא פותרות מצב שבו אותו שדה נקרא בקובץ אחד סכום ובקובץ אחר סה"כ.
כאן הפתרון הוא להפסיק להתייחס לכותרות כמבנה. בתוך שאילתת הדוגמה, זו שרצה על כל קובץ, מבטלים את הציר ומקבלים שתי עמודות בלבד: שם השדה וערכו. כל הקבצים נעשים בעלי אותו מבנה בדיוק, כי לכולם שתי עמודות. מאחדים, ואז ממפים את שמות השדות לשמות אחידים ומחזירים את הציר בסוף.
זו העבודה הרבה ביותר מהשלוש, והיא היחידה ששורדת מקור שמשנה כותרות. בייצוא ממערכת שמתחלפת בה גרסה אחת לשנה, זה מה שאני בונה.
ומה שנשאר נכון בכל מקרה
בחרו את קובץ הדוגמה במודע. בחלון האיחוד יש רשימה נפתחת שמאפשרת לבחור קובץ אחר במקום הראשון, וכדאי לבחור את המלא ביותר.
ואל תלחצו על איחוד מיד. מיקרוסופט ממליצה בעצמה לבחור קודם בטרנספורמציה של הנתונים, לסנן שם את רשימת הקבצים, ורק אז לאחד.
שימו לב מה קרה לרשימת האבחון אחרי שהחלפתם שלב אחד. ספירת העמודות כבר אינה נחוצה, כי המנגנון עצמו מפסיק להשמיט. ספירת השורות וספירת הקבצים נשארות, כי הן תופסות דברים אחרים לגמרי.
הכלל
איחוד קבצים באקסל אינו פעולה שמביאה את הנתונים. הוא פעולה שמביאה את הנתונים שמתאימים לצורה של קובץ אחד, וזו ברירת מחדל ולא גזירה.
ההמלצה הרשמית היא לסדר את המקור. במציאות המקור אינו בשליטתכם, ולכן העבודה היא להוציא את רשימת העמודות מהיד ולתת למנוע לגלות אותה בכל רענון.
זה שלב אחד בשאילתה. מי שמכיר אותו מפסיק לספור עמודות, כי אין יותר מה לספור.
והשאלה שנשארה פתוחה
נניח שהחלפתם את השלב, והטבלה המאוחדת מביאה עכשיו כל עמודה מכל קובץ. פתרתם את ההשמטה.
וכאן מתחילה השאלה שהמאמר הזה לא עונה עליה. שאילתה שמגלה את העמודות בעצמה גם משנה את הצורה שלה בלי לשאול, ודוח שהמבנה שלו זז בין חודשים שובר טבלאות ציר, נוסחאות ומדדים שיושבים אחריו. איפה עובר הגבול בין שאילתה שמסתגלת לבין שאילתה שמתריעה, מה נכון לקבע במכוון גם כשאפשר לא לקבע, ואיך מציגים את השינוי הזה להנהלה כך שהוא ייראה כשדרוג בקרה ולא כפרויקט טכני.
את הדיון הזה אני מנהל בתוך מועדון The UNIQUE Way, ושם נמצא גם המדריך המלא לשלוש הדרכים, עם הנוסח המדויק להעתקה של כל אחת מהן. ההצטרפות חינם.
