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

"חכם  מסוגל ללמוד משאלה טיפשית יותר מכפי שטיפש מסוגל ללמוד מתשובה חכמה." ברוס לי

ה POWER QUERY או ה QUERY EDITOR הוא השלב הראשון בניתוח המידע. שלב זה נקרא ETL . 

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

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

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

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

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

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

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

https://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  .

למידע נוסף אפשר להכינס להיקישור הזה : או  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  דרך לשונית "ממקורות אחרים"  לכן לפני זה צריכים להיות מחוברים על החשבון באתר שלהם בקישור הזה https://datamarket.azure.com/browse/data חלק המאגרים בחינם וחלק בתשלום. חייבים להיכנס לאתר ולהוסיף את בסיסי הנותנים שאנו רוצים לחשבון שלנו ורק לאחר מכן ניתן לייבא לתוך POWER QUERY . בפעם הראשונה שתבקשו להוריד מידע מהאתר תתבקשו להזין מפתח (מספר מאוד ארוך שניתן להעתיק ולהדביק מהאתר לאקסל).

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

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

קישור לסבר על יצירת עמודה חדשה מדוגמה באתר מיקרוסופט.  כלי המקביל למילוי מהיר באקסל (Flash Fill ).
טיפ: כמו באקסל ניתן להשתמש בקיצורהמקשים CTRL+E.

חוברת הדרכה לגבי ה POWER QUERY+ POWER PIVOT באנגלית 

שפת M 

כאשר נכנסים לעריכה מתקדמת של השאילתות מגיעים לשפת M. זוהי שפת שאילות מאפשר ליצור חיבור לנתונים ועיצוב בצורה גמישה מאוד, יצירה של פונקציות ועוד.
בעזרת שפת M ניתן לבצע הרבה יותר ממנה שניתן לבצע דרך הממשק של  POWER QUERY.
חשוב לדעת השפה רגישה לאותיות קטנות / גדולות באנגלית כלמור היא CaseSensitive . יש הבדל בין x קטנה לבין X גדולה. 
ניתן ליצור שאילתה חדשה שאילתה ריקה ואז להוסיף את הקוד של M . לדוגמה: 
let
MyTable= #table({"FirsName","Age"},{{"Dani",44},{"Yossi",19}})
in
MyTable

קוד זה יוצר טבלה עם 2 עמודות (שם פרטי וגיל) המכילה 2 שורות עבור דני ויוסי. 
הקוד כולל 4 אובייטים עיקריים : רשימות, רשומות, טבלאות ופונקציות (List, Record, Table, fanction).
שפת M עובדת עם סוגים שונים של מידע Date Type כגון: 

 List, Record, Table, fanction
 Null null
Logical true, false
Number 1, 1.2, 3 #infinity, #nan
Text "Hello World!"
Date #date(2019,5,25)
Time #time(15.10,0)
DateTime #dateime(2019,5,28,15,10,0)
DateTimeZonw #dateime(2019,5,28,15,10,0,-8,0)
Duration #duration(1,13,59,12.34)

1. רשימות LISTS יופיע עם סוגריים מסולסלים לדוגמה {1,2,3,"Hello World"}=
יוצר עמודה בצורה הזאת:

1
2
3
Hello World

2. רשומות RECORDS יופיע עם סוגריים מרוביים לדוגמא [Name="Dani",Age=32]=
קוד זה יוצר רשימה 
Name Dani
Age 32

 3. טבלאות מתחילות עם הסימן סולמית # לדוגמה 

#MyTable(
{"NameColumn1", "AgeColumn2"}, {
{"Dani",32},
{"Yosi",44}
})

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

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

MyFunction=(x,y, optional z) =>
   if z=null then
      x+y
  else
   (x+y)/z

דוגמה לקריאה לפונצקציה: 

להסבר נוסף אודות פונקציות ורשימת פונקציות https://radacad.com/power-query-library-of-functions-shared-keyword 

הוספת שורה הערה או קטע שלם BLOCK הערה

 הדוגמאות נלקחו מהאתר הזה : https://radacad.com/basics-of-m-power-query-formula-language 

לסיכום:

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

דוגמא לקוד M :

למידע נוסף אודות שפת M :

לימוד שפת M - מהאתר של מיקרוסופט 

הסבר בסיסי על שפת M מהאתר של RADACAD

פורום לשאלות בנושא POWER QUERY 

לחומר הדרכה בנושא https://www.poweredsolutions.co/ 

לרכישת ספר מקיף בעברית בנושא POWER QUERY