Power Query: ייבוא והמרת נתונים כמו מקצוענים

אם אתם מבלים שעות בכל שבוע בהורדת קובצי CSV, מחיקת שורות ריקות, עיצוב תאריכים וכתיבת נוסחאות מקוננות ומורכבות רק כדי להכין את הנתונים שלכם לניתוח, אתם עובדים קשה מהדרוש. הכירו את Power Query — הכלי העוצמתי ביותר לאוטומציית נתונים המובנה ישירות בתוך Microsoft Excel.

Power Query, שלעתים קרובות נקרא "קבל והמר נתונים" (Get & Transform Data), מאפשר לכם להתחבר כמעט לכל מקור נתונים, לנקות ולעצב מחדש את המידע, ולטעון אותו לגיליון האלקטרוני שלכם. והחלק הטוב ביותר? הוא מקליט את השלבים שלכם. בפעם הבאה שתקבלו נתונים חדשים, לא תצטרכו לחזור על העבודה הידנית; פשוט תלחצו על רענן (Refresh).

במדריך מקיף זה, נחקור מהו Power Query, כיצד לנווט בממשק שלו, ונעבור על דוגמה מעשית של המרת סט נתונים מבולגן למידע נקי ומוכן לניתוח.

מה זה Power Query?

Power Query הוא מנוע לחיבור והכנת נתונים. בעולם ניהול מסדי הנתונים, תהליך זה ידוע כ-ETL: חילוץ, המרה וטעינה (Extract, Transform, Load).

• חילוץ (Extract): משיכת נתונים ממקורות שונים (חוברות עבודה של Excel, קובצי CSV וטקסט, דפי אינטרנט, מסדי נתונים מסוג SQL, ואפילו שירותי ענן).
• המרה (Transform): ניקוי ועיצוב מחדש של הנתונים. פעולה זו כוללת סינון שורות, שינוי סוגי נתונים, פיצול טקסט, מיזוג טבלאות ויצירת עמודות מחושבות.
• טעינה (Load): פלט של הנתונים המלוטשים ישירות אל טבלת Excel, אל PivotTable או אל מודל הנתונים של Power Pivot.

באופן מסורתי, משתמשי Excel הסתמכו על שילוב של פונקציות כמו TRIM, PROPER, SUBSTITUTE ו-VLOOKUP יחד עם העתקה והדבקה ידניות כדי להתמודד עם משימות אלו. Power Query מחליף את תזרים העבודה המייגע הזה בממשק ויזואלי וידידותי למשתמש.

למה כדאי לכם ללמוד Power Query

אם אתם עדיין מתלבטים אם ללמוד כלי חדש ב-Excel, הנה הסיבות לכך ששליטה ב-Power Query תשנה את כללי המשחק עבור הפרודוקטיביות שלכם:

• אין צורך בכתיבת קוד: למרות ש-Power Query פועל על שפת נוסחאות הנקראת "M", תוכלו לבצע 95% מהמרות הנתונים רק באמצעות כפתורי רצועת הכלים ותפריטי קליק ימני.
• אוטומציה ללא VBA: אינכם צריכים לדעת איך לכתוב מאקרו כדי לבצע אוטומציה של משימות חוזרות. Power Query זוכר את "השלבים שהוחלו" (Applied Steps) שלכם ומחיל אותם על נתונים חדשים באופן אוטומטי.
• טיפול בביג דאטה: ל-Excel יש מגבלה פיזית של 1,048,576 שורות. Power Query יכול להתחבר למערכי נתונים עם מיליוני שורות, לקבץ את הנתונים ברקע, ולטעון רק את התוצאות המסוכמות לתוך הגיליון האלקטרוני שלכם.
• עריכה לא הרסנית: Power Query לעולם לא משנה את מקור הנתונים המקורי שלכם. הוא יוצר חיבור, קורא את הנתונים, ומפיק עותק נקי וחדש.

מתחילים: עורך ה-Power Query

כדי לגשת אל Power Query, פתחו חוברת עבודה ריקה ב-Excel ונווטו אל כרטיסיית נתונים (Data) ברצועת הכלים. חפשו את קבוצת קבל והמר נתונים (Get & Transform Data).

מכאן, תוכלו ללחוץ על קבל נתונים (Get Data) כדי לראות תפריט נפתח של מקורות נתונים זמינים. ברגע שתבחרו קובץ ותלחצו על "המר נתונים" (Transform Data), יפתח עורך Power Query בחלון חדש. ממשק זה מורכב מארבעה אזורים עיקריים:

1. רצועת הכלים: ממוקמת בחלק העליון, ומכילה כרטיסיות כמו בית (Home), המרה (Transform), הוספת עמודה (Add Column) ותצוגה (View). כאן נמצאים כל הכלים שלכם.
2. חלונית השאילתות (Queries Pane): ממוקמת בצד, ומציגה רשימה של כל חיבורי הנתונים הפעילים בחוברת העבודה שלכם.
3. תצוגה מקדימה של הנתונים (Data Preview): הרשת המרכזית המציגה תצוגה מקדימה חיה של איך נראים הנתונים שלכם בשלב הנבחר הנוכחי.
4. חלונית הגדרות השאילתה (שלבים שהוחלו): ממוקמת בצד השני. זהו החלק החשוב ביותר בעורך. בכל פעם שאתם משנים משהו, Power Query מתעד זאת כאן כשלב עוקב. ניתן למחוק, לשנות את הסדר או לערוך את השלבים הללו בכל עת.

מדריך צעד-אחר-צעד: ניקוי נתוני מכירות מבולגנים

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

הנה דוגמה לנתונים הגולמיים והמבולגנים שלנו:

ייצוא מערכת: דוח מכירות רבעון 3	Column2	Column3
הופק בתאריך: 10/01/2023
Rep_ID_Name	Order_Date	Revenue
101-John Doe	2023-08-15	1500.5
102-Jane Smith	09/01/2023	$2,340.00
103-Bob_Jones	23-Sep-2023	850.75

אם היינו משתמשים בנוסחאות מסורתיות, היינו צריכים להשתמש ב-LEFT, RIGHT, FIND ו-VALUE כדי לחלץ את שמות נציגי המכירות ולתקן את המספרים. בואו נשתמש ב-Power Query במקום זאת.

שלב 1: ייבוא הנתונים

שמרו את הנתונים המבולגנים כקובץ CSV או Excel. פתחו חוברת עבודה חדשה של Excel, היכנסו אל נתונים > קבל נתונים > מקובץ (Data > Get Data > From File), ובחרו את הקובץ שלכם. כאשר מופיע חלון התצוגה המקדימה, לחצו על המר נתונים (Transform Data). עורך ה-Power Query יפתח.

שלב 2: הסרת שורות עליונות וקידום כותרות

שתי השורות הראשונות של הנתונים שלנו הן מטא-דאטה של ייצוא המערכת, ולא רשומות נתונים בפועל. אנחנו צריכים להיפטר מהן.

• בכרטיסייה בית (Home), לחצו על הסר שורות > הסר שורות עליונות (Remove Rows > Remove Top Rows). הזינו "2" ולחצו על אישור (OK).
• כעת, כותרות העמודות האמיתיות שלנו יושבות בשורה 1. לחצו על הכפתור השתמש בשורה הראשונה ככותרות (Use First Row as Headers) בכרטיסייה בית. באופן מיידי, "Rep_ID_Name", "Order_Date" ו-"Revenue" הופכות לכותרות העמודות הרשמיות שלכם.

שלב 3: פיצול עמודת הנציג

העמודה "Rep_ID_Name" מכילה גם את מספר הזיהוי וגם את שם העובד מופרדים במקף.

• לחצו קליק ימני על כותרת העמודה "Rep_ID_Name".
• בחרו ב-פצל עמודה > לפי תוחם (Split Column > By Delimiter).
• בחרו "מותאם אישית" (Custom) והקלידו מקף ( - ). לחצו על אישור (OK).
• Power Query יפצל את הנתונים לשתי עמודות. כעת תוכלו ללחוץ פעמיים על כותרות העמודות החדשות כדי לשנות את שמן ל-"Rep_ID" ול-"Rep_Name".

כדי לנקות את הקווים התחתונים בשם של בוב (Bob_Jones), לחצו קליק ימני על עמודת Rep_Name, בחרו ב-החלף ערכים (Replace Values), הקלידו קו תחתון (_) בתיבה "ערך למציאה" (Value to Find), והשאירו את "החלף ב-" (Replace With) ריק או הוסיפו רווח. לחצו על אישור (OK).

שלב 4: תקינה של סוגי נתונים

שמים לב כיצד התאריכים וההכנסות שלנו נמצאים בפורמטים שונים לחלוטין? Power Query הופך את התקינה של זה לקלה במיוחד.

• לחצו על הסמל הקטן בצד כותרת העמודה "Order_Date" (כנראה כתוב שם "ABC" או "Any"). שנו אותו ל-תאריך (Date). Power Query יתרגם באופן אוטומטי את פורמטי התאריכים השונים לתאריך סטנדרטי ואחיד.
• לחצו על הסמל שליד "Revenue" ושנו אותו ל-מטבע (Currency) או מספר עשרוני. הוא יסיר את סימני הדולר ויעצב הכל בצורה נקייה.

שלב 5: הוספת עמודה מותנית

נניח שאנו רוצים לסווג מכירות מעל $1,000 כ-"High Value". במקום לכתוב פונקציית IF מורכבת כמו =IF(C2>=1000, "High Value", "Standard") ב-Excel, נוכל להשתמש בממשק של Power Query.

עברו אל הכרטיסייה הוסף עמודה (Add Column) ולחצו על עמודה מותנית (Conditional Column). הגדירו את הכללים: אם [Revenue] גדול או שווה ל-1000, הפלט יהיה "High Value", אחרת "Standard". מאחורי הקלעים, Power Query ייצור את קוד ה-M הבא עבור שלב זה:

= Table.AddColumn(#"Changed Type", "Sales Category", each if [Revenue] >= 1000 then "High Value" else "Standard")

מיזוג שאילתות: החלופה המודרנית ל-VLOOKUP

אחת המשימות הנפוצות ביותר בניתוח נתונים היא שילוב טבלאות. אם יש לכם טבלה נפרדת המכילה את האזור של כל נציג מכירות, בדרך כלל הייתם פונים אל המדריך המלא שלנו ל-VLOOKUP כדי למשוך את הנתונים הללו.

עם זאת, הרצת אלפי נוסחאות VLOOKUP או INDEX ו-MATCH עלולה להאט באופן דרסטי את חוברת העבודה שלכם. ב-Power Query, תוכלו להשתמש בתכונת מיזוג שאילתות (Merge Queries).

פשוט ייבאו את שתי הטבלאות אל Power Query, בחרו את טבלת המכירות הראשית שלכם, ולחצו על מיזוג שאילתות (Merge Queries) בכרטיסייה בית. בחרו את הטבלה השנייה (טבלת האזורים), לחצו על העמודה התואמת בשתי הטבלאות (לדוגמה, "Rep_ID"), ולחצו על אישור (OK). Power Query מבצע את המקבילה ל-VLOOKUP במהירות שיא תוך שניות, לא משנה אם יש לכם עשר שורות או עשרה מיליון.

ביטול ציר (Unpivot) לנתונים: הנשק הסודי של Power Query

לעיתים קרובות, אתם מקבלים נתונים שכבר מקובצים למבנה דמוי טבלת ציר (לדוגמה, חודשים הנפרסים לאורך העמודות: ינואר, פברואר, מרץ, אפריל). בעוד שזה קל לקריאה עבור בני אדם, זה נורא ליצירת תרשימים או PivotTables.

בחרו את עמודות המזהה שלכם (כמו שם הנציג), לחצו קליק ימני על הכותרת, ובחרו ב-בטל ציר לעמודות אחרות (Unpivot Other Columns). Power Query הופך באופן מיידי את הנתונים הרחבים והמוצלבים שלכם לפריסה טבלאית שטוחה עם עמודה חדשה של "תכונה" (חודש) ו-"ערך" (מכירות). ביצוע פעולה זו עם נוסחאות Excel סטנדרטיות הוא כמעט בלתי אפשרי, מה שהופך את תכונת ה-Unpivot לאחת מהמפורסמות ביותר של Power Query.

טעינת הנתונים שהומרו

לאחר שהנתונים שלכם נקיים לחלוטין, הגיע הזמן לשלוח אותם בחזרה ל-Excel.

בכרטיסייה בית, לחצו על סגור וטען (Close & Load). כברירת מחדל, זה יטען את הנתונים שהומרו לתוך טבלת Excel ירוקה וחדשה בגיליון עבודה חדש. אם אתם מעדיפים לשלוח את הנתונים ישירות לשלב הניתוח שלכם, תוכלו ללחוץ על חץ התפריט הנפתח, לבחור ב-סגור וטען אל... (...Close & Load To), ולבחור בדוח PivotTable במקום. אם אתם זקוקים לרענון בבניית הסיכומים הללו, בדקו את המדריך שלנו על יצירת טבלאות ציר למתחילים.

הקסם של כפתור ה-"רענן"

הכוח האמיתי של Power Query מתברר בשבוע הבא, כאשר תקבלו ייצוא חדש של נתוני מכירות גולמיים. אין צורך לחזור על השלבים שלמעלה!

פשוט שמרו את קובץ ה-CSV החדש על גבי הישן (הקפידו על אותו שם קובץ ומיקום תיקייה בדיוק). לאחר מכן, פתחו את חוברת העבודה שלכם ב-Excel, לחצו קליק ימני בכל מקום בתוך טבלת הנתונים הנקיים שלכם, ולחצו על רענן (Refresh).

Power Query ניגש אל הקובץ, מחיל מחדש כל שלב ושלב — הסרת שורות, קידום כותרות, פיצול עמודות, החלפת טקסט, בדיקת תנאים ומיזוג טבלאות — ומעדכן את הפלט הסופי שלכם בשבריר שנייה. זהו רכיב חיוני בתהליכי העבודה של אוטומציה ב-Excel.

לפשט את מסע הנתונים שלכם עם AI

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

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

שאלות נפוצות

האם Power Query משנה את קובץ הנתונים המקורי שלי?

לא. Power Query יוצר חיבור חד-כיווני אל נתוני המקור שלכם. הוא קורא את הנתונים, מחיל את ההמרות בזיכרון, ומפיק תוצאה חדשה ב-Excel. קובץ ה-CSV, מסד הנתונים או חוברת העבודה המקוריים שלכם נשארים בטוחים וללא כל שינוי.

האם אני יכול להשתמש ב-Power Query ב-Mac?

כן, Microsoft שיפרה משמעותית את התמיכה של Power Query ב-Excel עבור Mac. בעוד שבגרסת ה-Mac באופן מסורתי היו חסרים חלק מהמחברים ומתכונות הממשק המתקדמות שזמינות ב-Windows, כיום ניתן להתחבר לקבצים מקומיים, למסדי נתונים, ולרענן שאילתות קיימות בצורה חלקה בגרסאות המודרניות של Microsoft 365.

מה ההבדל בין מיזוג (Merge) להוספה (Append) ב-Power Query?

מיזוג (Merge) הוא המקבילה של VLOOKUP או INDEX/MATCH. משתמשים בו כדי להוסיף עמודות נתונים חדשות על ידי התאמת מזהה משותף בין שתי טבלאות. הוספה (Append) דומה להעתקה והדבקה של נתונים בתחתית הגיליון. משתמשים בזה כדי לערום טבלאות זו על גבי זו, תוך הוספת שורות חדשות (לדוגמה, שילוב מכירות של ינואר ומכירות של פברואר).

למה רענון ה-Power Query שלי נכשל?

הסיבה הנפוצה ביותר לכישלון רענון שאילתה היא שקובץ המקור הועבר, שמו שונה, או שהוא נמחק. בעיה נפוצה נוספת היא שכותרת עמודה בנתונים הגולמיים השתנתה (לדוגמה, המערכת שינתה את "Revenue" ל-"Total Revenue"). תוכלו לתקן זאת על ידי פתיחת עורך ה-Power Query, מעבר אל חלונית "שלבים שהוחלו" (Applied Steps), ועדכון שלב המקור (Source) או שינוי שם העמודה בלוגיקת השלבים שלכם.