שנים עשר קבצים בתיקייה, אחד לכל חודש. בכולם הכותרת של עמודת הכסף היא סכום.
על פניו אין כאן מה לבדוק. אבל בקובץ של מרץ המערכת המייצאת כתבה סה"כ, ובקובץ של אפריל נשאר רווח אחד אחרי המילה סכום.
ברענון הבא השאילתה שמאחדת את כל השנה נעצרת. ההודעה בחלונית הצהובה אומרת שהעמודה סכום לא נמצאה בטבלה, ואתם יודעים שהיא שם, כי אתם רואים אותה בקובץ.
היא אכן שם. היא פשוט נקראת קצת אחרת, ושלב אחד בשאילתה מחפש אותה לפי השם המדויק. את השלב הזה לא כתבתם. הוא נוצר לבד, והשארתם אותו.
רגע, מה זה בכלל Power Query
זהו מנוע שליפת הנתונים שיושב בתוך Excel בכרטיסיית הנתונים, ובתוך Power BI. מגדירים פעם אחת מאיפה הנתונים מגיעים ומה עושים איתם, והוא חוזר על אותן פעולות בכל רענון.
איחוד טבלאות ב-Power Query מתיקייה הוא השימוש הנפוץ ביותר שלו אצל אנשי כספים. מצביעים על התיקייה, לוחצים על איחוד, ומקבלים טבלה אחת מכל הקבצים.
למה שם אחד עוצר את כל הקבצים
כשלוחצים על איחוד, מיקרוסופט מתעדת שנוצרות שתי שאילתות עזר. שאילתת דוגמה שמבצעת את כל השלבים על קובץ אחד, ופונקציה שמקושרת אליה ורצה על כל קובץ בתיקייה. כל שלב שנמצא בשאילתת הדוגמה חל על כל הקבצים.
בעמוד על איחוד קבצים מופרדים בפסיקים כתוב מה קורה אחרי שהשורה הראשונה הופכת לכותרות. המנוע מזהה בעצמו את סוגי הנתונים, ומוסיף שלב של שינוי סוג.
השלב הזה מחזיק את שמות העמודות בכתב, מילה במילה, כפי שהופיעו בקובץ הדוגמה ביום שבו לחצתם על איחוד. בקובץ שבו השם שונה, אפילו ברווח אחד, השלב מחפש שם שאינו קיים.
בעמוד על התמודדות עם שגיאות מיקרוסופט מתארת בדיוק את המקרה הזה: שלב שמפנה ישירות לשם של עמודה שאינה קיימת, והודעה שהעמודה לא נמצאה בטבלה. שגיאה ברמת השלב, לפי אותו עמוד, מונעת מהשאילתה להיטען.
ובהערה בעמוד על איחוד קבצים היא מפנה לשם בעצמה. אם שינוי כלשהו נוגע לשמות עמודות או לסוגים, כתוב שם, בדקו את השלב האחרון בשאילתת הפלט, כי הוספת שלב שינוי סוג עלולה ליצור שגיאה שמונעת את הצגת הטבלה.
ההבדל בין שבירה לבין השמטה
שגיאה שעוצרת את הדוח מעצבנת, אבל היא המקרה הטוב. מישהו רואה אותה.
בחלון האיחוד יש אפשרות לדלג על קבצים עם שגיאות, ולפי התיעוד היא מוציאה מהתוצאה הסופית כל קובץ שנכשל. אם מישהו סימן אותה פעם כדי שההודעה תפסיק להופיע, הקובץ של מרץ לא עוצר יותר כלום. הוא פשוט לא נמצא בטבלה, ודוח שחסר בו חודש שלם נראה תקין.
לפני שבועיים וחצי כתבתי על עמודה חדשה שנעלמת באיחוד קבצים בלי שגיאה. זה אותו מנגנון מהצד השני: שם שנכתב פעם אחת לפי קובץ אחד, וקובץ אחר שלא התאים לו.
סדר האבחון
שלוש בדיקות, לפני שמשנים משהו.
פתחו את שאילתת הדוגמה. היא נמצאת בקבוצת שאילתות העזר. עברו על השלבים וסמנו כל שלב שכתוב בו שם של עמודה. כמעט תמיד זה שלב שינוי הסוג, ולפעמים גם שינוי שם או הסרת עמודות.
קראו את פרטי השגיאה. מיקרוסופט מתעדת שבפרטים מופיע השם שהשלב חיפש. השוו אותו לכותרת בקובץ שנכשל, תו אחרי תו.
בדקו את האפשרות לדילוג. אם היא מסומנת, ספרו את הקבצים בתוצאה מול הקבצים בתיקייה.
מה שמיקרוסופט ממליצה
בעמוד על שגיאות, הפתרון שמוצע בדוגמה הוא להסיר את השלב שמפנה לשם שהשתנה, כשהשם הנכון כבר מגיע מהקובץ. ובעמוד על איחוד קבצים התנאי מנוסח מראש: אפשר לאחד את כל הקבצים בתיקייה כל עוד יש להם אותו סוג ואותו מבנה, כולל אותן עמודות.
שתי העצות נכונות, ושתיהן מתקנות את החודש הזה. מול מערכת שכר או מול ספק אף אחד לא מבטיח לכם אותן עמודות, ובשינוי הכותרת הבא תפתחו את השאילתה שוב.
ואיך עוקפים את זה בפועל
הרעיון הוא להפסיק לתת לכותרות שבקובץ להיות המבנה. מוסיפים ארבעה שלבים לשאילתת הדוגמה, מיד אחרי קידום הכותרות ולפני כל שלב אחר, וכולם נשענים על פונקציות מתועדות.
ראשון, לנקות את השמות
הפונקציה Table.TransformColumnNames מפעילה פונקציה על כל שם של עמודה. יחד עם Text.Trim, שמסירה רווחים בתחילת הטקסט ובסופו, רווח בסוף כותרת מפסיק להיות הבדל.
שני, למפות שמות נרדפים
טבלה של שתי עמודות בגיליון: השם שמגיע בקובץ, והשם האחיד שלכם. הפונקציה Table.RenameColumns מקבלת את הרשימה הזו, ובתיעוד שלה כתוב ששם שאינו קיים מחזיר שגיאה, אלא אם מוסיפים את הפרמטר MissingField.Ignore. איתו, אותה רשימה עובדת על כל קובץ, גם על קובץ שבו מופיע רק חלק מהשמות.
היתרון הגדול כאן אינו טכני. כשהמערכת תחליף כותרת שוב, מי שמתחזק את הדוח מוסיף שורה בגיליון, ולא פותח את עורך השאילתות.
כלל אחד לטבלה: אסור ששני שמות שמופיעים באותו קובץ ימופו לאותו שם אחיד. שמות העמודות חייבים להיות ייחודיים, ושינוי שם לשם שכבר קיים מחזיר שגיאת התנגשות.
שלישי, לקבע את רשימת העמודות
הפונקציה Table.SelectColumns מחזירה רק את העמודות שביקשתם, בסדר שביקשתם. בלי פרמטר נוסף, עמודה חסרה היא שגיאה. עם MissingField.UseNull, לפי הדוגמה בתיעוד, העמודה החסרה נוצרת ריקה. כל קובץ יוצא מהשלב הזה עם אותן עמודות בדיוק.
רביעי, רק עכשיו לשנות סוגים
זו אותה פעולה שנוצרה לבד ושברה את השאילתה, וההבדל היחיד הוא המקום שלה. אחרי השלב השלישי כל עמודה קיימת בכל קובץ, ולכן שינוי הסוג כבר לא יכול להיתקל בשם חסר. מוחקים את השלב המקורי, וכותבים אותו מחדש כאן.
שימו לב לעמודת המחלקה בטבלה המאוחדת. היא קיימת רק בקבצים החדשים, ובישנים היא ריקה. הריק הזה אינו תקלה. הוא הדיווח.
הכלל
שאילתה שיודעת את שמות העמודות בעל פה תישבר ביום שמישהו יקליד רווח.
השמות האחידים שלכם הם המבנה. הכותרות שבקובץ מביאות נתונים, ולא מחליטות איך הטבלה תיראה.
והשאלה שנשארה פתוחה
שלבי הנרמול פותרים את השבירה, ופותחים בעיה הפוכה. כותרת חדשה לגמרי, שאינה ברשימה האחידה ואינה בטבלת המיפוי, נזרקת בשלב השלישי בלי מילה. איך בונים שאילתה שמדווחת על כל שם שלא זוהה לפני שהדוח נשלח, בלי לחזור לשאילתה שנשברת, היא השאלה שהמאמר הזה לא עונה עליה.
את הדיון הזה אני מנהל בתוך מועדון The UNIQUE Way, ושם נמצאת גם ערכת הנרמול המלאה: ארבעת השלבים בנוסח להעתקה, טבלת המיפוי, שאילתה שמתריעה על כל כותרת שלא זוהתה, טבלת תקלות ורשימת הבדיקה לפני שמחליפים שאילתה חיה. ההצטרפות חינם.
