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

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

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

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

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

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

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

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

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

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

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

1. בחר בית > פרמטרים >פרמטר חדש.

2. הזן את המידע הבא בתיבת הדו-שיח ניהול פרמטר :

   שם	CSVFileDrop
   תיאור	מיקום חלופי של שחרור קבצים
   נדרשת	כן
   סוג	טקסט
   ערכים מוצעים	כל ערך
   ערך נוכחי	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 עם שני תאים: כותרת וערך.
    

   הסינון שלי
   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. בחר בית > סגור >סגור & טעינה.

תוצאה

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

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

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

2. בחלונית מימין, תחת כללי, בחרעורך Power Query.

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