Power Query 3 | Group By all - Extract Values

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

 

נושאי המאמר

1. סרטון המאמר

2. הסבר המטרה

3. Group By 

3.1 Group By ע"י שימוש בפונקציית Sum

3.2 Group By all

4. Table.Column

5. Extract Values

 

מאמרים נוספים הקשורים לנושא Power Query

1. Power Query 1 | Fill Down and Split to Columns

2. פיצול ערכים לשורות | Text to Rows

1

 

 

סרטון המאמר

 

2

 

 

הסבר המטרה

במאמר זה אנו מדברים על שינוי מבנה נתונים משורות לתא בודד (למעשה נבצע פעולה הפוכה מזו שביצענו במאמר Power Query 2 - Split to Rows). 

מבנה הטבלה שלנו הוא כזה:

עמודה המכילה מס' הזמנה ועמודה נוספת המכילה רכיבים נפרדים מהמספר הסידורי של אותה הזמנה. כאשר כל רכיב מייצג קוד מסויים.

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

B1

איור מס' 1

3.1 

 

 

Group By ע"י שימוש בפונקציית Sum

 

לצורך הסבר על השימוש ב Group By נשתמש בטבלה שונה המכילה את הנתונים הבאים:

מספר קטגוריה, מספר פריט, כמות (ראו איור מס' 2)

המטרה היא ליצור טבלה המסכמת את כמות המלאי עבור כל קטגוריה.

 

איור מס' 2

 

לאחר שייצרנו שאילתה (Query) חדשה מטבלת הנתונים שלנו (להסבר נוסף על ייצירת שאילתה)

נלחץ על לחצן Group By שבלשונית Home 

 

איור מס' 3

 

בחלונית הבאה נבצע את הפעולות הבאות:

1. נבחר את העמודה עבורה אנו רוצים לקבץ את הנתונים (בדוגמה זו - מס הזמנה)

2. ניתן שם לעמודה החדשה (בדוגמה זו - סה"כ כמות במלאי)

3. נבחר את הפונקציה הרצויה (בדוגמה זו - Sum)

4. נבחר את העמודה המחושתב (בדוגמה זו - כמות)

אישור ונקבל את התוצאה הרצויה (איור מס' 5)

איור מס' 4

B4

 

איור מס' 5

3.2

 

 

Group By all

 

סדר פעולות זהה לזה שראינו בדוגמה לעיל, רק שהפעם נבחר באפשרות all ולא sum.

 

איור מס 6

 

איור מס 7

 

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

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

 

איור מס' 8 

 

אם נלחץ על הטבלה נניח בשורה הראשונה נקבל את הטבלה הבאה:

 

איור מס' 9

 

אנחנו למעשה רואים טבלה רק עבור מספר הזמנה 1024. כמובן שאם נבחר בטבלה בשורה השנייה נקבל טבלה זהה אבל עבור מסםר הזמנה 1025 וכו'.

A4

 

 

Table.Column

 

קישור לדף התמיכה של Microsoft בנושא פונקצייה Table.Column

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

נשתמש בפונקציית Table.Column פונקציה המחזירה עמודה של נתונים בצורת רשימה (List)

הפונקציה מקבלת שני ארגומנטים:

1. שם הטבלה  (לשים לב ששם הטבלה הוא למעשה שם העמודה בטבלה הראשית)
2. שם העמודה ממנה נרצה לחלץ את הנתונים (מדובר בשם העמודה בטבלה המשנית)

 

הוספת הפונקציה שלבים:

נבחר בלשונית Add column ← Add custom Column ← ונקליד בחלונית את שם הפונקציה והערכים הרצויים (כפי שמוצג באיורים מס' 10 ו 11)

שם הטבלה - "New Column"

שם העמודה - "מספר סידורי"

B5

 

איור מס' 10

 

איור מס' 11

 

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

 

איור מס 12

A5

 

 

Extract Values - חיבור הערכים ברשימה עם תו מפריד בניהם

 

נלחץ על החצים בראש עמודת Costum  ← נבחר Extract Values (איור מס' 13) ← נבחר את המפריד הרצוי (איור מס' 14).  

 

איור מס' 13 

 

איור מס 14

 

בשלב זה נשאר רק להסיר את עמודת New Column שאין בה כבר צורך ונקבל את הטבלה הבאה

 

איור מס' 14