GroupBy ב-Power Query

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

חלונית ה-Group By (קבץ לפי)

לחצו על 'קבץ לפי' (Group By) בלשונית 'בית' (Home)

חלונית ההגדרות תיפתח:

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

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

קיבוץ בסיסי מול קיבוץ מתקדם

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

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

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

ניתוח הנתונים:

אנחנו רואים שסך משכורות הגברים מחלקת הייצור הוא 2,569,860 שח, ואילו השכר הממוצע של הגברים במחלקת הייצור הוא 6,522 ש"ח.
סך משכורות הנשים באותה המחלקה הוא 2,352,471 וממוצע המשכורות הוא 6,358 ₪.
המלצה: לקריאה נוחה יותר כדאי למיין את הטבלה.

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

שאלות ותשובות בנושא Group By ב-Power Query

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

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

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