רקע
עד היום כאשר רצינו לייבא מידע לתוך האקסל היו קיימות מס' אפשרויות לייבוא נתונים, לדוגמא:
ייבוא של נתונים לתוך גיליון אקסל (נמצא בתוך לשונית הDATA) / ליצור קוד ב VBA אשר מייבא את המידע לתוך גיליון אקסל וכו'.
לשיטת עבודה זו יש כמה חסרונות:
- כמות נתונים – החסרון הגדול הוא במקרים בהם אנחנו רוצים לייבא כמות גדולה של מידע, האקסל נחנק. לאקסל עלול לקחת המון זמן לייבא את המידע, וגם לבצע חישובים לאחר מכן.
- תהליך ETL – במידה ואנחנו נייבא מידע מהאינטרנט לא נקבל את המידע בצורה מסודרת.
האקסל מייבא את כל העמוד שאנו רוצים לייבא, ולאחר מכן עלינו לעבוד קשה עד שנגיע לטבלה נקיה עם הנתונים שאנחנו רוצים לראות. - מגוון מקורות המידע מצומצם ? כמות המקורות מאוד קטנה יחסית בהשוואה לPower Query, עליו נדבר עוד מעט.
ניתן לסכם ולומר כי שיטת ייבוא הנתונים דרך הגיליון הינה מסורבלת וקשה משמעותית בהשוואה ל Power Query.
אז מה זה Power Query?
Power Query הוא כלי שמטרתו העיקרית הוא לייבא את הנתונים לתוך הקובץ ולאחר מכן לבצע בעזרתו תהליך של המרת הנתונים וטיוב המידע (ETL), כך שבשלבים מתקדמים נוכל לנתח את הנתונים בנוחות וביעילות.
בעזרת Power Query אנו יכולים לייבא כמות גדולה של מידע ממקורות מידע שונים, ובנוסף להכין את הנתונים לקראת ניתוחם בהמשך הדרך (בעזרת Power Pivot).
היתרון הגדול של ה Power Query הוא שאנו יכולים לבצע את כל הפעולות הנ"ל טרם העברת הנתונים לאקסל.
יתרון נוסף הוא פשטות רענון המידע שיש בPower Query. לאחר שטענו את המידע לקובץ, כל שעלינו לעשות זה ללחוץ על לחצן הרענון והנתונים יתעדכנו בהתאם לנתונים העדכניים ממקור המידע.
חשוב להבין את תפקידו של Power Query בתוך הציר בו נעים הנתונים עד אשר מוצגים בגיליון.
1) בשלב הראשוני המידע מיובא דרך Power Query שם נטייב הנתונים ונכין אותם לניתוח.
2) בשלב שני נטען את הנתונים במודל שלנו ? בתוך Power Pivot, שם ניתן ליצור מדדים מחושבים על בסיס המודל שלנו, ליצור היררכיות וקשרים בין הטבלאות.
נציין כי גם קיימת האפשרות לדלג על טעינת המידע ל Power Pivot ולטעון ישירות את המידע לגיליון האקסל.
3) בשלב האחרון נבנה Dashboard ונציג את המידע על גיליון האקסל.
רקע כללי על Power Query – יצא לאור כתוסף שניתן להתקינו בגרסת Excel 2010, תחת השם Data Explorer וכעבור זמן מה שונה שמו ל Power Query. כיום ניתן להתקין את התוסף Power Query בגירסת Microsoft Office 2013 Professional Plus.
מקורות המידע של Power Query
כיום ניתן לייבא מידע בעזרת Power Query ממגוון מקורות שונים. רשימת המקורות מתארכת מכיוון שמיקרוסופט מקפידה לעדכן מקורות נוספים על בסיס קבוע.
מקורות המידע מהם ניתן לייבא את המידע הם:
1. אינטרנט ?
א. עפ"י כתובת URL שנזין.
ב. באמצעות אפשרות של חיפוש מידע.
2. מתוך קובץ חיצוני ?
א. קובץ אקסל
ב. קובץ CSV
ג. קובץ XML
ד. קובץ טקסט
ה. מתוך תיקייה ? ניתן לייבא מתוך תיקייה נתונים, במידה וקיימים מספר קבצים (מהסוג הנ"ל).
3. מתוך Data Base ?
א. SQL
ב. ACCESS
ג. קוביית מידע בתוך SQL
ד. Oracle
ה. Ibm DB2
ו. MySql
ז. PostgreSQL
ח. SyBase
ט. TeraData
4. קבוצת Azure ? זהו שוק מקוון גלובלי בו ניתן לקנות, להפיץ ולמכור נתונים ציבוריים ומסחריים.
5. ממקורות אחרים ?
א. Share Point
ב. OData Feed
ג. Hadoop File
ד. Active Directory
ה. Microsoft Exchange
ו. Facebook
ז. SAP Business Objects
ח. Sales Force Objects/Reports
ט. ODBC
י. Blank Query- ניתן ליצור מקור נתונים כמו לדוגמא מימד הזמן.
6. מתוך טבלה שנמצאת בקובץ אליו אנחנו רוצים לייבא את המידע.
תחילת עבודה ב Power Query
אז בואו נלמד איך לטעון את המידע דרך Power Query.
תחילה, יש להוריד את התוסף ולהתקינו באקסל. הורדה
בדוגמא שלנו אנו נטען נתונים מתוך לחצן החיפוש באינטרנט
נחפש בשורת החיפוש את זוכי המונדיאל ולכן נקיש בשורת החיפוש: World Cup Winners.
נקבל רשימה של מקורות מידע שעונים על תנאי החיפוש שלנו, אנחנו נבחר בתוצאה הראשונה ונלחץ על עריכה של טבלה זו.
כעת יפתח לנו חלון Power Query בו נוכל לעבד את הטבלה ולהכין אותה לניתוח בעתיד.
אני מעוניין להציג את נתונים הבאים: שנת המונדיאל, המדינה שזכתה במונדיאל והמדינה שאירחה את המונדיאל.
לכן תחילה אסיר את העמודות הלא רלוונטיות.
לאחר מכן, אני שם לב שהעמודה הראשונה מכילה את שנת המונדיאל ואת המילה Details.
אסיר את המילה Details מהעמודה הזו ע"י שאני אסמן את העמודה ואלחץ על לחצן Replace Values
בשורת חיפוש אחפש את הערך Details ואותיר את הערך החדש שיופיע במקומו ריק.
כעת הערכים Details הוסרו ונותרו רק השנים בהם הוצעו המונדיאל.
נושא שטרם דיברנו עליו עד כה הוא הגדרת סוג הנתונים של כל עמודה.
חשוב מאוד להגדיר עמודות מסויימות בסוג הנתונים המתאים. לדוגמא, עמודות מספריות יש להגדיר בתור ערכים מספריים ? שלמים או מספרים עשרוניים (וזאת במידה ואנחנו רוצים לבצע חישובים מספריים המתבססים עליהם בעתיד) או להגדיר עמודות של תאריכים בתור תאריכים. לרובPower Query לא יזהה את סוג הנתונים באופן אוטומטי ולכן יש להגדירם ידנית.
לענייננו, אנו נגדיר את עמודת השנים בתור מספר עגול ולכן נסמן את העמודה, ונלחץ על סוג נתונים של מספרים עגולים.
שימו לב שמצד ימין של המסך אנו רואים את כל השלבים שביצענו עד כה בנתונים. ניתן לחזור אחורה וללחוץ על כל שלב בנפרד ולראות את השפעתו על הטבלה.
כעת אנחנו יכולים לטעון את הנתונים לקובץ.
טעינת הנתונים תתבצע ע"י לחצן
סיכום
עוצמתו של Power Query נמדדת ביכולת לייבא ולארגן את הנתונים עוד לפני הגעתם לגיליון ומבלי לחנוק את האקסל.
