Power Query מציע כמה דרכים ליצירה ולטעינה של שאילתות Power בחוברת העבודה שלך. באפשרותך גם להגדיר הגדרות ברירת מחדל לטעינת שאילתה בחלון אפשרויות שאילתה .
עצה כדי לדעת אם הנתונים בגליון עבודה בצורת Power Query, בחר תא נתונים, ואם כרטיסיית רצועת הכלים תלוית ההקשר של השאילתה מופיעה, הנתונים נטענו Power Query.
אודות השילוב של Power Query ל- Excel
לדעת באיזו סביבה אתה נמצא 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. כעת, ברור איזו כרטיסיה מכילה את הנתונים והכרטיסיה שכוללת את השאילתה.
יצירת שאילתה
באפשרותך ליצור שאילתה מתוך נתונים מיובאים או ליצור שאילתה ריקה.
יצירת שאילתה מנתונים מיובאים
זוהי הדרך הנפוצה ביותר ליצירת שאילתה.
- יבא נתונים מסוימים. לקבלת מידע נוסף, ראה ייבוא נתונים ממקורות נתונים חיצוניים.
- בחר תא בנתונים ולאחר מכן בחר עריכת שאילתה>.
יצירת שאילתה ריקה
ייתכן שתרצה להתחיל מאפס. ניתן לעשות זאת בשתי דרכים.
- בחר נתונים>קבל נתונים ממקורות>אחרים שאילתה>ריקה.
- בחר נתונים>קבל נתונים>הפעלת עורך Power Query.
בשלב זה, באפשרותך להוסיף שלבים ונוסחאות באופן ידני אם אתה מכיר היטב את שפת Power Query M.
לחלופין, באפשרותך לבחור בית ולאחר מכן לבחור פקודה בקבוצה שאילתה חדשה. בצע אחת מהפעולות הבאות.
- בחר מקור חדש כדי להוסיף מקור נתונים. פקודה זו זהה לפקודה קבלת>נתונים ברצועת הכלים של Excel.
- בחר מקורות אחרונים כדי לבחור ממקור נתונים שאתה עובד איתו. פקודה זו זהה לפקודה מקורות>אחרונים של נתונים ברצועת הכלים של Excel.
- בחר הזן נתונים כדי להזין נתונים באופן ידני. באפשרותך לבחור בפקודה זו כדי לנסות את עורך Power Query ללא קשר למקור נתונים חיצוני.
טעינת שאילתה
בהנחה שהשאילתה שלך חוקית ולא מכילה שגיאות, באפשרותך לטעון אותה בחזרה לגליון עבודה או למודל נתונים.
טעינת שאילתה עורך Power Query
בתיבת עורך Power Query, בצע אחת מהפעולות הבאות:
כדי לטעון לגליון עבודה, בחר בית סגור>& סגור וטען>& טען.
כדי לטעון למודל נתונים, בחר סגור בית>& סגור את>& טען אל.
בתיבתהדו-שיח ייבוא נתונים, בחר הוסף נתונים אלה למודל הנתונים.
עצה לעתים הפקודה טען אל מעומעמת או לא זמינה. הדבר עשוי להתרחש בפעם הראשונה שאתה יוצר שאילתה בחוברת עבודה. במקרה זה, בחר סגור &, בגליון העבודה החדש, > בחר שאילתות נתונים&> שאילתות חיבורים, לחץ באמצעות לחצן העכבר הימני על השאילתה ולאחר מכן בחר טען אל. לחלופין, ברצועת הכלים עורך Power Query בחר טען אל>שאילתה.
טעינת שאילתה מהחלונית 'שאילתות וחיבורים'
ב- Excel, ייתכן שתרצה לטעון שאילתה בגליון עבודה אחר או במודל נתונים אחר.
- ב- Excel, בחר>שאילתות & חיבורים ולאחר מכן בחר את הכרטיסיה שאילתות .
- ברשימת השאילתות, אתר את השאילתה, לחץ באמצעות לחצן העכבר הימני על השאילתה ולאחר מכן בחר טען אל. תיבת הדו-שיח ייבוא נתונים תופיע.
- החלט כיצד ברצונך לייבא את הנתונים ולאחר מכן בחר אישור. לקבלת מידע נוסף אודות השימוש בתיבת דו-שיח זו, בחר את סימן השאלה (?).
עריכת שאילתה בגליון עבודה
קיימות כמה דרכים לעריכת שאילתה שנטענות לגליון עבודה.
עריכת שאילתה מנתונים בגליון עבודה של Excel
- כדי לערוך שאילתה, אתר שאילתה שנטען בעבר עורך Power Query, בחר תא בנתונים ולאחר מכן בחר עריכת שאילתה>.
עריכת שאילתה מהחלונית '& חיבורים'
ייתכן שתמצא את החלונית שאילתות & חיבורים היא נוחה יותר לשימוש כאשר יש לך שאילתות רבות בחוברת עבודה אחת וברצונך למצוא שאילתה במהירות.
- ב- Excel, בחר>שאילתות & חיבורים ולאחר מכן בחר את הכרטיסיה שאילתות .
- ברשימת השאילתות, אתר את השאילתה, לחץ באמצעות לחצן העכבר הימני על השאילתה ולאחר מכן בחר ערוך.
עריכת שאילתה מתיבת הדו-שיח 'מאפייני שאילתה'
- ב- Excel, > בחר נתונים&> שאילתות חיבורים, לחץ באמצעות לחצן העכבר הימני על השאילתה ובחר מאפיינים, בחר בכרטיסיה הגדרה בתיבת הדו-שיח מאפיינים ולאחר מכן בחר ערוך שאילתה.
עצה אם אתה נמצא בגליון עבודה עם שאילתה, > בחר מאפייני נתונים, בחר בכרטיסיה הגדרה בתיבת הדו-שיח מאפיינים ולאחר מכן בחר ערוך שאילתה.
עריכת השאילתה של טבלה במודל נתונים
מודל נתונים מכיל בדרך כלל כמה טבלאות מסודרות בקשר גומלין. עליך לטעון שאילתה למודל נתונים באמצעות הפקודה טען אל כדי להציג את תיבת הדו-שיח ייבוא נתונים ולאחר מכן בחירה בתיבת הסימון הוסף נתונים אלה למצב נתונים l. לקבלת מידע נוסף אודות מודלי נתונים, ראה גלה אילו מקורות נתונים נמצאים בשימוש במודל נתונים של חוברת עבודה, יצירת מודל נתונים ב- Excel והשימוש בטבלאות מרובות כדי ליצור PivotTable.
כדי לפתוח את מודל הנתונים, בחר ניהול Power Pivot>.
בחלק התחתון של חלון Power Pivot, בחר את לשונית גליון העבודה של הטבלה הרצויה.
ודא שהטבלה הנכונה מוצגת. מודל נתונים יכול לכלול טבלאות רבות.
שים לב לשם הטבלה.
כדי לסגור את חלון Power Pivot, בחר סגור>קובץ. ייתכן שיחלפו כמה שניות כדי לקבל בחזרה את הזיכרון.
בחר חיבורי>נתונים & הכרטיסיה שאילתות>מאפיינים , לחץ באמצעות לחצן העכבר הימני על השאילתה ולאחר מכן בחר ערוך.
לאחר שתסיים לבצע שינויים בתפריט עורך Power Query, בחר סגור קובץ>& טען.
Result
השאילתה בגליון העבודה והטבלה במודל הנתונים מתעדכנת.
טעינת שאילתה למודל נתונים נמשכת זמן רב במיוחד
אם אתה מבחין שטעינת שאילתה למודל נתונים נמשכת זמן רב יותר מאשר טעינה לגליון עבודה, עיין בשלבי Power Query כדי לראות אם אתה מסנן עמודת טקסט או עמודה מובנית של רשימה באמצעות אופרטור מכיל. פעולה זו גורמת ל- Excel לספור שוב את ערכת הנתונים כולה עבור כל שורה. יתר על כן, ל- Excel אין אפשרות להשתמש ביעילות בביצוע מרובה פריטים. כדי לעקוף את הבעיה, נסה להשתמש באופרטור אחר כגון שווה אומתחיל ב.
Microsoft מודעת לבעיה זו והיא נמצאת בחקירה.
הגדרת אפשרויות טעינת שאילתה
באפשרותך לטעון Power Query:
לגליון עבודה. בתיבת הדו-עורך Power Query, בחר סגור בית>& סגור את>& טען.
למודל נתונים. בחלונית עורך Power Query, בחר סגור בית>& סגור את>& LoadTo.
כברירת מחדל, Power Query טוען שאילתות לגליון עבודה חדש בעת טעינת שאילתה בודדת, וטוענת שאילתות מרובות בו-זמנית למודל הנתונים. באפשרותך לשנות את אופן הפעולה המהווה ברירת מחדל עבור כל חוברות העבודה שלך או רק עבור חוברת העבודה הנוכחית. בעת הגדרת אפשרויות אלה, Power Query אינה משנה תוצאות שאילתה בגליון העבודה או נתונים וביאורים של מודל נתונים.
באפשרותך גם לעקוף באופן דינאמי את הגדרות ברירת המחדל עבור שאילתה באמצעות תיבת הדו-שיח ייבוא, אשר מוצגת לאחר בחירה באפשרות סגור & LoadTo.
הגדרות כלליות החלות על כל חוברות העבודה שלך
בתיבת הדו-עורך Power Query, בחר אפשרויות>קובץ והגדרות אפשרויות>שאילתה.
בתיבת הדו-שיח אפשרויות שאילתה, בצד ימין, תחת המקטע הכללי , בחר טעינת נתונים.
תחת המקטע הגדרות טעינת שאילתה המהוות ברירת מחדל, בצע את הפעולות הבאות:
- בחר השתמש בהגדרות טעינה סטנדרטיות.
- בחר ציין הגדרות טעינה מותאמות אישית המוגדרות כברירת מחדל ולאחר מכן בחר או נקה את האפשרות טעןלגליון עבודה או טען למודל נתונים.
עצה בחלק התחתון של תיבת הדו-שיח, באפשרותך לבחור שחזר ברירות מחדל כדי לחזור בנוחות להגדרות ברירת המחדל.
הגדרות חוברת עבודה החלות רק על חוברת העבודה הנוכחית
בתיבת הדו-שיח אפשרויות שאילתה, בצד ימין, תחת המקטע חוברת עבודה נוכחית , בחר טעינת נתונים.
בצע אחת או יותר מהפעולות הבאות:
תחת זיהוי סוגים, בחר או נקה את זהה סוגי עמודות וכותרות עבור מקורות לא ברורים.
אופן הפעולה המהווה ברירת מחדל הוא לזהות אותם. נקה אפשרות זו אם אתה מעדיף לעצב את הנתונים בעצמך.
תחת קשרי גומלין, בחר או נקה את האפשרות צור קשרי גומלין בין טבלאות בעת הוספה למודל הנתונים בפעם הראשונה.
לפני הטעינה למודל הנתונים, אופן הפעולה המוגדר כברירת מחדל הוא חיפוש קשרי גומלין קיימים בין טבלאות, כגון מפתחות זרים במסד נתונים יחסי וייבא אותם עם הנתונים. נקה אפשרות זו אם אתה מעדיף לעשות זאת בעצמך.תחת קשרי גומלין, בחר או נקה את עדכן קשרי גומלין בעת רענון שאילתות שנטענו למודל הנתונים.
אופן הפעולה המוגדר כברירת מחדל הוא לא לעדכן קשרי גומלין. בעת רענון שאילתות שכבר נטענו למודל הנתונים, Power Query את קשרי הגומלין הקיימים בין טבלאות כגון מפתחות זרים במסד נתונים יחסי ומעדכן אותם. פעולה זו עשויה להסיר קשרי גומלין שנוצרו באופן ידני לאחר ייבוא הנתונים או הצגת קשרי גומלין חדשים. עם זאת, אם ברצונך לעשות זאת, בחר את האפשרות.
תחת נתוני רקע, בחר או נקה את אפשר הורדה ברקע של תצוגות מקדימות של נתונים.
אופן הפעולה המהווה ברירת מחדל הוא להוריד תצוגות מקדימות של נתונים ברקע. נקה אפשרות זו אם ברצונך לראות את כל הנתונים באופן מיידי.