יצירת שאילתת פרמטר (Power Query)

ייתכן שאתה מכיר היטב שאילתות פרמטר בשימוש בהן ב- SQL או ב- Microsoft Query. עם זאת, לפרמטרים של Power Query יש הבדלים עיקריים:

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

הערה אם אתה מעוניין בדרך השניה ליצירת שאילתות פרמטר, ראה יצירת שאילתת פרמטר ב- Microsoft Query.

יצירת פרמטר

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

1. בחר נתונים>, קבל נתונים>מקורות> אחריםהפעל את עורך Power Query.

2. ב- עורך Power Query, בחר בית>ניהול פרמטרים חדשים פרמטרים>.

3. בתיבת הדו-שיח ניהול פרמטר , בחר חדש.

4. הגדר את הפריטים הבאים לפי הצורך:

   שם	זה אמור לשקף את פונקציית הפרמטר, אך הקפד שהוא יהיה קצר ככל האפשר.
   תיאור	ערך זה יכול להכיל פרטים שיעזרו לאנשים להשתמש בפרמטר בצורה נכונה.
   נדרש	בצע אחת מהפעולות הבאות: כל ערך באפשרותך להזין כל ערך מכל סוג נתונים בשאילתת הפרמטר. רשימת ערכים באפשרותך להגביל את הערכים לרשימה ספציפית על-ידי הזנתם ברשת הקטנה. עליך גם לבחור ערך ברירת מחדלוערך נוכחי להלן. שאילתה בחר שאילתת רשימה, שדומה לעמודה מובנית של רשימה המופרדת באמצעות פסיקים ומוקפת בסוגריים מסולסלים. לדוגמה, שדה מצב בעיות יכול להכיל שלושה ערכים: {"חדש", "מתמשך", "סגור"}. עליך ליצור את שאילתת הרשימה מראש על-ידי פתיחת העורך המתקדם (בחר 'דף הבית>עורך מתקדם'), הסרת תבנית הקוד, הזנת רשימת הערכים בתבנית רשימת השאילתות ולאחר מכן בחירה באפשרות 'סיום'. לאחר שתסיים ליצור את הפרמטר, שאילתת הרשימה תוצג בערכי הפרמטרים.
   Type	ערך זה מציין את סוג הנתונים של הפרמטר.
   ערכים מוצעים	במידת הצורך, הוסף רשימת ערכים או ציין שאילתה כדי לספק הצעות לקלט.
   ערך ברירת מחדל	הגדרה זו מופיעה רק אם האפשרות 'ערכים מוצעים ' מוגדרת כ'רשימת ערכים' ומציינת איזה פריט רשימה הוא ברירת המחדל. במקרה זה, עליך לבחור ברירת מחדל.
   ערך נוכחי	בהתאם למיקום שבו אתה משתמש בפרמטר, אם הוא ריק ייתכן שהשאילתה לא תחזיר תוצאות. אם האפשרות 'נדרש ' נבחרה, הערך הנוכחי אינו יכול להיות ריק.

5. כדי ליצור את הפרמטר, בחר אישור.

שימוש בפרמטר לשינוי מקור נתונים

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

שלב 1: יצירת שאילתת פרמטר

בדוגמה הבאה, קיימים כמה קבצי CSV שאתה מייבא באמצעות פעולת תיקיית הייבוא (בחירת נתונים>, קבל נתונים>מ- FilesFrom>Folder) מתיקייה C:\DataFilesCSV1. אך לעתים תיקיה אחרת משמשת מדי פעם כמיקום לשחרור הקבצים, C:\DataFilesCSV2. באפשרותך להשתמש בפרמטר בשאילתה כערך חלופי עבור התיקיה השונה.

1. בחר בית>ניהול פרמטרים>פרמטר חדש.

2. הזן את המידע הבא בתיבת הדו-שיח Manage Parameter :

   שם	CSVFileDrop
   תיאור	מיקום חלופי לשחרור קבצים
   נדרש	כן
   Type	Text
   ערכים מוצעים	כל ערך
   ערך נוכחי	C:\DataFilesCSV1

3. בחר אישור.

שלב 2: הוספת הפרמטר לשאילתת הנתונים

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

שלב 3: עדכון ערך הפרמטר

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

1. בחר חיבורי נתונים>& שאילתותהכרטיסיהשאילתות>, לחץ באמצעות לחצן העכבר הימני על שאילתת הפרמטר ולאחר מכן בחר ערוך.
2. הזן את המיקום החדש בתיבה 'ערך נוכחי ', כגון C:\DataFilesCSV2.
3. בחר בית,>סגור & טען.
4. כדי לאשר את התוצאות, הוסף נתונים חדשים למקור הנתונים ולאחר מכן רענן את שאילתת הנתונים באמצעות הפרמטר המעודכן (בחר'רענןנתונים> הכל').

שימוש בפרמטר לסינון נתונים

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

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

2. בחר את חץ המסנן בכותרת עמודה כלשהי כדי לסנן את הנתונים ולאחר מכן בחר פקודת סינון, כגון 'מסנני תאריך/שעה'>אחרי. תיבת הדו-שיח 'סינון שורות' מופיעה .

   

3. בחר את הלחצן מימין לתיבה ' ערך ' ולאחר מכן בצע אחת מהפעולות הבאות:

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

4. הזן את התאריך החדש בתיבה ערך נוכחי ולאחר מכן בחר בית>סגור & טען.

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

6. הזן את התאריך החדש בתיבה 'ערך נוכחי '.

7. בחר בית,>סגור & טען.

8. כדי לאשר את התוצאות, הוסף נתונים חדשים למקור הנתונים ולאחר מכן רענן את שאילתת הנתונים באמצעות הפרמטר המעודכן (בחר'רענןנתונים> הכל').

שימוש בערך תא לסינון נתונים

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

1. בגליון העבודה בחוברת העבודה שבה נטענת השאילתה שברצונך לסנן, צור טבלת Excel עם שני תאים: כותרת עליונה וערך.

   MyFilter
   G

2. בחר תא בטבלת Excel ולאחר מכן בחר 'נתונים>קבלת נתונים>מטבלה/טווח'. עורך Power Query מופיע.

3. בתיבה שם בחלונית הגדרות שאילתה משמאל, שנה את שם השאילתה כך שיהיה בעל משמעות, כגון FilterCellValue.

4. כדי להעביר את הערך בטבלה, ולא את הטבלה עצמה, לחץ באמצעות לחצן העכבר הימני על הערך בתצוגה מקדימה של נתונים ולאחר מכן בחר 'הסתעפות'.
   שים לב שהנוסחה השתנתה ל- = #"Changed Type"{0}[MyFilter]
   כאשר תשתמש בטבלת Excel כמסנן בשלב 10, Power Query יפנה לערך הטבלה כתנאי המסנן. הפניה ישירה אל טבלת Excel תגרום לשגיאה.

5. בחר בית:>סגור & טען>,סגור & טען אל. כעת יש לך פרמטר שאילתה בשם "FilterCellValue", שבו אתה משתמש בשלב 12.

6. בתיבת הדו-שיח 'ייבוא נתונים ', בחר 'צור חיבור בלבד' ולאחר מכן בחר 'אישור'.

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

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

9. הזן ערך כלשהו בתיבה ערך , כגון "G" ולאחר מכן בחר אישור. במקרה זה, הערך הוא מציין מיקום זמני עבור הערך בטבלה FilterCellValue שאותו תזין בשלב הבא.

10. בחר את החץ בצד השמאלי של שורת הנוסחאות כדי להציג את הנוסחה כולה. להלן דוגמה של תנאי מסנן בנוסחה:

    = Table.SelectRows(#"Changed type", each Text.StartsWith([Name], "G"))

11. בחר את הערך של המסנן. בנוסחה, בחר "G".

12. באמצעות M IntelliSense, הזן את האות הראשונה של הטבלה FilterCellValue שיצרת ולאחר מכן בחר אותה מהרשימה המופיעה.

13. בחר 'בית'>, 'סגור','סגירה> & טעינה'.

Result

השאילתה שלך משתמשת כעת בערך בטבלת Excel שיצרת כדי לסנן את תוצאות השאילתה. כדי להשתמש בערך חדש, ערוך את תוכן התא בטבלת Excel המקורית בשלב 1, שנה את "G" ל- "V" ולאחר מכן רענן את השאילתה.

שליטה בשימוש בשאילתות פרמטר

באפשרותך לקבוע אם שאילתות פרמטר מותרות או לא מותרות.

1. ב- עורך Power Query, בחר 'אפשרויות קובץ>'ו'אפשרויות> שאילתתהגדרות'עורך>Power Query.
2. בחלונית מימין, תחת כללי, בחר עורך Power Query.
3. בחלונית משמאל, תחת פרמטרים, בחר או נקה את אפשר תמיד הגדרת פרמטרים בתיבות הדו-שיח 'מקור נתונים' ו'המרה'.

למידע נוסף

עזרה עבור Power Query for Excel

שימוש בפרמטרים של שאילתה (docs.com)