Power Query 1 | Fill Down and Split to Columns
נושאי המאמר
1 מדוע ללמוד לעבוד עם עורך השאילתות (Power Query)
3 יצירה, שמירה ורענון שאילתה
3.1.יצירת שאילתה מטבלה/טווח נתונים
3.2. שימרת השאילתה בקובץ העבודה
אז למה כדאי ללמוד לעבוד עם עורך השאילתות ?
שליטה בכלי תפתח בפנכם עולם חדש של אפשרויות לביצוע משימות הן ברמת המורכבות והן ברמת האוטומציה.
ככל שתלמדו לשלוט בכלי בצורה טובה ומתקדמת יותר כך תוכלו לבצע בקלות רבה יותר את הפעולות הבאות:
- ניקוי, טיוב, עיבוד ומניפולציה בנתונים
- איחוד קבצים וטבלאות בקלות וביעילות
- רענון אוטומטי של נתונים ומשיכת נתנים מקבצים ותיקיות
- אלטרנטיבה לביצוע פעולה זהה ל Vlookup עם מספר יתרונות בולטים
- פעולות סויימות המקבילות Pivot Table
- ביטול Pivot - פעולה הפוכה מ Pivot
כך לדוגמה אם אתם מבצעים פעמים פעולות רבות כגון:
- שליפת נתונים מטבלת PIVOT ע"י שימוש ב VLOOKUP
- העתקות והדבקות של עשרות טבלאות מקבצים וגיליונות שונים לצורך איחודם
- VLOOKUPפים מסורבלים ללא סוף
- קישורים מיותרים בין קבצים
- קבצים עם טבלאות נתונים גדולות מאוד
ובקיצור הרבה עבודה מסורבלת ומייגעת החוזרת על עצמה פעמים רבות ולעיתים מצריכה כמה ימים של עבודה כדי להייצר דוח - יכול להיות שתוכלו לייעל את העבודה משמעותית! עד לרמה של לחליצת כפתור ורענות קובץ וזאת מבלי לדעת לכתוב קוד VBA.
סרטון המאמר
יצירת שאילתה מטבלה/טווח נתונים
באופן כללי ניתן להקים שאילתות ממקורות שונים כמו קובץ אקסל (חיצוני לקובץ עליו אנו עובדים), קבצי טקסט, תיקייה ומקורות, שרתים שונים באינטרנט.
אנו מתחילים בדוגמה הפשוטה ביותר בה מקור השאלתה שלנו הוא טווח או טבלת נתונים בקובץ עליו אנו עובדים – כלומר אנו לא עושים שימוש במקור חיצוני.
כדי לייצר שאילתה חדשה יש לעמוד בתוך טווח הנתונים שלנו ← לשונית DATA ← From Table
בשלב זה אנו ניכנס לממשק המשתמש של עורך השאילתות (Power Query)
שמירת השאילתה בקובץ העבודה
לאחר שביצענו את הפעולות הרצויות נרצה לשמור את השאילתה במקום הרצוי כאשר האפשרויות שלנו הן:
1. באחד מהגיליונות הקיימים בקובץ
2. בגיליון חדש
3. חיבור בלבד – הנתונים אינם ממוקמים בקובץ עצמו אלא נוצר לנו קישור לתוצאות השאילתה ונוכל לעשות בהם שימוש במידה ונרצה.
אפשרות ראשונה:
מיקום השאילתה בקובץ עליו אנו עובדים בגיליון חדש
לשונית File ← Close and load
במצב זה יפתח גיליון חדש ובו תמוקם השאילתה
אפשרות שנייה:
ליצור חיבור בלבד. במצב זה השאילתה ממוקמת ממש באחד מגיליונות העבודה שלנו, אך יש לנו גישה לנתוני השאילתה ע"י החיבור שנוצר.
לשונית File ← Close and Load to ← בחירה ביעד הרצוי
יצירת חיבור בלבד - Only create connection
בדוגמה זו אנו נבחר באפשרות Close and load
רענון השאילתה
לאחר שהעלנו את השאילתה היא תישאר מקושרת לנתוני המקור – כלומר לטבלה המקורית וכל שינוי שיעשה בנתוני הטבלה המקורית יתעדכן בשאלתה לאחר ריענון השאילתה.
כדי לרענן שאלתה יש להציג את חלונית השאילתות ← ללחוץ על ריענון
מילוי ערכים ורענון השאילתה
בדוגמה זו המטרה היא למלא את התאים הריקים במספר הקטגוריה שלהם
בעורך השאילתות נסמן את עמודה "מספר קטגוריה" ← לשונית Transform ← נבחר ב Fill Down
ונראה שהתאים הריקים מקבלים את ערכי הקטגוריה שלהם
בשלב זה נבחר באפשרות Close and Load כדי להעלות את השאילתה לגיליון בקובץ העבודה.
פיצול ערכים לעמודות נפרדות - Split to column
לחצו כאן לדף התמיכה של Microsoft בנושא זה
בדוגמה זו יש לנו טבלה הכוללת עמודה בה מספר הקטגוריה ושם הקטגוריה נמצאים באותו התא ללא תו מפריד בניהם.
נעלה את הנתונים לעורך השאילתות כפי שלמדנו בחלק השני של המאמר ← נסמן את העמודה הרצויה ← נבחר ב Split Columns ← By number of characters ← נבחר במספר התווים הרצוי (4 בדוגמה זו) ← נבחר בכיוון ממנו נרצה לספור את התווים (שמאל בדוגמה זו)
הערה – בדוגמה הזו ניתן ועדיף לבחור באפשרות של פיצול לפי הפרדה בין מספר לטקסט. הסיבה שזו לא הדרך המוצגת במאמר שישנם גרסאות לא מעודכנות בהן האפשרות אינה קיימת
ונקבל את התוצאה הרצויה
