טבלת פרמטרים אופקית

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

אז מה יש לנו

טבלה בשם Sales שמכילה את נתוני המכירות במגרש מכוניות:

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

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

שלב ראשון

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

איך פונים לפרמטרים?

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

PRM[Value]{0}

כלומר – פנינו לשאילתה בשם PRM, לעמודה בשם Value ואז לשורה הראשונה (שמספרה 0) שמכילה את הדגם.

אבל, יש לנו כאן שתי בעיות…

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

אז מה הפתרון?

נהפוך את הטבלה באמצעות 'בצע חילוף' בכרטיסיית 'המר':

וזו התוצאה שהתקבלה:

נקדם את הכותרות בכרטיסיית 'בית' > 'השתמש בשורה ראשונה ככותרות':

ונקבע את סוג המידע המתאים לכל עמודה.
זו התוצאה שהתקבלה:

ומה היתרון בשיטת העבודה זו?

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

שלב שני – טעינת טבלת המידע

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

Table.SelectRows(#"סוג שהשתנה", each ([Model] = "ביואיק"))

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

([Model] = "ביואיק")

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

= Table.SelectRows(#"סוג שהשתנה", each ([Model] = PRM[Model]{0}))

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

שימו לב:

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

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

= Table.SelectRows(#"סוג שהשתנה", each ([Model] = "קרייזלר") and ([Color] = "כחול"))

נקליד:

= Table.SelectRows(#"סוג שהשתנה", each ([Model] =PRM[Model]{0}) and ([Color] = PRM[Color]{0}))

את המחיר המקובע, נחליף למחיר שמבוסס על הפרמטר:
במקום:

= Table.SelectRows(#"שורות מסוננות", each [Price] > 100000)

נקליד

= Table.SelectRows(#"שורות מסוננות", each [Price] > PRM[From Price]{0})

ובמקום התאריכים האקראיים שבחרנו:

= Table.SelectRows(#"שורות מסוננות1", each [Selling Date] >= #date(2020, 1, 1) and [Selling Date] <= #date(2020, 1, 15))

נקליד:

= Table.SelectRows(#"שורות מסוננות1", each [Selling Date] >= PRM[From Date]{0} and [Selling Date] <= PRM[To Date]{0})

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

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

להורדת קובץ העבודה לחצו כאן