מה מבצע Power Query בתהליך העבודה?
הסבר: זה בדיוק ETL: משיכה מהמקור, טרנספורמציה בעורך, וטעינה — הכול לפני שהנתונים יוצאים מ-Power Query.
Power Query — חדר המכונות
כל טכניקות ה-ETL, אחת-אחת, עם צילום מסך מתוך העורך לכל אחת: טיפוסים, תאריכים, פיצול, עמודה מותנית, Merge ו-Append.
32נושאים בשיעור
40שאלות בוחן
10משימות בתרגיל
21צילומי מסך
פתיחה
איך עובד השיעור
שלושה דברים לדעת לפני שמתחילים, ואז תוכן השיעור, ניווט בין השיעורים וההכנה לתרגול.
01
ניווט בין נושאים
הכפתורים הקודם והבא קבועים בראש המסך. אפשר גם בחצי המקלדת, בהחלקת אצבע בטלפון, או לקפוץ מרשימת הנושאים.
02
צילומי מסך אמיתיים
כמעט לכל טכניקה יש צילום מסך מתוך העורך. הקישו עליו כדי לפתוח בגודל מלא ולראות בדיוק איפה לוחצים.
03
שאלות ותרגול
הכפתור למבחן מוביל לבוחן של 40 שאלות ולתרגיל הבית — הכול על Power Query בלבד.
ארבעת שיעורי הקורס
המסלול המלא. אפשר לעבור לכל שיעור בלחיצה.
הכנה לתרגול
התרגיל המעשי בסוף השיעור. כדאי לפתוח את Power BI Desktop ואת קובץ AdventureWorks כבר עכשיו.
תרגול
AdventureWorks_Sales.xlsx
שבעה גיליונות, 121,253 שורות מכירה ו-397 מוצרים — אותו קובץ משיעור 2, עכשיו מזווית ה-ETL.
פרק I · מה זה Power Query
המנוע שמכין את הנתונים
Power Query הוא המנוע שמבצע ETL: מושך נתונים ממקור (Extract), מנקה ומעצב (Transform), וטוען למודל (Load).
1
כמעט בלי קוד
כל קליק מייצר מאחורי הקלעים שורת קוד בשפת M. עובדים בעכבר, והקוד נכתב לבד.
2
חזרתיות מלאה
מגדירים פעם אחת. בכל Refresh כל הניקוי רץ מחדש אוטומטית על הנתונים החדשים.
3
מאות מקורות
Excel, בסיסי נתונים, אתרים, תיקיות ו-API — והכול מתאחד למקום אחד.
המשל: Power Query הוא המטבח שלפני המסעדה. לא מגישים חומר גלם — קודם מנקים, חותכים ומסדרים, ורק נתונים מבושלים מגיעים לצלחת, כלומר למודל.
פרק I · מה זה Power Query
אימות והתחברות מאובטחת
בחיבור למקור חדש, Power Query מבקש פרטי הזדהות פעם אחת ושומר אותם למקור — לא לקובץ.
| סוג אימות | מתי משתמשים |
|---|---|
| Anonymous | מקורות ציבוריים שלא דורשים הזדהות — אתר פתוח או API ציבורי |
| Windows | הרשאות המשתמש המחובר — רשת ארגונית וקבצים מקומיים |
| Database / Basic | שם משתמש וסיסמה — טיפוסי ל-SQL Server, Oracle ובסיסי נתונים |
| Organizational account | חשבון ארגוני (Microsoft / Google) בחלון התחברות מאובטח, בלי סיסמה בקובץ |
| Web API / Key | מפתח גישה שמודבק בכותרת הבקשה — לשירותים מבוססי API |
⚙
איפה מנהלים
File ← Options ← Data source settings. שם עורכים, מוחקים או מחליפים סיסמה שפגה.
🔒
רמות פרטיות
Private / Organizational / Public — מונעות דליפת מידע רגיש בין מקורות בזמן מיזוג.
⚠
טעות נפוצה
בחירת סוג אימות שגוי = שגיאת התחברות. בדקו מול מנהל המערכת מה המקור דורש.
פרק I · מה זה Power Query
כלל ברזל — קריאה בלבד
Power Query פועל בסביבת קריאה בלבד. הוא מושך עותק של הנתונים ומעצב אותו לדוח — שום פעולה כאן לא נוגעת בנתוני המקור.
✓
המסך הזה בטוח לחלוטין
נמחק עמודות, נסנן שורות או נחליף טקסט — שום שינוי לא ישפיע על קובץ ה-Excel או על שרת ה-SQL. אפשר להתנסות בלי חשש.
↧
רוצים לתקן מהיסוד?
שינוי שאמור להשפיע על כל מערכות הארגון חייב להתבצע במקור עצמו — בבסיס הנתונים או בקובץ, לא כאן.
כלל אצבע: עיצוב לטובת הדוח ← Power Query. תיקון אמת ← המקור.
פרק II · עורך השאילתות
Applied Steps — מכונת הזמן
Power Query עובד כמו מקליט מאקרו חכם: כל קליק נרשם כשלב נפרד. זו ההיסטוריה המלאה של תהליך ה-ETL.
| שלב | מה הוא עשה |
|---|---|
| Source · Navigation | התחברות למקור ומשיכת הטבלה הנכונה |
| Changed Type | המערכת זיהתה ושינתה סוגי נתונים — טקסט, מספר שלם, תאריך |
| Split Column by Delimiter | פיצול עמודה לפי מפריד |
| Changed Type1 | שינוי טיפוס נוסף שנוצר אוטומטית אחרי הפיצול |
| Added Conditional Column | העמודה המותנית שנוספה — השלב המסומן כרגע |
למעלה בפאנל: Properties ← Name — שם השאילתה הוא שם הטבלה כפי שתופיע במודל הנתונים.
פרק II · עורך השאילתות
ארבעה דברים שחובה לדעת על Applied Steps
✖
אין Ctrl+Z
אי אפשר לבטל פעולה בדרך הרגילה. כדי לתקן טעות מוחקים את השלב בלחיצה על ה-✖ שמופיע לצידו.
⚙
עריכת שלב קיים
לחלק מהשלבים יש גלגל שיניים. לחיצה עליו פותחת מחדש את חלון הפעולה — לשנות ערך בלי לעשות הכול מהתחלה.
⏱
מסע בזמן
לחיצה על כל שלב קודם מראה בדיוק איך הנתונים נראו לפני שהפעולות הבאות בוצעו.
🔎
איתור באגים
בזכות מסע הזמן אפשר לנפות תקלות שלב אחר שלב ולזהות היכן בדיוק הנתונים השתבשו.
פרק III · תאריכים וזמן
שינוי פורמט תאריך — Using Locale
מתי: תאריך אמריקאי (MM/DD/YYYY) עלול להתפרש הפוך — יום מול חודש — או לזרוק שגיאה.
| שלב | מה לוחצים |
|---|---|
| 1 | ב-Power BI: Home ← Transform data (פתיחת העורך) |
| 2 | קליק ימני על כותרת עמודת התאריך |
| 3 | Change Type ← Using Locale… |
| 4 | בחלון: Data Type = Date · Locale = English (US) |
| 5 | לחצו OK — נוסף שלב ב-Applied Steps |
דוגמה: עמודת Order Date: הערך 01/07/2017 בלי Locale נקרא 1 ביולי; עם English (US) נקרא נכון 7 בינואר.
פרק III · תאריכים וזמן
חילוץ חלקי תאריך — Extract Date Parts
מתי: לפלח לפי שנה, חודש או רבעון בלי נוסחאות — מחלצים מעמודת תאריך קיימת.
| שלב | מה לוחצים |
|---|---|
| 1 | בחרו את עמודת התאריך (קליק על השם) |
| 2 | לשונית Add Column — כדי לשמור על המקורית |
| 3 | קבוצת Date & Time ← Date |
| 4 | Month ← Name of Month |
| 5 | עמודה חדשה נוצרת |
דוגמה: מ-01/07/2017 ← שם החודש ← July.
פרק IV · מספרים וכסף
פורמט כסף — Fixed Decimal Number
מתי: מקבע ל-4 ספרות עשרוניות — בדיוק לכסף, בלי שגיאות עיגול בחישובים.
| שלב | מה לוחצים |
|---|---|
| 1 | בחרו את עמודת הכסף (למשל Standard Cost) |
| 2 | לחצו על אייקון הטיפוס (ABC / 123) משמאל לשם העמודה |
| 3 | בחרו Fixed Decimal Number |
| 4 | חלופה: Transform ← Data Type ← Fixed Decimal |
| 5 | העמודה נעולה ל-4 ספרות אחרי הנקודה |
דוגמה: Standard Cost = 868.6342 ← Fixed Decimal (כסף מדויק).
פרק IV · מספרים וכסף
עיגול מספרים — Rounding
מתי: לנקות מספרים ארוכים מחישוב ולקבוע כמה ספרות יישארו.
| שלב | מה לוחצים |
|---|---|
| 1 | בחרו את העמודה המספרית |
| 2 | לשונית Transform |
| 3 | קבוצת Number Column ← Rounding ← Round… |
| 4 | הקלידו מספר ספרות: 2 |
| 5 | לחצו OK |
דוגמה: 868.6342 ← עיגול ל-2 ספרות ← 868.63.
פרק V · טקסט וניקוי
פיצול עמודה — שבע שיטות
מתי: לפרק עמודה "דחוסה" למספר עמודות — לפי תו מפריד, מספר תווים, מיקום, או מעבר בין סוגי תווים.
| שיטה | מה היא עושה |
|---|---|
| By Delimiter | פיצול לפי תו נבחר — פסיק, מקף, רווח. השכיחה והשימושית ביותר |
| By Number of Characters | חיתוך כל כמות קבועה של תווים — למקטעים וקודים באורך אחיד |
| By Positions | חיתוך באינדקסים מדויקים (תו 3, תו 8) — למבנה קשיח בלי מפריד |
| By Lowercase to Uppercase | פיצול במעבר מאות קטנה לגדולה — mountainBike ← mountain | Bike |
| By Uppercase to Lowercase | ההפך — מעבר מאות גדולה לקטנה: MACdonald ← MAC | donald |
| By Digit to Non-Digit | פיצול במעבר ממספר לאות: 12345ABC ← 12345 | ABC |
| By Non-Digit to Digit | פיצול במעבר מאות למספר: PO9876 ← PO | 9876 |
דוגמה: SKU = FR-R92B-58 ← By Delimiter "-" ← FR · R92B · 58.
פרק V · טקסט וניקוי
פיצול לפי מפריד — צעד אחר צעד
| שלב | מה לוחצים |
|---|---|
| 1 | בחרו את העמודה (למשל SKU) |
| 2 | Home או Transform ← Split Column |
| 3 | By Delimiter |
| 4 | בחרו Custom והקלידו - |
| 5 | Split at: Each occurrence ← OK |
| מפריד | איפה פוגשים אותו |
|---|---|
| פסיק , | רשימות CSV, "שם משפחה, שם פרטי" |
| נקודתיים : | שעות (14:30:00) או מפתח-ערך (Category: Bikes) |
| שווה = | כתובות URL ופרמטרים: ProductID=5432 |
| נקודה-פסיק ; | ייצוא ממערכות אירופאיות, רשימות מיילים בתא אחד |
| רווח Space | שם פרטי ומשפחה, פירוק משפט למילים |
| טאב Tab | נתונים שהועתקו מקובץ TXT או מאקסל ישן |
| מותאם אישית | כל תו שלא ברשימה: מקף, לוכסן, קו תחתון, או מילה שלמה |
פרק V · טקסט וניקוי
עיצוב טקסט — Format
מתי: ניקוי וסטנדרטיזציה של טקסט. תפריט אחד שמרכז שבע פעולות נפוצות.
| פעולה | מה היא עושה |
|---|---|
| lowercase | אותיות קטנות — לסטנדרטיזציה של כתובות אימייל |
| UPPERCASE | אותיות גדולות — לקודי מדינות ומטבעות (USD, ILS) |
| Capitalize Each Word | אות ראשונה גדולה בכל מילה — לשמות וערים |
| Trim | הסרת רווחי קצה בלבד — מתחילת המחרוזת ומסופה |
| Clean | ניקוי תווים נסתרים שאינם מודפסים (Enter, Tab) — חובה בייבוא מטקסט חופשי |
| Add Prefix | טקסט קבוע לפני הערך: 123 ← ID-123 |
| Add Suffix | טקסט קבוע אחרי הערך: liran ← liran@company.com |
למה Trim קריטי: הערך "Apple " עם רווח בסוף אינו זהה ל-"Apple". במיזוג ובקשרים זה ההבדל בין התאמה לבין שורה יתומה.
פרק V · טקסט וניקוי
החלפת ערכים — Replace Values
מתי: לתקן ערכים שגויים או לאחד כתיבים שונים לאותו ערך.
| שלב | מה לוחצים |
|---|---|
| 1 | קליק ימני על כותרת העמודה |
| 2 | Replace Values… |
| 3 | למעלה: הערך שרוצים להחליף · למטה: הערך החדש |
| 4 | ב-Advanced options אפשר להגביל להתאמה מלאה של התא בלבד |
| 5 | OK |
| מקרה | דוגמה |
|---|---|
| איחוד כתיבים | "USA" · "U.S.A" · "United States" ← ערך אחד |
| ניקוי סימנים | הסרת ₪ או % שנתקעו בעמודה מספרית |
| טיפול בריקים | החלפת null בטקסט "לא ידוע" לפני טעינה למודל |
פרק VI · הוספת עמודות
עמודה מותנית — Conditional Column
מתי: לוגיקת IF…THEN…ELSE בממשק גרפי — ערך חדש לפי תנאי, בלי לכתוב קוד.
| שלב | מה לוחצים |
|---|---|
| 1 | Add Column ← Conditional Column |
| 2 | הקלידו שם: סיווג עסקה |
| 3 | If [Sales Amount] > 5000 Then "גדולה" |
| 4 | Add Clause ← Else If > 1000 Then "בינונית" |
| 5 | Else: "קטנה" ← OK |
פרק VI · הוספת עמודות
עקרון המַפָּל
מתי: סיווג נתונים רציפים לקטגוריות (Bucketing) — וההבנה איך Power Query קורא את התנאים.
≡
ממשק במקום קוד
בונים IF…THEN…ELSE בלחיצות, בלי לכתוב שורת M אחת.
⚠
אין Between
בחלון אין אופרטור "בין לבין". מי שמגיע מ-SQL או מאקסל מחפש אותו — ולא מוצא.
✓
המפל פותר זאת
התנאים נקראים מלמעלה למטה. הראשון שמתקיים קובע, ושורה נבדקת רק אם כל הקודמות לה נכשלו.
!
הסדר הוא הכול
תמיד מהגבוה לנמוך. בסדר הפוך, הערכים הגבוהים "ייתפסו" מוקדם וה"גבוה" לא יתקיים לעולם.
דוגמה: List Price > 70 גבוה · ≥50 בינוני · ≥30 נמוך · אחרת נמוך מאוד.
פרק VI · הוספת עמודות
חוק המפל — צעד אחר צעד
| שלב | התנאי | מה קורה |
|---|---|---|
| 1 | If > 70 ← "מחיר גבוה" | המנוע עובר שורה-שורה; 70.01 ומעלה מקבל "גבוה" ולא בודק את השאר |
| 2 | Else If ≥ 50 ← "מחיר בינוני" | מגיעים לכאן רק אם שלב 1 נכשל — כך נוצר בפועל הטווח 50–70 בלי Between |
| 3 | Else If ≥ 30 ← "מחיר נמוך" | רק רשומות שמתחת ל-50 מגיעות לכאן: התנאי תופס את 30.01–49.99 |
| 4 | Else ← "מחיר נמוך מאוד" | רשת הביטחון: כל מה שנשאר, כולל 0 ושליליים, מקבל אוטומטית את התנית הזו |
דוגמה: List Price = 1431.5 ← מחיר גבוה · 9.5 ← מחיר נמוך מאוד.
פרק VI · הוספת עמודות
עמודת אינדקס — Index Column
מתי: ליצור מזהה רץ ייחודי לכל שורה — שימושי למפתח מלאכותי, למיון יציב או לאיתור כפילויות.
| שלב | מה לוחצים |
|---|---|
| 1 | Add Column ← Index Column |
| 2 | בחרו From 0 או From 1 (או Custom עם קפיצה משלכם) |
| 3 | העמודה נוספת בקצה הטבלה ומספרת את השורות לפי סדרן הנוכחי |
שימו לב: האינדקס נקבע לפי סדר השורות ברגע היצירה. אם תמיינו לפני כן — המספור ישתנה בהתאם.
פרק VII · סינון וטיפוסים
סינון מספרים — Number Filters
מתי: לצמצם שורות לפי חוקיות מספרית — באופן דינמי ששורד עדכוני נתונים.
| אפשרות | מה היא עושה |
|---|---|
| Greater / Less Than | "רצפה" או "תקרה": רק מחירים ≥50, או רק פריטים שזולים מ-20 |
| Equals / Does Not Equal | התאמה מדויקת לערך — למשל סינון החוצה של מחיר 0 |
| Between | כאן Between כן קיים — בכלי הסינון, בניגוד לחלון העמודה המותנית |
| סימון ידני בתיבות | נוח, אבל חוסם ערכים חדשים שייכנסו ברענון הבא |
סימון ידני מול תנאי לוגי: תיבות הסימון מתייחסות לערכים שקיימים עכשיו. תנאי לוגי ימשיך לתפוס גם ערכים חדשים.
דוגמה: Between 30…70 על List Price ← רק טווח המחירים הבינוני.
פרק VII · סינון וטיפוסים
שינוי סוג נתונים — Change Type
מתי: הפעולה החשובה ביותר. מספר שמוגדר כטקסט אי אפשר לסכם או לממוצע.
| שלב | מה לוחצים |
|---|---|
| 1 | לחצו על אייקון הטיפוס (ABC / 123) משמאל לשם העמודה |
| 2 | בחרו Whole Number (או Decimal / Text / Date) |
| 3 | אם נשאלתם — Replace current במקום Add new step |
| 4 | עכשיו אפשר לבצע אגרגציות על העמודה |
דוגמה: Order Quantity עם אייקון ABC (טקסט) ← Whole Number.
פרק VIII · שילוב טבלאות
Merge — פתיחת המיזוג
מתי: המקבילה ל-JOIN או ל-VLOOKUP. מחברים את Sales ל-Product לפי ProductKey.
| שלב | מה לוחצים |
|---|---|
| 1 | עומדים על Sales ← Home ← Merge Queries |
| 2 | נפתח חלון Merge — למעלה כבר מופיעה Sales |
| 3 | בתפריט הנפתח התחתון בוחרים את הטבלה השנייה: Product |
| 4 | שתי הטבלאות מוצגות זו מעל זו — מוכנים להגדיר את המפתח המשותף |
פרק VIII · שילוב טבלאות
Merge — המפתח המשותף
מתי: כאן מגדירים לפי מה הטבלאות מתחברות. בלי זה אין מיזוג.
| שלב | מה לוחצים |
|---|---|
| 1 | קליק על עמודת ProductKey בטבלה העליונה — היא נצבעת |
| 2 | קליק על ProductKey בטבלה התחתונה — נצבעת גם |
| 3 | אימות: למטה מופיע 121253 of 121253 rows matched |
| 4 | Join Kind = Left Outer (ברירת מחדל) ← OK |
שורת ההתאמה היא מד האיכות: 121,253 מתוך 121,253 אומר שכל שורת מכירה מצאה מוצר. מספר נמוך יותר מסמן מפתחות יתומים או רווחים בטקסט.
פרק VIII · שילוב טבלאות
Merge — עמודת Table החדשה
מתי: המיזוג נוצר, אבל הנתונים עדיין מקופלים בתוך עמודה אחת.
| מה קרה | איפה רואים |
|---|---|
| נוצרה עמודה חדשה בשם Product | בסוף הטבלה — בכל תא כתוב Table |
| נוסף שלב | Applied Steps ← Merged Queries |
| הקוד שנכתב מאחורי הקלעים | Table.NestedJoin(...) |
| מה עושים עכשיו | מרחיבים (Expand) כדי לראות את הנתונים — בשלב הבא |
פרק VIII · שילוב טבלאות
Merge — Expand ושליפת העמודות
| שלב | מה לוחצים |
|---|---|
| 1 | קליק על אייקון ה-Expand (⇆) בראש עמודת Product |
| 2 | בוחרים רק את העמודות שצריך — למשל List Price ו-Category |
| 3 | מבטלים את Use original column name as prefix לשמות קצרים |
| 4 | OK |
אל תסמנו הכול. כל עמודה מיותרת נשארת בזיכרון ומכבידה על המודל.
פרק VIII · שילוב טבלאות
Merge — התוצאה הסופית
- העמודה Product הוחלפה בעמודות אמיתיות.
- לכל שורת מכירה יש עכשיו קטגוריה ומחיר — בלי VLOOKUP ידני.
- בכל Refresh המיזוג ירוץ אוטומטית מחדש על הנתונים החדשים.
שאלה שכדאי לשאול: האם באמת צריך את המיזוג? במודל כוכב תקין, קשר בין הטבלאות עושה את אותה עבודה בלי לנפח את טבלת העובדות.
פרק VIII · שילוב טבלאות
Append — שרשור שאילתות
מתי: המקבילה ל-UNION. לשרשר טבלאות עם מבנה זהה לטבלה אחת ארוכה.
| שלב | מה לוחצים |
|---|---|
| 1 | עומדים על השאילתה הראשונה ← Home ← החץ שליד Append Queries |
| 2 | בוחרים Append Queries as New — שומר את המקור נקי ויוצר שאילתה שלישית |
| 3 | בוחרים Two tables או Three or more ואת הטבלה לשרשור |
| 4 | OK — המנוע משרשר אותן זו תחת זו |
דוגמה: Sales_Jan + Sales_Feb (תאריך · פריט · כמות) ← טבלה אחת רציפה. זה התרחיש של איחוד קבצים חודשיים.
פרק VIII · שילוב טבלאות
תעלומת ה-NULL — Append מול SQL
זו השאלה הראשונה של כל מי שמגיע מ-SQL: למה נוצרו עמודות מלאות null?
≡
מקביל ל-UNION ALL, לא ל-UNION
Append מוסיף את כל השורות אחת מתחת לשנייה, בלי להסיר כפילויות.
!
מאחד לפי שם, ורגיש לאותיות
שלא כמו SQL שמחבר לפי מיקום, Power Query מחבר אך ורק לפי שם העמודה — ו-Case-Sensitive. City ו-city ייחשבו לשתי עמודות שונות.
טיפ זהב: לפני Append ודאו ששמות העמודות זהים ב-100%, כולל רישיות ורווחים. לא זהים? בצעו Rename ליישור קו לפני השרשור.
פרק IX · סיכום ובדיקה עצמית
טבלת הטכניקות — הכול במקום אחד
כל מה שעברנו, לפי מיקום בממשק.
| טכניקה | איפה |
|---|---|
| שינוי פורמט תאריך | קליק ימני ← Change Type ← Using Locale |
| חילוץ חלקי תאריך | Add Column ← Date |
| פורמט כסף | אייקון טיפוס / Transform ← Fixed Decimal |
| עיגול מספרים | Transform ← Rounding |
| פיצול עמודה | Home / Transform ← Split Column |
| עיצוב טקסט | Transform ← Format |
| הסרת רווחים | Transform ← Format ← Trim |
| החלפת ערכים | קליק ימני ← Replace Values |
| עמודה מותנית | Add Column ← Conditional Column |
| עמודת אינדקס | Add Column ← Index Column |
| סינון מספרים | חץ הסינון ← Number Filters |
| שינוי סוג נתונים | אייקון הטיפוס |
| מיזוג טבלאות | Home ← Merge Queries |
| שרשור טבלאות | Home ← Append Queries |
פרק IX · סיכום ובדיקה עצמית
מה למדנו — שמונה נקודות
01
ETL לפני המודל
Power Query מושך, מנקה וטוען. מה שמגיע למודל כבר נקי.
02
קריאה בלבד
שום פעולה בעורך לא נוגעת בקובץ המקור או בשרת.
03
Applied Steps
כל קליק הוא שלב. אין Ctrl+Z — מוחקים שלב, עורכים שלב, וחוזרים בזמן.
04
טיפוסים קודם
מספר שנטען כטקסט לא ניתן לסיכום. Change Type הוא הצעד הראשון.
05
Using Locale
תאריך אמריקאי מתפרש הפוך בלי הגדרת אזור.
06
עקרון המפל
אין Between בעמודה מותנית — הסדר מלמעלה למטה יוצר את הטווחים.
07
Merge מול Append
Merge מוסיף עמודות לצד (JOIN), Append מוסיף שורות מלמטה (UNION ALL).
08
שם העמודה קובע
ב-Append ההתאמה לפי שם ורגישה לאותיות; ב-Merge שורת ההתאמה היא מד האיכות.
40 שאלות, כולן על Power Query — בלי מודלינג ובלי DAX
פרק IX · סיכום ובדיקה עצמית
בוחן — 40 שאלות על Power Query
שאלה אחת בכל פעם, ארבע אפשרויות, משוב מיידי עם הסבר. כל השאלות עוסקות בעורך Power Query בלבד.
פעולה שביצעתם בעורך — איך היא משפיעה על קובץ המקור?
הסבר: העורך פועל בסביבת קריאה בלבד. אפשר להתנסות בלי שום חשש לנתוני המקור.
בעורך המתקדם (Advanced Editor) נפתח קוד M. מה מכיל מבנה let … in?
הסבר: בין let ל-in מוגדר שלב אחרי שלב, וכל שלב מקבל שם; אחרי in מציינים איזה שלב הוא הפלט של השאילתה.
מה נוצר מאחורי הקלעים בכל לחיצה בעורך Power Query?
הסבר: העורך הוא מקליט: עובדים בעכבר, והקוד ב-M נכתב לבד וניתן לצפייה ב-Advanced Editor.
מה קורה לשלבי הניקוי בכל Refresh?
הסבר: מגדירים פעם אחת, והתהליך חוזר על עצמו בכל רענון.
רוצים שאילתה נוספת שתמשיך מהשלבים הקיימים, ותתעדכן איתם. מה בוחרים?
הסבר: Duplicate יוצר עותק מנותק; Reference ממשיך מהשאילתה המקורית, וכל שינוי בה זורם גם לשאילתה החדשה.
התחברתם למקור חדש והתבקשתם פרטי הזדהות. איפה הם נשמרים?
הסבר: הפרטים נשמרים ברמת המקור, ומנוהלים ב-File ← Options ← Data source settings.
לאיזה סוג אימות מתאים מקור ציבורי כמו אתר פתוח או API ללא הרשאות?
הסבר: מקור שלא דורש הזדהות מתחבר אנונימית; Power Query שומר את הבחירה להתחברויות הבאות לאותו מקור.
איזה סוג אימות בעורך משתמש בהרשאות המשתמש המחובר למחשב?
הסבר: טיפוסי לתיקיות רשת ארגוניות ולקבצים מקומיים — Power Query מזדהה בשם המשתמש שכבר מחובר.
מה תפקיד רמות הפרטיות (Privacy Levels)?
הסבר: כל מקור מסומן Private, Organizational או Public, וזה משפיע על מה שמותר לשלב.
בעמודה יש ערך אחד ומתחתיו תאים ריקים ששייכים לאותה קבוצה. איך ממלאים אותם?
הסבר: Fill Down מעתיק את הערך האחרון שאינו ריק כלפי מטה — פתרון קלאסי לדוחות שיוצאו מ-Excel עם תאים ממוזגים.
מה נרשם ברשימת Applied Steps?
הסבר: העורך עובד כמו מקליט: כל קליק הופך לשלב נפרד ברשימה.
עשיתם טעות. איך מבטלים אותה בעורך?
הסבר: אין ביטול רגיל. הדרך היחידה היא למחוק את השלב מהרשימה.
רוצים לשנות ערך שהוגדר בשלב קודם, בלי לעשות הכול מחדש. מה עושים?
הסבר: לחלק מהשלבים יש גלגל שיניים שמאפשר לערוך אותם במקום.
בעמודה יש ערכים כפולים ורוצים להשאיר שורה אחת לכל ערך. מה עושים בעורך?
הסבר: הפעולה משאירה את המופע הראשון של כל ערך ומסירה את השאר — לפי העמודות שנבחרו בלבד.
איזה שלב נוצר כמעט תמיד אוטומטית מיד אחרי החיבור למקור?
הסבר: המערכת מנחשת סוגי נתונים לכל עמודה ורושמת זאת כשלב.
מדוע כדאי לבדוק את שלב זיהוי הטיפוסים האוטומטי?
הסבר: זיהוי אוטומטי נוח, אבל הוא ניחוש — ולכן דורש אימות.
עמודה מספרית נטענה כטקסט. מה נחסם בעורך עצמו?
הסבר: הרצועה מציעה פעולות לפי סוג העמודה. כל עוד היא טקסט, כלי המספרים מושבתים.
איפה הכי מהר לשנות סוג נתונים של עמודה?
הסבר: האייקון (ABC / 123) פותח את רשימת כל הטיפוסים.
העמודה מציגה 01/07/2017 והתאריך מתפרש הפוך. מה הפתרון?
הסבר: Locale אומר למנוע לפי איזה מוסכמה לקרוא את התאריך.
השורה הראשונה בטבלה מכילה את שמות העמודות, אך נטענה כשורת נתונים. מה עושים?
הסבר: הפעולה מקדמת את השורה הראשונה לכותרות ומוסיפה שלב ל-Applied Steps. יש גם פעולה הפוכה — Use Headers as First Row.
רוצים עמודה עם שם החודש מתוך עמודת תאריך קיימת. מה הדרך הנכונה?
הסבר: עובדים ב-Add Column כדי לשמור על עמודת המקור.
למה מעדיפים Add Column על פני Transform בחילוץ חלקי תאריך?
הסבר: לרוב רוצים לשמור את התאריך המלא לצד חלקיו.
איזה סוג נתונים מתאים לעמודת כסף שדורשת דיוק בחישובים?
הסבר: הוא מקבע ארבע ספרות אחרי הנקודה ומונע שגיאות עיגול מצטברות.
רוצים לקצר מספרים ארוכים לשתי ספרות אחרי הנקודה. מה משתמשים?
הסבר: Round מקבל את מספר הספרות הרצוי; יש גם Round Up ו-Round Down.
עמודת SKU מכילה FR-R92B-58 ורוצים לפרק אותה לשלוש עמודות. מה הדרך?
הסבר: זו שיטת הפיצול השכיחה: בוחרים את התו המפריד ומפצלים בכל הופעה שלו.
הערך mountainBike אמור להתפצל ל-mountain ו-Bike. איזו שיטה?
הסבר: הפיצול מתבצע בנקודת המעבר מאות קטנה לאות גדולה.
בפיצול עמודה: הערך PO9876 אמור להתפצל ל-PO ול-9876. איזו שיטת Split Column מתאימה?
הסבר: נקודת הפיצול היא המעבר מאות לספרה, ולכן Non-Digit to Digit — בלי לציין מפריד או מיקום.
לאיזו מטרה משמשת הפעולה Trim?
הסבר: הערך "Apple " עם רווח בסוף אינו זהה ל-"Apple" — וזה שובר התאמות במיזוג.
בייבוא מטקסט חופשי נכנסו תווי Enter ו-Tab נסתרים. מה מנקה אותם?
הסבר: Clean מסיר תווים שאינם מודפסים, בעוד Trim מטפל ברווחי קצה בלבד.
רוצים לאחד "USA", "U.S.A" ו-"United States" לערך אחד. מה הכלי?
הסבר: מחליפים כל כתיב בערך אחיד, לפני שהנתונים מגיעים למודל.
מה מאפשרת עמודה מותנית?
הסבר: ערך חדש נקבע לפי תנאי, בלי לכתוב שורת קוד.
מחפשים אופרטור "בין לבין" בחלון העמודה המותנית ולא מוצאים. מה נכון?
הסבר: התנאים נקראים מלמעלה למטה, וכל שורה נבדקת רק אם הקודמות נכשלו — כך נוצר טווח.
בעמודה מותנית בעורך — באיזה סדר יש לכתוב את שורות התנאי?
הסבר: התנאים נבדקים מלמעלה למטה, ולכן בסדר הפוך התנאי הנמוך היה תופס הכול והתנאי הגבוה לא היה מתקיים לעולם.
מה תפקיד שורת ה-Else בסוף העמודה המותנית?
הסבר: בלי Else השורות שלא התאימו יקבלו ערך ריק.
איפה כן קיים Between?
הסבר: Number Filters כולל Between — בניגוד לעמודה המותנית.
מה עושה Merge Queries?
הסבר: זו המקבילה ל-VLOOKUP או JOIN: הנתונים מגיעים לצד, כעמודות נוספות.
בחלון Merge מופיעה השורה "121253 of 121253 rows matched". מה היא אומרת?
הסבר: זו בדיקת האיכות החשובה ביותר במיזוג; מספר נמוך יותר מסמן מפתחות יתומים.
אחרי Merge נוצרה עמודה שבכל תא בה כתוב Table. מה עושים?
הסבר: הנתונים מקוננים בתוך העמודה עד להרחבה, ומומלץ להביא רק את העמודות שבאמת צריך.
שרשרתם שתי טבלאות ב-Append ונוצרו עמודות מלאות null. למה?
הסבר: שלא כמו SQL שמחבר לפי מיקום, כאן City ו-city הן שתי עמודות שונות. Append מקביל ל-UNION ALL ואינו מסיר כפילויות.
טרם ענית על שאלות — 0 מתוך 40
עכשיו לעורך עצמו, על קובץ AdventureWorks
פרק X · תרגול מעשי
תרגיל בית — 10 משימות עם פתרונות
כל המשימות בעורך Power Query על AdventureWorks. אחרי כל משימה יש פתרון מלא עם המספר שאמור להתקבל.
לפני שמתחילים
AdventureWorks_Sales.xlsx
Home ← Transform data פותח את העורך. כל מה שנעשה כאן לא נוגע בקובץ המקור.
מעקב התקדמות
0 מתוך 5
1 פתיחת העורך ובדיקת טיפוסים
Home ← Transform data. עברו על שבע השאילתות ובדקו את אייקון הטיפוס של כל עמודה.
| שלב | מה עושים |
|---|---|
| 1 | בחרו את Sales ובדקו את Order Quantity — אמור להיות מספר שלם |
| 2 | בדקו את Sales Amount — אמור להיות עשרוני |
| 3 | סמנו לעצמכם כל עמודה שסומנה ABC ואמורה להיות מספר |
| טבלה | שורות |
|---|---|
| Sales | 121,253 |
| Product | 397 |
| Customer | 18,485 |
| Date | 1,461 |
| Reseller | 702 |
| Sales Territory | 11 |
| Sales Order | 121,253 |
✓ ספירת השורות בתחתית העורך חייבת להתאים לטבלה. אם לא — נוסף שלב סינון בטעות.
2 תיקון פורמט תאריך
בטבלת Date ודאו שעמודת התאריך נקראת נכון.
| שלב | מה עושים |
|---|---|
| 1 | קליק ימני על כותרת עמודת התאריך |
| 2 | Change Type ← Using Locale… |
| 3 | Data Type = Date · Locale = English (US) ← OK |
✓ טווח התאריכים אמור להיות 1.7.2017 עד 30.6.2021 — 1,461 ימים רצופים.
3 חילוץ שם החודש
הוסיפו עמודה עם שם החודש מתוך עמודת התאריך.
| שלב | מה עושים |
|---|---|
| 1 | בחרו את עמודת התאריך |
| 2 | Add Column ← Date ← Month ← Name of Month |
✓ נוצרת עמודה חדשה עם January עד December, בלי לפגוע בעמודת המקור.
4 פורמט כסף ועיגול
בטבלת Product: קבעו את Standard Cost ככסף, ועגלו את List Price.
| שלב | מה עושים |
|---|---|
| 1 | אייקון הטיפוס של Standard Cost ← Fixed Decimal Number |
| 2 | בחרו את List Price ← Transform ← Rounding ← Round… ← 2 ← OK |
✓ הערך 868.6342 הופך ל-868.63 אחרי עיגול לשתי ספרות.
5 פיצול עמודת SKU
בטבלת Product פצלו את SKU לשלושה חלקים.
| שלב | מה עושים |
|---|---|
| 1 | בחרו את עמודת SKU |
| 2 | Transform ← Split Column ← By Delimiter |
| 3 | Custom ← הקלידו - ← Split at: Each occurrence ← OK |
✓ FR-R92B-58 מתפצל לשלוש עמודות: FR · R92B · 58. שימו לב שנוסף אוטומטית שלב Changed Type.
6 ניקוי טקסט
ודאו שאין רווחי קצה בעמודות הטקסט של Product.
| שלב | מה עושים |
|---|---|
| 1 | בחרו את עמודת Product (שם המוצר) |
| 2 | Transform ← Format ← Trim |
| 3 | חזרו על כך גם עבור Category |
✓ Trim מסיר רווחים מתחילת הערך ומסופו בלבד. אם יש תווים נסתרים — הוסיפו Clean.
7 עמודה מותנית — סיווג מחיר
צרו עמודה שמסווגת את List Price לארבע רמות.
| שלב | מה עושים |
|---|---|
| 1 | Add Column ← Conditional Column · שם: סיווג מחיר |
| 2 | If List Price > 70 Then "מחיר גבוה" |
| 3 | Else If ≥ 50 Then "מחיר בינוני" · Else If ≥ 30 Then "מחיר נמוך" |
| 4 | Else ← "מחיר נמוך מאוד" ← OK |
✓ סדר יורד הוא קריטי: 1431.5 מקבל "גבוה", 9.5 מקבל "נמוך מאוד". בסדר הפוך הכול היה נופל לקטגוריה אחת.
8 מיזוג Sales עם Product
הביאו לכל שורת מכירה את הקטגוריה והמחיר של המוצר.
| שלב | מה עושים |
|---|---|
| 1 | עומדים על Sales ← Home ← Merge Queries |
| 2 | בוחרים Product בתפריט התחתון |
| 3 | קליק על ProductKey בשתי הטבלאות |
| 4 | בודקים את שורת ההתאמה ← Join Kind = Left Outer ← OK |
| בדיקה | תוצאה |
|---|---|
| שורות שהותאמו | 121,253 מתוך 121,253 |
| עמודה שנוצרה | Product — בכל תא כתוב Table |
| שלב שנוסף | Merged Queries |
✓ התאמה מלאה אומרת שאין מפתחות יתומים. מספר נמוך יותר היה מסמן בעיה בנתונים.
9 הרחבת המיזוג
שלפו רק את שתי העמודות שצריך.
| שלב | מה עושים |
|---|---|
| 1 | קליק על אייקון ה-Expand בראש עמודת Product |
| 2 | סמנו רק List Price ו-Category |
| 3 | בטלו את Use original column name as prefix ← OK |
✓ עכשיו לכל שורת מכירה יש קטגוריה ומחיר — בלי VLOOKUP ידני, ובכל רענון זה ירוץ מחדש.
10 ניסוי Append
שרשרו שתי טבלאות ובדקו מה קורה כששמות העמודות לא זהים.
| שלב | מה עושים |
|---|---|
| 1 | שכפלו את Reseller (קליק ימני ← Duplicate) ושנו בעותק שם עמודה אחת לאותיות קטנות |
| 2 | עמדו על המקור ← Home ← Append Queries ← Append Queries as New |
| 3 | בחרו את העותק ← OK |
✓ העמודה שהשם שלה שונה תיווצר פעמיים, וכל אחת תתמלא null בחצי מהשורות. זו ההוכחה שההתאמה לפי שם ורגישה לאותיות.
פרק XI · מה הלאה
משאבים והמשך הדרך
להעמיק ב-Power Query
אפשר לנווט גם בכפתורים שבראש המסך, בחצי המקלדת, ובטלפון בהחלקת אצבע