איך ליצור אינדקס שמתאפס עבור כל שינוי ערך בעמודה

כבר כמה פעמים נתקלתי בצורך ליצור אינדקס לטבלה, אבל לא כזה שמתחיל מ-1 ורץ לכל אורך העמודה,
אלא כזה שמתאפס ומתחיל מחדש בכל פעם שערך מסוים בעמודה משתנה.
בפרויקט האחרון שפיתחתי, לדוגמה, הדרישה היתה ליצור מספור לפריטים באותה החשבונית, שמתאפסים עבור כל חשבונית.
משתמשי אקסל יפתרו את זה באמצעות COUNTIFS או IF, אבל ב-Power Query אין "תא מעליי" ואי אפשר לגרור נוסחאות,
וכדי להשיג את אותה התוצאה, אנחנו צריכים להשתמש בטכניקה חכמה של קיבוץ נתונים.

אז מה יש לנו?

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

אם נביט בטבלה, נראה של-David Cohen יש שתי חשבוניות שונות – האחת (20001) מכילה 3 פריטים שונים והשנייה (20003) מכילה פריט אחד בלבד.
המטרה שלנו היא ליצור עמודת אינדקס שתמספר את הפריטים בתוך כל חשבונית (1, 2, 3), ותתאפס בחזרה ל-1 כשעוברים לחשבונית הבאה.

שלב ראשון – טעינה ומיון הנתונים

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

שלב שני – קיבוץ השורות (Group By)

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

בכרטיסייה בית, נלחץ על קבץ לפי
תיפתח חלונית ובה:

• נבחר את העמודה שלפיה אנחנו רוצים לאפס את המונה (Invoice ID).
• ניתן שם לעמודה החדשה. מכיוון שהיא עמודה זמנית, אקרא לה Temp
• תחת 'פעולה' נבחר ב'כל השורות', שלוקחת את כל השורות המקוריות של אותה הקבוצה ו"אורזת" אותן לתוך תא אחד, באובייקט Table.

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

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

שלב שלישי – הוספת האינדקס (Add Custom Column)

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

= Table.AddIndexColumn([Temp], "Item #", 1, 1)

כש Temp הוא השם שנתנו לעמודה שמכילה את החשבוניות המקובצות, ו Item # הוא השם של העמודה החדשה. המספור מתחיל ב-1, וגדל ב-1.

שלב רביעי – הרחבה (Expand)

נלחץ על שני החצים הקטנים (Expand) בראש העמודה החדשה שיצרנו (New) ונבחר את העמודות שאותן נרצה להציג (אין צורך לבחור עמודות שכבר מופיעות בטבלה).
אני נוהגת להסיר את ה- V מ'השתמש בשם העמודה המקורי כקידומת':

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

קיבלנו טבלה שבה עבור כל חשבונית, הפריטים ממוספרים מ-1 ואילך, ומתאפסים כאשר עוברים לחשבונית הבאה.

שאלות ותשובות בנושא מספור מותנה ב-Power Query

שאלה:
מדוע לא ניתן להשתמש בכפתור "הוסף אינדקס" הרגיל למספור פריטים בחשבונית?
תשובה:
כפתור האינדקס הרגיל (Index Column) ב-Power Query יוצר מספור רציף לכל שורות הטבלה (1, 2, 3…) ללא התחשבות בשיוך לקבוצות.
כדי למספר פריטים בנפרד לכל חשבונית (כך שהמספור יתחיל מ-1 בכל חשבונית חדשה), נדרשת מניפולציה של קיבוץ הנתונים תחילה.

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

שאלה:
באיזו פונקציה משתמשים כדי להוסיף את המספור הפנימי?
תשובה:
לאחר הקיבוץ, מוסיפים עמודה מותאמת אישית (Custom Column) ומשתמשים בפונקציה Table.AddIndexColumn.
פונקציה זו מקבלת את הטבלה הפנימית שנוצרה בשלב הקיבוץ ומוסיפה לה עמודת אינדקס שמתחילה מ-1 (או מ-0) עבור אותה קבוצה ספציפית.

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