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

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

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

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

יצירת פרמטר

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

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

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

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

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

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

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

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

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

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

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

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

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

   שם	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 עם שני תאים: כותרת וערך.

   הסינון שלי
   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)