שינויים בנתוני השאילתה עצמה – Power Query

פעולות לשינוי תוכן הנתונים עצמם

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

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

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

הגדרת סוגי נתונים

ב-Power Query סוג נתונים מגדיר את הטבע של הערכים בעמודה – לדוגמה, האם מדובר במספרים, טקסט, תאריכים, ערכים בוליאניים (אמת/שקר) ועוד.

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

שינוי סוג הנתונים נחשבת כפעולה שמשנה נתונים.

הגדרת סוגי נתונים מדויקת היא קריטית לתהליך העיבוד והניתוח מהסיבות הבאות:

• דיוק בחישובים: אם עמודה שמכילה מספרים מוגדרת בטעות כטקסט – לא ניתן יהיה לחשב סכומים, ממוצעים או כל פעולה מתמטית.
• פעולות סינון ומיון תקינות: מיון תאריכים יתבצע נכון רק אם העמודה מוגדרת כ Date-ולא כ-Text.
• יצירת קשרים (Relationships) בין טבלאות: מודל הנתונים דורש התאמה בין סוגי שדות – לדוגמה, לא ניתן לקשר בין עמודת תאריך לבין עמודת טקסט.
• ביצועים: סוגי נתונים מדויקים מאפשרים למנוע של Power BI לפעול בצורה אופטימלית ויעילה.
• הגנה משגיאות עתידיות: כאשר הנתונים מרועננים בעתיד – אם סוגי הנתונים הוגדרו מראש, הסיכון לקריסת שאילתה קטן משמעותית.

סוג הנתונים בשדה

נקבעים בשיטות הבאות:

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

שתי שיטות חלופיות:

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

• סימון עמודה ולחיצה על פקדData Type בסרגל הכלים. הפקד מופיע גם בלשונית Home וגם בלשונית Transform:

סוגי הנתונים הנפוצים

קיבוץ שאילתה

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

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

זוהי פעולה המקבילה לפקודת Group By בשפת SQL או לקיבוץ PivotTable באקסל אך עם שליטה גמישה יותר על החישובים והמבנה.

ביצוע הפעולה

1. בחירת עמודה (או עמודות) שעל פיהן יתבצע הקיבוץ.
2. לחיצה על פקד Group by בסרגל הכלים יציג את המסך הבא (במידה ומוצג מסך מצומצם ללחוץ על אופצית Advanced options לקבלת התצוגה שלהלן):

3. המסך נפתח אוטומטית עם השדות המסומנים בשאילתה כשדות מקבצים. ניתן להוסיף או למחוק שדות מקבצים.

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

5. בחירת פונקציית סיכום:

• • Sum – סכום
  • Average – ממוצע
  • Count Rows – ספירת שורות
  • Min / Max – ערך מינימלי / מקסימלי
  • All Rows – שמירת כל השורות המקוריות תחת קבוצת משנה (לקיבוץ מתקדם)

ניתן לקבץ לפי יותר מעמודה אחת – למשל לפי "סניף" ו-"שנה".

ניתן לבצע כמה חישובים שונים בו-זמנית – למשל סכום מכירות וממוצע לקוחות.

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

החלפת ערכים

Replace Values היא פעולה ב-Power Query שמאפשרת להחליף ערכים מסוימים בעמודה בערכים אחרים. מדובר בפעולה ישירה ופשוטה שמבצעת החלפה טקסטואלית או מספרית ללא צורך בתנאים מורכבים או פונקציות.

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

שימושים נפוצים

• לניקוי נתונים: למשל החלפת ערכים כמו "לא זמין" או "N/A" בערך ריק .(null)
• לתיקון שמות שגויים או אחידים: לדוגמה, שינוי "תל אביב-יפו" ל-"תל אביב".
• להתאמת ערכים לשמות מקובלים: למשל שינוי "Q1" ל-"רבעון ראשון".
• להמרת ערכים טקסטואליים לסוג אחר של קידוד או פורמט.

שלבי הביצוע

1. בוחרים את העמודה הרלוונטית.
2. בתפריט Home או Transform לוחצים על הפקד Replace Values.

3. מופיע חלון שבו:

• • מציינים את הערך שברצוננו להחליף (Value To Find).
  • מציינים את הערך החדש (Replace With).

4. מאשרים – וכל ההופעות של הערך בעמודה מוחלפות.

ההחלפה מתבצעת על התא כולו – כלומר, חייבת להיות התאמה מדויקת (לא ניתן להחליף חלקים מתוך מחרוזת (לכך יש פקודות אחרות כמו .Replace Text with Pattern

הערך שצוין להחלפה רגיש לאותיות (case sensitive) – אלא אם שונה בהגדרות.

כל שלב של Replace נשמר כחלק מרשימת השלבים (Applied Steps) וניתן לערוך אותו בדיעבד.

המרה לרשימה

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

מלא (Fill)

הפקודה  Fill ב-Power Query נועדה להשלים ערכים חסרים (null) בעמודה מסוימת באמצעות העתקה של ערכים קיימים משורות סמוכות.

הפקודה מאפשרת לבחור את כיוון ההשלמה:

• Fill Down   ממלא ערכים כלפי מטה (מהשורה העליונה כלפי התחתונה).
• Fill Up   ממלא ערכים כלפי מעלה (מהשורה התחתונה כלפי העליונה).

הפעלת הפקודה

1. בוחרים את העמודה שברצונך למלא.
2. בלשונית  Transformלוחצים על החץ שליד הפקד Fill.
3. בוחרים בין:
   • Fill Down למילוי כלפי מטה.
   • Fill Up למילוי כלפי מעלה.
4. הערכים יתמלאו בתאים הריקים לפי הכיוון הנבחר.

הפעולה מתבצעת רק על ערכים חסרים (null) – תאים שכבר מכילים ערך לא משתנים.

Fill אינו משנה את מבנה הטבלה, אלא רק משכפל ערכים קיימים כדי להשלים שדות ריקים.

אם הערכים הרלוונטיים לא מופיעים בסדר תקין (למשל: נתוני שורה מתחת לערך העליון), יש לשקול שימוש ב-.Fill Up

פעולות עריכת עמודות טקסט

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

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

פירוט הפקודות

עיצוב טקסט

פעולות אלה משפיעות על המראה של מחרוזת הטקסט:

• Lowercase הופך את כל האותיות במחרוזת לאותיות קטנות. דוגמה"  "ABC" → "abc"
• Uppercase הופך את כל האותיות לאותיות גדולות. דוגמה  "abc" → "ABC" .
• Capitalize Each Word הופך את האות הראשונה של כל מילה לאות גדולה. דוגמה: "john smith" → "John Smith"
• Trim מסיר רווחים עודפים בתחילת ובסוף המחרוזת. לדוגמא: " מוצר ">,"מוצר"
• Clean מסיר תווים בלתי נראים (כגון תווי שורה, תווי בקרה או תווים לא מודפסים)

חילוץ טקסט

פעולות שמאפשרות לחלץ חלקים מתוך הטקסט לפי כללים שונים:

• First Characters / Last Characters חילוץ מספר תווים מההתחלה או מהסוף של המחרוזת. דוגמה: תווים 2 עד 4 מתוך "ABCDEFG" → "BCD"
• Range חילוץ תווים ממיקום מסוים ולאורך מסוים. דוגמה: תווים 2 עד 4 מתוך "ABCDEFG" → "BCD"
• Text Before/After/Between Delimiter חילוץ טקסט לפי תו מפריד (delimiter)
• Before Delimiter טקסט לפני התו (למשל לפני מקף)

• After Delimiter טקסט אחרי התו
• Between Delimiters טקסט בין שני תווים

פעולות המרת מספרים (Number Column Transformations)

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

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

פירוט הפקודות

עיבודים סטנדרטיים  (Standard)

• Add / Subtract / Multiply / Divide: מבצע פעולה חשבונית על כל ערך בעמודה מול מספר קבוע – חיבור, חיסור, כפל או חילוק.
• Percentage מחלק את הערכים ב־100. לדוגמה: 25 → 0.25. שימושי כאשר ערכים באים באחוזים אך רשומים כמספר שלם.

Rounding

• Round Up עיגול כלפי מעלה
• Round Down עיגול כלפי מטה
• Round to Nearest עיגול מתמטי רגיל ניתן לקבוע את מספר הספרות אחרי הנקודה, תצוגה מדעית (Scientific)

Scientific Notation

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

פעולות מידע על המספר

• (Information) Is Even / Is Odd מחזיר TRUE או FALSE לכל ערך – בהתאם להיותו זוגי או אי-זוגי
• Sign מחזיר: 1- אם המספר שלילי, 0 אם אפס, 1 אם חיובי

פעולות סטטיסטיות (Statistics)

פעולות אלה מופעלות על העמודה כולה ומחזירות ערך בודד:

• Sum – סכום כל הערכים
• Average – ממוצע
• Minimum / Maximum – ערך מינימלי או מקסימלי
• Median – חציון
• Standard Deviation / Variance – סטיית תקן ושונות

הערה: פעולות אלה לא מחזירות טבלה, אלא ערך סיכום נפרד.

פעולות המרת תאריכים וזמן (Date / Time Transformations)

כאשר עמודה מוגדרת כסוג Date או Time, ה-Power Query מאפשר לבצע עליה מגוון פעולות לפירוק, מיון, סינון וחישוב רכיבים מתוך ערכי התאריך והשעה. הפעולות זמינות בלשונית Transform, תחת

קבוצת הפקודות Date & Time columns.

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

פירוט הפקודות

 שינוי סוג (Date/Time Type)

• Date תאריך בלבד (ללא שעה)
• Time שעה בלבד (ללא תאריך)
• Date/Time תאריך ושעה יחד
• Date/Time/Zone כולל אזור זמן (פחות נפוץ)
• Duration מרווח זמן (למשל: הבדל בין שני תאריכים)

שינוי הסוג קובע אילו פעולות יהיו זמינות לעמודה.

פעולות על תאריכים (Date)

חילוץ הערכים הבאים מערכי התאריך:

Year

• Year מחלץ את השנה (למשל 2024)
•  Start of Year יוצר תאריך 1.1 של אותה שנה
• End of Year יוצר תאריך 31.12 של אותה שנה

Month

• Month מספר החודש (1–12)
• Name of Month שם החודש ("ינואר", "פברואר" וכו')
• Start/End of Month מחזיר את היום הראשון או האחרון בחודש

Quarter

• Quarter of Year מספר הרבעון (1–4)
• Start of Quarter תאריך תחילת הרבעון
• End of Quarter תאריך סיום הרבעון

Week

• Week of Year מספר השבוע בשנה
• Week of Month מספר השבוע בחודש
• Start of Week תאריך יום ראשון של אותו שבוע

Day

• Day מספר היום בחודש
• Day of Week מספר היום בשבוע (0=ראשון, 6=שבת)
• Name of Day שם היום (לדוגמה "שני")
• Start/End of Day תאריך ושעה של תחילת/סיום היום

פעולות על זמן (Time)

• Hour  שעת היום  (0–23)
• Minute  דקת השעה  (0–59)
• Second  שניית הדקה  (0–59)

פעולות על Date/Time

• Date Only מחלץ את רכיב התאריך בלבד מתוך  Date/Time
• Time Only מחלץ את רכיב השעה בלבד
• Start of Hour / End of Hour עיגול השעה כלפי מטה או מעלה
• Start of Day / End of Day קובע את השעה ל־00:00 או 23:59
• Add/Subtract Duration הוספה או החסרה של פרק זמן (Duration)