כיצד נשנה את שם הקובץ באמצעות פרמטר ב Power Query

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

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

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

ראשית, נעשה ספירת מלאי של כל מה שיש לנו (לצורך הנוחות, אני משתמשת באותם הקבצים שבהם השתמשתי במאמר שדיבר על סינון באמצעות פרמטרים):
1. תיקיה שמכילה 3 קבצי מכירות עבור השנים 2018-2020

2. תא שמכיל את הנתיב של הקובץ שאותו ארצה להציג, ובו רשימה נפתחת של כתובת הקבצים:

לתא הזה נתתי את השם Path
שימו לב, לא השתמשתי בטבלה חכמה, אלא במתן שם בדרך הסטנדרטית (דרך מנהל השמות או תיבת השם)

ולעבודה…

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

וזו הטבלה שהתקבלה:

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

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

נסמן את התא שמכיל את ההפניה לשם הקובץ, ותחת כרטיסיית 'נתונים' נבחר ב'מתוך טבלה/טווח'
נקבל טבלה, שמכילה שורה אחת בלבד:

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

2. קיבלנו טבלה עם כותרת:

3. נלחץ על הנתיב לחיצה ימנית>בצע הסתעפות (Drill Down)

4. וזו התוצאה:

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

ועכשיו – שימוש בפרמטר ליצירת הדינמיות

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

נחליף את הנתיב בשם הפרמטר ונלחץ על Enter:

רוב הסיכויים שבשלב הזה תקבלו הודעת שגיאה של Folrmula.Firewall:

כדי לפתור את הבעיה שנוצרה,
ניגש לכרטיסיית 'קובץ' > 'אפשרויות והגדרות' > 'אפשרויות שאילתה'

תחת 'פרטיות' נסמן את 'התעלם מרמות הפרטיות ושפר את הביצועים פוטנציאלית':

נאשר, ולאחר מכן נסגור ונשמור את השאילתה
ועכשיו, כל מה שנשאר זה לשנות את הנתיב, לרענן את הטבלה ולקבל את המידע החדש:

הערה חשובה:

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

רוצים לשפר את הפרויקט? זה אפשרי עם VBA…

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

בחירת קובץ:

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

עדכון אוטומטי של הנתונים לאחר שינוי הנתיב

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

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

If Target.Name.Name = "Path" Then

ActiveWorkbook.RefreshAll

End If

End Sub

(מעוניינים ללמוד עוד על מאקרו אירוע? בדף מאמרי VBA יש המון מידע…)