יצירה, טעינה או עריכה של שאילתה ב- Excel (Power Query)

לדעת באיזו סביבה אתה נמצא Power Query משולב היטב בממשק המשתמש של Excel, במיוחד בעת ייבוא נתונים, עבודה עם חיבורים ועריכת טבלאות Pivot Table, טבלאות Excel וטווחים בעלי שם. כדי למנוע בלבול, חשוב לדעת באיזו סביבה אתה נמצא כעת, ב- Excel או ב- Power Query, בכל שלב נתון.

גליון העבודה המוכר של Excel, רצועת הכלים והרשת	רצועת הכלים של עורך Power Query ותצוגה מקדימה של נתונים

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

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

באפשרותך ליצור שאילתה מתוך נתונים מיובאים או ליצור שאילתה ריקה.

יצירת שאילתה מנתונים מיובאים

זוהי הדרך הנפוצה ביותר ליצירת שאילתה.

1. יבא נתונים מסוימים. לקבלת מידע נוסף, ראה ייבוא נתונים ממקורות נתונים חיצוניים.

2. בחר תא בנתונים ולאחר מכן בחר שאילתה ולאחר> ערוך.

יצירת שאילתה ריקה

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

• בחר נתונים > לקבל נתונים > ממקורות אחרים או >ריקה.

• בחר נתונים > לקבל נתונים >את עורך Power Query.

בשלב זה, באפשרותך להוסיף שלבים ונוסחאות באופן ידני אם אתה מכיר היטב את שפת הנוסחאות M של Power Query.

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

• בחר מקור חדש כדי להוסיף מקור נתונים. פקודה זו זהה לפקודה ' > 'קבלת נתונים ' ברצועת הכלים של Excel.

• בחר מקורות אחרונים כדי לבחור ממקור נתונים שאתה עובד איתו. פקודה זו זהה לפקודה ' מקורות> אחרונים' ברצועת הכלים של Excel.

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

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

טעינת שאילתה מעורך Power Query

בעורך Power Query, בצע אחת מהפעולות הבאות:

• כדי לטעון לגליון עבודה, בחר בית > סגור & טען > סגור & טעינה.

• כדי לטעון למודל נתונים, בחר בית > סגור & טען > סגור & אל.
  
  בתיבת הדו-שיח ייבוא נתונים, בחר הוסף נתונים אלה למודל הנתונים.

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

טעינת שאילתה מהחלונית 'שאילתות וחיבורים' 

ב- Excel, ייתכן שתרצה לטעון שאילתה בגליון עבודה אחר או במודל נתונים אחר.

1. ב- Excel, בחר > שאילתות & חיבורים ולאחר מכן בחר בכרטיסיה שאילתות .

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

3. החלט כיצד ברצונך לייבא את הנתונים ולאחר מכן בחר אישור. לקבלת מידע נוסף אודות השימוש בתיבת דו-שיח זו, בחר את סימן השאלה (?).

קיימות כמה דרכים לעריכת שאילתה שנטענות לגליון עבודה.

עריכת שאילתה מנתונים בגליון עבודה של Excel

• כדי לערוך שאילתה, אתר שאילתה שנטעןה בעבר מעורך Power Query, בחר תא בנתונים ולאחר מכן בחר שאילתה > עריכה.

עריכת שאילתה מהחלונית '& חיבורים'

ייתכן שתמצא את החלונית שאילתות & חיבורים היא נוחה יותר לשימוש כאשר יש לך שאילתות רבות בחוברת עבודה אחת וברצונך למצוא שאילתה במהירות.

1. ב- Excel, בחר > שאילתות & חיבורים ולאחר מכן בחר בכרטיסיה שאילתות .

2. ברשימת השאילתות, אתר את השאילתה, לחץ באמצעות לחצן העכבר הימני על השאילתה ולאחר מכן בחר ערוך.

עריכת שאילתה מתיבת הדו-שיח 'מאפייני שאילתה'

• ב- Excel, בחר נתונים > נתונים & חיבורים > שאילתות, לחץ באמצעות לחצן העכבר הימני על השאילתה ובחר מאפיינים, בחר בכרטיסיה הגדרה בתיבת הדו-שיח מאפיינים ולאחר מכן בחר ערוך שאילתה.

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

מודל נתונים מכיל בדרך כלל כמה טבלאות מסודרות בקשר גומלין. עליך לטעון שאילתה למודל נתונים באמצעות הפקודה טען אל כדי להציג את תיבת הדו-שיח ייבוא נתונים ולאחר מכן בחירה בתיבת הסימון הוסף נתונים אלה למצב נתונים l. לקבלת מידע נוסף אודות מודלי נתונים, ראה גלה אילו מקורות נתונים נמצאים בשימוש במודל נתונים של חוברת עבודה, יצירת מודל נתונים ב- Excel והשימוש בטבלאות מרובות כדי ליצור PivotTable.

1. כדי לפתוח את מודל הנתונים, בחר Power Pivot > ניהול.

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

3. שים לב לשם הטבלה.

4. כדי לסגור את חלון Power Pivot, בחר קובץ > סגור. ייתכן שיחלפו כמה שניות כדי לקבל בחזרה את הזיכרון.

5. בחר נתונים > חיבורים & מאפיינים > שאילתות , לחץ באמצעות לחצן העכבר הימני על השאילתה ולאחר מכן בחר ערוך.

6. לאחר שתסיים לבצע שינויים בעורך Power Query, בחר קובץ> סגור & טעינה.

Result

השאילתה בגליון העבודה והטבלה במודל הנתונים מתעדכנת.

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

Microsoft מודעת לבעיה זו והיא נמצאת בחקירה.

באפשרותך לטעון Power Query:

• לגליון עבודה. בעורך Power Query, בחר בית> סגור & טען > סגור & טעינה.

• למודל נתונים. בעורך Power Query, בחר בית> סגור & טען > סגור & אל. 
  
  כברירת מחדל, Power Query טוען שאילתות לגליון עבודה חדש בעת טעינת שאילתה בודדת, וטוענת שאילתות מרובות בו-זמנית למודל הנתונים.  באפשרותך לשנות את אופן הפעולה המהווה ברירת מחדל עבור כל חוברות העבודה שלך או רק עבור חוברת העבודה הנוכחית. בעת הגדרת אפשרויות אלה, Power Query אינו משנה את תוצאות השאילתה בגליון העבודה או נתונים וביאורים של מודל הנתונים.
  
  באפשרותך גם לעקוף באופן דינאמי את הגדרות ברירת המחדל עבור שאילתה באמצעות תיבת הדו-שיח ייבוא, אשר מוצגת לאחר בחירה באפשרות סגור & טעןאל. 

הגדרות כלליות החלות על כל חוברות העבודה שלך

1. בעורך Power Query, בחר קובץ> אפשרויות והגדרות >שאילתה.

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

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

   • בחר השתמש בהגדרות טעינה סטנדרטיות. 

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

תשר    בחלק התחתון של תיבת הדו-שיח, באפשרותך לבחור שחזר ברירות מחדל כדי לחזור בנוחות להגדרות ברירת המחדל.

הגדרות חוברת עבודה החלות רק על חוברת העבודה הנוכחית

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

2. בצע אחת או יותר מהפעולות הבאות:

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

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

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

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

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