POWER QUERY
POWER BI
POWER QUERY
Power BI
הכלי האולטימטיבי ליצירה ולעיבוד של מידע
Power Query הוא תוסף חינמי מבית מיקרוסופט שמיועד לייבוא נתונים ממגוון מסדי נתונים, ליצירה ולעיבוד של מידע, וכל זאת באמצעות ממשק משתמש אינטואיטיבי ונוח לתפעול, ללא צורך בכתיבת קוד.
תהליך העבודה נקרא ETL (Extract, Transform & Load), ובו אנחנו שולפים את הנתונים המאוחסנים במגוון מקורות (Extract), מעבדים אותם (Transform) ולבסוף טוענים אותם אל תוך מערכת המידע שלנו (Load).
כמשתמע משמו – עיקר העבודה בכלי הוא ביצירת שאילתות לניתוח המידע.
אז מהי שאילתה?
בקצרה, שאילתה היא פנייה למסד נתונים לשם קבלת מידע, כאשר המידע יכול להופיע בטבלה אחת, במספר טבלאות הקשורות ביניהן בקשרי גומלין, ואפילו מידע שנשען על שאילתות אחרות.
השאילתה עצמה מכילה רק את הבקשה למידע, ולא את התוצאות, אשר מחושבות בכל פעם מחדש, ומתעדכנות בהתאם לשינויים במסד הנתונים.
לאחר יצירת השאילתות באמצעות הממשק, נוכל להפעיל אותן שוב ושוב, בלי שנצטרך לכתוב אותן מחדש!
החל מגרסת אקסל 2016 הכלי הוא חלק מכרטיסיית 'נתונים' (Data) ואין צורך להתקינו.
באקסל 2016 הוא נקרא 'קבלה והמרה' (Get & Transform) ובגרסת 365 הוא נקרא 'קבל והמר נתונים' (Get & Transform Data).
הכלי יודע להתחבר למסדי נתונים מגוונים, לערוך עליהם מניפולציות, ולטעון את המידע אל טבלאות אקסל או אל מודל הנתונים.
במקרים רבים הוא מהווה את השלב המקדים לשימוש בכלים אחרים (כמו יצירת טבלאות ציר או שימוש ב-Power Pivot).
באמצעות הכלי נוכל:
- לטעון נתונים ממקורות שונים (קבצי אקסל, קבצי מסדי נתונים שונים, קבצי טקסט ואפילו נתונים מהאינטרנט)
- לבצע חיפוש מקוון של מידע (לדוגמה – מתוך ויקיפדיה, Azure Marketplace)
- לאחד מידע ממספר טבלאות לטבלה אחת
- להסיר מידע שאינו רלוונטי (לדוגמה – לבחור רק עמודות מסוימות מתוך כלל מסד הנתונים)
- לבחור אילו פריטים מתוך העמודות לטעון (לדוגמה, נתוני מחלקה מסוימת בלבד)
- לבצע חישובים על הנתונים (לדוגמה – להוסיף מע"מ כבר בעת טעינת הנתונים)
- ליצור רמות קיבוץ, כלומר – ליצור מעין טבלת ציר כבר בעת איסוף הנתונים
- למזג נתונים, כלומר – לאסוף מידע רלוונטי ממספר טבלאות ולהציגו בטבלה אחת
- לאחד קבצים (לדוגמה – לטעון בפעם אחת אל תוך טבלה אחת את כל הקבצים החודשיים)
- לערוך חישובים
- ועוד…
החיבור אל מסדי הנתונים יעשה לרוב מתוך האקסל, והמניפולציות על הנתונים יערכו בתוך העורך המיוחד של התוכנה.
יתרונות השימוש ב- Power Query
פעולות רבות שתוכלו לבצע באמצעות הכלי, ניתן לבצע ישירות גם מתוך האקסל עצמו.
אם כך, לשם מה עלינו ללמוד להשתמש בו?
היתרון הגדול טמון בהבנה שכל פעולה שאנחנו עושים בכלי נשמרת וניתן להשתמש בה שוב ושוב.
מהמקום הזה שימוש בכלי דומה להקלטת מאקרו או לכתיבת קוד VBA, אשר מאפשרים לחזור על משימות רוטיניות בלחיצת כפתור.
תארו לכם סיטואציה שבה בכל שבוע אתם מקבלים טבלה שיש בה שורות ריקות, ועליכם למחוק אותן, או סיטואציה שבה אתם מקבלים קבצים חודשיים ורוצים לאחד אותם לטבלה מצטברת אחת, וכל שעליכם לעשות הוא לשמור את הקובץ בתיקייה, והשאילתה תדע לקחת את הנתונים החדשים באופן אוטומטי ולהחיל עליהם את העיבוד הדרוש.
ומה עם מקרים פשוטים של מיון או סינון? גם כאן – יצרתם את השאילתה פעם אחת, ועכשיו, בכל פעם שתפעילו אותה, הפעולות יתבצעו בעצמן, מבלי שתצטרכו לחזור עליהן שוב ושוב.
מעבר לכך – נכון, אמנם ישנן פעולות רבות שניתן לבצע באמצעותו, אך ישנן פעולות שלא ניתן, או שהדרך לביצוען מסובכת מאוד, לדוגמה – Unpivot, שהיא הפעולה ההפוכה לטבלת ציר ומייצרת טבלה תקנית ממטריצה. זו אחת המשימות הנדרשות, אך היא אינה טריוויאלית בכלי האקסל הרגילים.
איפה נמצא הכלי?
את כל האפשרויות לעבודה עם ה Power Query תמצאו בכרטיסיית 'נתונים' (Data).
באופיס 365 הכלי נקרא 'קבל והמר נתונים' (Get & Transform Data)
ובאקסל 2016 הכלי נקרא 'קבלה והמרה' (Get & Transform).
באקסל 2010 ו-2013 יש להוריד את התוסף ולהתקינו (את הוראות ההתקנה תוכלו למצוא בעמ' 155). בסיום ההתקנה תתווסף כרטיסיה חדשה בשם Power Query אל הרצועה:
בגרסת אקסל 365 רוב עבודת ייבוא הנתונים תעשה מתוך הלחצן 'קבל נתונים' (Get Data). בגרסת אקסל 2016 רוב עבודת ייבוא הנתונים תעשה דרך הלחצן 'שאילתה חדשה' (New Query).
מאמרים
איתור קבצים כפולים בעזרת Power Query
פתיח – ובו הודעה מפחידה… ככה פתאום, בלי שום התרעה, קיבלתי הודעה שהדרייב שלי מלא. זה קרה אחרי שקניתי מחשב חדש והייתי צריכה להגדיר את
צירוף אוטומטי של טבלאות בקובץ
איך ניתן לצרף את כל הטבלאות בקובץ אקסל לטבלה רציפה אחת באופן אוטומטי באמצעות Power Query, כך שכל טבלה חדשה, תתווסף גם היא לטבלה המרכזת?
Power Query – טעינת קבצים מתיקיה: צעד אחר צעד
הדרך הידנית לטעון קבצים מרובים מתיקיה מאפשרת לנו לערוך מניפולציות על הקבצים, שלא תמיד אפשריות בדרך האוטומטית.
מאמר זה מסביר כיצד לטעון את הנתונים באופן ידני, כבסיס ליצירת מניפולציות
הפונקציה IF ב Power Query – עמודה מותאמת אישית
כיצד נוסיף את הפונקציה IF ב Power Query על ידי שימוש בעמודה מותאמת אישית?
כיצד נוסיף תנאים של Or או and? כל זאת ועוד, במאמר
מיזוג טבלאות
כיצד נמזג טבלאות נתונים שמכילות מפתח לטבלה אחת שמכילה את כל המידע? באקסל אנחנו משתמשים ב vlookup (או xlookup) ואילו ב Power Query נבחר במיזוג
עמודה מותנית (המקבילה של IF) ב Power Query
כיצד נשתמש בפונקציה החשובה IF בתוך Power Query?
התשובה היא 'עמודה מותנית' וההסבר המאויר במאמר
פרמטר (Parameter) לשינוי ההפניה לשם הקובץ ב Power Query
כיצד נוכל לשנות את הקבצים שמזינים את השאילתה מתוך הגיליון עצמו, בלי שנצטרך להכנס לעורך ה Power Query בכל פעם שנרצה לשנות את הניתוב?
הסוד הוא שימוש בפרמטרים!
המאמר הזה יסביר לכם בדיוק איך…
סינון על פי פרמטר ב Power Query
איך מסננים טבלה ב Power Query על פי קריטריון שנמצא בגיליון העבודה?
איך מאפשרים למשתמש לבחור את הערך המתאים, ולקבל טבלה מעודכנת?
זאת ועוד במאמר הנ"ל
קיבוץ נתונים לתא אחד
כיצד נאחד לתא אחד רשימת ערכים שמופיעים בצורה טבלאית, באמצעות שימוש ב Power Query, כולל שימוש בפונקציה Table.Column בשפת M
טעינת נתונים מהאינטרנט – שליפת שערי מטבע
כיצד נתחבר למקורות אינטרנטיים לקבלת מידע כדוגמת שערי מטבע, מידע על מניות או כל מידע אחר שמקורו ברשת? קראו במאמר המצ"ב
איך צוברים מידע ב Power Query
אם יצא לכם להציץ ב Power Query אתם בטח יודעים שאין כמעט בעיה באקסל, שהוא לא יודע לתת לה פתרון. זה אחד השיפורים המשמעותיים ביותר,
צירוף נתונים מטבלאות שונות לטבלה רציפה
כיצד נצרף נתונים מטבלאות שונות באותו הקובץ לטבלה רציפה אחת?
ההסבר המלא במאמר
עמודה מדוגמאות (מילוי מהיר)
אפשרות עוצמתית להוספת עמודות המבוססות על דפוסים, בדומה לשימוש ב'מילוי מהיר' (Flash Fill) באקסל.
סדר העבודה ב Power Query
סדר העבודה ב- Power Query ידידותי ואינטואיטיבי, ומורכב מארבעה שלבים: 1. טעינת הנתונים 2. עריכת חישובים ומניפולציות על הנתונים3. שמירת הנתונים4. ניתוח הנתונים באמצעות טבלאות
אוטומציה של תהליכים באמצעות Power Query
אחד היתרונות הגדולים בשימוש ב Power Query הוא האפשרות ליצור אוטומציה של תהליכים, דבר שעד היום התאפשר רק באמצעות כתיבת קוד ב VBA. ה Power
תיקון תאריכים שמגיעים בקובץ אקסל בפורמט אמריקאי
מודה – תאריכים באקסל הם בלבול מוח לא קטן, תיקון תאריכים – בלבול מוח גדול… בארץ אנו כותבים תאריכים בפורמט dd/mm/yy. בארה"ב הפורמט המקובל הוא
טעינת קבצים מתיקיה
טעינת קבצים מתיקיה Power Query מאפשרת לייבא קבצים רבים בו-זמנית, בתנאי שהם בעלי מבנה זהה. האפשרות הזאת נקראת 'טעינת קבצים מתיקיה' והיא מאפשרת לשמור בתיקיה
Power Query – יצירת טבלה מסוננת על פי קריטריונים – בגיליון אחר
באחת הקבוצות באינטרנט עלתה שאלה של משתמשת שיש לה טבלה ובה בעמודה מסוימת חיווי על הפעולה. היא רצתה ליצור טבלה נוספת שבה יופיעו רק שורות
תיקון תאריכים שמגיעים בפורמט אמריקאי
מודה – תאריכים באקסל הם בלבול מוח לא קטן, תיקון תאריכים – בלבול מוח גדול… בארץ אנו כותבים תאריכים בפורמט dd/mm/yy. בארה"ב הפורמט המקובל הוא
UnPivot – ביטול הגדרת ציר של עמודות
האם אי פעם במהלך עבודתכם קיבלתם או יצרתם טבלאות במבנה הבא? טבלאות מהסוג הזה נוחות מאוד להקלדת נתונים, אך הן אינן טבלאות תקניות, ולכן לא
גרף פיזור (Scatter) מטבלת ציר
גרף פיזור בטבלת ציר? אין חיה כזאת… גרף פיזור משמש להצגת קשרים בין ערכים, באמצעות הצגת נקודה עבור סט של שני ערכים, אם תיצרו טבלת