עורך השאילתות של Power Query מאפשר לבצע שינויים מבניים בטבלת הנתונים – כלומר: מה ייכלל בטבלה, איך היא תיראה, כיצד יסודרו הנתונים, אילו שורות ועמודות יישארו, ואילו ישונו או יוסרו.
כל פעולה מבנית בעורך השאילתות מתבצעת על השאילתה הפעילה שנבחרה ובמסגרתה – על העמודות שסומנו באותו רגע.
המשתמש הוא שקובע במדויק על מה יופעל כל שינוי, באמצעות שתי פעולות בסיסיות:
בחירת שאילתה
ברשימת השאילתות (בצד ימין של המסך), מוצגות כל השאילתות הקיימות בקובץ.
לחיצה אחת על שם שאילתה כלשהי טוענת אותה לחלונית העריכה המרכזית.
מכאן ואילך – כל פעולה שתבוצע תיושם על אותה שאילתה בלבד.
בחירת עמודה או עמודות
בתוך הטבלה של השאילתה, יש ללחוץ על כותרת עמודה כדי לסמנה:
- לחיצה אחת בודדת תבחר עמודה אחת.
- לחיצה עם מקשCtrl תאפשר לבחור עמודות נוספות.
- לחיצה עם מקשShift תאפשר בחירה של רצף עמודות.
רק העמודות שנבחרו – ייכללו בפעולה הבאה.
אם לא נבחרה עמודה – חלק מהפקדים יהיו אפורים (לא פעילים).
כך שחשוב להבין :הפקודות אינן פועלות אוטומטית על כל הטבלה ,אלא על פי מה שהמשתמש בחר בפועל – הן ברמת השאילתה והן ברמת העמודות.
שליטה זו מאפשרת גמישות מרבית וביצוע שינויים מדויקים.
במקטע זה יסקרו כל הפקודות בסרגל הכלים העוסקות בתחום זה:
בחירת עמודות
מאפשר לבחור את העמודות הרצויות ולהסיר את כל השאר.
שימושי במיוחד בטבלאות מרובות שדות – ממקד רק את הנתונים הדרושים להמשך העיבוד.
Choose Columns
לחיצה על הפקד מציג את המסך הבא האפשר בחירה נוחה בתמהיל עמודות מבוקש:
לחיצה על "ok" מוחקת את כל העמודות שלא נבחרו. נוח מאוד בתרחיש שבו בשאילתה יש כמות גדולה של עמודות (100 עמודות, לדוגמה).
פקד Go to Column
מאפשר בחירת עמודה מבלי למחוק. שימוש בתרחיש של שאילתה מרובת עמודות. לחיצה על הפקד מציגה את המסך הבא:
בחירת שם שאילתה ולחיצת "OK" תעביר את המיקוד לעמודה שנבחרה.
הסרת עמודות
מוחק עמודות מיותרות או בלתי רלוונטיות מהטבלה.
מתייחס לעמודות שנבחרו.
מאפשר או להסיר את העמודות שנבחרו או להסיר את כל העמודות שלא נבחרו.
השארת שורות
לחיצה על הפקד פותח מסך עם מספר פקדים שכל אחד מפעיל תנאי אחר להשארת שורות:
שימושי לצמצום נתונים ולהתמקדות ברשומות חשובות בלבד.
מחיקת שורות
לחיצה על הפקד פותח מסך עם מספר פקדים שכל אחד מפעיל תנאי אחר למחיקת שורות:
מסיר שורות מהטבלה לפי קריטריונים כגון:
- שורות ריקות
- שורות עליונות או תחתונות
- שורות כפולות
- שורות עם שגיאות
מיון
משנה את סדר השורות לפי ערכים בעמודה נבחרת בסדר עולה או יורד.
מועיל להצגה ברורה, חישובים סדרתיים או הכנה לקיבוץ.
פיצול עמודה
מחלק עמודה אחת לשתי עמודות או יותר, לפי:
• תו מפריד (כמו פסיק או רווח)
• אורך קבוע
• מיקום תו מסוים
• אותיות גדולות/קטנות־תחתון
• תווים מספריים
מחייב שהעמודה הנבחרת תהיה מסוג טקסט
השתמש בשורה עליונה ככותרת
הופך את השורה הראשונה בטבלה לכותרות העמודות.
שימושי כשמקור הנתונים אינו כולל שמות שדות מוגדרים מראש.
ב-Power Query כאשר נתוני שאילתה נטענת ממקור נתונים (למשל קובץ Excel אוCSV ), המערכת לא תמיד מזהה שהשורה הראשונה של הנתונים מהווה שמות העמודות ((Headers. לעיתים השורה הראשונה נתפסת כחלק מהנתונים, ושמות העמודות מקבלים שמות אוטומטיים כמו Column1 ,Column2 וכן הלאה.
הפקודה "השתמש בשורה עליונה ככותרת" נועדה להמיר את הערכים שמופיעים בשורה העליונה – לשמות של שדות (עמודות) בטבלה.
מתי נדרש להשתמש?
- כאשר Power Query טוען את הנתונים ללא זיהוי נכון של כותרות.
- כאשר הנתונים מגיעים מקבצים בפורמט לא אחיד (למשל CSV או קובצי Excel שנשמרו באופן לא תקני).
- כאשר בשלב קודם בוצעו פעולות כמו "שנה סוג נתונים" לפני השימוש בכותרות ונדרש לשחזר את המצב התקין.
מה קורה בפועל בפעולה זו?
- Power Query מקדם את שורת הנתונים העליונה לשורת שמות השדות.
- שורת הנתונים עצמה מוסרת מהטבלה.
- שמות העמודות משתנים בהתאם לערכים שהיו בשורה הראשונה.
הפוך עמודות לשורות (Transpose)
הפקודה Transpose (היפוך טבלה) מאפשרת להפוך שורות לעמודות ועמודות לשורות. כלומר, היא מחליפה את הציר האופקי בציר האנכי – פעולה שימושית כאשר הנתונים נמצאים במבנה שאינו מתאים לניתוח או להצגה ונדרש סידור מחדש שלהם.
מתי משתמשים ב-Transpose?
- כאשר נתונים התקבלו בטבלה "רוחבית מדי" ויש צורך להפוך אותם למבנה אנכי (למשל עבור טבלת מדדים לפי שנים).
- כאשר העמודות מייצגות ערכים שיש להתייחס אליהם כרשומות ולא כשדות קבועים.
- בעת הכנת נתונים לפעולת (Unpivot) פירוק עמודות לרשומות.
- לצורך ניתוח טבלאות מיוחדות שהוכנו ידנית באקסל במבנה לא ניתוחי.
כיצד הפעולה מתבצעת?
- Power Query לוקח את כל הערכים בטבלה, והופך כל שורה לעמודה חדשה ו־כל עמודה לשורה.
- שמות העמודות המקוריים מאבדים את שמם (הם הופכים לחלק מהשורות), אלא אם מגדירים אותם מחדש אחרי ה-.Transpose
- הפעולה הופכת את מבנה הטבלה ולכן יש לוודא התאמה למבנה הרצוי לפני שממשיכים.
הפוך שורות
יוצר את סדר השורות ההפוך (השורה האחרונה הופכת לראשונה ולהפך).
מועיל לעיבוד סדרי זמן או טבלאות שנקראו הפוך.
שינוי שם עמודה
מאפשר לתת שמות ברורים או תקניים לעמודות – לדוגמה, החלפת "Column1" ב-"תאריך אספקה." לישום נדרש לבחור עמודה אחת בלבד בשאילתה הנוכחית. בחירת יותר מעמודה אחת תהפוך את הפקד ללא זמין.
חשוב למעקב והבנת המודל, בעיקר בעבודה מרובת שאילתות.
Unpivot
הפקודה Unpivot Columns ב־Power Query מאפשרת להמיר עמודות מרובות לשורות, כלומר – להפוך מבנה טבלה "רוחבי" למבנה "ארוך". פעולה זו היא ההפך מפעולת Pivot Column.
במקום שכל ערך יופיע בעמודה נפרדת – הערכים מאוחדים לשתי עמודות:
- אחת שתייצג את שם העמודה המקורי (הקטגוריה).
- שנייה שתכיל את הערך שהופיע באותה עמודה ובאותה שורה.
מתי משתמשים ב־Unpivot?
- כאשר הנתונים מגיעים במבנה רחב מדי – לדוגמה, מכירות לפי חודשים בעמודות נפרדות – ויש צורך לאחד את המידע לעמודת קטגוריה ועמודת ערכים.
- כאשר רוצים לאפשר סינון, חישובים והשוואות בין קטגוריות (למשל לפי חודש, שנה, סוג מוצר) שלא ניתן לבצע כאשר כל אחת מהן היא עמודה.
- כשנדרשת הכנה לפעולות ניתוח כגון ,Group By חישוב מדדים לפי קטגוריה, פילוחים ועוד.
דוגמא:
הטבלה המקורית מציגה בצורה מטריציונית נתוני מכירות לסניף לפי חודשים, כאשר בכותרת שורה מצויינים הסניפים, וככותרות עמודות מצויינים החודשים.
לאחר ביצוע פעולת הUnpivot- מבנה הטבלה משתנה כך ש:
- החודשים השונים מופיעים כערכים בשדה "Attribute", כך שכל הצטלבות בין סניף לחודש בטבלת המקור מופיעה כרשומה יחודית בטבלה לאחר פעולת ה-Unpivot.
- ערך המכירות לכל צירוף סניף/ חודש נרשם בעמודת ה-"Value".
תיאור תהליך הפעולה
על מנת להפעיל את פקודת ה-Unpivot נדרש לסמן את העמודות שרוצים לעשות להן Unpivot. בדוגמא הנ"ל – לסמן את כל עמודות החודשים.
- כל ערך בעמודות שנבחרו הופך לרשומה חדשה.
- העמודות שנבחרו מתאחדות לשתי עמודות:
- Attribute – מציין את שם העמודה המקורית.
- Value – מכיל את הערך שהופיע בתא המקורי.
ניתן לשנות את שמות העמודות לאחר מכן לצורך הבהרה (למשל "חודש" ו־"מכירות").
הפקודה זמינה בקליק ימני על העמודות, Unpivot Columns או דרך הלשונית Transform
ניתן להפעילUnpivot Other Columns – פעולה זהה אך הופכת את כל העמודות למעט אלה שנבחרו.
לאחר ביצוע הפעולה, לרוב רצוי:
- לשנות את שמות העמודות Attribute ו Value-לשמות בעלי משמעות (למשל "חודש" ו"מכירות").
- לוודא שהעמודות שהתקבלו הוגדרו עם סוג נתונים מתאים (תאריך, מספר, טקסט וכו').
Pivot
פקודת Pivot Column בPower Query- מאפשרת להפוך ערכים בעמודה אחת – לעמודות חדשות בטבלה. מדובר בפעולה של סיבוב טבלה לצורך ריכוז מידע – פעולה הפוכה לUnpivot-.
כל ערך ייחודי בעמודה שנבחרת כעמודת Pivot יהפוך לשם של עמודה חדשה, ותחתיה יוצגו הערכים המתאימים – לפי עמודת ערכים שנבחרה.
מתי משתמשים ב־Pivot?
- כאשר יש נתונים בפורמט של רשומות (שורות) מרובות עבור אותו מזהה (למשל, מכירות לפי חודשים לכל סניף) ורוצים לרכז את כל הערכים האלה לעמודות לפי קטגוריה (חודשים).
- כאשר רוצים להפוך טבלת מידע "ארוכה" לטבלה "רחבה" – לצורכי ניתוח, הצגה או השוואה.
- כאשר נדרש לבצע סיכום לפי קטגוריות מסוימות ולהציג אותן בצורה של טבלה חוצה.
תרשים דוגמה:
אם יש מספר רשומות עבור אותה קטגוריה (בדוגמה – קיימות שתי רשומות לתל אביב וינואר) – תהליך ה-Pivot יפעיל פונקצית צבירה (בדוגמה – סכימה) על כל הרשומות המשוייכות לאותו סניף ולאותו חודש.
במידה ויש יותר מאשר שדה כמותי אחד (בדוגמה – סכום מכירות וכמות מכירות) – תהליך ה-Pivot יקבל שדה כמותי אחד בלבד שעליו יפעיל את פונקצית הצבירה הנבחרת. שאר השדות הכמותיים יעלמו מתוצאת השאילתה לאחר הפעלת תהליך ה-Pivot.
לתשומת הלב – פעולת צבירה משנה את נתוני השאילתה – לא רק משנה את מבנה נתוני השאילתה. עם זאת, הוחלט לשלב את ההסבר על הפקודה כאן, משום רהיטות ההסבר (לאחר ההסבר על Unpivot) ומשום עיקר מהותה של הפקודה שהיא שינוי מבנה.
תיאור תהליך הפקודה
כדי לבצע Pivot, יש לסמן את העמודה שעל פיה תיווצר חלוקה לעמודות – זו תהיה עמודת ה-Pivot (בדוגמה – עמודת החודש).
ללחוץ על פקד ה-Pivot בסרגל הכלים.
הלחיצה תציג את המסך הבא:
במסך זה נדרש לבחור את השדה הכמותי שיצבר בתאי הערכים. כברירת מחדל פעולת הצבירה היא סכימה. פתיחת Advanced option תאפשר למשתמש לשנות את פעולת הצבירה (ממוצע, מינימום, מקסימום, ספירת שורות וכו').
הזזת עמודות
משנה את סדר העמודות בטבלה: ניתן להזיז עמודה שמאלה, ימינה, להתחלה או לסוף.
פעולה ויזואלית בלבד, ללא השפעה על המידע.
מחייב בחירת עמודה אחת לפחות. במידה ונבחרת קבוצת עמודות – כל העמודה מוזזת.
