יצירת שאילתת פרמטר

1. לחץ על data _GT_ קבל _AMP_ המרת נתונים _GT_ קבל את הנתונים > ממקורות אחרים > מ-Microsoft Query.

2. בצע את שלבי אשף השאילתות. במסך של אשף השאילתות-סיום , בחר הצג נתונים או ערוך שאילתה ב-Microsoft Query ולאחר מכן לחץ על סיום. חלון השאילתה של Microsoft נפתח ומציג את השאילתה.

3. לחץ על הצג את > SQL. בתיבת הדו SQL שמופיעה, אתר את פסוקית WHERE – שורה המתחילת במילה שם, בדרך כלל בסוף קוד ה-SQL. אם אין משפט WHERE, הוסף אחד על-ידי הקלדת המקום בשורה חדשה בסוף השאילתה.

4. לאחר המיקום, הקלד את שם השדה, אופרטור השוואה (=, <, >, LIKE וכן הלאה) ואחת מהאפשרויות הבאות:

   • עבור בקשת פרמטר כללי, הקלד סימן שאלה (?). אף צירוף מילים שימושי אינו מוצג בבקשה שמופיעה כאשר השאילתה מופעלת.

     

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

     

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

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

   

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

   

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

   

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

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

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

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

בחוברת עבודה ריקה, אנו יוצרים חיבור למסד הנתונים של Access הכולל את רשומות תעבורת האינטרנט שאנו רוצים – כולל שדות המציינים מתי כל עמוד פורסם במקור. מוטען ב-Power Query, הוא נראה כך:

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

בשלב הבא, ניצור פרמטר להגבלת התוצאות לפי התאריך שבו העמוד פורסם במקור. לחץ על Home > Parameters > נהל פרמטרים כדי לפתוח את תיבת הדו פרמטרים.

לחץ על חדש, והטופס מציג פרמטר חדש בשם Parameter1 ללא מידע אחר.

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

• שינוי שם ל- FirstPubD

• שנה את התיאור לתאריך שבו פורסם העמוד לראשונה.Description

• שינוי סוגלתאריך כך שהפרמטר מקבל רק ערכי תאריך

• הגדר את הערך הנוכחי כך שהפרמטר לא יסנן את כל השורות כאשר לא סיפקנו קלט-אנו משתמשים ב-1/1/2010.

לחץ על אישור כדי ליצור את הפרמטר ולראות אותו בעורך השאילתות של Power Query.

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

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

בתיבת הדו סינון שורות, נבחר פרמטר מרשימת האפשרויות במסנן.

הזן או בחר ערך מוחלף ברשימה של פרמטרים זמינים. יש רק אחד, זה שבדיוק יצרנו, FirstPubD.

נבחר אותה ולחץ על אישור. עורך השאילתות של Power יטען את השאילתה באמצעות הפרמטר החדש כמסנן.

כדי לבדוק את הפרמטר, אנו משנים את הערך שלו ל- 1/1/2018.

אנו מרעננים את השאילתה, שמציגה כעת רק שורות הכוללות FirstPublishDate לאחר 1/1/2018.

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

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

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

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

מאחר שהנתונים שנטענו כסוג הנתונים תאריך/שעה, אנו צריכים לשנות אותו לסוג הנתונים תאריך/שעה, כך שהוא תואם לפרמטר שלנו, ולאחר מכן לחץ על בית _GT_ המרה _GT_ Transformסוג נתונים > Date.

אנו גם משנה את שם השאילתה שלנו למשהו בעל משמעות רבה יותר מ-Table2. כדי להבהיר מה משמעות הדבר, אנו קוראים לו FirstPubDate.

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

התצוגה המקדימה מציגה כעת את הערך במקום הטבלה.

אין צורך שהנתונים של השאילתה החדשה ייטענו בכל מקום-הנתונים שלה כבר נמצאים בגליון העבודה שבו אנו מעוניינים בכך. אנו זקוקים רק לחיבור כך ש-Power Query יוכל לקבל את ערך הפרמטר. לאחר מכן, אנו לוחצים על File _GT_ Close & Load to. .. כדי לפתוח את תיבת הדו ייבוא נתונים, ולאחר מכן בחר צור חיבור בלבד.

כעת יש לנו שאילתה בשם "FirstPubDate" אשר מושכת ערך תאריך בודד מטבלה בגליון העבודה ממש מעל המקום שבו השאילתה הראשית שלנו נטענת. כעת עלינו רק להשתמש בשאילתה זו כפרמטר לסינון השאילתה הראשית שלנו. לאחר מכן, אנו פותחים את השאילתה הראשית ועורכים את השלב המסנן שורות באמצעות העמודה FirstPublishDate. אנו נרחיב את שורת הנוסחאות ונבחר את הפרמטר שיצרנו בעבר (FirstPubD). לאחר מכן, הקלד "a" לאחר FirstPubD -מאחר שהשם של השאילתה החדשה מתחיל עם אותן אותיות כמו הפרמטר, Power query מציג אותה כאפשרות לבחירה.

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

הכל נראה נכון, ולכן אנו יוצאים מעורך השאילתה של Power Query ושומרים את השינויים שלנו. כדי לבדוק את הפרמטר, בגליון העבודה של הדוח נשנה את ערך התא בטבלה בחלק העליון של 5/4/2019, ולאחר מכן נרענן את החיבור כדי לראות את הנתונים המסוננים.

המסנן החדש שלנו עובד! כדי לשמור ולסגור את חוברת העבודה. כעת כל אדם המשתמש בחוברת העבודה יכול לציין תאריך של פרסום ראשון שישמש כמסנן שאילתה – ממש שם באותו גליון עבודה שבו השאילתה נטענת.

1. לחץ על data _GT_ קבל _AMP_ המרת נתונים > קבל נתונים _GT_ הפעלה של Power Query Editor.

2. בעורך Power Query, לחץ על Home > Parameters > נהל פרמטרים.

3. בתיבת הדו פרמטרים, לחץ על חדש.

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

   • Name -זה אמור לשקף את הפונקציה של הפרמטר, אך לשמור אותו קצר ככל האפשר.

   • תיאור -פעולה זו עשויה להכיל פרטים שיעזרו לאנשים להשתמש בפרמטר כראוי.

   • נדרש -בחר כדי לגרום לפרמטר זה לדרוש ערך.

   • Type -פעולה זו מציינת את סוג הנתונים שאליו נדרש הפרמטר.

   • ערכים מוצעים -אם תרצה, הוסף רשימת ערכים או ציין שאילתה שתספק הצעות לקלט.

   • ערך ברירת מחדל -פעולה זו מופיעה רק אם הערכים המוצעים מוגדרים לרשימת ערכים ומציינת את פריט הרשימה כברירת המחדל.

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

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

1. פתח שאילתה בעורך השאילתות של Power Query.

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

3. בתיבת הדו סינון שורות, לחץ על הלחצן משמאל לתנאי המסנן ולאחר מכן בצע אחת מהפעולות הבאות:

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

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

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

2. לחץ על הערך ולאחר מכן לחץ על data _GT_ קבל _AMP_ המרת נתונים _GT_ מטבלה/טווח.

3. בעורך Power Query, בצע שינויים כלשהם בחיבור הטבלה (לדוגמה, שינוי סוג הנתונים או השם) ולאחר מכן לחץ על Home > Close > Close & load > close &...

4. בתיבת הדו ייבוא נתונים, לחץ על צור חיבור בלבד, באופן אופציונלי בחר הוסף למודל נתוניםולאחר מכן לחץ על אישור.

5. פתח את השאילתה שברצונך לסנן בעורך השאילתות של Power Query.

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

7. בצע את אחת מהפעולות הבאות:

   • בחר ערך מתוך הרשימה הנפתחת של ערכים (אלה מגיעים מנתוני השאילתה).

   • בחר ערך באמצעות הלחצן בקצה השמאלי של תנאי המסנן.

8. לחץ על החץ בקצה השמאלי של שורת הנוסחאות כדי להציג את השאילתה כולה.

9. תנאי המסנן עוקב אחר המילה כל אחת:

   • שם העמודה המסוננת מופיעה בסוגריים מרובעים.

   • אופרטור ההשוואה מתבצע מיד אחרי שם העמודה.

   • ערך המסנן מתבצע מיד אחרי אופרטור ההשוואה ומסתיים בתו הסוגריים הסוגר. בחר את הערך כולו.

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

11. לחץ על Home > Close > close & Load.

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