התקשרו אלינו: 052-2928949
ימים א'-ה' בין השעות 9:00-17:00

התקשרו אלינו: 052-2928949 א-ה 9:00-17:00

סינון על פי פרמטר ב Power Query

Power Query

סינון על פי פרמטר ב Power Query

Power Query

שימוש בפרמטרים שנמצאים בגיליון כדי לסנן נתונים ב Power Query

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

במאמר זה אציג בפניכם את הדרך לעשות זאת.
זו אינה דרך טריוויאלית, והיא כוללת בתוכה שימוש בפרמטרים (Parameters) ושינוי של שפת הקוד M Language, אולם הפעולה עצמה פשוטה יחסית.
אז בואו נתחיל…

מה זה פרמטר ב Power Query?

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

אז מה יש לנו?

ראשית, נעשה ספירת מלאי של כל מה שיש לנו:
1. תיקיה שמכילה את כל הקבצים

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

2. רשימה נפתחת בגיליון שמאפשרת לבחור את השנה הרצויה:

(כמובן שאפשר להקליד את הנתונים ואין צורך לבחור אותם מתוך רשימה נפתחת, זה פשוט הרבה יותר קל בצורה הזאת…)

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

בכרטיסיית 'נתונים' נבחר ב 'יבוא נתונים' > 'מקובץ' > 'מתיקייה' וננווט אל התיקייה הרצויה

(לא זוכרים איך טוענים נתונים מתיקיה? לחצו על הקישור למאמר 'טעינת נתונים מתיקייה')

נטען את הטבלה אל הגיליון בלחיצה על 'שילוב וטעינה'

(אם צריך לערוך מניפולציות על הנתונים בחרו ב'שלב והמר נתונים' ובצעו את הדרוש בעורך).
הטבלה נטענה אל הגיליון:

ועכשיו לעבודה…

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

עכשיו, אנחנו צריכים להפוך את הערך שנמצא בתא לפרמטר. את זה עושים כך:

1. ראשית, נייבא את הערך אל תוך האקסל דרך כרטיסיית 'נתונים' > 'מתוך טבלה/ טווח'

2. ב'שלבים שהוחלו' נמחק את כל השלבים, פרט לשלב המקור, בלחיצה על ה X

וכך זה נראה:

3. התקבלה טבלה שמכילה את הערך שבחרנו:

4. ניתן לראות שמדובר בטבלה, על פי הסמן שנמצא בפינה הימנית העליונה

5. עלינו להפוך את הטבלה לשורת טקסט בודדת. את זה עושים בלחיצה ימנית על הערך > 'בצע הסתעפות' (Drill Down)

6. קיבלנו ערך בודד במקום טבלה:

7. כעת ניתן שם משמעותי לפרמטר, לדוגמה – PrmYr

8. נסגור וניצור חיבור בלבד (שהרי אין לנו צורך בטבלה נוספת שמכילה את אותו הערך…)

בשלב השני, אנחנו רוצים לחזור אל השאילתה שיצרנו בעורך, ולסנן על פי ערך אקראי כלשהו, למשל – 2018:

וכאן מגיע השלב המעניין –
1. תחת 'שלבים שהוחלו' נסמן את השלב הרצוי, במקרה שלנו – שלב הסינון

2. נביט בשורת הנוסחאות (אם הכותרות שלכם בעברית, אני מציעה לכם למקם את הסמן בשורת הנוסחאות וללחוץ על שילוב המקשים Ctrl+Shift שמאליים, כדי להציג את הטקסט בצורה נוחה יותר):

3. מבלי להיכנס להסבר על M Language, אנחנו יכולים לראות את המספר 2018 שמופיע בפונקציה.
בשלב הזה עלינו להחליף אותו בשם הפרמטר, כפי שאתם יכולים לראות באיור הבא:

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

ועכשיו, בואו נחזור לגיליון:

אני מזכירה לכם שכבר ביצענו סינון לשנת 2018, ולכן הטבלה נראית כך:

עכשיו נבחר את שנת 2019, נרענן את הטבלה ו…
קסם!

דילוג לתוכן