קבץ לפי (Group By) ב-Power Query – שימוש באפשרות All Rows

הכוח שמאחורי All Rows

במאמר הקודם למדנו כיצד להשתמש בחלונית ה-Group By כדי לסכם נתונים ולצמצם את הטבלה.
זו הטבלה שאיתה עבדנו:

אבל ישנם מקרים שבהם אנחנו רוצים ליהנות משני העולמות –
מצד אחד לשמור על רמת הפירוט של כל עובד, ולהציג את כל השדות בטבלה, ומצד שני להציג בעמודה נוספת נתון מסכם של הקבוצה אליה הוא שייך.
כאן נכנסת לתמונה אפשרות ה-All Rows (כל השורות).
היא מאפשרת לנו לבצע חישובים קבוצתיים (כמו ממוצע) ועדיין להשאיר את נתוני המקור זמינים להמשך עבודה.
כלומר, בניגוד לקיבוץ רגיל, שבו הנתונים מסוכמים והפירוט נמחק, באפשרות 'כל השורות' נשמרות כל השורות של כל קבוצה בתוך תא אחד בצורת טבלה מקוננת (Nested Table).
כך ניתן לחשב ערך מסכם לכל קבוצה (כמו ממוצע שכר מחלקתי),
ובמקביל לשמור על הגישה לכל שורה מקורית, אליהן ניתן לגשת לאחר הרחבת הטבלה המקוננת בחזרה לפירוט המלא.

הבעיה המעשית: השוואת שכר אישי לממוצע המחלקה

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

שלב ראשון – הגדרת הקיבוץ והחישובים

1. נעמוד בעמודת המחלקה ונלחץ על Group By. זהו השדה שקובע שכל החישובים יתבצעו ברמה המחלקתית
2. נסמן את האפשרות 'מתקדם'
   ניתן לעמודה החדשה שם: ממוצע שכר מחלקתי
   פעולה: ממוצע
   עמודה: משכורת
3. עבור החישוב השני נלחץ על 'הוסף מצבור'
4. נמלא את השדות:
   שם העמודה החדשה: מקור
   פעולה: כל השורות

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

שלב שני – הרחבת הטבלה

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

(אל תשכחו להסיר את ה V מ'המשתמש בשם העמודה המקורי כקידומת')

קיבלנו טבלה שמכילה את כל העובדים, כשעבור כל עובד מופיע ממוצע השכר המחלקתי והמשכורת האישית שלו:

שלב שלישי – חישוב יחס השכר

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

1. נעבור לכרטיסיית הוסף עמודה ונבחר בעמודה מותאמת אישית
2. בשם העמודה נכתוב: ב'השוואה לממוצע'
3. בנוסחה נבצע חלוקה של המשכורת חלקי ממוצע השכר המחלקתי ונאשר
   
4. וזו התוצאה שהתקבלה, לאחר שינוי סוג הנתונים לאחוז:
   

ולסיום, קוסמטיקה

נסדר את העמודות בסדר נוח לקריאה
נשנה את חישוב ההשוואה לממוצע לאחוזים (בהזדמנות הזאת כדאי גם לבדוק ששאר העמודות מוגדרות נכון)
וזו התוצאה שהתקבלה:

מתי נשתמש ב Group By?

• לביצוע השוואות יחסיות בתוך קבוצה
• לפישוט תהליכים (במקום להסתבך עם מיזוג שאילתות מול שאילתת סיכום חיצונית)
• לחישוב ערכי דירוג פנים קבוצתיים (למשל – מיון עובדים לפי שכר בתוך כל מחלקה או זיהוי הרשומה הגבוהה ביותר בכל קבוצה)

מתי לא נשתמש בקיבוץ?

• בבסיסי נתונים ענקיים (יצירת אובייקטים של טבלאות מקוננות Nested Tables עלולה להכביד על זכרון המחשב ולבאט את זמן הרענון של השאילתה)
• בהשוואה לערך גלובלי (אם כל מה שנחוץ הוא השוואה לממוצע של כל עובדי המפעל, יש דרכים פשוטות יותר להשוואה, אותן אציג במאמר עתידי)
• כשנדרשת גמישות דינמית למשתמש הקצה (אם המשתמש רוצה לשנות את החיתוכים בלחיצת כפתור, Pivot Table יהיה פתרון גמיש יותר)
• שאלות ותשובות בנושא קיבוץ נתונים ב-Power Query עם All Rows

שאלות ותשובות על All Rows בקיבוץ ב-Power Query

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

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

שאלה:
באילו מצבים לא מומלץ להשתמש בטכניקת All Rows?

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