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 זו, הנקראת טבלה1, קל להתבלבל. תמיד מומלץ לשנות את שמות ברירת המחדל של לשוניות גליון העבודה לשמות שנראים לך הגיוניים יותר. לדוגמה, שנה את השם גיליון1 ל - DataTableוטבלה1לטבלה שאילתה. כעת ברור באיזו כרטיסיה יש את הנתונים ובאיזו כרטיסיה יש את השאילתה.
יצירת שאילתה
באפשרותך ליצור שאילתה מנתונים מיובאים או ליצור שאילתה ריקה.
יצירת שאילתה מנתונים מיובאים
זוהי הדרך הנפוצה ביותר ליצירת שאילתה.
- ייבוא נתונים מסוימים. לקבלת מידע נוסף, ראה ייבוא נתונים ממקורות נתונים חיצוניים.
- בחר תא בנתונים ולאחר מכן בחר'עריכתשאילתה>'.
יצירת שאילתה ריקה
אולי כדאי פשוט להתחיל מאפס. ניתן לעשות זאת בשתי דרכים.
- בחירת נתונים>קבלת נתונים>ממקורות> אחריםשאילתה ריקה.
- בחר נתונים>, קבל נתונים>, הפעל את עורך Power Query.
בשלב זה, באפשרותך להוסיף שלבים ונוסחאות באופן ידני אם אתה יודע היטב את שפת הנוסחאות M של Power Query.
לחלופין, באפשרותך לבחור בית ולאחר מכן פקודה בקבוצה שאילתה חדשה . בצע אחת מהפעולות הבאות.
- בחר 'מקור חדש' כדי להוסיף מקור נתונים. פקודה זו זהה לפקודה'קבלת נתונים>' ברצועת הכלים של 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, בחר File>Close & Load.
Result
השאילתה בגליון העבודה והטבלה במודל הנתונים מתעדכנים.
טעינת שאילתה במודל נתונים נמשכת זמן רב במיוחד
אם הבחנת שטעינת שאילתה במודל נתונים נמשכת זמן רב יותר מטעינה לגליון עבודה, בדוק את השלבים ב- Power Query כדי לראות אם אתה מסנן עמודת טקסט או עמודה מובנית של רשימה באמצעות האופרטור מכיל. פעולה זו גורמת ל- Excel לספור שוב את כל ערכת הנתונים עבור כל שורה. יתר על כן, Excel אינו יכול להשתמש ביעילות בביצוע מרובה הליכי משנה. כדי לעקוף את הבעיה, נסה להשתמש באופרטור אחר, כגון 'שווה ל' או 'מתחיל ב'.
Microsoft מודעת לבעיה זו והיא נבדקת כעת.
הגדרת אפשרויות טעינת שאילתה
באפשרותך לטעון Power Query:
לגליון עבודה. ב-עורך Power Query, בחר בית>,סגירה & טעינה>,סגירה & טעינה.
למודל נתונים. עורך Power Query, בחר Home>Close & Load>Close & LoadTo.
כברירת מחדל, Power Query טוען שאילתות לגליון עבודה חדש בעת טעינת שאילתה יחידה, וטוען שאילתות מרובות בו-זמנית למודל הנתונים. באפשרותך לשנות את אופן הפעולה המוגדר כברירת מחדל עבור כל חוברות העבודה שלך או רק עבור חוברת העבודה הנוכחית. בעת הגדרת אפשרויות אלה, Power Query אינו משנה את תוצאות השאילתה בגליון העבודה או בנתונים ובביאורים של מודל הנתונים.
ניתן גם לעקוף באופן דינאמי את הגדרות ברירת המחדל של שאילתה באמצעות תיבת הדו-שיח 'ייבוא ', המוצגת לאחר בחירת 'סגור & LoadTo'.
הגדרות כלליות החלות על כל חוברות העבודה שלך
ב- עורך Power Query, בחר 'אפשרויות קובץ>' ו'הגדרות>' אפשרויות שאילתה.
בתיבת הדו-שיח 'אפשרויות שאילתה ', בצד ימין, תחת המקטע 'גלובלי ', בחר 'טעינת נתונים'.
תחת המקטע הגדרות טעינת שאילתה המהוות ברירת מחדל , בצע את הפעולות הבאות:
- בחר השתמש בהגדרות טעינה רגילות.
- בחר ציין הגדרות טעינה מותאמות אישית המהוות ברירת מחדל ולאחר מכן בחר או נקה את טען לגליון עבודה או טען למודל נתונים.
עצה בחלק התחתון של תיבת הדו-שיח, באפשרותך לבחור באפשרות 'שחזר ברירת מחדל' כדי לחזור בנוחות להגדרות ברירת המחדל.
הגדרות חוברת עבודה החלות על חוברת העבודה הנוכחית בלבד
בתיבת הדו-שיח אפשרויות שאילתה , בצד ימין, תחת המקטע חוברת עבודה נוכחית , בחר טעינת נתונים.
בצע אחת או יותר מהפעולות הבאות:
תחת 'זיהוי סוג', בחר או נקה את 'זהה סוגי עמודות וכותרות עליונות עבור מקורות שאינם מובנים'.
אופן הפעולה המוגדר כברירת מחדל הוא זיהוי שלהם. נקה אפשרות זו אם אתה מעדיף לעצב את הנתונים בעצמך.
תחת קשרי גומלין, בחר או נקה את האפשרות צור קשרי גומלין בין טבלאות בעת הוספה למודל הנתונים בפעם הראשונה.
לפני הטעינה למודל הנתונים, אופן הפעולה המהווה ברירת מחדל הוא לאתר קשרי גומלין קיימים בין טבלאות, כגון מפתחות זרים, במסד נתונים יחסי ולייבא אותם עם הנתונים. נקה אפשרות זו אם אתה מעדיף לעשות זאת בעצמך.תחת 'קשרי גומלין', בחר או נקה את האפשרות 'עדכן קשרי גומלין בעת רענון שאילתות שנטענו במודל הנתונים'.
התנהגות ברירת המחדל היא לא לעדכן קשרי גומלין. בעת רענון שאילתות שכבר נטענו למודל הנתונים, Power Query מאתר קשרי גומלין קיימים בין טבלאות, כגון מפתחות זרים, במסד נתונים יחסי ומעדכן אותם. פעולה זו עשויה להסיר קשרי גומלין שנוצרו באופן ידני לאחר ייבוא הנתונים או להציג קשרי גומלין חדשים. עם זאת, אם ברצונך לעשות זאת, בחר את האפשרות.
תחת נתוני רקע, בחר או נקה את האפשרות אפשר הורדה בתצוגות מקדימות של נתונים ברקע.
אופן הפעולה המוגדר כברירת מחדל הוא הורדת תצוגות מקדימות של נתונים ברקע. נקה אפשרות זו אם ברצונך לראות את כל הנתונים באופן מיידי.