טעינת נתונים מתיקיה כששמות הגיליונות בכל אחד מהקבצים שונה
במאמר הזה למדנו כיצד לטעון קבצים בעלי מבנה זהה שנמצאים באותה התיקיה.
במאמר הזה למדנו כיצד לעשות את התהליך באופן ידני, ללא האוטומציה של Power Query.
אולם, שתי הדרכים מחייבות ששמות הגיליונות בכל הקבצים יהיו זהים, אחרת אקסל לא ידע אילו גיליונות לשלב.
לאחרונה פנה אליי לקוח לביצוע פרויקט, שחלק ממנו היה שילוב קבצים שנמצאים באותה התיקיה.
הבעיה היתה שהמערכת הוציאה את הקבצים בשמות שונים, בהתאם לחודש שבו יוצר הקובץ.
הפתרון הפשוט היה להגיד ללקוח לשנות ידנית את שם הגיליון, אבל לפתרון הזה כמה בעיות עיקריות:
- עבודה נוספת של הלקוח,
- הלקוח עלול לשכוח לתקן את הגיליונות,
- הלקוח עלול להקליד את שמות הגיליונות בצורה מעט שונה בכל פעם.
לכן, חיפשתי פתרון שימנע מהלקוח לשנות את שמות הגיליונות באופן ידני, ויעשה את זה במקומו.
הפתרון שמצאתי דורש אמנם שימוש בשפת M (M Language)
אולם, מדובר בפקודה אחת פשוטה מאוד, שנותנת מענה לכל הבעיות שעלולות לצוץ מכך שהמשתמש יצטרך לדאוג לשינוי שמות הגיליונות בעצמו.
אז מה יש לנו
תיקיה ובה קבצים חודשיים:
בכל קובץ גיליון אחד, ששמו הוא שם החודש:
ובו טבלה שמרכזת את הקריאות החודשיות:
שלב ראשון – שילוב המידע:
בשלב הראשון נטען נתונים מתיקיה:
'יבוא נתונים' > 'מקובץ' > 'מתיקיה'
ננווט אל התיקיה הרצויה ונלחץ על 'פתח'.
תופיע החלונית הבאה:
בחלונית הזאת, במקום לבחור ב'שלב והמר נתונים', שבעצם עושה עבורנו את התהליך, נלחץ על 'המרת נתונים'.
שלב שני – המרת נתונים
לאחר שלחצנו על 'המרת נתונים', נקבל רשימה של כל הקבצים שקיימים בתיקיה:
נוכל לראות את שמות הגיליונות תחת העמודה 'Name', וזוהי העמודה שגורמת לבעיות.
שלב שלישי – הוספת עמודה מותאמת אישית
בשלב הזה עלינו להוסיף עמודה מותאמת אישית, שבה נבקש לחלץ את התוכן של העמודה Content.
הדרך לעשות זאת היא בכתיבה של קוד M:
=Excel.Workbook([Content])
חשוב לדעת ששפת M תלוית רישיות, ולכן צריך להקפיד על אותיות גדולות וקטנות, בזמן כתיבת הפקודה.
קיבלנו עמודה חדשה מותאמת אישית, ובה אובייקט מסוג טבלה עבור כל קובץ:
נלחץ על שני החצים כדי להרחיב את העמודה, ונבקש לחלץ את Data:
בשלב הבא, נסיר את כל העמודות האחרות:
נרחיב את העמודה:
ונקבל טבלה מצטברת שמכילה את כל המידע מכל הקבצים, ללא תלות בשם הגיליון:
טיוב הנתונים
ועכשיו, כל מה שנשאר לעשות זה כמה תיקונים, על פי המידע שמופיע בקובץ שלכם:
בטבלה שלי – קידמתי את השורה הראשונה לשורת הכותרת,
הסרתי את המופעים הנוספים של הכותרות בקובץ,
ולסיום, קבעתי את סוג העמודות הרצוי.
התקבלה טבלה מצטברת, שמכילה את כל המידע:
כדי לוודא שכל החודשים מופיעים, נוכל ללחוץ על לחצן סינון התאריך, ולראות את שמות החודשים:
כמה מילים לסיכום
הדרך שנלמדה כאן היא הדרך שבה אני נוהגת להשתמש כדי לטעון נתונים מתיקיה. יש לה שני יתרונות ברורים על התהליך האוטומטי,
הראשון הוא שאני לא תלויה בשמות הגיליונות, אם בעתיד מישהו יחליט לשנות את שמם,
השני הוא שבמקום השלבים המרובים של שילוב והמרה, נוצרה שאילתה אחת בלבד, שלא מעמיסה על רשימת השאילתות, ומונעת בלבול עתידי.