- Power Query מאפשר לך לאחד קבצים מרובים מתיקייה מקומית או מתיקייה בענן, כל עוד הם חולקים את אותה סכימת נתונים.
- שימוש בקובץ לדוגמה מקל על יישום טרנספורמציות בכמות גדולה, כגון הסרת שורות עליונות או שינוי כותרות.
- ישנן מגבלות כאשר עמודות אינן תואמות במדויק, מה שמחייב ניקוי ידני או שימוש בסקריפטים של VBA עבור מקרים מורכבים.
דמיינו שיש לכם חבורה של דוחות חודשיים או שבועיים מפוזרים על פני תיקיות, ובמקום לבזבז את הבוקר שלכם בהעתקה והדבקה של נתונים, תוכלו לאחד הכל לגיליון אלקטרוני יחיד של אקסל בכמה לחיצות בלבד. כאן בדיוק נכנס לתמונה Power Query, כלי שהוא למעשה יד ימינו של כל מי שעובד עם נתונים, ומאפשר לנו לאחד קבצים שחולקים את אותו מבנה ללא מאמץ.
הדבר הכי מגניב במערכת הזו הוא שהיא לא מגבילה אותך לתיקייה מקומית במחשב שלך; אתה יכול למשוך נתונים מאתרי SharePoint, Azure Data Lake או Azure Blobs . המפתח לביצוע עבודה חלקה של המערכת הוא שלקבצים יש את אותה סכימה, כלומר העמודות תואמות, למרות ש-Power Query חכם מספיק כדי לעזור לנקות את הנתונים אם הם מכילים רעש או שורות מיותרות.
תהליך השילוב שלב אחר שלב
כשאתם מתחברים למקור נתונים, הדבר הראשון שתראו הוא תצוגת מערכת הקבצים. כאן, העמודה החשובה ביותר היא תוכן, המכילה את הייצוג הבינארי של כל מסמך. אם תלחצו על אחד מערכי הנתונים הבינאריים הללו, התוכנית תנסה לקבוע אם מדובר בקובץ CSV, JSON או Excel על מנת לפתוח אותו. כדי לשלב את כל הקבוצה, יש לכם שתי אפשרויות: לחיצה ישירה על כפתור מיזוג, או, אם אתם קפדניים יותר, בחרו " שינוי נתונים" כדי לסנן את רשימת הקבצים (לדוגמה, להתעלם מתיקיות ספציפיות) לפני שתמשיכו במיזוג.
לאחר כניסה לתיבת הדו-שיח של המיזוג, Power Query משתמש בקובץ לדוגמה כדי לנתח את המבנה . כברירת מחדל, הוא בוחר את הקובץ הראשון ברשימה, אך ניתן לשנות אותו אם קיים קובץ אחר המייצג טוב יותר את מערך הנתונים. כאן, ניתן לבחור באפשרות לדלג על קבצים עם שגיאות , דבר המציל חיים כאשר דוח פגום או ריק, ומונעת את עצירת התהליך כולו.
ניהול פלט וניקוי נתונים
לאחר קבלת המיזוג, המערכת מבצעת קסם טכני מאחורי הקלעים: היא יוצרת שאילתת דוגמה ופונקציה פרמטרית. משמעות הדבר היא שכל שינוי שתבצעו בקובץ הדוגמה ישוכפל אוטומטית לכל שאר הקבצים בתיקייה. אם, לדוגמה, קבצי ה-CSV שלכם מכילים ארבע שורות של כותרות מיותרות לפני הנתונים בפועל, תוכלו לעבור לשאילתת הטרנספורמציה ולהשתמש באפשרות להסיר שורות עליונות כדי לנקות את העומס הזה.
לאחר ניקוי השורות, חיוני להחיל את הפונקציה " השתמש בשורה הראשונה ככותרות" . זכור שכאשר אתה עושה זאת, Power Query בדרך כלל מוסיף שלב המרת סוג נתונים אוטומטית. אם, לאחר חזרה לשאילתה הראשית, אתה רואה שגיאה המציינת כי לא ניתן למצוא את העמודה 'Column1', אל דאגה; זה נורמלי מכיוון ששינית את שם העמודות בדוגמה , והשלב הבא כבר לא מזהה את השמות הישנים. פשוט מחק את השלב השגוי מחלונית השלבים שהוחלו, והכל יחזור לקדמותו.
אתגרים נפוצים וחלופות מתקדמות
לפעמים החיים לא כל כך פשוטים, ואנחנו נתקלים בקבצים שאינם זהים. לדוגמה, אם יש לכם דוחות ServiceNow שבהם כותרת אחת נקראת 'resolved_on' ואחרת 'closed_on', Power Query עלול לגרום לכם לבעיות מכיוון שהוא דורש מבנה עקבי . אם קובץ הדוגמה אינו תואם במדויק את השאר, הכלי עלול להשמיט את המידע מהקבצים שאינם תואמים או לגרום לשגיאות ייבוא.
במקרים בהם המבנה כאוטי לחלוטין או שנפח הנתונים עצום (עולה על מיליון שורות באקסל), ישנן אפשרויות אחרות. חלק מהמשתמשים בוחרים בסקריפטים של VBA באמצעות ממשקי CSV , המאפשרים צירוף קבצים מבלי לחזור על כותרות, ללא קשר אם מפריד השדות או הרשומות משתנה מקובץ לקובץ. בעוד שמעטפת Windows מהירה ביותר, היא נכשלת לעתים קרובות בעת טיפול במעברי שורה מוטמעים, בעוד שפתרון VBA חזק אינו מעמיס על זיכרון RAM ויכול לטפל במיליוני רשומות בקלות.
כדי להשלים את התהליך, לאחר שילוב הנתונים ב-Power Query, זכרו להקצות את סוג הנתונים הנכון לכל עמודה (תאריך עבור תאריכים, מטבע עבור הכנסה וכו') לפני טעינת הטבלה הסופית. ניתן לוודא שהכל נכון על ידי סינון עמודת שם המקור כדי לוודא שכל הקבצים בתיקייה עובדו בהצלחה.
היכולת להפוך את הצירוף של קבצי CSV מרובים לאוטומטי מאפשרת לך לעבור מעבודה ידנית מייגעת לתהליך עבודה מקצועי, בין אם על ידי מינוף הגמישות של Power Query לניקוי נתונים או שימוש ב-VBA כאשר מבנים אינם תואמים או שנפחי האחסון חורגים ממגבלות הגיליונות האלקטרוניים.