עדכון (מאי 2016): ישנו גם הפוסט המשלים שסוקר לעומק את משפחת מוצרי Power BI של מיקרוסופט, וכן פוסט אודות החשיבות של BI באופן כללי

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

1. הצורך לאסוף נתונים רלוונטים לניתוח ממגוון מקומות שונים, למשל: מידע אודות מכירות ממערכת הנהלת חשבונות (למשל: פריוריטי, SAP, FreshBooks ואחרות), מידע על הזדמנויות לא מנוצלות ממערכת ה-CRM  (למשל: Salesforce.com, Microsoft Dynamics ואחרות) ומידע על מידת השימושיות במוצר ממערכת ה-Analytics (למשל: Google Analytics, mixpanel, Flurry, Localytics ואחרות).
2. הצורך לאחד ולעבד את המידע שנאסף, מבלי להיות מוגבל על ידי אקסל.

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

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

אז איך Power Query בעצם עובד?

ראשית, צריך לטעון את הטבלה שאתם הולכים לעבוד איתה. זה יכול להיות קובץ אקסל, קובץ טקסט, קובץ CSV, ספריה שמכילה בתוכה קבצי אקסל, View של מסד נתונים, טבלה מתוך דף אינטרנט, דוח של Salesforce, אובייקט של Salesforce ועוד המון מקורות מידע אחרים. מה שמגניב זה שאם בחרתם למשוך נתונים ממקור דינמי כמו דף אינטרנט או View של מסד נתונים, אתם תוכלו לרענן את המידע בלחיצת כפתור בפעם הבאה שתבחרו לעבוד עם הקובץ. כלומר, נאמר ובחרתי למשוך נתונים מטבלה המכילה תוצאות משחקי NBA אשר מתעדכנת באופן רציף, בפעם הבאה שאעבוד עם הטבלה אוכל ללחוץ על Refresh והתוצאות מהמשחקים האחרונים יתווספו לנתונים שהיו שם קודם. פעולת משיכת המידע והעיבוד שלו נקראת בעגה המקצועית (של אנשי מסדי נתונים) שאילתא, או Query.

עכשיו, כשיש לנו נתונים, נשאלת השאלה כיצד לעבוד איתם, או יותר נכון לעבד אותם. אפשר לעבד אותם בתוך האקסל הרגיל, אבל זה פחות מומלץ. עדיף להכין את הטבלה באמצעות ה-Query Editor, שם יש המון אפשרויות מגניבות שזמינות בלחיצת כפתור. אפשר למשל להוסיף טור שמכיל את שתי האותיות הראשונות מהטור לידו;  אפשר גם למלא תאים ריקים בערך של התא הקודם להם שמכיל ערך כלשהו. אפשר לאחד (Merge) שתי שאילתות יחדיו, או סתם לצרף אחת אחרי השנייה (Append). בגדול, אפשר לעשות כל דבר שהייתם יכולים לעשות עם טבלאות אם הייתם יודעים SQL או שפה אחרת לעבודה עם נתונים. כאחד שהסתמך בכתיבת התיזה שלו על חבילת pandas של פייטון אני רוחש הרבה כבוד לממשק הידידותי של Power Query. מה שכן, אני ממליץ ללמוד קצת את המינוחים המקצועיים של העולם הזה אם אתם רוצים להגיע לרמת תפעול גבוהה של התוסף כדי שתוכלו לדעת מה לחפש בגוגל אם נתקעתם. כשמסיימים לעבוד עם ה-Query Editor, לוחצים על Close & Load וממשיכים את העבודה על גבי האקסל הרגיל.

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

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

1. עבור צרכים בסיסיים, חשוב להתאפס על בניית טורים מחושבים (Calculated Fields) עם התניות (If Statement), ולכן קראו את הפוסט אודות התניות רגילות ב-Power Query ואודות התניות מקוננות ב-Power Query. מאוחר יותר אדגים כיצד השתמשתי בהתניות רגילות כאשר יצרתי שאילתא.
2. במידה ולא הצלחתם למצוא כיצד לבצע את מה שאתם מעוניינים באמצעות הממשק, אפשר לכתוב את הפונקציה בשפת M ולהשמש בה בטור המחושב שאתם רוצים ליצור. איך? פשוט תזינו בגוגל Power Query ואת שם הפונקציה שאתם מעוניינים להמיר לשפת M. למשל, הנה התשובה עבור Vlookup. לאחר מכן, טענו את הפונקציה לשאילתם והשתמשו בנוסחא בשדה המחושב. במידה ולא הצלחתם למצוא את התשובה בעצמכם, תוכלו לשכור פרילנסר ב-Elance שיעשה עבורכם את העבודה, אין צורך ללמוד שפת M עבור זה.

יצירת טבלה מאוחדת ודינמית עם Power Query – דוגמה מעשית

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

בקיצור, כך עשיתי את זה.

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

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

לאחר מכן, הוספתי בכל טבלה טור בשם Territory שבטבלה הישראלית קיבל את הערך "Israel" וטבלה האירופית המאוחדת קיבל את הערך "Europe". לאחר מכן, איחדתי את הטבלאות הללו לטבלת חשבוניות אחת, שטוחה ויפיפיה.

לאחר מכן, צריך להכין את שערי החליפין שישמשו להמרת העסקאות. לאחר אינספור חיפושים גיליתי שהתשובה הייתה מתחת לאף שלי כל הזמן. מסתבר שלבנק ישראל ישנו קובץ סטטי של שערי החליפין בשנים 2007-2012, ואליו צירפתי באמצעות Append קובץ שמתעדכן מדי יום ומכיל את שערי החליפין עבור 2013 ואילך. הטעינה של הקובץ הראשון הוא טעינה של קובץ רגיל ששמרתי על המחשב (From File) והטעינה של הקובץ השני היא טעינת מדף אינטרנט (From Web). אגב, לפני הצירוף מחקתי כמה שורות וטורים מיותרים אבל זה פחות חשוב כרגע. על כל פנים, כעת הייתה לי טבלה עם שערי החליפין הרצויים מ-2007 ועד היום, שאפשר לרענן אותה מדי יום והיא תמיד תכיל את המידע המעודכן מדף האינטרנט (נסו והיווכחו בעצמכם). אבל ישנה בעיה: הטבלה איננה מכילה את שער החליפין בימים שבהם אין מסחר (ימי שבת וראשון). לכן, היה עליי קודם כל למלא את התאריכים החסרים ואז למלא בהם את שער החליפין מהיום הקודם בו היו נתונים. כדי למלא את התאריכים החסרים לקחתי טבלת תאריכים מלאה (שזה תמיד טוב שיש, במיוחד כשמשתמשים ב-Power Pivot) ועשיתי Merge עם הטבלה החסרה שלי, תוך פתיחת (Expand) שערי המטבע. כך יצא שעבור תאריכים שלא היה בהם שער יציג, הכלי הציב ערך null. די התבאסתי כי לא היה לי מושג איך לכתוב קוד שימלא את החסר, אבל למזלי לא הייתי צריך: מסתבר שבממשק קיימת אפשרות למילוי תאים ריקים באמצעות הערך שבתא שלפניהם. וואו, איזה כיף. זה אשכרה היה החלק הכי מפחיד בכל התהליך הזה.¹

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

if [Territory]="Israel" then [Qprice]/[ILS_USD] else [Qprice]*[ILS_EUR]/[ILS_USD]

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

עכשיו, כשיש לנו טבלה כזו יפה, שטוחה ודינמית, לא נעשה איזה פיבוט?

בטח שנעשה.

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

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

סיכום

אקסל הוא כלי נהדר, ואני ממליץ לכולם לעשות מאמץ ולעלות מדרגה ל"רמה הבאה" מעבר ל-Sumif. השלב הראשון הוא להכיר פונקציות מעריכיות, Match, Offset וכו'. השלב השני הוא להעמיק את הידע בכלים שנוספו בגרסאות האחרונות של אקסל, בעיקר אלה ממשפחת ה-Power BI. אני מאמין שלא רחוק היום בו הם יהיו סטנדרט בתעשייה לכל מי שעוסק בניתוח נתונים. חוץ מ-Power Query, שהוא הכלי העיקרי שיצא לי להתעסק איתו, חבילת ה-Power BI מכילה גם כאמור את תוסף ה-Power Pivot וכלי ליצירת לוחות מכוונים (Dashboards) בשם Power BI Designer. מהקצת שיצא לי לשחק עם האחרון, מדובר בכלי די חמוד, טיפה פחות עשיר וידידותי מכלי הדוחות של Salesforce.com, שהחיסרון המרכזי שלו הוא שאפשר לשתף את הלוחות רק בתוך הארגון, באמצעות SharePoint, ולא באמצעות כתובת URL שנגישה לכל. כלומר, במקרה שלי, זה דורש ממני ללמוד להשתמש בסביבה נוספת כדי שאהיה מסוגל לפרסם דוחות שלי, ולכן אני משהה כרגע את ההתקדמות שלי בכיוון הזה. בכל אופן, אני מתכנן לכתוב בעתיד הקרוב פוסט בנושא הטמעת מערכת BI, ושם ארחיב קצת יותר על כל נושא ה-Dashboards.

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

חוצמזה, זה די כיף ליצור טבלאות גדולות וגרפים יפים.