POWER  QUERY - חומר הדרכה

 

1. חוברת תרגילים ל POWER QUERY.

2. טבלת תלמידים - לתרגול POWER QUERY. 

3. פתרון - קובץ שמתקבל בסיום התרגיל.

4. סרטוני הדרכה בנושא (באנגלית) - ראה בתחתית העמוד.

ניתן לבצע התקנה של התוסף באופיס 2013 PRO PLUS (ניתן לראות את גרסת התוכנה בקובץחשבוןעל מנת לדעת האם יש לכם אפשרות להתקין את התוספים).
לביצוע ההתקנה לחץ בתפריט באקסל על  
קובץ , אפשרויות, תוספות, לבחור תוספות מסוג COM , לסיום 
ברשמיה שנפתחת צריך לבחורצריך POWER QUERY.
למי שאין לא את האפשרות הזאת צריך להוריד ולהתקין בנפרד
.

קישור להורדת POWER QUERY :  

http://bit.ly/pqdownload 

 

http://www.microsoft.com/en-us/download/details.aspx?id=39379

שימו לב להתקין את התוסף לפי הגרסה מתאימה באופיס 32 ביט או 64 ביט (יתן לראות את גרסת התוכנה בתפריט, קובץחשבון).

לאחר סיום ההתקנה אנו נתחיל לעבוד עם POWER QUERY לחיפוש אונליין של מידעלדוגמה : ביצוע חיפוש מתוך התוסף של  population by country יאפשר לנו להוריד מידע מוויקפדיה.

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

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

 

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

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

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

ניתן גם לייבא גם מידע מאתרי אינטרנט. 

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

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

פשוט מעתיקים את ה URL של האתר ומדביקים אותו בתוך PQ  והוא מושך את הנתונים מהאתר.

POWER QUERY זאת הדלת להביא את המידע לאקסל.

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

כלים לצמצום המידע

קודם כל צריך לוודא שהשורה הראשונה מכילה כותרת.

ניתן להסיר עמודות שלא צריך. או לסמן רק את העמודות שצריך לבקש ממנו להסיר את השאר.

הסרת שורות כפולות על ידי סימון העמודות שהם צריכים להיות ייחודיות ואז ללחוץ על הסר כפילויות.

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

כלים לשינוי המידע

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

ניתן לבצע חפש והחלף על מנת להחליף את NULL באפסים.

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

עמודות מותאמות אישית

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

אפשר להוסיף בצורה זאת עמודות סיכום עם פונקציה כמו SUM או חישוב אחר.
אפשר להשתמש בשפת הפונקציות של POWER QUERY  אשר נקראת  שפת M  .


למידע נוסף אפשר להכינס להיקישור הזה : http://bit.ly/xlPQref או  https://support.office.com/en-us/article/Learn-about-Power-Query-formulas-6bc50988-022b-4799-a709-f8aafdee2b2f?CorrelationId=27062ea0-5823-4bbd-b03b-b145b88b02e6&ui=en-US&rs=en-US&ad=US&fromAR=1

הוספה של שאילתה אחת לשנייה APPENDING  או שניתן למזג עמודה אחת ליד השנייה

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

שכפול שאילתה

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

מיזוג שאילתות.

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

המיזוג דומה מאוד לפונקציית ה VLOOKUP באקסל.

התחברות למקור  מידע  באקסל

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

התחברות למקורות מידע מסחריים.

ניתן בעזרת חשבון של מיקרוסופט או חשבון ארגוני באופיס.  לתהחבר למאגר מידע שלהם בשם Windows Azure Marketplace  דרך לשונית "ממקורות אחרים"  לכן לפני זה צריכים להיות מחוברים על החשבון באתר שלהם בקישור הזה http://datamarket.azure.com/browse/data חלק המאגרים בחינם וחלק בתשלום. חייבים להיכנס לאתר ולהוסיף את בסיסי הנותנים שאנו רוצים לחשבון שלנו ורק לאחר מכן ניתן לייבא לתוך POWER QUERY . בפעם הראשונה שתבקשו להוריד מידע מהאתר תתבקשו להזין מפתח (מספר מאוד ארוך שניתן להעתיק ולהדביק מהאתר לאקסל).

פתרון בעיות בשאילתות  בPOWER QUERY

כאשר אנו מנסים להמיר טקסט למספרים על מנת לבצע בהם חישובים נוצרים בעיות בהמרת מספרים שלילים מטקסט למספר.  יתכן ויש רווחים בין הסימן לבין המספר לכן כדי להשתמש בפונקציה TRIM בכפתור TRANSFORM  בעורך. פונקציה שמסירה את הרווחים המיותרים בטקסט.
יתכן ויש סימנים שלא מופיעים שמפריעים לבצע את ההמרה לכן אפשר לבחור ב CLEAN  - לא רואים שינוי ויזואלי אבל הוא מסיר תווים מיותרים. אפשר לבצע חיפוש וחילוף של הסימן מ – (קו מפריד) ל – לסימן מינוס. שנראים אותו הדבר אבל לא מאפשרים לבצע את ההמרה.

 

 


Joomla SEF URLs by Artio